1

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.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.