MySQL tweaking – best practices and tips for SysAdmin

Need for MySQL tweaking

Everyone needs a high-speed, stable and safe environment for our online space. Almost all websites have its private databases to store its data. MySQL is one of the most widely using relational database service to maintain user databases. For an excellent performance, we require MySQL tweaking.

It’s open source and its usages are really simple and user-friendly.

In a shared server, the MySQL service itself can produce high load (CPU usage), memory usages or any other resources usages and then it makes the system irresponsive. A proper optimization can bring things down to its normal state.

Continuously do optimizations before performing a hardware upgrades, like RAM, CPU, and HDD. A proper MySQL optimization can drop the total load on the server about half.

How to optimize a MySQL server?

Note: It demands at least 24 hours uptime MySQL service for its best analysis.  And so, don't restart the MySQL server, in case if you are going to execute a MySQL tuner script. 

Now let's divide the optimization process into three main parts and other sub-sessions.

1. Monitoring the services.
2. Check logs.
3. Use some diagnostic tools.

I)  Monitoring the services

Yes, this is the first step for MySQL tweaking. You have to monitor the services of your server in several ways.

Refer above monitoring tools, those will help you to start the MySQL optimization process. After that, we can use the following command to find out the MySQL processes details:

# mysqladmin processlist

Or

# mysqladmin proc stat

This command will give you a summary too at its bottom section. 

Before executing the tuner script, we have to check something in the current configuration. The MySQL configuration file is “/etc/my.cnf“

I start tweaking by checking the variable “max_connections“

If it’s not in the configuration file, execute the following command to check its current value:

mysqladmin variables|grep max_connections

You need to execute this as root user. 

You can also check all variable's details from the MySQL command line prompt. 

Go to web server (Apache) configuration and check the value for “max_client.”

[root@aries ~]# grep -i Maxclient /usr/local/apache/conf/httpd.conf
MaxClient 140

Note: In Apache 2.4 the variable name "MaxClient" has changed as "MaxRequestWorkers."

How to increase the value of “max_connections”?

You need to increase the value of “max_connections,” if your web server has a high MaxRequestWorkersvalue. Otherwise, it will create some bottleneck situations at high connection time. MySQL can’t manage query from those connections if it doesn’t have large “max_connections” directive.

Go ahead and set it as high.

Tips : You can set MySQL variables before restrting the service using "SET GLOBAL" variables. 

SET GLOBAL max_connections = 1000; 

Refer this link for more details.

Tweak Cache and Buffers

The next step for MySQL tweaking are Cache and Buffers

1. Thread Cache Size

This is off by default. This is the number of threads the MySQL server should cache for reuse. This should be enabled for a good performance.

When a client disconnect, the client’s threads will put into the cache space if the number is less than the thread_cache_size.

Refer the below links for best practices:

MySQL doc : thread_cache_size
Question in serverfault : What value of thread_cache_size should I use?

If you see hundreds of connections per second, you can increase its value to a higher number.

2. 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. This one of the important parameter for MySQL tweaking.

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.

3. Query Cache

This is the amount of memory allocated for caching query results. This will speed up results from MySQL databases. This variable is ON by default. The main three variables associated with Query Cache are listed below:

# query_cache_type
# query_cache_size
# query_cache_limit

This variable defines the limit for query cache.

Do not cache results that are larger. The default value is 1MB.

Buffers

This variable is depended with MySQL engine. InnoDB and MyIsam engines have different buffer variables. Index blocks for MySQL tables are buffered and are shared by all threads. Main buffer variables that we want to check are listed below:

For MyIsam engine…

1. Key Buffer Size

Index blocks for MyISAM tables are buffered and are shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache.

Refer : key-buffer-size

2. Read Buffer Size (read-buffer-size)

Determines the maximum number of bytes to be read from the transaction log during each scan. Because the LogReader reads bytes, it requires a buffer to store the bytes read.

3. Join Buffer Size (join-buffer-size)

The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible.

Refer : join-buffer-size

InnoDB is the default engine since MySQL 5.6. Important buffer cache settings in InnoDB are listed below:

1. innodb_buffer_pool_size

This is one of the important settings to look after the installation of MySQL which has InnoDB enabled. The buffer pool is where data and indexes are saved.

The default value is 128MB. The maximum value depends on the CPU architecture.

Refer : MySQL doc.

Some suggested values

5-6 GB for 8 GB server.
20-25 GB for 38 GB server.
100-120 GB for 128 GB server.

2. innodb_log_file_size

This variable helps to speed up the writes and crash recovery. Crash recovery performance has improved a lot since MySQL 5.5. Upto MySQL 5.5 the size of log file was limited to 4 GB and starts from 512 MB.

Since MySQL 5.6 we can start this from 4GB.

3. innodb_file_per_table

This directive tells InnoDB to store data and indexes of a database to shared tablespace or in separate .idb file for each table.

This is ON by default from MySQL 5.6.

Hmmm… Those are the important Cache and Buffer variables to check as an initial step.

II) Checking MySQL log files.

MySQL has 3 types of important log files.

1. Error log

MySQL error log is located under MySQL data directory /var/lib/mysql. By default the error log is /var/lib/mysql/hostname.err

2. General log

By default it is disabled. You can enable it nu adding entry in MySQL configuration file.

3. Slow query log

Is disabled by default. This is one of the important log file to save slowly loading databases. Enable slow query logging by adding the following entry in MySQL configuration.

slow_query_log=/var/lib/mysql/slow.log

The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. The minimum and default values of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds.

More..

III) Diagnostic tools - MySQL tuner

Tuner scripts are available to check the MySQL performance.