{"id":20674,"date":"2020-11-30T04:54:59","date_gmt":"2020-11-29T20:54:59","guid":{"rendered":"https:\/\/web.mwwsb.com.my\/pjci\/?post_type=kb&#038;p=20674"},"modified":"2023-01-18T10:58:13","modified_gmt":"2023-01-18T02:58:13","slug":"tweak-mysql-using-mysqltuner","status":"publish","type":"kb","link":"https:\/\/www.casbay.com\/guide\/kb\/tweak-mysql-using-mysqltuner","title":{"rendered":"Tweak MySQL using MySQLTuner"},"content":{"rendered":"\t\t<div data-elementor-type=\"wp-post\" data-elementor-id=\"20674\" class=\"elementor elementor-20674\" data-elementor-post-type=\"kb\">\n\t\t\t\t\t\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-97fc6d9 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"97fc6d9\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-91ccaeb\" data-id=\"91ccaeb\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t\t\t<div class=\"elementor-element elementor-element-b487b19 elementor-widget elementor-widget-heading\" data-id=\"b487b19\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h2 class=\"elementor-heading-title elementor-size-default\">Tweak MySQL using MySQLTuner<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-5e4aa33 elementor-widget elementor-widget-heading\" data-id=\"5e4aa33\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h2 class=\"elementor-heading-title elementor-size-medium\">What is MySQLTuner\n?<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-2486a9d elementor-widget elementor-widget-text-editor\" data-id=\"2486a9d\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>MySQLtuner is an open-source <a href=\"https:\/\/www.perl.org\/\" rel=\"noopener\">Perl<\/a> script that analyzes the setup of MySQL and we are able to modify it according to its recommendations. It will be necessary to install and execute the script and show the correct parameters to change to enhance performance in the\u2019 my.cnf\u2019 file. So, get started with us to download and install by following the steps below:<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-0ae90df elementor-widget elementor-widget-heading\" data-id=\"0ae90df\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h2 class=\"elementor-heading-title elementor-size-medium\">General recommendations:<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-8941515 elementor-widget elementor-widget-text-editor\" data-id=\"8941515\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<ul><li>Run OPTIMIZE TABLE to defragment tables for better performance<\/li><li>MySQL started within last 24 hours \u2013 recommendations may be inaccurate<\/li><li>Enable the slow query log to troubleshoot bad queries<\/li><li>When adjusting, make tmp_table_size\/max_heap_table_size equal<\/li><li>Reduce your SELECT DISTINCT queries without LIMIT clauses<\/li><li>Set thread_cache_size to 4 as a starting value<\/li><li>Increase table_cache gradually to avoid file descriptor limits<\/li><li>Your applications are not closing MySQL connections properly<\/li><\/ul>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-38dff97 elementor-widget elementor-widget-heading\" data-id=\"38dff97\" data-element_type=\"widget\" data-widget_type=\"heading.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t<h2 class=\"elementor-heading-title elementor-size-medium\">Variables to adjust:<\/h2>\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-f2b16ad elementor-widget elementor-widget-text-editor\" data-id=\"f2b16ad\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<ul><li>query_cache_size (&gt;= 16M)<\/li><li>sort_buffer_size (&gt; 16M)<\/li><li>read_rnd_buffer_size (&gt; 256K)<\/li><li>tmp_table_size (&gt; 25M)<\/li><li>max_heap_table_size (&gt; 20M)<\/li><li>thread_cache_size (start at 4)<\/li><li>table_cache (&gt; 64)<\/li><li>innodb_buffer_pool_size (&gt;= 35M)<\/li><\/ul>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-7e8c8be elementor-widget elementor-widget-text-editor\" data-id=\"7e8c8be\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>Last but not least, you need to know that these suggestions have to be reviewed and modifications made within my.cnf file. Before you start, it is best to backup the old configuration. Once completed, restart the \u2018 mysql \u2018 service and see if it will improve efficiency.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t<div class=\"elementor-element elementor-element-0ba816c elementor-widget elementor-widget-text-editor\" data-id=\"0ba816c\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<p>To get more information regarding Database (MS SQL and MySQL), please refer to our <a href=\"https:\/\/www.casbay.com\/guide\/kb\/working-with-mysql-database-engines\">Knowledge Base<\/a>.<\/p>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>Tweak MySQL using MySQLTuner What is MySQLTuner ? MySQLtuner is an open-source Perl script that analyzes the setup of MySQL and we are able to modify it according to its recommendations. It will be necessary to install and execute the script and show the correct parameters to change to enhance performance in the\u2019 my.cnf\u2019 file. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"site-sidebar-layout":"no-sidebar","site-content-layout":"page-builder","ast-site-content-layout":"","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"disabled","ast-breadcrumbs-content":"","ast-featured-img":"disabled","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"default","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-gradient":""}}},"kbtopic":[60],"kbtag":[106],"mkb_version":[],"_links":{"self":[{"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/kb\/20674"}],"collection":[{"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/kb"}],"about":[{"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/types\/kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/comments?post=20674"}],"version-history":[{"count":14,"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/kb\/20674\/revisions"}],"predecessor-version":[{"id":38202,"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/kb\/20674\/revisions\/38202"}],"wp:attachment":[{"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/media?parent=20674"}],"wp:term":[{"taxonomy":"kbtopic","embeddable":true,"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/kbtopic?post=20674"},{"taxonomy":"kbtag","embeddable":true,"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/kbtag?post=20674"},{"taxonomy":"mkb_version","embeddable":true,"href":"https:\/\/www.casbay.com\/guide\/wp-json\/wp\/v2\/mkb_version?post=20674"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}