Re: Occasional very long LOAD DATA commands

[Rick James]

Are you doing other operations on that table? Note that a 5-second SELECT could show this symptom -- blocking the writes (LOAD) for 5 seconds. If, on the other hand, you used InnoDB, such blocks would be rarer.

30 per LOAD is not being very efficient. Can you change your 100ms to 1000ms?

SHOW VARIABLE LIKE '%buffer%';

Have you verified that it is not Java's "garbage collection" that is causing the problem?

Read up on optimizations for building strings -- in some languages (I don't know about Java) it is better to gather an array, then "join" or "implode" the set all at once. Appending to a string is an order of magnitude slower because of all the memory allocations. (Maybe StringBuffer solves this issue a different way.)

Are you ever swapping? That is a killer for MySQL performance.

Can you graph the memory usage? Java may have a "sawtooth" pattern; see if the 5-sec delays happen at the points. (MySQL's pattern is very flat.)