Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I am not very familiar with Python, but you're sure that time.time()+1 isn't = time+1microsecond, right? :) –  Kao Sep 21 '12 at 11:35
    
Yeah thats for sure.. time.time() + 1 is 1 second and not microsecond.. –  Kartik Sep 21 '12 at 11:50
2  
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. –  FreshPhilOfSO Sep 21 '12 at 13:21
add comment

migrated from stackoverflow.com Sep 21 '12 at 13:18

This question came from our site for professional and enthusiast programmers.

2 Answers

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.

share|improve this answer
add comment
up vote 1 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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