Re: Occasional very long LOAD DATA commands
>>> Are you doing other operations on that table?
Not during these tests. There will be occasional queries going on in parallel with insertions in the future, but they should all be quite fast, using the indexes. But yes, I will consider switching to InnoDB if the queries would prove to be an issue.
>>> 30 per LOAD is not being very efficient. Can you change your 100ms to 1000ms?
Yes, I definitely could, the 100 msec was just a compromise in trying to get better efficiency than with individual inserts but in limiting the delay between when the data were received by the application and when the corresponding rows were inserted into the DB.
Anyway, I tried to set the buffering to 1000 msec, but that did not make any difference to the occasional 5 sec delays.
>>> SHOW VARIABLE LIKE '%buffer%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| bulk_insert_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 1019215872 |
| myisam_sort_buffer_size | 408944640 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 65536 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 134217728 |
| sql_buffer_result | OFF |
+-------------------------+------------+
>>> Have you verified that it is not Java's "garbage collection" that is causing the problem?
Yes, I have turned on GC logging, and the GC is behaving very well: I have a GC every ~1.5 - 2 sec and each GC lasts for less than 0.1 sec.
>>> Read up on optimizations for building strings…
I am reasonably confident that I use the most efficient Java way: I build a StringBuffer from which I create the String only when ready to map it to the InputStream. Besides, all the Strings have a similar size, and the 5 sec. delay happens only once in a big while.
>>> Are you ever swapping? That is a killer for MySQL performance.
According to Perfmon there is no swapping in the system: Committed memory is < total system memory and Pages Output/sec is zero during the whole test
>>> Can you graph the memory usage? Java may have a "sawtooth" pattern; see if the 5-sec delays happen at the points.
I can see the memory used by Java in the GC log, it slowly grows up to ~ 1GB and then a more aggressive GC brings it back to ~ 600 MB, but even the more aggressive GC takes less than 0.1 secs. And no, these more aggressive GC do not seem to correspond to the 5-sec delays
I really appreciate for your help. Is there some way to find out more what happens during the execution of a SQL command? I tried the SHOW PROFILE, but that does not tell me much, all the time seems to be spent in the System lock status.
Thanks again
Andrea