Why MySQL tweaking?

We all need fast, reliable and secure environment for our online space. Almost all websites has its own databases to store its data. MySQL is one of the most widely using relational database service to manage user databases. For a better performance we need MySQL tweaking.

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

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

Always do optimizations before going with hardware upgrade, like RAM, CPU and HDD.. We can save a lot of money by doing so! Simply,improve the performance of server without any hardware upgrade.

A proper MySQL optimization can drop the total load load on the server about half. Cool!!

Tips to optimise MySQL server!! MySQL tweaking..

How we optimize a MySQL server?

Tips : Do not restart the MySQL server, if you are going to execute a MySQL tuner script. It requires at-least 24 hours uptime MySQL service for its best check.

I like to divide the optimization process in three main parts, and other sub sessions. The main three parts are listed below:

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

These three are the core area that we need to cover for a better MySQL tweaking process.

I.. Monitoring the services.

Yeah, this is the first thing you have to do on a server with high resource usage. First step for MySQL tweaking. You can monitor the services on your server in many ways.. We already discussed some of the monitoring options in our previous articles..

Load monitoring in Linux servers – top, w and uptime commands’ usage for checking the load on server

This is a necessary thing to check the load-average on your server for its better and stable performance. In a Linux server/system we can check the load by different ways. High load on the server cause performance degrade. That means if you have a shared server with overload, the website on that server will load slowly normally. Read more…

Mytop

How to monitor MySQL databases using mytop – CLI tool?

In this article I’m explaining the usage of a smart command-line tool “mytop.” Which is similar in look to the basic resource monitoring tool “top.” By using this command, you can monitor the MySQL queries lively. There are a lot of helping switches are included with this tool. In this topic I’ll cover the installation steps and basic usage of mytop command for your Linux server. Read more…

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. See the examples pasted below:

Before executing the tuner script, we have to check something in 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. See the example added below:

[[email protected] ~]# mysqladmin variables|grep max_connections
| max_connections                                        | 151

You can also check all variables’ details from the MySQL command line prompt. That’s explained below:

Enter to MySQL command prompt:

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

Cool!!

Yeah, it’s showing the default MySQL value, 151. This value is okay for a normal server with average connections. If the websites in the server have large number of connections, you must need to increase its value as per the settings Apache configuration.

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 150

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

How to increase it?

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

Go ahead and set it as high.

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

SET GLOBAL max_connections = 1000; 

Refer this link for more details…

Tweak Cache and Buffers

Cache and Buffers, the next step for MySQL tweaking

Then go to some Cache and Buffers in MySQL configuration. Rearrange its value, to deliver MySQL its best performance.

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

3.1 query_cache_type

3.2 query_cache_size

3.3 query_cache_limit

This variable define the limit for query cache.

Do not cache results that are larger than this number of bytes. 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)

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 speedup 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 table space or in separate .idb file for each table.

This is ON bydefault 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. We already discussed how to use MySQL tuner to tweak MySQL. Please see the details pasted below:

Performance tuning script for MySQL – Command-line Mysql Tuner

MySQL has a lot of variables in its configuration. We can edit and set/alter variables to our requirements on our server. We can improve the MySQL performance by varying those values in configuration file. A lot of methods and shell scripts are available for tuning your MySQL server. Here I am explaining the installation and usage of one shell script for tuning the MySQL. Read more….

MySQL tweaking scripts has major role in MySQL tweaking. These are the basics in MySQL optimisation.
Please let us know your suggestions as comments.