Mysql insert code#

Using multiple threads muddies things a bit because some versions of MySQL have working group-commit in innodb - this means that multiple threads doing their own commits can share a single write to the transaction log, which is good because it means fewer syncs to persistent storage. I'm also assuming that you're using a single thread to do these inserts. I'm of course assuming that you're using a transactional engine (usually innodb) AND that you haven't tweaked the settings to reduce durability. This is inherently rather slow and will probably become the limiting factor in these cases.

It needs to wait for the data to reach a persistent storage location (hopefully the battery-backed ram in your RAID controller). This means that if you do one insert per row, you're going to be committing a transaction for each row.Īssuming a single thread, that means that the server needs to sync some data to disc for EVERY ROW. Hope that helps people decide on (a) whether to use the multiprepare idea, and (b) how many VALUE blocks to create per statement (assuming you want to work with data that may be large enough to push the query past the max query size for MySQL, which I believe is 16MB by default in a lot of places, possibly larger or smaller depending on the value of max_allowed_packet set on the server.)Ī major factor will be whether you're using a transactional engine and whether you have autocommit on.Īutocommit is on by default and you probably want to leave it on therefore, each insert that you do does its own transaction. So yes, even just bundling 2 or 3 writes together provides a dramatic improvement in speed (runtime cut by a factor of n), until you get to somewhere between n = 5 and n = 10, at which point the improvement drops off markedly, and somewhere in the n = 10 to n = 50 range the improvement becomes negligible. It was written in such a way as to allow me to dynamically adjust the number of VALUE blocks per prepared INSERT (ie, insert n rows at a time, where I could adjust the value of n before a run.) I also ran the test multiple times for each n.ĭoing single VALUE blocks (eg, 1 row at a time) took 5.7 - 5.9 seconds to run. NET connector (Mysql.Data.*) stuff to INSERT the data from memory into a table on a MySQL server via prepared statements.

Mysql insert code#

The code I wrote for this benchmark in C# uses ODBC to read data into memory from an MSSQL data source (~19,000 rows, all are read before any writing commences), and the MySql. I know I'm answering this question almost two and a half years after it was asked, but I just wanted to provide some hard data from a project I'm working on right now that shows that indeed doing multiple VALUE blocks per insert is MUCH faster than sequential single VALUE block INSERT statements.