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.<\/p>\n
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.<\/p>\n
Before You Tune<\/strong><\/p>\n 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.<\/p>\n 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.<\/p>\n Downloading the scripts<\/strong><\/p>\n Download both tuning scripts. These can be placed in whichever folder you wish. We\u2019ll use \/root for convenience.<\/p>\n Make the scripts executable with the following command:<\/p>\n Executing the Scripts<\/strong><\/p>\n Once the scripts are in place, we are ready to execute them.<\/p>\n 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.<\/p>\n Run mysqltuner.pl<\/p>\n 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.<\/p>\n We can edit the file and add\/modify the corresponding entries like so:<\/p>\n The updated file looks like this: In our example, we had already applied some changes. We’ll modify the corresponding entries according to the proposed tweaks. <\/p>\n 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\u2019ll set it at 3M here.<\/p>\n 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.<\/p>\n After making the adjustments described above, the tweak section of our my.cnf file should look as follows:<\/p>\n 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. <\/p>\n This output indicates that the tweaks made in response to mysqltuner.pl are likely to be beneficial.<\/p>\n 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.<\/p>\n With the results of the two scripts, we can be confident that the recommended tweaks will improve the performance of our MySQL instance.<\/p>\n Restart your MySQL instance<\/strong><\/p>\n To apply the tweaks made the MySQL configuration file (\/etc\/my.cnf), restart the mysql daemon.<\/p>\n 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.<\/p>\n 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. <\/p>\n","protected":false},"excerpt":{"rendered":" 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<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[61],"tags":[],"class_list":["post-3168","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"\nwget https:\/\/raw.githubusercontent.com\/major\/MySQLTuner-perl\/master\/mysqltuner.pl
\nwget https:\/\/launchpadlibrarian.net\/78745738\/tuning-primer.sh<\/code><\/p>\nchmod +x mysqltuner.pl
\nchmod +x tuning-primer.sh<\/code><\/p>\nroot@server [~]# .\/mysqltuner.pl<\/code>
\n >> MySQLTuner 1.2.0 - Major Hayden
\n >> Bug reports, feature requests, and downloads at http:\/\/mysqltuner.com\/
\n >> Run with '--help' for additional options and output filtering
\n-------- General Statistics --------------------------------------------------
\n[--] Skipped version check for MySQLTuner script
\n[OK] Currently running supported MySQL version 5.5.52-cll
\n[OK] Operating on 64-bit architecture
\n-------- Storage Engine Statistics -------------------------------------------
\n[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
\n[--] Data in MyISAM tables: 591M (Tables: 339)
\n[--] Data in InnoDB tables: 7G (Tables: 3641)
\n[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
\n[--] Data in MEMORY tables: 1M (Tables: 136)
\n[!!] Total fragmented tables: 467
\n-------- Security Recommendations -------------------------------------------
\n[OK] All database users have passwords assigned
\n-------- Performance Metrics -------------------------------------------------
\n[--] Up for: 4d 11h 54m 43s (526M q [1K qps], 1M conn, TX: 364B, RX: 235B)
\n[--] Reads \/ Writes: 92% \/ 8%
\n[--] Total buffers: 2.1G global + 6.5M per thread (512 max threads)
\n[OK] Maximum possible memory usage: 5.3G (34% of installed RAM)
\n[OK] Slow queries: 0% (433\/526M)
\n[OK] Highest usage of available connections: 12% (63\/512)
\n[OK] Key buffer size \/ total MyISAM indexes: 32.0M\/261.5M
\n[OK] Key buffer hit rate: 100.0% (418M cached \/ 178K reads)
\n[OK] Query cache efficiency: 83.5% (419M cached \/ 502M selects)
\n[!!] Query cache prunes per day: 15833584
\n[OK] Sorts requiring temporary tables: 0% (1K temp sorts \/ 30M sorts)
\n[!!] Joins performed without indexes: 145541
\n[!!] Temporary tables created on disk: 32% (7M on disk \/ 21M total)
\n[OK] Thread cache hit rate: 99% (2K created \/ 1M connections)
\n[!!] Table cache hit rate: 1% (5K open \/ 329K opened)
\n[OK] Open file limit used: 1% (866\/50K)
\n[OK] Table locks acquired immediately: 99% (185M immediate \/ 185M locks)
\n[!!] InnoDB data size \/ buffer pool: 7.7G\/2.0G
\n-------- Recommendations -----------------------------------------------------
\nGeneral recommendations:
\n Run OPTIMIZE TABLE to defragment tables for better performance
\n Enable the slow query log to troubleshoot bad queries
\n Adjust your join queries to always utilize indexes
\n When making adjustments, make tmp_table_size\/max_heap_table_size equal
\n Reduce your SELECT DISTINCT queries without LIMIT clauses
\n Increase table_cache gradually to avoid file descriptor limits
\nVariables to adjust:
\n query_cache_size (> 32M)
\n join_buffer_size (> 2.0M, or always use indexes with joins)
\n tmp_table_size (> 16M)
\n max_heap_table_size (> 16M)
\n table_cache (> 6000)
\n innodb_buffer_pool_size (>= 7G)<\/code><\/p>\nnano \/etc\/my.cnf<\/code><\/p>\n
\nmax_connections = 512
\nquery_cache_size = 32M
\nquery_cache_limit = 4M
\nthread_cache_size = 8
\ntable_cache = 12000
\ntable_definition_cache = 6000
\nopen_files_limit = 50000
\njoin_buffer_size = 2M
\nread_buffer_size = 2M
\nsort_buffer_size = 2M
\nkey_buffer_size = 32M
\ninnodb_buffer_pool_size = 2048M
\nmax_allowed_packet=268435456
\ndefault-storage-engine=MyISAM<\/code><\/p>\nquery_cache_size = 48M
\njoin_buffer_size = 3M
\ntmp_table_size = 32M
\nmax_heap_table_size = 32M
\ntable_cache = 12000
\ninnodb_buffer_pool_size = 3096M<\/code><\/p>\nmax_connections = 512
\nquery_cache_size = 48M
\nquery_cache_limit = 4M
\nthread_cache_size = 8
\ntable_cache = 12000
\nmax_heap_table_size = 32M
\ntmp_table_size = 32M
\ntable_definition_cache = 12000
\nopen_files_limit = 50000
\njoin_buffer_size = 3M
\nread_buffer_size = 2M
\nsort_buffer_size = 2M
\nkey_buffer_size = 32M
\ninnodb_buffer_pool_size = 3096M
\nmax_allowed_packet=268435456
\ndefault-storage-engine=MyISAM<\/code><\/p>\nroot@server [~]# .\/tuning-primer.sh <\/code><\/p>\n -- MYSQL PERFORMANCE TUNING PRIMER --
\n - By: Matthew Montgomery -
\nMySQL Version 5.5.52-cll x86_64
\nUptime = 4 days 12 hrs 5 min 53 sec
\nAvg. qps = 1353
\nTotal Questions = 526850500
\nThreads Connected = 13
\n<\/br>
\nServer has been running for over 48hrs.
\nIt should be safe to follow these recommendations
\nTo find out more information on how each of these
\nruntime variables effects performance visit:
\nhttp:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/server-system-variables.html
\nVisit http:\/\/www.mysql.com\/products\/enterprise\/advisors.html
\nfor info about MySQL's Enterprise Monitoring and Advisory Service
\n<\/br>
\nSLOW QUERIES
\nThe slow query log is NOT enabled.
\nCurrent long_query_time = 10.000000 sec.
\nYou have 433 out of 526850789 that take longer than 10.000000 sec. to complete
\nYour long_query_time seems to be fine
\n<\/br>
\nBINARY UPDATE LOG
\nThe binary update log is NOT enabled.
\nYou will not be able to do point in time recovery
\nSee http:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/point-in-time-recovery.html
\n<\/br>
\nWORKER THREADS
\nCurrent thread_cache_size = 8
\nCurrent threads_cached = 2
\nCurrent threads_per_sec = 0
\nHistoric threads_per_sec = 0
\nYour thread_cache_size is fine
\n<\/br>
\nMAX CONNECTIONS
\nCurrent max_connections = 512
\nCurrent threads_connected = 14
\nHistoric max_used_connections = 63
\nThe number of used connections is 12% of the configured maximum.
\nYour max_connections variable seems to be fine.
\n<\/br>
\nINNODB STATUS
\nCurrent InnoDB index space = 6.44 G
\nCurrent InnoDB data space = 7.70 G
\nCurrent InnoDB buffer pool free = 0 %
\nCurrent innodb_buffer_pool_size = 2.00 G
\nDepending on how much space your innodb indexes take up it may be safe
\nto increase this value to up to 2 \/ 3 of total system memory
\n<\/br>
\nMEMORY USAGE
\nMax Memory Ever Allocated : 2.47 G
\nConfigured Max Per-thread Buffers : 3.25 G
\nConfigured Max Global Buffers : 2.07 G
\nConfigured Max Memory Limit : 5.32 G
\nPhysical Memory : 15.53 G
\nMax memory limit seem to be within acceptable norms
\n<\/br>
\nKEY BUFFER
\nCurrent MyISAM index space = 261 M
\nCurrent key_buffer_size = 32 M
\nKey cache miss rate is 1 : 2345
\nKey buffer free ratio = 25 %
\nYour key_buffer_size seems to be too high.
\nPerhaps you can use these resources elsewhere
\n<\/br>
\nQUERY CACHE
\nQuery cache is enabled
\nCurrent query_cache_size = 32 M
\nCurrent query_cache_used = 22 M
\nCurrent query_cache_limit = 4 M
\nCurrent Query cache Memory fill ratio = 71.32 %
\nCurrent query_cache_min_res_unit = 4 K
\nMySQL won't cache query results that are larger than query_cache_limit in size
\n<\/br>
\nSORT OPERATIONS
\nCurrent sort_buffer_size = 2 M
\nCurrent read_rnd_buffer_size = 256 K
\nSort buffer seems to be fine
\n<\/br>
\nJOINS
\nCurrent join_buffer_size = 2.00 M
\nYou have had 145555 queries where a join could not use an index properly
\nYou should enable \"log-queries-not-using-indexes\"
\nThen look for non indexed joins in the slow query log.
\nIf you are unable to optimize your queries you may want to increase your
\njoin_buffer_size to accommodate larger joins in one pass.
\nNote! This script will still suggest raising the join_buffer_size when
\nANY joins not using indexes are found.
\n<\/br>
\nOPEN FILES LIMIT
\nCurrent open_files_limit = 50000 files
\nThe open_files_limit should typically be set to at least 2x-3x
\nthat of table_cache if you have heavy MyISAM usage.
\nYour open_files_limit value seems to be fine
\n<\/br>
\nTABLE CACHE
\nCurrent table_open_cache = 6000 tables
\nCurrent table_definition_cache = 6000 tables
\nYou have a total of 4157 tables
\nYou have 5857 open tables.
\nCurrent table_cache hit rate is 1%
\n, while 97% of your table cache is in use
\nYou should probably increase your table_cache
\n<\/br>
\nTEMP TABLES
\nCurrent max_heap_table_size = 16 M
\nCurrent tmp_table_size = 16 M
\nOf 14447005 temp tables, 32% were created on disk
\nPerhaps you should increase your tmp_table_size and\/or max_heap_table_size
\nto reduce the number of disk-based temporary tables
\nNote! BLOB and TEXT columns are not allow in memory tables.
\nIf you are using these columns raising these values might not impact your
\nratio of on disk temp tables.
\n<\/br>
\nTABLE SCANS
\nCurrent read_buffer_size = 2 M
\nCurrent table scan ratio = 1002 : 1
\nread_buffer_size seems to be fine
\n<\/br>
\nTABLE LOCKING
\nCurrent Lock Wait ratio = 1 : 31200
\nYour table locking seems to be fine<\/code><\/p>\nroot@server [~]# service mysql restart<\/code>
\nShutting down MySQL..... [ OK ]
\nStarting MySQL.. [ OK ]
\nroot@server [~]#<\/code><\/p>\nConclusion<\/h2>\n