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:

• InnoDB Startup Options and System Variables
• MySQL Innodb Performance Tuning For Disk-Bound Workloads On CMT Servers
• MysqlperformanceBlog  Presentation
• MySQL Server Variables – SQL layer or Storage Engine specific.
• Innodb Performance Optimization Basics
• Configuring InnoDB – An InnoDB tutorial
• Tuning / Optimizing my.cnf file for MySQL
• InnoDB thread concurrency
• MySQL Server Memory Usage
• XtraDB/InnoDB CPU bound benchmarks on 24cores server
• Optimizing MySQL/InnoDB Performance
• Choosing innodb_buffer_pool_size
• How to calculate a good InnoDB log file size
• SHOW INNODB STATUS walk through
• The 5 minute DBA: Default My.cnf File
• Why is InnoDB disabled? (SOLVED)
• What to do when MySQL says skip-innodb is defined
• Server System Variables
• MySQL Scalability @2008
• http://yoshinorimatsunobu.blogspot.com/
• Plug In for Performance and Scalability
• MySQL Commands