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.
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
|
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 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
|
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
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.