title: MySQL insert becomes slow after a few fast inserts
body: <p>I am doing a large insert by reading a file. The file looks like,</p>
  
  <pre><code>sampletext1
  sampletext2
  ..........
  ..........
  sampletextN
  </code></pre>
  
  <p>There are millions of lines in the file and it is around 3 GB in size. Reading each line to a variable and then doing a single INSERT won't work because I have only around 2 GB of RAM. </p>
  
  <p>I read line by line and create the mysql INSERT string. When the code has read 5000 lines, I INSERT them to the DB, so there will be 5000 records in an INSERT. The MySQL query in my code ( INSERT IGNORE INTO $curr VALUES $string ) runs as usual until around 25000 lines are read and INSERTed, but then it slows down and takes around 5-10 second just for one INSERTion. I think it decreases linearly as the records increases.</p>
  
  <p>Perl code snippet :</p>
  
  <pre><code>sub StoreToDB {
  my $self = shift;;
  $self-&gt;_doPreliminary();
  my $data_struc = $self-&gt;_getDATA();
  my $file = $data_struc-&gt;{DOMAIN_FILE};
  my ($count,$cnt,$string,$curr) = (0,0,'',$self-&gt;_getTLD() . '_current');
  open FH,$file or ( FullLogger($self-&gt;_getTLD(),"Cant open $file from StoreToDB : $!\n") and return );
  $self-&gt;_dbConnect();
  while (&lt;FH&gt;) {
     chomp;
     if ( $cnt == MAX ) {
        $self-&gt;_dbExecute("INSERT IGNORE INTO $curr VALUES $string");
        $count += $cnt;
        $cnt = 0;
        $string = '';
        Logger("Inside StoreToDB, count is : $count ***\n");
     }
     $string .= "('" . $_ . "')";
     ++$cnt;
     $string = ($cnt != MAX ? $string . ',' : $string . ';');
  }#while
  close FH;
  $self-&gt;_dbDisconnect();
  return 1;
  }#StoreToDB
  
  ==============================
  DB table details :
  
  mysql&gt; SHOW CREATE TABLE com_current;
  +-------------+-------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table |
  +-------------+-------------------------------------------------------------------------------------------------------------------------------+
  | com_current | CREATE TABLE `com_current` (
  `domain` varchar(60) NOT NULL,
  PRIMARY KEY (`domain`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
  +-------------+-------------------------------------------------------------------------------------------------------------------------------+
  
  1 row in set (16.60 sec)
  
  mysql&gt;
  </code></pre>
  
  <p>MySQL status output :</p>
  
  <pre><code>Uptime: 1057 Threads: 2 Questions: 250 Slow queries: 33 Opens: 38 Flush tables: 1 Open tables: 28 Queries per second avg: 0.236
  </code></pre>
  
  <p>===============================================================
  UPdATE :</p>
  
  <p>So far I have tried the below methods, but none of them was better:</p>
  
  <pre><code>1) LOCK TABLES my_table WRITE;
     then after inserting, I unlock it,
     UNLOCK TABLES;
  2) INSERT DELAYED IGNORE INTO $curr VALUES $string
  3) LOAD DATA INFILE '$file' IGNORE INTO TABLE $curr
     this is currently in progress, but seems worse than the original method.
  </code></pre>
  
  <p>I don't know whether or not my my.cnf has any issues. So I have pasted it here.</p>
  
  <pre><code>[client]
  port        = 3306
  socket      = /tmp/mysql.sock
  
  [mysqld]
  datadir = /mnt/mysql/data
  port        = 3306
  socket      = /tmp/mysql.sock
  skip-external-locking
  key_buffer_size = 16M
  max_allowed_packet = 1M
  table_open_cache = 64
  sort_buffer_size = 512K
  net_buffer_length = 8K
  read_buffer_size = 256K
  read_rnd_buffer_size = 512K
  myisam_sort_buffer_size = 8M
  log-bin=mysql-bin
  binlog_format=mixed
  server-id   = 1
  
  [mysqldump]
  quick
  max_allowed_packet = 16M
  
  [mysql]
  no-auto-rehash
  
  [myisamchk]
  key_buffer_size = 20M
  sort_buffer_size = 20M
  read_buffer = 2M
  write_buffer = 2M
  
  [mysqlhotcopy]
  interactive-timeout
  </code></pre>