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?

  • I am not very familiar with Python, but you're sure that time.time()+1 isn't = time+1microsecond, right? :)
    – Kao
    Sep 21, 2012 at 11:35
  • Yeah thats for sure.. time.time() + 1 is 1 second and not microsecond..
    – Kartik
    Sep 21, 2012 at 11:50
  • 3
    Testing over a period of 1 second will not give you an accurate figure - the database has to parse the query for the first time (which carries an overhead). Run it over a period of a minute and see what figure you get.
    – Philᵀᴹ
    Sep 21, 2012 at 13:21

2 Answers 2


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.


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
data = file.read()

#Credentials to connect to Server Database
host = “localhost″
username = “username”
password = “password”
db_name = “database_name”

cur = con.cursor()

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

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

#commit transaction

#print number of rows inserted
print count

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.