MySQL tuning

Take this documentation as is, not as a definitive guide to tuning MySQL. A lot of information should be read before changing the MySQL configuration. The documentation is based on experience shared by OpenKM server administrators.

Useful links:

• MySQL: InnoDB Performance Tuning Tips
• MySQL: The MySQL Query Cache
• InnoDB - High disk write I/O on ibdata1 file and ib_logfile0

Sample one

Server information

• Virtualized Windows Server 2008
• CPU: Intel Xeon X3470 @2.93 GHz
• RAM: 7GB
• JVM settings for Tomcat: -Xms 3072 -Xmx 3072 -XX:PermSize=256m -XX:MaxPermSize=512m
• The repository is 1GB

MySQL my.cnf changes

• innodb_buffer_pool_size=1792M
• innodb_additional_mem_pool_size=16M
• innodb_log_file_size=256M
• innodb_log_buffer_size=8M

Using the MySQL server’s InnoDB engine with the default configuration parameters in the my.cnf file can cause a severe performance bottleneck in applications with high database read-write activity. MySQL should have enough RAM to cache the repository and avoid high disk I/O activity.

The most important parameters are:

• innodb_buffer_pool_size
• innodb_additional_mem_pool_size
• innodb_log_file_size
• innodb_log_buffer_size

Tip

According to some database experts, innodb_buffer_pool_size should occupy 70 percent of the RAM of a dedicated database server.

Caution

Before changing these values, please read the MySQL documentation carefully. Before making any changes, you should ensure that the MySQL server has been stopped correctly (check the server log). Otherwise, InnoDB will become corrupted, and the MySQL server won’t restart.

Considerations The repository is 1 GB, so 1792M is enough RAM for innodb_buffer_pool_size. The larger the repository, the more memory you need to allocate to innodb_buffer_pool_size. On an x86 MySQL server, you cannot allocate more than 2 GB of RAM.

Information collected from OpenKM forum posts.

Sample two

Server information

• CPU: Intel Xeon E5606 @2.13GHz
• RAM: 4GB
• System: Windows Server 2008, 64-bit
• JAVA_OPTS=-Xms256m -Xmx1024m -XX:PermSize=256m -XX:MaxPermSize=512m -Djava.awt.headless=true -Dfile.encoding=utf-8
• Database size: 2.5G
• Repository size: 40GB
• 100,000 nodes (folders + documents)

MySQL my.cnf changes

• query_cache_size=0
• table_open_cache=2000
• tmp_table_size=16M
• thread_cache_size=9
• myisam_max_sort_file_size=100G
• myisam_sort_buffer_size=32M
• key_buffer_size=8M
• read_buffer_size=64K
• read_rnd_buffer_size=256K
• sort_buffer_size=256K
• innodb_additional_mem_pool_size=16M
• innodb_log_buffer_size=8M
• innodb_buffer_pool_size=1042M

Results

Before optimization, the login time was about 45-55 seconds, with a lot of hard disk I/O usage. After the MySQL optimization, login takes about 10 seconds.

Note

With more RAM, it will be possible to increase the MySQL buffer and probably achieve better performance.

Information collected from OpenKM Forum post.