Web Site Tuning – The Database

Summary:

• Adjust MySQL’s system variables.
• Use tuning-primer.sh script.

The most active application of this blog requires the use a database system to store data such as this content, comments, tags, or links. The database of choice here is MySQL. By default, it comes with a default set of system variables, designed for common and light-weight use. However, tuning can be performed to make it even more compact and efficient.

Below are the settings in my.cnf file for this low traffic and light weight site:

• sort_buffer_size = 64K
  Default is 2M, reduced to 64K because I’ll hardly do big complex sorting.
• read_buffer_size = 256K
  Default is 128K. WordPress does a lot of sequential scans, and giving it more space is helpful.
• net_buffer_length = 2K
  Default is 16K. Apache/PHP client does a lot of quick lookups. The expectation is the client will not need that much socket buffer to begin with. The buffer will dynamically enlarge, as needed, to 1M (default for max_allowed_packet).
• thread_stack = 64K
  Default is 192K. Connection from this site may only be 1 or 2 clients. Other external clients are not allowed. A large value, such as the default, is not necessary. The only sure way to know the limit is to run MySQL benchmark suite.

There is a MySQL Performance Tuning Primer script that can be used to provide more tweaking recommendations. The database must be active for more than 48 hours before using this script. The following changes are based on the script’s recommendations:

• long_query_time = 5
  Default is 10 seconds. When SQL queries usually go fast, it’s advisable to reduce the threshold. This is used in conjunction with –log-slow-queries option to figure out any queries that is out of the ordinary.
• max_connections = 10
  Default is 100. When there’s no external clients and the database only have a handful of local connections from the Apache/PHP, reducing this to a more reasonable number will conserve file descriptors and possibly memory usage.
• key_buffer_size = 128K
  Default is 128K. As an experiment, set at 16K, the key cache miss ratio was 1:8 and key buffer was at 50% fill ratio. Getting the right number will help reduce SQL command execution time. Setting it to default produces miss rate of 1:212 and 100% fill ratio. That seems like the correct setting for this site. There is a post on InformIT that mentions how to determine the proper key_buffer_size setting.
• query_cache_type = 1
  query_cache_limit = 256K
  query_cache_size = 3M
  Defaults are 1, 1M, and 0, respectively. With frequent querying, it will help to ensure most common queries are cached and ready to run. The number to watch is the Fragmentation size. The more fragmentation, the database will have worse performance. With this setting, fragmentation is currently at 4%. There is a more in-depth review of MySQL’s Query Cache (old but still relevant).
• max_heap_table_size = 96M
  tmp_table_size = 192M
  Defaults are 16M and 32M. This is for the efficiency of doing table lookups in memory. When there is not enough space allocated in memory, the temp tables will be created on disk. Lookups on disk is slow. If there is enough RAM on the server, it is better to move those temp tables to memory. The tuning-primer will show how many temp tables being used and a percentage of what’s on disk. If the machine is a dedicated server, increase until all of temp tables are not created on disk. Using this setting, this site has 68% temp tables created on disk. It is possible to allocate more memory but this machine is not a dedicated database server. Some memory is needed for other applications.

Database tuning is a tricky job. Professional Database Administrators spend a lot of time tweaking and tuning the settings to get their databases perform optimally. Since there are so many variables, it is almost an educated guess and plenty of experimentation to get it right. With the proper tools, one must not be afraid to take a risk and try out all of the combination.

Further readings: MySQL tips and tricks list, InnoDB Performance Tuning Tip, and Getting Maximum Performance from MySQL.

Tags: applications, mysql, optimization, server