3

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.

Improve this question
4
  • Another hopefully relevant question: How to use insert delay with the InnoDB engine and use less connection for insert statements?. Check the section about log buffers.
    – ypercubeᵀᴹ
    May 1, 2014 at 22:02
  • And a thought. Are you sure the java application is not closing and opening a new connection for every insert statement? And how many concurrent connections do you have?
    – ypercubeᵀᴹ
    May 1, 2014 at 22:03
  • I'm using a single connection and do not close / reopen it during the programs execution, although I don't know if Java's SQL library does that by default every now and then, but even if it did, I don't think it would have that much of an impact. To be specific about the Java code: I open a single connection, create a single statement, then loop through query executions.
    – Max
    May 1, 2014 at 22:34
  • 1
    Some inserts will cause an index rebalancing, and others will not. Causing an index rebalance on one table should be independent of whether an index rebalance on the others (unless the index fields are the same, but that appears not to be the case). Clearly an insert requiring an index rebalance will take longer than one without.
    – Pieter Geerkens
    Aug 2, 2014 at 13:44

1 Answer 1

Reset to default
1

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).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.