Mysql giving only 25 inserts per second

Question

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?

Answer 1

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.

Answer 2

I tried MySQLdb instead of _mysql with the following code. Its giving me around 8k-10k inserts per second. Although LOAD DATA INFILE didn't work as expected (more inserts than normal Insert)

import MySQLdb as mdb
import time

#open file to insert data from
#should be present in /var/lib/mysql/my_db folder
file=open("new.txt")
data = file.read()

#Credentials to connect to Server Database
host = “localhost″
username = “username”
password = “password”
db_name = “database_name”
con=mdb.connect(host,username,password,db_name)

cur = con.cursor()

#setting time for 1 second
stop = time.time()+1

#loop running for 1 second and insert values
count=0
while time.time() < stop:
    if cur.execute("LOAD DATA INFILE 'new.txt' INTO TABLE mytable"): count=count+1

#commit transaction
con.commit()

#print number of rows inserted
print count