Re: Occasional very long LOAD DATA commands
>>> In MyISAM, a slow INSERT will block any SELECT; a slow SELECT will block any INSERT.
Yes, I understand that.
>>> Wow! What application can't afford a 100ms delay?
It is a telemetry recording application. Actually, a 100 ms delay is OK, it is the 1 sec delay that is frowned upon. Anyway, I increased the buffering time to 1000ms, but it did not make any real difference to the number of LOAD DATA executions taking 5 seconds.
>>> That's a reasonable setting if you have 4-6GB of RAM, aside from Java. How much RAM do you have?
Right now I have 4 GB, but I have already ordered an upgrade to 8 GB, even though I don’t have many hopes that it will solve the problem. The GC logs show that Java never grows above ~ 1GB.
>>> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
I understand that the query cache helps performance only if one has reoccurring queries, is that correct? Are LOAD DATA or INSERT statements considered queries in this context? In other words, should I have a query cache?
>>> Nor I :(
Don’t say that, you are actually very helpful see just below!
>>> Instead of using LOAD DATA, consider building a batched INSERT statement.
I tried, but the problem of the 5 sec remains, this time it is the INSERT that occasionally takes this long. But this was a very good advice, it made me think that maybe the problem was in the JDBC connector, since in both cases there was a substantial amount of data transmitted by the client through the connector.
So, I tried to really write the rows to a local text file and to really read the file with a LOAD DATA INFILE (without LOCAL), which would drastically reduce the amount of data transmitted by the JDBC connector. And this did the trick: The 5 sec latencies are gone, and the extra delay caused by writing and reading of the rows to/from the SSD is negligible.
Rick, I think I can consider the cause of the problem identified, and I have to thank you for your help. I will post something to this effect on the JDBC and Java forum, hopefully somebody there will look into it.
Thanks again!
Andrea