Category Archives: InnoDB

Weekly Highlights #47-48

Running Sysbench 0.4.12 on Ubuntu 9.04

I decided to try benchmarking MySQL 5 with Sysbench 0.4.12 but as it turned out it was not so easy task.First I followed the steps as described by the dbadojo blog:

  1. Download last version of Sysbench
  2. tar -xzvf sysbench-0.4.XX.tar.gz
  3. cd sysbench-0.4.XX
  4. ./configure && make && make install

But the compiling wasn’t done and I get a strange error:

../libtool: line 2412: Xsysbench: command not found

Thanks to the tip from I managed to compile it properly.Here what you have to do in short.



and re-run


and your are ready to

./configure && make && make install

After successfully compiling Sysbench you have to create a database called sbtest in MySQL:

mysql -u root -p yourpassword
create database sbtest;
grant all on *.* to ‘root’@’%’;

Then you can prepare your Sysbench test and start benchmarking:

  1. sysbench –test=oltp –mysql-table-engine=innodb –oltp-table-size=1000000 –mysql-user=root –mysql-password=yourpassword prepare
  2. sysbench –num-threads=4 –max-time=900 –max-requests=500000 –test=oltp –oltp-table-size=80000000 –mysql-user=root –mysql-password=yourpassword –mysql-table-engine=innodb –oltp-test-mode=complex run  >  test_thrd_4.txt
  3. sysbench –num-threads=4 –max-time=900 –max-requests=500000 –test=oltp –oltp-table-size=80000000 –mysql-user=root –mysql-password=yourpassword –mysql-table-engine=innodb –oltp-test-mode=complex cleanup

Here how the output from test_thrd_4.txt look like:

sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 4

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using “BEGIN” for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 500000
Threads started!
Time limit exceeded, exiting…
(last message repeated 3 times)

OLTP test statistics:
queries performed:
read:                            2541588
write:                           907710
other:                           363070
total:                           3812368
transactions:                        181528 (201.68 per sec.)
deadlocks:                           14     (0.02 per sec.)
read/write requests:                 3449298 (3832.30 per sec.)
other operations:                    363070 (403.38 per sec.)

Test execution summary:
total time:                          900.0583s
total number of events:              181528
total time taken by event execution: 3597.9221
per-request statistics:
min:                                  0.00ms
avg:                                 19.82ms
max:                                484.69ms
approx.  95 percentile:             118.68ms

Threads fairness:
events (avg/stddev):           45382.0000/149.29
execution time (avg/stddev):   899.4805/0.02

For more information on all possible input parameters and test types you can check the Sysbench manual page . I hope this helps and wish you successful benchmarking !

Tunning MySQL InnoDB performance

Tunning MySQL InnoDB storage engine is not an easy task. It’s more like a game where you set mini goals and then try to find the right value and achieve them. Playing with the InnoDB variables is an art where you have to check your performance results each time you change a configuration parameter. The start point of this game is your MySQL my.cnf file. The optimal configuration depends on your hardware and on the type of workload you are running. I played a few days with the configs and it was really helpful to see a performance gain at the end of the game. So I am sharing here some helpful commands and links that can save me some time in the next level of the game.

MySQL Innodb Only Memory-related variables:

–  innodb_buffer_pool_size – Set the amount of memory allocated to both Innodb data and index buffer cache. If the server requests data available in the cache, the data can be processed right away. Otherwise, the operating system will request that the data be loaded from the disk into the buffer. It is important to set this value as high as possible to use the more efficient innodb data and index buffer cache instead of operating system buffer. For the sysbench I/O bound workload on a T2000 server with 8G RAM, increasing innodb_buffer_pool_size from 4G to 5G can improve performance by around 11%.

Configure an Optimum Number of User Threads:

MySQL is a single-process, multithreaded application.  There is one master thread with highest priority to control the server. For every client request, it creates a dedicated user thread running at normal priority in the thread pools to process the user request and send back the result to each client once the result is ready. And there is one single user thread that waits for input from the console, and a group of utility threads running at lower priority to handle some background tasks. Currently, MySQL cannot scale well with the number of concurrent user connections. On a T2000 server, in the OLTP I/O bound read-write sysbench test, MySQL can scale from 2 up to 64 concurrent user threads to reach the peak performance point. After that, increasing the number of user connections will increase the user level lock contention observed from prstat –mL output(LCK) to reduce MySQL performance. For applications where the number of user connections is tunable, you need to test to get the optimum number of user connections for peak performance. For applications where the number of user connections is not tunable, the innodb_thread_concurrency parameter can be configured to set the number of threads working inside the InnoDB engine. You need to increase this value when you see many queries in the queue in show innodb status. Setting this value at 0 will disable it. On the T2000 server, we set it to be around 2*(Num of disks) in the sysbench OLTP I/O bound workload test. Testing and tuning the optimal value for the innodb_thread_concurrency parameter according to the kind of workload, and behavior of your system at runtime, can affect performance significantly.

Examples of  MySQL options:

Here is the  example of  /etc/my.cnf on T2000(32x1200MHz, 8GB RAM, Solaris 10 11/06) in sysbench OLTP I/O bound test(100M-row): Listing of another example my.cnf:

grep -v “#” /etc/my.cnf|sed -e ‘/^$/d’


port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

log-error = /data/error.txt



max_connections = 3000

table_cache = 1024

max_allowed_packet = 1M

sort_buffer_size = 64K

thread_cache = 8

thread_concurrency = 32

query_cache_size = 0M

query_cache_type = 0

default-storage-engine = innodb

transaction_isolation = REPEATABLE-READ

tmp_table_size = 1M

innodb_data_file_path = ibdata1:100M:autoextend

innodb_buffer_pool_size = 5500M

innodb_additional_mem_pool_size = 20M

innodb_log_file_size =1900M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit =1

innodb_lock_wait_timeout = 300

innodb_max_dirty_pages_pct = 90

innodb_thread_concurrency =32

port            = 3306
socket          = /tmp/mysql.sock
port            = 3306
socket          = /tmp/mysql.sock
back_log = 50
max_connections = 200
max_connect_errors = 10
table_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default_table_type =myISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
long_query_time = 2
server-id = 1
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_data_home_dir =/mnt/mysql/data/
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
max_allowed_packet = 16M
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
open-files-limit = 8192

If InnoDB is disabled:

mysql> show engine innodb status;
ERROR 1235 (42000): Cannot call SHOW INNODB STATUS because skip-innodb is defined

The solution is to remove the log files so that MySql can generate new one on start:

/etc/init.d/mysql stop

$mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak

$mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak

/etc/init.d/mysql start

Useful commands:

Connect to MySql :        $mysql –u root  -p

Show table properties: $mysql> SHOW TABLE STATUS LIKE ‘mytable’ \G

Print my.cnf in nice format :

perl -ne ‘m/^([^#][^\s=]+)\s*(=.*|)/ && printf(“%-35s%s\n“, $1, $2)’ /etc/my.cnf

Show MySQL  statistics:

mysql> show global status;  #mysql global resources and options

mysql> show variables;    #shows all mysql option variables and their values

mysql> show innodb status;   #status information specific to innodb