I have a Java program running on a VM that is sequentially feeding a queue of single row INSERTs through a single connection to a MySQL server on the same VM. The queue execution is not artificially capped – once a query is done executing, the next one gets executed immidiately. The queue is also never empty.
The data gets inserted into four different MyISAM tables. Two of these have a UNIQUE column and the other two each have a UNIQUE CONSTRAINT across two columns. Most of the data generated by the program already exists in the db, but instead of checking for duplicates, the program just throws the data at the db and ignores duplicate errors.
Now I am logging the execution time of every query with the java program. Obviously that execution time is slightly skewed because it is timed by Java and not MySQL, but I am able to verify the accuracy of the average execution time by dividing a longer time frame by the total number of queries executed in that time frame. So I will assume that the timing from my Java application is accurate.
Onto the actual problem: I have observed that the average time per INSERT is going up and back down by up to factor two fairly consistently (but not gradually) every few hours or minutes. That increase or decrease is consistent across the different kinds of INSERTS, so it should be independent of the table used.
MySQL is steadily using 22% RAM of the VM, Java is using about 7%.
The VM has four CPU cores. Two are never used, Java is using 10-30% of one and MySQL is using 30-60% (never more) of another one.
There's nothing else running on the VM.
If I fire up a second application, queueing INSERTs for another set of tables, CPU usage goes up by 50-100% on each cores and the average execution time goes up a bit (I am not sure about this because the execution time is so inconsistent anyway).
Once the average execution time was at the minimum for about 20 hours, usually it's at the minimum or at the maximum for a few hours a day and then goes up and down a bit for most of the day.
I am pretty clueless about the possible causes for this seemingly random volatility in performance, so any ideas are greatly appreciated.
Edit: I should add that I am experiencing the same phenomenon when doing 100-row to 1000-row bulk INSERTs into InnoDB tables.
Best Answer
The simple answer is: it is not possible to guess what is the source of contention.
The more detailed: MySQL 5.6+ can instrument a break-down of query execution time, so that you can see if performance is stalled waiting on IO, locks, etc.
The diagnostic feature is called
performance_schema. The easiest way to start using it, is to download MySQL Workbench 6.1 and chose "Performance Reports" (under Performance).