How to reduce server load by MySQL caching and optimization

As the web traffic starts growing, the CMS based websites take more time to load and therefore MySQL server needs to be optimized or at least it should utilize available server resources judiciously in order to meet the future traffic demands. Database caching can significantly improve your CMS performance during peak hours. Although the main factor that affects database performance is how queries have been written but still significant performance boost can be achieved by tweaking MySQL settings. Lets learn how to tweak MySQL settings to optimize and improve its caching in order to get optimum performance and reduce server load.

The MySQL database server has a configuration file that allows us to change some parameters and configuration settings. The default settings may not solve your purpose, so you need to edit them in order to gain the maximum benefit. The file is called my.ini or my.cnf and is usually found in /etc/ or /etc/mysql/. You can use vi editor to open the file and edit it. Run the command below for this.

vi /etc/my.cnf

A screenshot of a sample configuration file for an optimized MySQL is shown below.

Lets discuss some important variables of the configuration file.

1. query_cache_size: MySQL allows you to cache the result of queries upto a certain size. This feature of MySQL can prove to be very useful in situations where the database has to repeatedly run the same queries on the same data set and return the same results each time. This result set can be cached by MySQL,thereby reducing the overhead of running through the data again and again and this can prove to be extremely helpful on busy servers.
2. key_buffer: The value of key_buffer determines the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will run and sooner the result will be returned. The value of the key_buffer should be in between one fourth to half of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).
   
   An easy way to check the actual performance of the buffer is to analyze four additional variables - key_read_requests, key_reads, key_write_requests and key_writes. These four variables should satisfy the following criteria:
   • key_read ÷ key_reads_requests <= 0.01
   • key_write ÷ key_writes_requests <=1
3. table_cache: The default value of table_cache is 64. Whenever MySQL accesses a table, it places it in the cache if possible. If the server accesses many tables, it is faster to have these in the cache. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.
4. sort_buffer: It can be useful when performing large numbers of sorts.
5. read_rnd_buffer_size: The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is allocated for each thread. This variable was renamed from record_rnd_buffer in MySQL 4.0.3. It defaults to the same size as the read_buffer_size. 1KB should be allocated for every 1MB of memory on the server, for example 2MB on a machine with 2GB memory.
6. thread_cache: For a busy server receiving a lot of quick connections, its thread cache should be high enough so that the threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the server.

Make the following changes in your my.cnf:

• In order to reduce memory usage, you can disable innodb and bdb. To do so just search in the configuration file for these terms - skip-bdb and skip-innodb and uncomment them i.e. remove the hash mark that lies in front of the lines containing these terms.
• Next locate the parameter key_buffer and change it accordingly as discussed above, for example 128MB for 1GB of RAM.
• Change the value of the parameter table_cache to 1024.
• Set query_cache_limit to 1M and query_cache_size to 32M for 1GB of RAM.

A sample my.cnf for a machine with 512MB of memory is shown below:

[mysqld]

socket  = /var/lib/mysql/mysql.sock

skip-locking

skip-bdb

skip-innodb

# Caches and Buffer Sizes

key_buffer = 256M

max_allowed_packet=16M

table_cache = 256

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 4M

record_buffer = 1M

myisam_sort_buffer_size = 128M

thread_cache = 128

query_cache_limit = 2M

query_cache_type = 1

query_cache_size = 32M

key_buffer = 16M

join_buffer = 2M

table_cache = 1024

#Time Outs

interactive_timeout = 100

wait_timeout = 100

connect_timeout = 10

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 2

# Maximum connections allowed

max_connections = 500

max_user_connections = 50

max_connect_errors = 10