I have several thousand InnoDB tables in MySQL 5.6 that I am trying to merge together into a single table. A simplified form of the queries I am executing (one for each of the tables to merge) is
INSERT INTO `merged_table` (table_id, field1, field2) SELECT 1 AS table_id, field1, field2 FROM `tableToMerge`
The merged table has a combined primary key of an auto incrementing id, and the table_id, whereby the table_id is being used for KEY() based partitioning. For this migration, there are no other indexes in the merged table.
Unfortunately, this takes very a long time and results in a table with over 7 million rows. I would like to know which MySql variables I should check/tweak to get the fastest performance possible. The variables I have been playing around with so far are:
read_buffer_size=1000M tmp_table_size=1000M max_heap_table_size=1000M innodb_buffer_pool_size=4000M innodb_flush_method=O_DIRECT
For testing, I am using a local "consumer grade" SSD that can perform over 1k transactions per second and 500MB/s write throughput, but during the merge it is only working at roughly 300-500 tps and a maximum write throughput of just 50MB/s. Also, the CPU in htop looks like it's barely putting any effort in.
The database is running in Virtualbox so I can restore from a snapshot and run a different scenario. If I need to allocate more memory, I can do so.