How to Improve the replication efficiency between large tables in mysql

How to Improve the replication efficiency between large tables in mysql if there is currently a log summary table logs_a, about million logs are generated in a week, partitioned by day, now we want to copy the data in this table to another table, logs_ B, or by talent. The two tables have different structures. We use the following method for copying:

1Insert into logs_b(…,…,…)2Select …,…,… from logs_a3Where log_date >= to_date(‘20120229’, ‘yyyymmdd’);

 

Now the problem is that this SQL statement can be terminated even if it takes N years. Is there any good way to replicate table data quickly?

Method 1: import data by PAGE, such as Insert into logs_ B (...,...,...) Select ...,...,... From logs_a Where log_date> = to_date ('201312', 'yyyymmdd') limit 20120229; Method 2: select into outfile first and then load data infileselect a, B, c from oldtable into outfile 'a.txt '; load data INFILE 'a.txt' into table newtable (a, B, c );

 

When a table is loaded from a text file, load data infile is used. This is usually dozens of times faster than using many insert statements. Load data infile does not select data first. Before load data infile, you can use myisamchk -- keys-used = 0-rq/path/to/db/tbl_name to remove all indexes from the table. After the import, use myisamchk-r-q/path/to/db/tbl_name to re-create the index. This will create an index tree in memory before writing data to the disk, and it is faster, because it avoids a large number of disk searches. Result index tree is also perfectly balanced. Method 3: Try to use insert into table_name values (...) in myisam engine (...), (.....), (.....) insert data in this way to avoid using inset into table_name values (); 2. Add bulk_insert_buffer_size (8 MB by default) 3. If a non-empty table is created, use alter table table_name disable keys, load data infile, and run alter table table_name enable keys after the data is imported. this operation is not required if the table is empty, because the myisam Table imports data in the empty table first and then establishes indexs. 4. When inserting data, consider using insert delayed... in this way, mysql puts the insert operation into the queue for concentrated insertion, which is faster. Using load data infile is nearly 20 times faster than using insert. Use this operation whenever possible. Under innodb engine

1. before importing data, run set unique_checks = 0 to disable the check of unique indexes. After the data is imported, run set unique_checks = 1.2. run set foreign_key_checks = 0 to disable foreign key check before importing data. After the data is imported, run set foreign_key_checks = 1. 3. before importing data, run set autocommit = 0 to disable automatic commit of Automatic transactions. After the data is imported, run set autocommit = 1 to restore automatic commit.

 

For tables using the innodb engine, physical storage is stored in PK order. Disable keys similar to myisam cannot be used. improving the disk I/0 on the hardware is very helpful for the insertion speed (so if you want to import and export large amounts of data, try to compare NB hardware to reduce the completion time, prevents problems)