MySQLTuner is a simple Perl script which will help you to collect necessary information and increase the performance and stability of your MySQL configuration by updating its variables/parameters. So lets get started how to Optimize MySQL server performance using MySQLTuner.
- Choose any location and download the MySQLTuner Perl script, let it be /usr/local/src
cd /usr/local/src
- Download the script from the URL http://mysqltuner.com/mysqltuner.pl
3. Make the script executable and run the script
chmod +x mysqltuner.pl
./mysqltuner.pl
Before making any changes, backup the configuration files for safety.
This test run was made on a Plesk server and this will help you understand what are these parameters and how can it be converted productively.
MySQL Server Configuration:
Well, first you should know where the configuration file is located depending on your operating system. In Linux systems, it is located in “/etc/mysql/my.cnf”.
Now it is time to pick your engine InnoDB vs. MyISAM. To make the choice easier, you should know that InnoDB became the default engine starting of MySQL 5.5 as it supports “row level locking, foreign keys and transactions” and MyISAM doesn’t support any of the features mentioned which make it rarely useful in modern applications.
After picking the right engine, it is time for setting some configuration variables in my.cnf file.
- max_connection Variable:
The max_connection variable represents how many connections are allowed from your application. The default value is 151 connection, however, you can easily increase this number if you got an error says ‘MySQL Error, Too many connections…’
max_connections = 170
- innodb_buffer_pool_size Variable:
To speed up things, MySQL will cache data in your server memory, and this variable is telling MySQL how many GigaBytes it can use. This variable is very useful if you save big blobs in your database. You can set it to 80–90% of your server memory. So if your server memory is 16GB, you can set it to 14GB.
innodb_buffer_pool_size = 14GB
- innodb_io_capacity Variable:
This variable tells MySQL how many Inputs/Outputs operation it can use and it depends on your disk. For example, a single 7200 RPM drive is limited to 200 I/O and an enterprise SSD disk is 50,000 I/O. You can easily find inputs/outputs values by command line on your operating system and set the variable to 90% of the available I/O, so MySQL will never use too many I/O operations.
innodb_io_capacity = 21000
- query_cache_limit and query_cache_size Variables:
Well, MySQL caches data in memory too, however, we can’t rely on it as a caching system since every time your application writes to a database table, MySQL will reset the query cache for the entire table. So, if your application has a high rate load, the MySQL cache will be completely useless, and in that case, it would be better to set these two variables to zero saving the overhead of managing the MySQL cache, and instead you can use something like Redis to manage the cache.
query_cache_limit = 0
query_cache_size = 0
- Slow Query Log:
The slow query log will show you which queries are taking longer than the threshold you defined without the need to guess which query is slower.
First, you have to enable slow_query_log in your configuration file. On Linux server, open “/etc/mysql/my.cnf” or equivalent on your system.
and add:
slow_query_log = 1
long_query_time = 1
Well, these two options will enable the slow query log and will log any query that takes more than one second. If you prefer to see your logs in a table instead of a file, you may add:
log_output = ‘TABLE’
Then you can find your logs in a table called ‘slow_log,’ where you can see information about all the slow queries that take more than one second to execute. The information includes when exactly the query is executed and how many rows are affected by the query and which user executed it.
A query is a coded request to search the database for data that matches a certain value. There are some query operators that, by their very nature, take a long time to run. If your environment relies on automated queries, they may be impacting performance. Check and terminate MySQL processes that may pile up in time.
Use Indexes Where Appropriate
Many database queries use a structure similar to this:
SELECT … WHERE
These queries involve evaluating, filtering, and retrieving results. You can restructure these by adding a small set of indexes for the related tables. The query can be directed at the index to speed up the query.
Functions in Predicates
Avoid using a function in the predicate of a query. For example:
SELECT * FROM MYTABLE WHERE UPPER(COL1)='123'
The UPPER notation creates a function, which has to operate during the SELECT operation. This doubles the work the query is doing, and you should avoid it if possible.
Avoid % Wildcard in a Predicate
The wildcard % stands for zero or more characters. If your query looks like this:
SELECT * FROM MYTABLE WHERE COL1 LIKE '%123'
The expression ‘%123’ includes all values that end with 123. The % sign stands for any type or number of characters preceding, including no characters. The query must now perform a full table scan to test for this condition.
Specify Columns in SELECT Function
A common expression is to use SELECT * to scan all of the database columns. If you specify the columns you need, your query won’t need to scan irrelevant columns.
Use ORDER BY Appropriately
The ORDER BY expression sorts results by the specified column. It can be used to sort by two columns at once. These should be sorted in the same order, ascending or descending. If you try to sort different columns in different order, it will slow down performance. You may combine this with an index to speed up the sorting.
