title: Why does an insert query occasionally take so long to complete?
body: <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>