A quick note on MySQL tweaking.
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:
- Monitoring the services.
- Check logs.
- 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…
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
# 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@example.com ~]# 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)
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 restrting 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:
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.
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:
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.
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:
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.
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.
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.
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.
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.