I want to copy all data from a MyISAM table with 16 millions rows to a InnoDB table, that will have a different (optimized) schema. See tables below to see the changes.
However, every approach I try leads to a slow result.
#1 INSERT
Using a simple INSERT with VALUES from a SELECT:
INSERT INTO new_table (field1,field2,...) SELECT * FROM old_table; Really slow and it locks the original table, which I can't have locked for much longer.
#2 IMPORT FROM SQL FILE
Creating the SQL file takes minutes, but ok. Importing the file is really slow.
#3 LOAD DATA
I dump all the data to a comma separate file, which takes about 30 seconds (quite good), and then I load it with LOAD DATA INFILE. But still very slow.
Why not drop unique keys before loading the data?
Yes, it loads faster. However, when I try to add the unique keys I get this:
Lock wait timeout exceeded; try restarting transaction
Let's split the into several files?
I tried that. Each file contained 100,000 rows.
- File 1 = 2.76 sec
- File 2 = 1 min 40 sec
- File 3 = 4 min 16 sec
I gave up on file 3. I'd have to do it 157 more times and on each iteration the processing time would increase. Too much time...
What is 'slow'
When I say it is slow, I mean, with unique keys off, it takes about 10 seconds to load 500,000 rows. No bad. But then I can't add unique keys because of the timeout.
With unique keys previously set, it takes almost 1 minute to insert 100,000 rows. And the throughput decreases the more rows is inserted.
The tables
MyISAM - Original table
+----------------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+-----------------------+------+-----+---------+-------+ | player_id | int(11) | NO | PRI | NULL | | | date | date | NO | PRI | NULL | | | time | int(2) | NO | PRI | NULL | | | minutes_online | decimal(5,0) | NO | | 0 | | | minutes_playing | decimal(5,0) | NO | | 0 | | | minutes_chatting | decimal(5,0) | NO | | 0 | | | minutes_away | decimal(5,0) | NO | | 0 | | +----------------------+-----------------------+------+-----+---------+-------+ InnoDB - New table
+----------------------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+-----------------------+------+-----+---------+-------+ | player_id | mediumint(8) unsigned | NO | PRI | NULL | | | date | date | NO | PRI | NULL | | | time | tinyint(2) | NO | PRI | NULL | | | minutes_online | tinyint(2) | NO | | 0 | | | minutes_playing | tinyint(2) | NO | | 0 | | | minutes_chatting | tinyint(2) | NO | | 0 | | | minutes_away | tinyint(2) | NO | | 0 | | +----------------------+-----------------------+------+-----+---------+-------+ Am I stuck on this? Meaning, will I have to accept the fact that it is slow and be patient? Or is there any way or tweak to load the data with an acceptable performance?
Solution I split into small CSV files, applied the tweaks that Shlomi Noach suggested and then loaded each file.
Not the perfect. But it loaded a bit faster. Dumping the original table to a CSV file took less than 1 minute, which avoided the table from being locked during the whole process. A script did the job to load each file, which would be very boring to do manually.
In the end I just had to get from the original table the rows inserted/updated in the last hours and update on the new table.
