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:

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