{"id":87,"date":"2009-07-17T00:29:20","date_gmt":"2009-07-17T06:29:20","guid":{"rendered":"http:\/\/blog.supportpro.com\/?p=87"},"modified":"2026-01-09T04:54:11","modified_gmt":"2026-01-09T10:54:11","slug":"mysql-tweaking","status":"publish","type":"post","link":"https:\/\/www.supportpro.com\/blog\/mysql-tweaking\/","title":{"rendered":"How to Optimize MySQL Without Breaking Your Server ?"},"content":{"rendered":"<p><span style=\"font-weight: 400;\">Optimizing MySQL performance is critical for high-traffic applications. This guide explains how to monitor and tune MySQL for <\/span><b>efficient resource usage<\/b><span style=\"font-weight: 400;\">, focusing on modern defaults, InnoDB, and current best practices.<\/span><\/p>\n<h2><b>1. Check MySQL Status and Variables<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">Log in to your MySQL server:<\/span><\/p>\n<p><b><i>mysql -u root -p<\/i><\/b><\/p>\n<p><span style=\"font-weight: 400;\">Check general statistics:<\/span><\/p>\n<p><b><i>SHOW STATUS LIKE &#8216;%tables%&#8217;;<\/i><\/b><\/p>\n<p><b><i>SHOW VARIABLES LIKE &#8216;table_open_cache&#8217;;<\/i><\/b><\/p>\n<p><b>Analysis:<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Open_tables <\/b><span style=\"font-weight: 400;\">\u2013 Number of tables currently open<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Opened_tables<\/b><span style=\"font-weight: 400;\"> \u2013 Tables opened because the cache was too small<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<\/ul>\n<p><b>Tuning tip:<\/b><b><br \/><\/b><span style=\"font-weight: 400;\">If <\/span><b>Opened_tables<\/b><span style=\"font-weight: 400;\"> increases rapidly, increase <\/span><b>table_open_cache<\/b><span style=\"font-weight: 400;\"> in your <\/span><b>my.cnf <\/b><span style=\"font-weight: 400;\">:<\/span><\/p>\n<p><b><i>[mysqld]<\/i><\/b><\/p>\n<p><b><i>table_open_cache = 4000<\/i><\/b><\/p>\n<h2><b>2. Thread Cache<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">Check thread usage:<\/span><\/p>\n<p><b><i>SHOW STATUS LIKE &#8216;Threads_created&#8217;;<\/i><\/b><\/p>\n<p><b><i>SHOW STATUS LIKE &#8216;Threads_connected&#8217;;<\/i><\/b><\/p>\n<p><b>Explanation:<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Threads_created<\/b><span style=\"font-weight: 400;\"> increases each time MySQL must create a new thread for a connection<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>Threads_connected<\/b><span style=\"font-weight: 400;\"> shows active connections<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<\/ul>\n<p><b>Tuning tip:<\/b><b><br \/><\/b><span style=\"font-weight: 400;\">Increase <\/span><b>thread_cache_size<\/b><span style=\"font-weight: 400;\"> if <\/span><b>Threads_created<\/b><span style=\"font-weight: 400;\"> rises quickly, especially under high connection rates:<\/span><\/p>\n<p><b><i>[mysqld]<\/i><\/b><\/p>\n<p><b><i>thread_cache_size = 50<\/i><\/b><\/p>\n<p><span style=\"font-weight: 400;\">This allows threads to be reused, improving performance.<\/span><\/p>\n<h2><b>3. Key Buffer (for MyISAM)<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">Although <\/span><b>InnoDB<\/b><span style=\"font-weight: 400;\"> is the default storage engine, some legacy tables may still use MyISAM. Check:<\/span><\/p>\n<p><b><i>SHOW VARIABLES LIKE &#8216;key_buffer_size&#8217;;<\/i><\/b><\/p>\n<p><b><i>SHOW STATUS LIKE &#8216;Key_read%&#8217;;<\/i><\/b><\/p>\n<p><b><i>SHOW STATUS LIKE &#8216;Key_write%&#8217;;<\/i><\/b><\/p>\n<p><b>Guidelines:<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">key_reads \/ key_read_requests &lt; 0.01<\/span><span style=\"font-weight: 400;\"> \u2192 key_buffer_size is sufficient<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Increase <\/span><span style=\"font-weight: 400;\">key_buffer_size<\/span><span style=\"font-weight: 400;\"> only if MyISAM tables are heavily used<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<\/ul>\n<p><b><i>[mysqld]<\/i><\/b><b><i><br \/><\/i><\/b><b><i>key_buffer_size = 256M<\/i><\/b><\/p>\n<p><span style=\"font-weight: 400;\">For modern MySQL, <\/span><b>InnoDB buffer pool tuning<\/b><span style=\"font-weight: 400;\"> is far more important than key_buffer_size.<\/span><\/p>\n<h2><b>4. InnoDB Buffer Pool (Modern Tuning)<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">For InnoDB-heavy databases (default in MySQL 5.7+):<\/span><span style=\"font-weight: 400;\">\u00a0<\/span><\/p>\n<p><b><i>SHOW VARIABLES LIKE &#8216;innodb_buffer_pool_size&#8217;;<\/i><\/b><\/p>\n<p><b><i>SHOW STATUS LIKE &#8216;Innodb_buffer_pool%&#8217;;<\/i><\/b><\/p>\n<p><b>Tuning tips:<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Set\u00a0 to ~70-80% of available RAM for dedicated database servers<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Monitor <\/span><span style=\"font-weight: 400;\">Innodb_buffer_pool_reads<\/span><span style=\"font-weight: 400;\"> vs <\/span><span style=\"font-weight: 400;\">Innodb_buffer_pool_read_requests<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Higher buffer pool size reduces disk reads and improves performance<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<\/ul>\n<p><b><i>[mysqld]<\/i><\/b><\/p>\n<p><b><i>innodb_buffer_pool_size = 8G<\/i><\/b><\/p>\n<p><b><i>innodb_buffer_pool_instances = 8<\/i><\/b><\/p>\n<h2><b>5. Connection Limits and Timeout<\/b><\/h2>\n<p><b><i>SHOW VARIABLES LIKE &#8216;max_connections&#8217;;<\/i><\/b><\/p>\n<p><b><i>SHOW VARIABLES LIKE &#8216;wait_timeout&#8217;;<\/i><\/b><\/p>\n<p><b><i>SHOW STATUS LIKE &#8216;Max_used_connections&#8217;;<\/i><\/b><\/p>\n<p><b>Guidelines:<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">max_connections<\/span><span style=\"font-weight: 400;\"> \u2013 Max concurrent connections<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">wait_timeout<\/span><span style=\"font-weight: 400;\"> \u2013 Seconds before idle connections are closed<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">Example tuning:<\/span><\/p>\n<p><b><i>[mysqld]<\/i><\/b><\/p>\n<p><b><i>max_connections = 500<\/i><\/b><\/p>\n<p><b><i>wait_timeout = 15<\/i><\/b><\/p>\n<p><b><i>max_connect_errors = 100<\/i><\/b><\/p>\n<p><span style=\"font-weight: 400;\">Adjust these based on your traffic patterns to avoid resource exhaustion.<\/span><\/p>\n<h2><b>6. Slow Query Logging<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">Logging slow queries helps identify performance bottlenecks.<\/span><\/p>\n<p><b><i>[mysqld]<\/i><\/b><\/p>\n<p><b><i>slow_query_log = 1<\/i><\/b><\/p>\n<p><b><i>slow_query_log_file = \/var\/log\/mysql\/slow.log<\/i><\/b><\/p>\n<p><b><i>long_query_time = 5<\/i><\/b><\/p>\n<p><b><i>log_queries_not_using_indexes = 1<\/i><\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Logs queries taking longer than 5 seconds<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Optionally logs queries not using indexes<\/span><span style=\"font-weight: 400;\"><br \/><\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">Analyze these logs regularly to optimize inefficient queries.<\/span><\/p>\n<h2><b>7. Query Cache (Removed)<\/b><\/h2>\n<p><b>Note:<\/b><span style=\"font-weight: 400;\"> The Query Cache feature has been <\/span><b>deprecated<\/b><span style=\"font-weight: 400;\"> since MySQL 5.7 and removed in MySQL 8.0.<\/span><span style=\"font-weight: 400;\"><br \/><\/span><span style=\"font-weight: 400;\">Modern performance gains come from <\/span><b>InnoDB buffer pool<\/b><span style=\"font-weight: 400;\">, <\/span><b>thread cache<\/b><span style=\"font-weight: 400;\">, and <\/span><b>proper indexing<\/b><span style=\"font-weight: 400;\">, not query cache.<\/span><\/p>\n<h2><b>8. General Recommendations<\/b><\/h2>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Use <\/span><b>InnoDB<\/b><span style=\"font-weight: 400;\"> for most tables<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Increase <\/span><span style=\"font-weight: 400;\">table_open_cache<\/span><span style=\"font-weight: 400;\"> and <\/span><span style=\"font-weight: 400;\">thread_cache_size<\/span><span style=\"font-weight: 400;\"> for high-traffic workloads<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Tune <\/span><span style=\"font-weight: 400;\">i<\/span><span style=\"font-weight: 400;\">nnodb_buffer_pool_size<\/span><span style=\"font-weight: 400;\"> for large datasets<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Enable <\/span><b>slow query logs<\/b><span style=\"font-weight: 400;\"> to identify bottlenecks<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Monitor regularly and adjust based on metrics, not assumptions<\/span><span style=\"font-weight: 400;\"><br \/><br \/><\/span><\/li>\n<\/ul>\n<h2><b>9. Monitoring Useful Metrics<\/b><\/h2>\n<p><b><i>SHOW STATUS LIKE &#8216;Connections&#8217;;<\/i><\/b><\/p>\n<p><b><i>SHOW STATUS LIKE &#8216;Threads_created&#8217;;<\/i><\/b><\/p>\n<p><b><i>SHOW STATUS LIKE &#8216;Open_tables&#8217;;<\/i><\/b><\/p>\n<p><b><i>SHOW STATUS LIKE &#8216;Opened_tables&#8217;;<\/i><\/b><\/p>\n<p><b><i>SHOW STATUS LIKE &#8216;Innodb_buffer_pool_reads&#8217;;<\/i><\/b><\/p>\n<p><span style=\"font-weight: 400;\">These metrics help you determine if your server is efficiently using resources.<\/span><\/p>\n<h2><b>Conclusion<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">Modern MySQL performance tuning focuses on <\/span><b>InnoDB buffer pool<\/b><span style=\"font-weight: 400;\">, <\/span><b>thread reuse<\/b><span style=\"font-weight: 400;\">, and <\/span><b>table cache management<\/b><span style=\"font-weight: 400;\">, along with regular monitoring of slow queries. Deprecated features like the query cache should be ignored, and tuning should always be guided by real-world metrics.<\/span><\/p>\n<p>If you require help, <a href=\"https:\/\/www.supportpro.com\/requestquote.php\">contact SupportPRO Server Admin<\/a><\/p>\n<p style=\"text-align: center;\"><!--HubSpot Call-to-Action Code --><span id=\"hs-cta-wrapper-9d590242-d641-4383-94b4-8cfd62f0af6b\" class=\"hs-cta-wrapper\"><span id=\"hs-cta-9d590242-d641-4383-94b4-8cfd62f0af6b\" class=\"hs-cta-node hs-cta-9d590242-d641-4383-94b4-8cfd62f0af6b\"><!-- [if lte IE 8]><\/p>\n\n\n\n\n\n<div id=\"hs-cta-ie-element\"><\/div>\n\n\n<![endif]--><a href=\"https:\/\/cta-redirect.hubspot.com\/cta\/redirect\/2725694\/9d590242-d641-4383-94b4-8cfd62f0af6b\"><img decoding=\"async\" id=\"hs-cta-img-9d590242-d641-4383-94b4-8cfd62f0af6b\" class=\"hs-cta-img\" style=\"border-width: 0px;\" src=\"https:\/\/no-cache.hubspot.com\/cta\/default\/2725694\/9d590242-d641-4383-94b4-8cfd62f0af6b.png\" alt=\"Server not running properly? Get A FREE Server Checkup By Expert Server Admins - $125 Value\" \/><\/a><\/span><script charset=\"utf-8\" src=\"https:\/\/js.hscta.net\/cta\/current.js\"><\/script><script type=\"text\/javascript\"> hbspt.cta.load(2725694, '9d590242-d641-4383-94b4-8cfd62f0af6b', {}); <\/script><\/span><!-- end HubSpot Call-to-Action Code --><\/p>\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Optimizing MySQL performance is critical for high-traffic applications. This guide explains how to monitor and tune MySQL for efficient resource usage, focusing on modern defaults, InnoDB, and current best practices.&hellip;<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_lmt_disableupdate":"","_lmt_disable":"","footnotes":""},"categories":[7],"tags":[],"class_list":["post-87","post","type-post","status-publish","format-standard","hentry","category-server-tweaking"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.supportpro.com\/blog\/wp-json\/wp\/v2\/posts\/87","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.supportpro.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.supportpro.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.supportpro.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.supportpro.com\/blog\/wp-json\/wp\/v2\/comments?post=87"}],"version-history":[{"count":15,"href":"https:\/\/www.supportpro.com\/blog\/wp-json\/wp\/v2\/posts\/87\/revisions"}],"predecessor-version":[{"id":15229,"href":"https:\/\/www.supportpro.com\/blog\/wp-json\/wp\/v2\/posts\/87\/revisions\/15229"}],"wp:attachment":[{"href":"https:\/\/www.supportpro.com\/blog\/wp-json\/wp\/v2\/media?parent=87"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.supportpro.com\/blog\/wp-json\/wp\/v2\/categories?post=87"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.supportpro.com\/blog\/wp-json\/wp\/v2\/tags?post=87"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}