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.