MySQL tweaking – best practices
Why tweaking MySQL?
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.
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!!
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.
I.. Monitoring the services.
Yeah, this is the first thing you have to do on a server with high resource usage. 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."
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….