title: Deleting Records from huge MySQL innoDB Table
body: <p>I know this question has been asked a hundred times, but unfortunately none of these answers helped, since most are many many years old, it should justify another one.</p>
  
  <p>I have two tables, <code>records</code> with <code>+140kk rows/+24GB</code> and <code>extra</code> with <code>+89kk rows/+70GB</code>.</p>
  
  <p>Every <code>extra</code> row has a foreign key relation to <code>records</code>, indexes are properly set between the two tables. Deletions from <code>records</code> cascade a delete to the related <code>extra</code> row.</p>
  
  <p>I need to clear old records <strong>during production</strong>. 
  Running <code>DELETE FROM records WHERE WHERE created &lt; UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) ORDER BY id LIMIT 1000;</code> takes endless (I killed it after 20 minutes where it was still in <code>init</code>). Doing the same with <code>SELECT</code> finishes in just a few milliseconds.</p>
  
  <p>Since the <code>SELECT</code> is that fast I tried it with an <code>id IN (SELECT id subquery^)</code>, unfortunately that didn't change a thing, so I killed it after 15 minutes.</p>
  
  <p>Since deleting single records is fast, I ended up with this work around:</p>
  
  <pre class="lang-sh prettyprint-override"><code>for i in `seq 1 100000`; do
      mysql database -e "SELECT id FROM records WHERE created &lt; UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) ORDER BY id LIMIT 1000;" | sed 's;/|;;g' | awk '{if(NR&gt;1)print "DELETE FROM records WHERE id = ",$1,";" }' | mysql database;
      now=$(date +"%Y/%m/%d %T")
      echo "[ $now ] $i.000"
  done
  </code></pre>
  
  <p>This is able to deal with a thousand records per second at the beginning but after a few loops it goes down to a thousand per 10-20 seconds. As I need to purge 100k rows this would take nearly a month to finish, which is about the same timeframe the records to purge are from, hence it would never finish (especially as there are multiple databases which need this).</p>
  
  <p>The database is stored on an SSD <code>Crucial_CT500MX200SSD1</code> and the Software is <code>MariaDB 10.1</code> with the <code>InnoDB</code> engine. <code>innodb_flush_log_at_trx_commit</code> is set to <code>0</code> to avoid unnecessary disk util.</p>
  
  <p>According to <code>atop</code> the bottleneck is the Disk, CPU is nearly sleeping and most of the mem is in system cache.</p>
  
  <p>Structure dump: <a href="https://gist.github.com/Slind14/0da34e09dba91cf411db2ead5ad666ef" rel="nofollow noreferrer">https://gist.github.com/Slind14/0da34e09dba91cf411db2ead5ad666ef</a></p>