StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhy does an insert query occasionally take so long to complete?
text
Body
copied!<p>This is a pretty simple problem. Inserting data into the table normally works fine, except for a few times where the insert query takes a few seconds. (I am <em>not</em> trying to bulk insert data.) So I setup a simulation for the insert process to find out why the insert query occasionally takes more than 2 seconds to run. Joshua suggested that the index file may be being adjusted; I removed the id (primary key field), but the delay still happens.</p> <p>I have a MyISAM table: <code>daniel_test_insert</code> (this table starts <em>completely</em> empty):</p> <pre><code>create table if not exists daniel_test_insert ( id int unsigned auto_increment not null, value_str varchar(255) not null default '', value_int int unsigned default 0 not null, primary key (id) ) </code></pre> <p>I insert data into it, and sometimes a insert query takes > 2 seconds to run. <em>There are no reads</em> on this table - Only writes, in serial, by a single threaded program.</p> <p>I ran the exact same query 100,000 times to find why the query occasionall takes a long time. So far, it appears to be a random occurrence.</p> <p>This query for example took 4.194 seconds (a very long time for an insert):</p> <pre><code>Query: INSERT INTO daniel_test_insert SET value_int=12345, value_str='afjdaldjsf aljsdfl ajsdfljadfjalsdj fajd as f' - ran for 4.194 seconds status | duration | cpu_user | cpu_system | context_voluntary | context_involuntary | page_faults_minor starting | 0.000042 | 0.000000 | 0.000000 | 0 | 0 | 0 checking permissions | 0.000024 | 0.000000 | 0.000000 | 0 | 0 | 0 Opening tables | 0.000024 | 0.001000 | 0.000000 | 0 | 0 | 0 System lock | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | 0 Table lock | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | 0 init | 0.000029 | 0.000000 | 0.000000 | 1 | 0 | 0 update | 4.067331 | 12.151152 | 5.298194 | 204894 | 18806 | 477995 end | 0.000094 | 0.000000 | 0.000000 | 8 | 0 | 0 query end | 0.000033 | 0.000000 | 0.000000 | 1 | 0 | 0 freeing items | 0.000030 | 0.000000 | 0.000000 | 1 | 0 | 0 closing tables | 0.125736 | 0.278958 | 0.072989 | 4294 | 604 | 2301 logging slow query | 0.000099 | 0.000000 | 0.000000 | 1 | 0 | 0 logging slow query | 0.000102 | 0.000000 | 0.000000 | 7 | 0 | 0 cleaning up | 0.000035 | 0.000000 | 0.000000 | 7 | 0 | 0 </code></pre> <p>(This is an abbreviated version of the SHOW PROFILE command, I threw out the columns that were all zero.)</p> <p>Now the update has an incredible number of context switches and minor page faults. Opened_Tables increases about 1 per 10 seconds on this database (not running out of table_cache space)</p> <p><strong>Stats:</strong></p> <ul> <li><p>MySQL 5.0.89</p></li> <li><p>Hardware: 32 Gigs of ram / 8 cores @ 2.66GHz; raid 10 SCSI harddisks (SCSI II???)</p></li> <li><p>I have had the hard drives and raid controller queried: No errors are being reported. CPUs are about 50% idle.</p></li> <li><p>iostat -x 5 (reports less than 10% utilization for harddisks) top report load average about 10 for 1 minute (normal for our db machine)</p></li> <li><p>Swap space has 156k used (32 gigs of ram)</p></li> </ul> <p>I'm at a loss to find out what is causing this performance lag. This does NOT happen on our low-load slaves, only on our high load master. This also happens with memory and innodb tables. Does anyone have any suggestions? (This is a production system, so nothing exotic!) </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload