MySQL Forums
Forum List  »  Performance

Occasional very long LOAD DATA commands
Posted by: Andrea Mazzario
Date: March 16, 2012 07:09PM

Hi, I am observing a very strange performance behavior with LOAD DATA LOCAL and I will appreciate any suggestion on how to resolve it or at least understand it.

I have a Java program that receives data via UDP, massages them and writes them as rows into a MyISAM table. In order to speed up the insertion process the program buffers the data for the new rows into a StringBuilder object, then every 100 msecs creates an InputStream over the content of the String representing the StringBuffer, passes the InputStream to the JDBC connector via the statement.setLocalInfileInputStream(inputStream)
command and finally invokes the
statement.execute("LOAD DATA LOCAL INFILE 'file.txt' INTO TABLE MinMax_500")
command. There are always ~ 30 rows available when invoking LOAD DATA.

Normally, everything works just fine and the time to complete the LOAD DATA LOCAL command is on the order of 20 msecs, but occasionally, on the order of once every 1000 executions, it takes just over 5 full seconds to execute the command.
I inserted an invocation of the SHOW PROFILE command when the program notices one of these slow LOAD DATA LOCAL, and the culprit for the slow-down is in the System lock status (all the other statuses have normal durations):

Status Duration CPU_usr CPU_sys
System lock 5.014204 0.015600 0.015600

Other than in these rare occurrences, the System lock status lasts only a few milliseconds. This is a typical example:

Status Duration CPU_usr CPU_sys
System lock 0.013788 0.015600 0.000000

I was originally suspecting that the problem could be related to the JDBC Java Connector, but I did some tests of LOAD DATA from the MySQL Command Line Client and the SHOW Profile indicated that all the work was actually done in the System lock status: the amount of time spent there was roughly proportional to the number of rows in the input file (for files with over 1000 rows) while the time spent in the other statuses was basically constant. So, it seems that (at least for the LOAD DATA command) the System lock status is actually doing much more than just requesting a lock (this seems to contradict the entry for System lock in the documentation 7.12.5.2. General Thread States), it actually does all the table and indexes update work. Can anyone confirm that?

I considered that the long System lock time would happen when MySQL flushes either the table or the indexes to the disk (an SSD), but the times at which these long delay happen change considerably between runs, and since all the runs start with an empty file I would expect those flushing to happen more or less at the same time. And again, why always an extra 5 seconds? That seems to be the case when the table has only a few thousand of records or when it reaches the hundreds of thousands.
I am running MySQL 5.5.15 winx64 on Windows 7 64, the mysql connector 5.1.17 and the JDK 1.7.0_03 x64.
I thank you in advance for any suggestion and recommendation!
Andrea

Options: ReplyQuote


Subject
Views
Written By
Posted
Occasional very long LOAD DATA commands
3822
March 16, 2012 07:09PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.