{"id":469,"date":"2014-07-30T23:27:12","date_gmt":"2014-07-30T23:27:12","guid":{"rendered":"https:\/\/qbytes.cloud\/?p=469"},"modified":"2014-07-30T23:27:12","modified_gmt":"2014-07-30T23:27:12","slug":"using-mysqltuner","status":"publish","type":"post","link":"https:\/\/www.qbytes.cloud\/index.php\/2014\/07\/30\/using-mysqltuner\/","title":{"rendered":"Using Mysqltuner"},"content":{"rendered":"

Download\/Installation<\/strong>
\nSimple<\/p>\n

\nwget https:\/\/raw.githubusercontent.com\/major\/MySQLTuner-perl\/master\/mysqltuner.pl\n# chmod +x mysqltuner.pl\n# .\/mysqltuner.pl\n<\/pre>\n
You can download the entire repository by using ‘git clone’ followed by the cloning URL above. The simplest and shortest method is:<\/p>\n
wget mysqltuner.pl\nperl mysqltuner.pl\n\n<\/pre>\n
Change the permissions you can execute it without calling perl directly.<\/p>\n
[root@austin ~]# chmod +x mysqltuner.pl\n\n<\/pre>\n
[root@austin ~]# .\/mysqltuner.pl\n\n &gt;&gt;  MySQLTuner 1.2.0 - Major Hayden &lt;major@mhtx.net&gt;\n &gt;&gt;  Bug reports, feature requests, and downloads at http:\/\/mysqltuner.com\/\n &gt;&gt;  Run with '--help' for additional options and output filtering\n\n-------- General Statistics --------------------------------------------------\n[--] Skipped version check for MySQLTuner script\n[OK] Currently running supported MySQL version 5.1.73-log\n[OK] Operating on 64-bit architecture\n\n-------- Storage Engine Statistics -------------------------------------------\n[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster\n[--] Data in MyISAM tables: 4M (Tables: 230)\n[--] Data in InnoDB tables: 12M (Tables: 419)\n[--] Data in MEMORY tables: 0B (Tables: 2)\n[!!] Total fragmented tables: 423\n\n-------- Security Recommendations  -------------------------------------------\n[OK] All database users have passwords assigned\n\n-------- Performance Metrics -------------------------------------------------\n[--] Up for: 16s (25 q [1.562 qps], 8 conn, TX: 62K, RX: 1K)\n[--] Reads \/ Writes: 93% \/ 7%\n[--] Total buffers: 58.0M global + 1.6M per thread (151 max threads)\n[OK] Maximum possible memory usage: 303.4M (3% of installed RAM)\n[OK] Slow queries: 0% (0\/25)\n[OK] Highest usage of available connections: 0% (1\/151)\n[OK] Key buffer size \/ total MyISAM indexes: 16.0M\/1.1M\n[!!] Key buffer hit rate: 77.6% (98 cached \/ 22 reads)\n[!!] Query cache is disabled\n[OK] Sorts requiring temporary tables: 0% (0 temp sorts \/ 2 sorts)\n[OK] Temporary tables created on disk: 20% (1 on disk \/ 5 total)\n[!!] Thread cache is disabled\n[OK] Table cache hit rate: 68% (15 open \/ 22 opened)\n[OK] Open file limit used: 3% (32\/1K)\n[OK] Table locks acquired immediately: 100% (29 immediate \/ 29 locks)\n[!!] Connections aborted: 25%\n[OK] InnoDB data size \/ buffer pool: 12.1M\/16.0M\n\n<\/pre>\n
[stextbox id=”warning”]——– Recommendations —————————————————–[\/stextbox]<\/p>\n
General recommendations:
\nRun OPTIMIZE TABLE to defragment tables for better performance
\nMySQL started within last 24 hours – recommendations may be inaccurate
\nEnable the slow query log to troubleshoot bad queries
\nSet thread_cache_size to 4 as a starting value
\nYour applications are not closing MySQL connections properly
\nVariables to adjust:
\nquery_cache_size (>= 8M)
\nthread_cache_size (start at 4)<\/p>\n
 <\/p>\n
Optimize tables<\/p>\n
See –\u00a0https:\/\/qbytes.cloud\/mysql-checkrepair-database\/<\/a><\/p>\n
Enable the slow query log to troubleshoot bad queries
\nSet thread_cache_size to 4 as a starting value
\nquery_cache_size (>= 8M)
\nthread_cache_size (start at 4)<\/p>\n
 <\/p>\n
[root@austin ~]# nano \/etc\/my.cnf\n\n[mysqld]\nport            = 3306\nsocket          = \/var\/lib\/mysql\/mysql.sock\nskip-locking\nkey_buffer_size = 16M\nmax_allowed_packet = 1M\ntable_open_cache = 64\nsort_buffer_size = 512K\nnet_buffer_length = 8K\nread_buffer_size = 256K\nread_rnd_buffer_size = 512K\nmyisam_sort_buffer_size = 8M\ninteractive_timeout=1800\nwait_timeout=1800\n<\/pre>\n
Add the following if mysql is 5.6.1 or higher<\/p>\n
slow_query_log = 1;\nslow_query_log_file = '\/var\/log\/mysql-slow.log';\nquery_cache_type = 1\nquery_cache_size = 32M\nthread_cache_size = 4\n<\/pre>\n
Other links and Info:<\/p>\n
I would recommend making sure the following is optimized.<\/p>\n
Your mysql queries in your code to make sure the queries are being closed.
\nYour mysql tables are optimized.
\nYour mysql configuration is optimized for your web site needs.<\/p>\n
Here are some helpful links on mysql optimization.<\/p>\n
http:\/\/www.codero.com\/knowledge-base\/questions\/319\/How+to+install+mytop+for+database+performance+monitoring%3A<\/a>
\nhttp:\/\/www.codero.com\/knowledge-base\/questions\/298\/How+can+I+view+currently+running+MySQL+queries%3F<\/a>
\nhttp:\/\/www.codero.com\/knowledge-base\/questions\/274\/How+to+check+and+repair+MySQL+database+tables<\/a>
\nhttp:\/\/www.codero.com\/knowledge-base\/questions\/96\/How+do+I+optimze+mysql%3F<\/a>
\nhttp:\/\/www.codero.com\/knowledge-base\/questions\/195\/How+can+I+trouble+shoot+my+MySQL+database%3F<\/a>
\nhttp:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/optimization.html<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Download\/Installation Simple wget https:\/\/raw.githubusercontent.com\/major\/MySQLTuner-perl\/master\/mysqltuner.pl # chmod +x mysqltuner.pl # .\/mysqltuner.pl You can download the entire repository by using ‘git clone’ followed by the cloning URL above. The simplest and shortest method is: wget mysqltuner.pl perl mysqltuner.pl Change the permissions you can execute it without calling perl directly. [root@austin ~]# chmod +x mysqltuner.pl [root@austin ~]# .\/mysqltuner.pl … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,70],"tags":[],"class_list":["post-469","post","type-post","status-publish","format-standard","hentry","category-administration","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/469","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/comments?post=469"}],"version-history":[{"count":0,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/469\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/media?parent=469"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/categories?post=469"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/tags?post=469"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}