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’

[mysqld]

port = 3306

socket = /tmp/mysql.sock

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

log-error = /data/error.txt

user=root

skip-locking

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

[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysqld]
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
log-bin=mysql-bin
log_slow_queries
long_query_time = 2
log_long_format
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
myisam_recover
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
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
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
mysql>

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

References:

3 thoughts on “Tunning MySQL InnoDB performance

  1. Pingback: Tunning MySQL InnoDB performance - DbRunas

  2. ivanovNo Gravatar Post author

    It depends on your system and workload but of course the number of CPUs is more important for thread concurrency!
    “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.”
    Do some tests with different configs and you’ll see.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>