Tuning

There are four areas that you can tune in MySQL

I will start with a brief section on tuning the operating system, hardware changes very quickly and the industry seems to adapting a cloud technology using virtualization software for example VMWare (which I have a whole topic on), this allows you to get the best bang for your buck from your hardware, it also allows to to add or remove resources from a virtual machine with easy (resources being cpu, memory or disk space with the aid of a SAN).

With virtualization you can start off with a small server and increase its resources as the database grows, also with operating's systems now by default supporting 64-bit computing, memory requirements are now longer a limitation. Normally however it is the disk I/O that is the bottleneck, especially with system that have large volumes of data, SAN technology has come a long way and with the increase of speed and caching they are just as fast as a directly attached hard disk. Now days SAN's are configured so that all data is spread across all the disks within the SAN, using raid technology to protect your data.

I am not going to cover each operating system regarding tuning but point you to my unix commands web page, on this page you will see a section entitled Performance Monitoring and Diagnostics, you can use the tools in this section to see if you have any performance problems with you underlying server.

Tuning MySQL server

Once you have the operating system tuned you can now focus on tuning MySQL, this means that you have to have a understanding of the status command and the global variables that are associated, the status command produces over 250 variables.

One word of warning some variables are specified in bytes while other maybe specified in megabytes, also some can be changed dynamically others will require a restart of MySQL, so it is always best to double check the MySQL documentation.

You have three ways on which to configure a system variable

My preference is using the my.cnf file, here you can see what variables have been changed, you can also add comments relating back to change controls on why a variable was changed. The configuration file supports options from multiple programs, each program has its own directive which is a keyword in brackets for example [mysqld], [client], [mysqld-safe].

below is a table that lists overall MySQL servers plus MyISAM and InnoDB options

Overall mysqld options
memlock locks the mysqld daemon into server memory, it can improve performance but if the server runs out of memory then mysqld daemon will crash
sort_buffer_size determines the amount of memory allocated for SQL sorts, if this space is exceeded then the server will use hard disk space resulting in worse performance
thread_cache_size mysqld creates a cache of unused connection thread rather than destroying threads and creating new ones, increase this variable if you have a lot of users logging in and out of the MySQL server
thread_concurrency only use on Solaris systems, this should be set to twice the number of CPU's.
tmp_table_size both this and max_heap_table_size are used to determine the maximum size allowed of an in-memory temporary table before it is converted to a MyISAM table, the smallest value of either one is used.
MyISAM configuration options
concurrent_inserts

allows for inserts into MyISAM tables without blocking reads

  • setting of 1 = if there are any deleted rows in the data file that space is filled, only if there are no deleted rows are inserts allowed to be added while the read occurs at the same time
  • setting of 2 = if no selects are occurring the empty space in the data file is filled, if there are reads then the inserts are written to the end of the data file at the same time
delayed_key_write delays flushing to disk for index changes to batch the changes for greater performance, the risk is that a data corruption could be caused by a system crash, but as it is only indexes these can be rebuilt apon starting up the database.
key_buffer

this configures the size of the MyISAM index buffer, remember MyISAM does not buffer the data so only the caching of data is done by the operating system buffers.

The cache ratio is the number of times a value in the buffer was read as a total percentage of the total number of times a value in the buffer was looked for, to determine the cache hit ratio run show global status like '%key_read%' and use the the key_read and key_read_requests status variables in the formula below

   hit_ratio = (key_reads / key_read_requests) * 100

to increase the buffer use the key_buffer option to set the key_buffer_size larger

The formula to find the percentage of the index buffer that is in use, look for about 80% usage

  100 * (1 - (key_blocks_unused * key_cache_block_size) / key_buffer_size)

InnoDB configuration options
innodb_buffer_pool_size

a static variable that specifies the size of the cache for InnoDB data and indexes, give generously if using a lot of InnoDB tables ideally you be allocating 50-70 of your systems memory.

to calculate the ratio of unused data pages to the total number of pages the formula below can be used

  Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total

if the ratio is high (close to 1) the the Innodb buffer pool is probably set to high, a less likely cause is that the innodb_max_dirty_pages_pct system variable is set to low and dirty pages are being flushed very often, freeing up pages long before they are needed

innodb_flush_log_at_trx_commit

there are three options (0|1|2), this dynamic variable manages how often the InnoDB log buffer is written (flushed) to the log file

  • 0 = the log buffer is written every second and the logs file flushes to disk
  • 1 = every commit will make the log buffer write to the log file
  • 2 = basically 0 and 1
innodb_flush_method

this static variable determines how InnoDB storage engine interacts with the operating system in respect to I/O operations, there are three methods

  • fsync is the default option and uses fsync() system call to flush the data and log files
  • O_DIRECT will bypass the operating system cache for both reads and writes of data and log files
  • O_SYNC uses the fsync() system call for data files but for log files it uses O_SYNC

O_DIRECT will provide you with better performance but make sure the RIAD controller has a battery backed up write cache, again make sure you test before making it your standard

innodb_log_buffer_size buffer used for writes to InnoDB logs, unless you use very large BLOBs this static variable should not be over 8MB
innodb_log_files_in_group a static variable that determines the size of each InnoDB log file
innodb_max_dirty_pages_pct this dynamic variable specifies the maximum percentage of pages in the InnoDB buffer pool that can be dirty, this defaults to 90%
innodb_thread_concurrency this dynamic variable determines the maximum number of system threads inside InnoDB, a good start would be twice the number of CPU's.

I will have another section on performance monitoring which will discuss using mysqlreport to determine what is happening in your system, the above is tuning MySQL at the build phase, some of the system variables will change after monitoring for 2-3 months if the server is not coping with demand or you system is growing rapidly.

Query Analysis section covers SQL tuning and schema/index tuning.