Mysql giving only 25 inserts per second

MySQL

I'm using ubuntu 12.04 LTS
Processor : Pentium(R) Dual-Core CPU E5200 @ 2.50GHz × 2
OS Type : 64-bit
Memory : 3.9 GiB
Disk : 101.1 GB

I installed mysql and wanted to test insert performance. Here is my table structure

 > create table mytable (text varchar(200)) engine=InnoDB;

   +-------+---------------+------+-----+---------+-------+
   | Field | Type          | Null | Key | Default | Extra |
   +-------+---------------+------+-----+---------+-------+
   | text  | varchar(2000) | YES  |     | NULL    |       |
   +-------+---------------+------+-----+---------+-------+

I wrote a simple python script for testing Insert performance.
i.e. How many inserts per second?

  import _mysql
  import time

  con = _mysql.connect('localhost', 'root', 'root', 'mydb')

  stop = time.time()+1

  while time.time() < stop:    # runs loop for 1 sec
      con.query("INSERT into mytable VALUES('Test Data')")

I expected atleast 8-10k inserts. But I'm getting only 25 inserts.

I tried many optimization techniques given in various blogs

1. Tried to run the loop in 1 transaction.
2. Tried LOAD DATA INFILE 'new.txt' INTO TABLE mytable

I also set innodb_flush_log_at_trx_commit=2 in /etc/mysql/my.cnf

But none of them worked, getting only 25-30 inserts per second. What to do?

Best Answer

First, I would argue that you are testing performance backwards. Instead of measuring how many inserts you can perform in one second, measure how long it takes to perform n inserts, and then divide by the number of seconds it took to get inserts per seconds. n should be at least 10,000.

Second, you really shouldn't use _mysql directly. It makes your code non-portable to other Python database adapters.

Third, _mysql is defaulting to whatever auto-commit setting is the default for your database, and MySQL is generally auto-commit on by default. MySQLdb turns off auto-commit by default.

Fourth, if you really want to insert a lot of rows fast, you should use cursor.executemany(sql, list_of_tuples_of_parameters). This is internally converted into a multi-row insert, and is an atomic operation for MySQL.

Please read PEP-249.

Related Question