Call us at 1-888-GTCOMM1
The MySQL relational database management system is highly configurable. Once you have installed MySQL on your Linux server there are an enormous number of ways that you may tune it to suit your needs. This present tutorial is designed to introduce you to the basics of MySQL tuning by taking you through the deployment and execution of a few tuning scripts. This tutorial should be applicable to any variety of Linux server.
Tutorial
In order to tune your MySQL instance exactly as you want it you need to know what all the MySQL configuration variables are and what they do. However, there are a few scripts out there that will enable you to accomplish a fair bit of tuning without such extensive knowledge. We will show you how to use two of the most widely used tuning scripts to get you started. Once we have shown you the basics, you will be ready to learn more by delving into the manual tuning sections of the official documentation for MySQL official documentation.
Before You Tune
Tuning scripts are typically designed to provide you with recommendations for how to tune your MySQL instance based on how MySQL is used on your server. Therefore, it is imperative that you only run tuning scripts when the MySQL instance has been up and running for at least the previous 24 hours. Furthermore, the traffic from the previous 24 hours needs to have been typical of the traffic on the server. Assuming you have had normal traffic, the recommended sampling time should provide the scripts with enough reliable data on your MySQL instance to make recommendations that will make your configuration serve your needs better.
As you may have guessed, tuning is not generally effective in development environments. Tuning is designed to take advantage of the use metrics in your production environment to drive changes in the configuration of that instance. The development environment simply will not see such traffic.
Downloading the scripts
Download both tuning scripts. These can be placed in whichever folder you wish. We’ll use /root for convenience.
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
wget https://launchpadlibrarian.net/78745738/tuning-primer.sh
Make the scripts executable with the following command:
chmod +x mysqltuner.pl
chmod +x tuning-primer.sh
Executing the Scripts
Once the scripts are in place, we are ready to execute them.
The first script (mysqltuner.pl) is designed to provide recommendations. This script is especially useful for beginners. The second script (tuning-primer.sh) simply provides data. The latter is recommended for advanced users, as the data it provides will point such users to the configuration parameters they need to change in order to optimize their setup.
Run mysqltuner.pl
root@server [~]# ./mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.52-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 591M (Tables: 339)
[--] Data in InnoDB tables: 7G (Tables: 3641)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 1M (Tables: 136)
[!!] Total fragmented tables: 467
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 4d 11h 54m 43s (526M q [1K qps], 1M conn, TX: 364B, RX: 235B)
[--] Reads / Writes: 92% / 8%
[--] Total buffers: 2.1G global + 6.5M per thread (512 max threads)
[OK] Maximum possible memory usage: 5.3G (34% of installed RAM)
[OK] Slow queries: 0% (433/526M)
[OK] Highest usage of available connections: 12% (63/512)
[OK] Key buffer size / total MyISAM indexes: 32.0M/261.5M
[OK] Key buffer hit rate: 100.0% (418M cached / 178K reads)
[OK] Query cache efficiency: 83.5% (419M cached / 502M selects)
[!!] Query cache prunes per day: 15833584
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 30M sorts)
[!!] Joins performed without indexes: 145541
[!!] Temporary tables created on disk: 32% (7M on disk / 21M total)
[OK] Thread cache hit rate: 99% (2K created / 1M connections)
[!!] Table cache hit rate: 1% (5K open / 329K opened)
[OK] Open file limit used: 1% (866/50K)
[OK] Table locks acquired immediately: 99% (185M immediate / 185M locks)
[!!] InnoDB data size / buffer pool: 7.7G/2.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 32M)
join_buffer_size (> 2.0M, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_cache (> 6000)
innodb_buffer_pool_size (>= 7G)
In our example, the output of the script indicates we should increase the value of a few parameters in our primary MySQL configuration file, my.cnf.
We can edit the file and add/modify the corresponding entries like so:
nano /etc/my.cnf
The updated file looks like this:
max_connections = 512
query_cache_size = 32M
query_cache_limit = 4M
thread_cache_size = 8
table_cache = 12000
table_definition_cache = 6000
open_files_limit = 50000
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 2M
key_buffer_size = 32M
innodb_buffer_pool_size = 2048M
max_allowed_packet=268435456
default-storage-engine=MyISAM
In our example, we had already applied some changes. We’ll modify the corresponding entries according to the proposed tweaks.
The script is not perfect. Some of its recommendations are counterproductive. For instance, the script will often incorrectly indicate that the join_buffer_size needs to be raised in response to the execution of non-indexed join inquiries. Regardless of the scripts output, we have found that keeping the join_buffer_size under 4M consistently helps maximize performance. We’ll set it at 3M here.
Another parameter the script often recommends increasing is the innodb_buffer_pool_size. When increasing this, make sure you do not set it such that you exceed the RAM memory available on your server. If you do so, your server will probably crash when the buffer pool size approaches its maximum. In our scenario, our system has about 2 to 3GB of usable RAM. To be safe, we will set the innodb buffer pool to be about half of that, or 1024 M. If the script recommends you increase this buffer pool size beyond what your server can handle, we would recommend you add more RAM so that your server can meet your needs.
query_cache_size = 48M
join_buffer_size = 3M
tmp_table_size = 32M
max_heap_table_size = 32M
table_cache = 12000
innodb_buffer_pool_size = 3096M
After making the adjustments described above, the tweak section of our my.cnf file should look as follows:
max_connections = 512
query_cache_size = 48M
query_cache_limit = 4M
thread_cache_size = 8
table_cache = 12000
max_heap_table_size = 32M
tmp_table_size = 32M
table_definition_cache = 12000
open_files_limit = 50000
join_buffer_size = 3M
read_buffer_size = 2M
sort_buffer_size = 2M
key_buffer_size = 32M
innodb_buffer_pool_size = 3096M
max_allowed_packet=268435456
default-storage-engine=MyISAM
Next, we will run tuning-primer.sh using the command below. Do this before you restart the mysql daemon. This script will provide us data that will help us to know if the tweaks we intend to apply are likely to help. In this scenario, the second script really just serves to confirm what the first script said.
root@server [~]# ./tuning-primer.sh
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.5.52-cll x86_64
Uptime = 4 days 12 hrs 5 min 53 sec
Avg. qps = 1353
Total Questions = 526850500
Threads Connected = 13
Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 433 out of 526850789 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 2
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 512
Current threads_connected = 14
Historic max_used_connections = 63
The number of used connections is 12% of the configured maximum.
Your max_connections variable seems to be fine.
INNODB STATUS
Current InnoDB index space = 6.44 G
Current InnoDB data space = 7.70 G
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 2.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE
Max Memory Ever Allocated : 2.47 G
Configured Max Per-thread Buffers : 3.25 G
Configured Max Global Buffers : 2.07 G
Configured Max Memory Limit : 5.32 G
Physical Memory : 15.53 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
Current MyISAM index space = 261 M
Current key_buffer_size = 32 M
Key cache miss rate is 1 : 2345
Key buffer free ratio = 25 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere
QUERY CACHE
Query cache is enabled
Current query_cache_size = 32 M
Current query_cache_used = 22 M
Current query_cache_limit = 4 M
Current Query cache Memory fill ratio = 71.32 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 2.00 M
You have had 145555 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
OPEN FILES LIMIT
Current open_files_limit = 50000 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_open_cache = 6000 tables
Current table_definition_cache = 6000 tables
You have a total of 4157 tables
You have 5857 open tables.
Current table_cache hit rate is 1%
, while 97% of your table cache is in use
You should probably increase your table_cache
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 14447005 temp tables, 32% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.
TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 1002 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 31200
Your table locking seems to be fine
This output indicates that the tweaks made in response to mysqltuner.pl are likely to be beneficial.
The tuning-primer.sh script shows that the table cache was getting full. An increase will help. The same is true of our indoor buffer pool. Recall that we had raised the join_buffer_size in response to the large amount of join queries without indexes; however the present script indicates, as we predicted, that such an increase will not address the performance issue. Rather, we will need to add indexes to the join queries in question.
With the results of the two scripts, we can be confident that the recommended tweaks will improve the performance of our MySQL instance.
Restart your MySQL instance
To apply the tweaks made the MySQL configuration file (/etc/my.cnf), restart the mysql daemon.
root@server [~]# service mysql restart
Shutting down MySQL..... [ OK ]
Starting MySQL.. [ OK ]
root@server [~]#
Your MySQL instance has been tuned. Going forward, further tuning may be necessary if there are changes in server traffic related to your databases. If you conclude that the performance of your MySQL instance is still not what you want it to be, let your server run for 24 to 48 hours and run the scripts again. We suggest you repeat this process until you have no further recommendations to apply on your server.
Conclusion
Your MySQL instance should now perform better than ever. With the knowledge you have gained here, you are ready to explore further tuning using the official MySQL documentation.
