title: How to increase performance for heavy inserts in mysql?
body: <p>I was researching ways to improve mysql performance for inserts. </p>
  
  <p>About the scenario, in a busy day, for every 3 seconds about 3000 rows for big table, another 100 for medium table and around 30 for small table concurrently. This has to be continue for 24 hours and then i'm choosing important part which is only 30.000-40.000 rows then i'm flushing all 3 tables.</p>
  
  <p>So, i'm using mysql 5.5.29. All tables are using innodb, there are few indexes plus primary keys.</p>
  
  <p>About the question, i saw a comment about using memory engine tables for those inserts and then moving them to primary tables:</p>
  
  <p>"using LOCK TABLE .. WRITE may speed up the insertion, but when processing as well as loading rows, you will lock out any reader. A select on primary key (which should take close to 0 seconds) takes up to 0.2 seconds when my loader is busy loading batches of 1000 rows.</p>
  
  <p>A very good workaround is to put the new rows into a temp table, then flush that table every now and then in a single tansaction. This makes the insert very quick, because all the checking and conversion was already done in the temp table.</p>
  
  <pre><code>CREATE TEMPORARY TABLE ..._temp (....) ENGINE=MEMORY;
  
  -- Loop from here --
  DELETE FROM .._temp;
  INSERT INTO .._temp ... VALUES (...);
  INSERT INTO .._temp ... VALUES (...);
  
  LOCK TABLE ... WRITE, ..._temp READ;
  INSERT INTO ... SELECT * FROM ..._temp;
  UNLOCK TABLES;
  -- Repeat until done --
  
  DROP TABLE ..._temp;
  </code></pre>
  
  <p>What I've seen in my system, is that the INSERTs go at about the same speed as when locking the table, but the clients do not notice any performance loss at all when the loader is running."</p>
  
  <p><a href="http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html" rel="nofollow">Source</a> </p>
  
  <p>Is that makes sense to using memory engine in that case ?
  Should i use innodb for avoding table locks in case of need to access rows or should i prefer to myisam for speed ?</p>