What is MySQLTuner

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.

In this blog, we are discussing the parameters which are updated and the configuration files of MySQL which are changed for the optimum performance. This script is a read-only script which gives suggestions that can be made to MySQL configuration but it cannot modify the MySQL configuration by its own, one have to make the changes manually.

MySQLTuner Script

 http://mysqltuner.com/mysqltuner.pl 

The above script is used for analysing what changes are to be made in the configuration files.
First of all, we need to know how our server resources are performing before running the script.
Never run the MySQLTuner script immediately after powering on the server or a reboot because the script works by analyzing the traffic and connections on MySQL which will be limited at this time.

How to download and run the MySQLTuner script

1. Choose any location and download the MySQLTuner Perl script, let it be /usr/local/src

 cd /usr/local/src 

2. Download the script from the URL http://mysqltuner.com/mysqltuner.pl

wget 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.

After running the script we will get a similar result as on the screenshot.

Let us go through the various sections, different variables and how they work.

By analysing the screenshots we alter our /etc/my.cnf file preferably for better performance.

In the Security Recommendations section,

From screenshot 5 you can see few lines under the “Security Recommendations” section with [OK] on it and few has two exclamation marks[!!].

Those values which shows OK are performing correctly but, those showing warnings should be corrected by cross checking the general recommendation section if possible.

Security Recommendations

