MySQL Queries Per Second – How to Measure and Optimize

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.

You said the total system RAM is:

1GB RAM

Your MySQL error log contains:

140530 10:58:40 InnoDB: Error: cannot allocate 524304384 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 7994352 bytes. Operating system errno: 12

Errno 12 is "Cannot allocate memory."

You have tried to allocate too much memory to InnoDB, leaving only 512MB for Linux kernel, other processes, filesystem cache, etc. That's probably not leaving enough memory for those other systems. You might even be trying to run applications on the same host, putting further pressure on the memory allocation.

You can reduce memory resource competition by allocating a smaller amount to innodb_buffer_pool_size. That's likely to be the largest single use of memory by MySQL. The default for this variable in MySQL 5.1.73 is 128MB, and this is more appropriate for your system with only 1GB of memory.

I recommend you upgrade your server with more memory. Consider the following survey of how much memory is most common for sites using MySQL. The most common answers are 16GB-64GB and 4GB-16GB.

http://www.mysqlperformanceblog.com/2012/11/02/how-much-memory-do-you-use-to-run-mysql/

Of course the size of the buffer pool depends on your database too. If your database is very small, there is little need for a large buffer pool. It's just commonly the case for databases to be larger than the available RAM.