In MySQL why is the first batch executed through client-side prepared statement slower?

Question

I have around a million lines to be inserted into a MySQL (InnoDB) database, so to speed things up I turned to batch/multi-line insert. To be specific, I connect to the database (I'm using Java, so it's done through Connector/J) with rewriteBatchedStatements=true appended at the end of URL, then I initialize all the required prepared statements with prepareStatement(). I use addBatch() to add lines into individual prepared statements and call executeBatch() when a certain number of lines is reached. After several calls of executeBatch(), I will do a commit.

In terms of final result everything is good, but I'm very curious about a behavior. Suppose I set the threshold of when to call executeBatch() to 10,000 lines, then during processing, the first call of executeBatch() will be significantly slower than the subsequent calls (in my scenario about 5 sec vs. < 1 sec). It feels as if the MySQL server is still "preparing" something.

From what I know, PostgreSQL has an option like setPrepareThreshold to set the number of queries issued before actually compiling the SQL statement. Is MySQL doing something similar? How can this delay be mitigated or is this behavior native to MySQL implementation?

EDIT (Some more background)

Below is one of the tables I will insert records into (others have similar schema).

'CREATE TABLE `flow_hourly` (
  `datetime` datetime NOT NULL,
  `customer_id` varchar(7) CHARACTER SET utf8mb4 NOT NULL,
  `pages` bigint(14) NOT NULL,
  `hits` bigint(14) NOT NULL,
  `bandwidth` bigint(14) NOT NULL,
  `nvpages` bigint(14) NOT NULL,
  `nvhit` bigint(14) NOT NULL,
  `nvbandwidth` bigint(14) NOT NULL,
  PRIMARY KEY (`datetime`,`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

Since this database is for development purposes, the current maximum size is also around a million records, but in production the rate of growth should be relatively fast (n*100K+ inserts per day).

One may guess from the schema above that I'm creating a database to record server traffic information. For each time interval (e.g. hourly, daily, monthly...) there is a dedicated table so that queries on different time intervals can be sped up accordingly. I receive traffic information on an hourly basis, so except for the hourly tables, I do INSERT ... ON DUPLICATE UPDATE to accumulate values.

Answer 1

Probably you have an ordinary "cold cache" case.

When you INSERT 10,000 rows into a table, the rows need to be added to the appropriate place(s) in the "data". Also, 10,000 entries need to be added to each index's BTree. (How many indexes do you have? Please provide SHOW CREATE TABLE.)

If you have AUTO_INCREMENT, then the rows will be "appended" to the table; this is not a caching issue. If, on the other hand, you have a UUID index, or some other 'random' index, then the "inserts" into the index will be random -- involving read-modify-write. The read and the write are cached. But, if the system were "cold" when you did the first 10,000, a lot of reads were probably necessary. Multiply that by how many indexes you have.

How big is the table? If it is small enough to fit into innodb_buffer_pool_size (if InnoDB) or small enough for the indexes to fit into key_buffer_size (if MyISAM), then soon all the index blocks will be cached, and the inserts will speed up.

If the table is too big for the cache, then the 'random' indexes will continue to hit the disk (and be slow). AUTO_INCREMENT will continue to be fast.

5 seconds is about 500 reads from a commodity spinning drive. So, I would guess that you have a modest sized table. Since the next 10K rows go in <1sec (<100 disk hits), I will guess that it fits in cache so far.

I often recommend 100-1000 for chunk size. This is partially because 5 sec can be a problem. You may as well COMMIT after each chunk, since the benefit of delaying it is minor. Furthermore, if you wait "too long" to commit, the 'transaction' could overflow the log_file, leading to an inefficiency.

A chunk size of 100 is about 10% slower than the theoretical maximum. 1000 is about 1% slower than max.

If you have replication, keep in mind that the 5 seconds will interfere with anything else being replicated. (Another argument against 10K.)