title: MySQL LOAD DATA INFILE slows down after initial insert using raw sql in django
body: <p>I'm using the following custom handler for doing bulk insert using raw sql in django with a MySQLdb backend with innodb tables:</p>
  
  <pre><code>def handle_ttam_file_for(f, subject_pi):
      import datetime
      write_start = datetime.datetime.now()
  
      print "write to disk start: ", write_start
      destination = open('temp.ttam', 'wb+')
      for chunk in f.chunks():
          destination.write(chunk)
      destination.close()
      print "write to disk end", (datetime.datetime.now() - write_start)
  
      subject = Subject.objects.get(id=subject_pi)
  
      def my_custom_sql():
          from django.db import connection, transaction
          cursor = connection.cursor()
  
          statement = "DELETE FROM ttam_genotypeentry WHERE subject_id=%i;" % subject.pk
          del_start = datetime.datetime.now()
          print "delete start: ", del_start
          cursor.execute(statement)
          print "delete end", (datetime.datetime.now() - del_start)
  
          statement = "LOAD DATA LOCAL INFILE 'temp.ttam' INTO TABLE ttam_genotypeentry IGNORE 15 LINES (snp_id, @dummy1, @dummy2, genotype) SET subject_id=%i;" % subject.pk
  
          ins_start = datetime.datetime.now()
          print "insert start: ", ins_start
          cursor.execute(statement)
          print "insert end", (datetime.datetime.now() - ins_start)
          transaction.commit_unless_managed()
  
      my_custom_sql()
  </code></pre>
  
  <p>The uploaded file has 500k rows and is ~ 15M in size.</p>
  
  <p>The load times seem to get progressively longer as files are added. </p>
  
  <pre><code>Insert times:
  1st:    30m
  2nd:    50m
  3rd:    1h20m
  4th:    1h30m
  5th:    1h35m
  </code></pre>
  
  <p>I was wondering if it is normal for load times to get longer as files of constant size (# rows) are added and if there is anyway to improve performance of bulk inserts.</p>