[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned

From the above section It is clear that the server has no anonymous accounts for any database users and all DB users have passwords assigned.

Security Recommendations

[!!] User ‘user123@%’ does not specify hostname restrictions.
[!!] User ‘testuser1@%’ does not specify hostname restrictions.
[!!] User ‘testdb_user@%’ has no password set.

Above one is a warning showing in security recommendations section because the users user123, testuser1, testdb_user does not specify hostname restrictions. MysqlTuner will check for users without passwords, users with username as password and also check for weak passwords.

Now, let us take another example Performance Metrics

In this section we can see few parameters below

[OK] Maximum reached memory usage: 477.2M (23.93% of installed RAM)
[OK] Maximum possible memory usage: 836.0M (41.92% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (93/50M)

Here, the maximum possible memory usages are less than 50%. Also there are no slow working queries. The system should maintain the percentage of slow queries to

There are various other sections as you can see from the screenshots. The script itself gives a general recommendation section which can be referred to update the variables and its values.

The most important two section of this MySQLTuner are General recommendations and Variables to adjust. Go through the general recommendations and update the variables for optimum performance. The variables and recommendations may vary from server to server.

Variables to adjust

Below is a sample of variables when run on a test server:

query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
table_open_cache (> 400)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 400.4M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

Query_cache_size

The query_cache_size variable indicates the particular amount of memory allocated for the cache in bytes. If query_cache_size value is 0, query caching will be off.
The default query_cache_size is 1MB and it can be set upto 4GB but lower values are preferred for better performance.

According to mysqltuner, increasing the query_cache size over 128M may reduce performance.
Very large query cache size leads to significant performance degradation due to cache overhead and locking issues. If query cache is larger the more system time is used for locks, flushes and cache overhead. High values are recommended only for sites whose tables are not modified frequently.

Setting the query_cache_type=0 will disable it, and the significant cache overhead in query execution is reduced.

Query_cache_type

The values of query_cache_type variable range from 0 to 2.

Value = 0 indicates that query caching is turned off.
Value = 1 means that query caching is turned on, with the exception of SELECT statements using the SQL_NO_CACHE option.
Value = 2 provides query caching for SELECT statements running with the SQL_CACHE option.

In the performance matrix section we can see

[!!] Query cache may be disabled by default due to mutex contention.

This warning occurs when we execute MySQLTuner because it is turned off.

By default it is turned off just turn it ON

Query_cache_limit

It is defined as the maximum amount of a single resultset in cache. The default value of this parameter is 1MB and very high values are not recommended.

Always make sure that query cache ratio should be maintained to a lower value for better performance.

Setting this to a very high value may cause cache to fill up too fast.

Tmp_table_size

tmp_table_size is the maximum size of internal memory temporary tables.

If tmp_table_size is not enough as MySQL also looks at max_heap_table_size variable and uses lower value as a limit for in memory temporary table after which it will be converted to on-disk table defined as per internal-tmp-disk-storage-engine.

The tmp_table_size and max_heap_table_size should be the same value.

In most of the servers we tested it is recommended to keep the value greater than 16M

tmp_table_size (> 16M)

Max_heap_table_size

max_heap_table_size is a type of memory table that can set a max_heap_table_size system variable to impose a maximum size on memory tables.

max_heap_table_size (> 16M)

Thread_cache_size

The thread_cache_size is the amount of threads that the server should cache.

If a client disconnects, then the threads are put in the cache if they are less than the respective thread_cache_size.

The suggested value is 4 which will be fine unless you get a high number of concurrent users.

To improve the performance of MySQL the thread_cache_size is set to a high value.

Table_open_cache

To improve performance table_open_cache variable is very much useful even if it uses some extra memory.

This number represents the maximum number of tables which the server can keep open a single table cache instance.

Before MariaDB 10.1.7 the default value was 400 and table_open_cache has now defaulted to 2000.

Key_buffer

For allocating more memory to MySQL we can update the value of key_buffer. Another advantage is we can speed up the databases by controlling the value.

The value of variable should not exceed 25 percentage of the total system memory in MyISAM engine and 70 in InnoDB. Setting this variable to a very high value results in resource wastage.

Join_buffer_size

From MySQL documentation the join_buffer_size is described as: “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.”

Unlike other buffer sizes join_buffer_size is used per connection. This buffer can be used for joining two tables without indexes.

we should reduce join_buffer_size to a value between 128K and 256K for a normal site.

join_buffer_size can be updated upto 64M to manage scalability problems of a high-traffic site whose queries are not particularly optimized.

It is recommended to keep its value less as it allocates memory per connection.

Max_allowed_packet

The value of max_allowed_packet describes the maximum size of the packet that can be shared to a client. It can be a single row, a table or logs send from a master to a client. This value should be set to the maximum value of the packet you are trying to send. In case you exceed the limit you will receive an error in MySQL logs.

Innodb_buffer_pool_size

The innodb_buffer_pool_size is one of the most important variables. It is a memory space that holds many in-memory data structures of InnoDB. This variable should be configured based on the available system RAM.

Recommended range: 60~80% of total RAM

It is anticipated to occupy most of the RAM on a dedicated MySQL/Innodb server, but of course other services may affect how this variable is tuned.

If this is set too big, it degrade your performance rapidly.

Performance_schema

The Performance Schema is enabled by default as of MySQL 5.6.6. It is a tool designed for monitoring what is happening inside MySQL database server by collecting various aspects form MySQL performance. It can help you fine tune the MySQL server Performance.

Now, I hope you have got a basic idea about the MySQLtuner and its variables.

Let’s see an example to understand how these changes take place.

I have tested this on a VM with 1G of RAM and CPU core 1 with 25G disk. The traffic on server was high and mysqld was using more than 75 percentage of CPU.

I disabled query_cache in /etc/my.cnf

query_cache_size=128M
query_cache_type=0
query_cache_limit = 8M

Then, I ran a select operation on a table named “word” on one of the largest DB named “wordpress”.

select * from word;
And got the below results:
| 156311 | storehouse |
| 156312 | value-addition |
| 156313 | stephene |
| 156314 | mallarme |
| 156315 | vocation |
| 156316 | `kavisena |
| 156317 | telugus |
| 156318 | `distance |
| 156319 | jnanpeeth |
| 156320 | india’s |
+——–+——————————————-+
156320 rows in set (0.16 sec)

After that, I enabled query_cache in /etc/my.cnf and found that it is using very low time frame to execute the operation at the second time.

query_cache_size=128M
query_cache_type=1
query_cache_limit = 8M
| 156311 | storehouse |
| 156312 | value-addition |
| 156313 | stephene |
| 156314 | mallarme |
| 156315 | vocation |
| 156316 | `kavisena |
| 156317 | telugus |
| 156318 | `distance |
| 156319 | jnanpeeth |
| 156320 | india’s |
+——–+——————————————-+
156320 rows in set (0.13 sec)

No big changes, aey? It is because the query will be cached on the first run and the execution time will be more or less the same. Run it again and you will see the difference.

| 156311 | storehouse |
| 156312 | value-addition |
| 156313 | stephene |
| 156314 | mallarme |
| 156315 | vocation |
| 156316 | `kavisena |
| 156317 | telugus |
| 156318 | `distance |
| 156319 | jnanpeeth |
| 156320 | india’s |
+——–+——————————————-+
156320 rows in set (0.03 sec) 

Conclusion

We can conclude that each servers performance requirement is unique so, it is always the user who need to decide the change of these variables to optimise MySQL and simply pasting these values may cause unexpected results.

Thanks for dropping by. Ready for the next blog?

Azure Site Recovery for Business Continuity and Disaster Recovery

The post Optimize MySQL server performance using MySQLTuner appeared first on Sysally.