Mysql InnoDB merge / copy data

开发者_运维技巧I have a scenario where I need to do relatively large amounts of inserts. Lets say I have million rows a minute... that gets inserted into structurally identical tables, but with different levels of aggregation applied to the data.

Also, in order to access this data again , its quite heavily indexed.

Now, in order to do the inserts as quickly as possible , to free up time for other processing, I thought of inserting into a "DUMP" table that has the exact same structure , but no indexes... and then running a "merge proc" of sorts that would ETL the data into the indexed aggregate tables... arguing that since it all happens Inside the database it should be faster than pushing it multiple times from the outside.

However, a simple "insert into tableB select * from tableA" easily exceeds my innodb_buffer_pool with row locks and then error 1026es me.

So, finally to the question: Is there a fast/reliable way of moving/merging large-ish volumes of data from 1 table to another?

The tables can and probably will be partitioned. I guess its wishful thinking that you could simply move a partition from one table to another.

If "select from insert into" is it, then I guess it'll be safer and easier to just insert it all from the application side.

You can certainly insert into an intermediate table, then transfer the rows. That might end up just making more work for the DB though.

INSERT ... SELECT is certainly your friend if you want to copy data from one table into another, it will definitely be faster than moving it via the client.

1M rows per minute is quite a lot of inserts, you will certainly need to build your application carefully. I assume you're doing these in big batches already (10,000 rows seems generally about right) but you also want to be mindful of the durability level of innodb (i.e. when it flushes).

Turning MySQL's binlog off reduces the number of fsyncs rather a lot (i.e. 50% in some cases) so is recommended for very high-commit workloads. You may also want to look at the various tuning work done by third parties. And of course, use the plugin not the original innodb.

To "Free up time for other processing", you mean on your client or your database? If it's your client, you should definitely be inserting asynchronously with other work - keep the records in memory or on disc (but be mindful of the durability of this store) until you have time to insert them, inserting them in big batches.