Tuning MySQL

 1. To know current default values for mysqld give 
      
        $ mysqld –verbose –help

2. While Tuning MySQL, 2 values are most important

     a. key_buffer_size  –> key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.
The maximum permissible setting for key_buffer_size is 4GB on 32-bit platforms. As of MySQL 5.0.52, values larger than 4GB are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning). The effective maximum size might be less, depending on your available physical RAM and per-process RAM limits imposed by your operating system or hardware platform.

    b. table_open_cache —> The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable.

Some examples:

Case 1: If you have at least 256MB of memory and many tables and want maximum performance with a moderate number of clients, use below command to invoke mysql:

$ mysqld_safe –key_buffer_size=64M –table_open_cache=256 –sort_buffer_size=4M –read_buffer_size=1M &

Case 2: If you have only 128MB of memory and only a few tables, but you still do a lot of sorting, use below command to invoke mysql:

$ mysqld_safe –key_buffer_size=16M –sort_buffer_size=1M

Case 3: If there are very many simultaneous connections, swapping problems may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections.

With little memory and lots of connections, use below command to invoke mysql:

& mysqld_safe –key_buffer_size=512K –sort_buffer_size=100K –read_buffer_size=100K &

Or 

$ mysqld_safe –key_buffer_size=512K –sort_buffer_size=16K –table_open_cache=32 –read_buffer_size=8K –net_buffer_length=1K &

Important parameters for InnoDB storage Engine:

innodb_buffer_pool_size – This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply – if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.

innodb_additional_mem_pool_size – This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

innodb_log_file_size – Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.

innodb_log_buffer_size – Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory – it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.

innodb_flush_log_at_trx_commit – Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.

Neelesh Gurjar has written 122 articles

Leave a Reply