{"id":2979,"date":"2018-08-17T11:30:31","date_gmt":"2018-08-17T11:30:31","guid":{"rendered":"https:\/\/tst-amo.net.ua\/blog\/?p=2979"},"modified":"2018-08-17T14:44:24","modified_gmt":"2018-08-17T14:44:24","slug":"2979","status":"publish","type":"post","link":"https:\/\/tst-amo.net.ua\/blog\/?p=2979","title":{"rendered":"Mysqltuner &#8211; \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u044f MySQL"},"content":{"rendered":"<p>Mysqltuner \u2014 \u044d\u0442\u043e perl-\u0441\u043a\u0440\u0438\u043f\u0442, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u0430\u043d\u0430\u043b\u0438\u0437\u0438\u0440\u0443\u0435\u0442 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0443 \u0440\u0430\u0431\u043e\u0442\u044b Mysql \u0438 \u0432\u044b\u0434\u0430\u0435\u0442 \u0441\u0432\u043e\u0438 \u0440\u0435\u043a\u043e\u043c\u0435\u043d\u0434\u0430\u0446\u0438\u0438 \u043f\u043e \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438 \u043d\u0430\u0441\u0442\u0440\u043e\u0435\u043a Mysql \u0441\u0435\u0440\u0432\u0435\u0440\u0430.<\/p>\n<pre># yum -y install mysqltuner<\/pre>\n<p>\u0417\u0430\u043f\u0443\u0441\u043a\u0430\u0435\u043c:<\/p>\n<pre># mysqltuner --user root --pass rootpassword<\/pre>\n<p>\u041e\u0441\u043e\u0431\u043e\u0435 \u0432\u043d\u0438\u043c\u0430\u043d\u0438\u0435 \u0441\u0442\u043e\u0438\u0442 \u0443\u0434\u0435\u043b\u0438\u0442\u044c \u0441\u0442\u0440\u043e\u043a\u0430\u043c, \u043f\u043e\u043c\u0435\u0447\u0435\u043d\u043d\u044b\u043c \u0441\u0438\u043c\u0432\u043e\u043b\u0430\u043c\u0438 [!!] \u0438 \u0441\u0435\u043a\u0446\u0438\u0438 Recommendations.<\/p>\n<p>\u041f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u044b \u0443\u043a\u0430\u0437\u0430\u043d\u043d\u044b\u0435 \u043f\u043e\u0441\u043b\u0435 \u0441\u0442\u0440\u043e\u043a\u0438\u00a0<b>Variables to adjust<\/b>\u00a0\u043d\u0443\u0436\u043d\u043e \u0438\u0437\u043c\u0435\u043d\u0438\u0442\u044c \u0432 \u0444\u0430\u0439\u043b\u0435 my.cnf, \u0432 \u0441\u043e\u043e\u0442\u0432\u0435\u0442\u0441\u0442\u0432\u0438\u0438 \u0441 \u0440\u0435\u043a\u043e\u043c\u0435\u043d\u0434\u0430\u0446\u0438\u044f\u043c\u0438 Mysqltuner. \u0415\u0441\u043b\u0438 \u0443\u043a\u0430\u0437\u0430\u043d\u043d\u043e\u0433\u043e \u043f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u0430 \u043d\u0435\u0442 \u0432 \u0444\u0430\u0439\u043b\u0435 my.cnf, \u0442\u043e \u0435\u0433\u043e \u0441\u043b\u0435\u0434\u0443\u0435\u0442 \u0434\u043e\u043f\u0438\u0441\u0430\u0442\u044c.<\/p>\n<pre>-------- Recommendations -----------------------------------------------------\r\nGeneral recommendations:\r\nRun OPTIMIZE TABLE to defragment tables for better performance\r\nMySQL started within last 24 hours - recommendations may be inaccurate\r\nReduce your overall MySQL memory footprint for system stability\r\nWhen making adjustments, make tmp_table_size\/max_heap_table_size equal\r\nReduce your SELECT DISTINCT queries which have no LIMIT clause\r\nVariables to adjust:\r\n*** MySQL's maximum memory usage is dangerously high ***\r\n*** Add RAM before increasing MySQL buffer variables ***\r\ntmp_table_size (&gt; 64M)\r\nmax_heap_table_size (&gt; 64M)<\/pre>\n<p>\u0421\u043e\u0433\u043b\u0430\u0441\u043d\u043e \u0440\u0435\u043a\u043e\u043c\u0435\u043d\u0434\u0430\u0446\u0438\u044f\u043c \u043f\u0440\u0430\u0432\u0438\u043c:<\/p>\n<pre>\/etc\/my.cnf\r\n<\/pre>\n<p>\u041f\u043e\u0441\u043b\u0435 \u0432\u043d\u0435\u0441\u0435\u043d\u0438\u044f \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439 \u0432 \u0444\u0430\u0439\u043b my.cnf \u043d\u0443\u0436\u043d\u043e \u043f\u0435\u0440\u0435\u0437\u0430\u0433\u0440\u0443\u0437\u0438\u0442\u044c Mysql-\u0441\u0435\u0440\u0432\u0435\u0440:<\/p>\n<pre># systemctl restart mariadb<\/pre>\n<p>\u041f\u043e\u0441\u043b\u0435 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u044f \u043a\u043e\u043d\u0444\u0438\u0433\u0443\u0440\u0430\u0446\u0438\u0438 Mysql-\u0441\u0435\u0440\u0432\u0435\u0440 \u0434\u043e\u043b\u0436\u0435\u043d \u043f\u0440\u043e\u0440\u0430\u0431\u043e\u0442\u0430\u0442\u044c \u043c\u0438\u043d\u0438\u043c\u0443\u043c 24 \u0447\u0430\u0441\u0430 \u0431\u0435\u0437 \u043f\u0435\u0440\u0435\u0437\u0430\u0433\u0440\u0443\u0437\u043e\u043a. \u0417\u0430\u0442\u0435\u043c, \u043c\u043e\u0436\u043d\u043e \u0441\u043d\u043e\u0432\u0430 \u0437\u0430\u043f\u0443\u0441\u0442\u0438\u0442\u044c \u0443\u0442\u0438\u043b\u0438\u0442\u0443 Mysqltuner \u0438 \u043f\u0440\u043e\u0430\u043d\u0430\u043b\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u0442\u044c \u0432\u044b\u0432\u043e\u0434 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438. \u0422\u0430\u043a\u0438\u043c \u043e\u0431\u0440\u0430\u0437\u043e\u043c, \u043c\u043e\u0436\u043d\u043e \u043f\u0440\u0438\u0432\u0435\u0441\u0442\u0438 \u043a\u043e\u043d\u0444\u0438\u0433\u0443\u0440\u0430\u0446\u0438\u043e\u043d\u043d\u044b\u0439 \u0444\u0430\u0439\u043b my.cnf \u0438 \u0440\u0430\u0431\u043e\u0442\u0443 Mysql-\u0441\u0435\u0440\u0432\u0435\u0440\u0430, \u0441\u043e\u043e\u0442\u0432\u0435\u0442\u0441\u0442\u0432\u0435\u043d\u043d\u043e, \u043a \u043e\u043f\u0442\u0438\u043c\u0430\u043b\u044c\u043d\u043e\u043c\u0443 \u0441\u043e\u0441\u0442\u043e\u044f\u043d\u0438\u044e.<\/p>\n<p>\u041c\u043e\u0439 \u043f\u0440\u0438\u043c\u0435\u0440 \u043a\u043e\u043d\u0444\u0438\u0433\u0430.<\/p>\n<pre># uname -rs\r\nLinux 3.10.0-862.11.6.el7.x86_64<\/pre>\n<pre># free -m\r\ntotal used free shared buff\/cache available\r\nMem: 2811 2086 91 9 633 540\r\nSwap: 2047 0 2047<\/pre>\n<pre># cat \/etc\/my.cnf\r\n[mysqld]\r\ndatadir=\/var\/lib\/mysql\r\nsocket=\/var\/lib\/mysql\/mysql.sock\r\n# Disabling symbolic-links is recommended to prevent assorted security risks\r\nsymbolic-links=0\r\n# Settings user and group are ignored when systemd is used.\r\n# If you need to run mysqld under a different user or group,\r\n# customize your systemd unit file for mariadb according to the\r\n# instructions in http:\/\/fedoraproject.org\/wiki\/Systemd\r\n\r\n## WordPress\/Owncloud\r\nmax_connections = 64\r\nwait_timeout = 22222\r\ninteractive_timeout = 22222\r\ntable_open_cache = 800\r\nopen_files_limit = 65535\r\nthread_cache_size = 32\r\nthread_concurrency = 8\r\nquery_cache_type = 1\r\nquery_cache_size = 32M\r\nquery_cache_limit = 1M\r\nkey_buffer_size = 24M\r\nkey_cache_division_limit=70\r\njoin_buffer_size = 512M\r\ntmp_table_size = 128M\r\nmax_heap_table_size = 128M\r\n\r\n# INNODB\r\ninnodb_buffer_pool_size = 128M\r\ninnodb_flush_log_at_trx_commit = 2\r\ninnodb_file_per_table = 1\r\ninnodb_buffer_pool_size = 512M\r\ninnodb_data_file_path = ibdata1:10M:autoextend:max:3999M\r\ninnodb_flush_method = O_DIRECT\r\n\r\n# MYISAM\r\nmyisam_sort_buffer_size = 32M\r\n\r\n#Logging\r\nslow_query_log = 1\r\nslow_query_log_file = \/var\/log\/mariadb\/mysql-slow.log\r\n\r\n[mysqld_safe]\r\nlog-error=\/var\/log\/mariadb\/mariadb.log\r\npid-file=\/var\/run\/mariadb\/mariadb.pid\r\n\r\n#\r\n# include all files from the config directory\r\n#\r\n!includedir \/etc\/my.cnf.d<\/pre>\n<pre># mysqltuner\r\nPlease enter your MySQL administrative login: root\r\nPlease enter your MySQL administrative password: &gt;&gt; MySQLTuner 1.6.0 - Major Hayden &lt;major@mhtx.net&gt;\r\n&gt;&gt; Bug reports, feature requests, and downloads at http:\/\/mysqltuner.com\/\r\n&gt;&gt; Run with '--help' for additional options and output filtering\r\n[--] Skipped version check for MySQLTuner script\r\n[OK] Currently running supported MySQL version 5.5.56-MariaDB\r\n[OK] Operating on 64-bit architecture\r\n\r\n-------- Storage Engine Statistics -------------------------------------------\r\n[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM\r\n[--] Data in MyISAM tables: 42K (Tables: 10)\r\n[--] Data in InnoDB tables: 39M (Tables: 87)\r\n[!!] Total fragmented tables: 3\r\n\r\n-------- Security Recommendations -------------------------------------------\r\n[OK] There is no anonymous account in all database users\r\n[OK] All database users have passwords assigned\r\n[!!] There is not basic password file list !\r\n\r\n-------- Performance Metrics -------------------------------------------------\r\n[--] Up for: 44m 53s (43K q [16.302 qps], 748 conn, TX: 34M, RX: 8M)\r\n[--] Reads \/ Writes: 87% \/ 13%\r\n[--] Binary logging is disabled\r\n[--] Total buffers: 840.0M global + 514.7M per thread (64 max threads)\r\n[!!] Maximum reached memory usage: 16.4G (597.34% of installed RAM)\r\n[!!] Maximum possible memory usage: 33.0G (1201.42% of installed RAM)\r\n[OK] Slow queries: 0% (0\/43K)\r\n[OK] Highest usage of available connections: 48% (31\/64)\r\n[OK] Aborted connections: 0.53% (4\/748)\r\n[OK] Query cache efficiency: 40.8% (24K cached \/ 59K selects)\r\n[OK] Query cache prunes per day: 0\r\n[OK] Sorts requiring temporary tables: 0% (0 temp sorts \/ 627 sorts)\r\n[!!] Temporary tables created on disk: 26% (18 on disk \/ 67 total)\r\n[OK] Thread cache hit rate: 95% (31 created \/ 748 connections)\r\n[OK] Table cache hit rate: 122% (140 open \/ 114 opened)\r\n[OK] Open file limit used: 7% (72\/1K)\r\n[OK] Table locks acquired immediately: 100% (16K immediate \/ 16K locks)\r\n\r\n\r\n-------- MyISAM Metrics -----------------------------------------------------\r\n[!!] Key buffer used: 18.7% (4M used \/ 25M cache)\r\n[OK] Key buffer size \/ total MyISAM indexes: 24.0M\/136.0K\r\n[!!] Read Key buffer hit rate: 75.0% (32 cached \/ 8 reads)\r\n\r\n-------- InnoDB Metrics -----------------------------------------------------\r\n[--] InnoDB is enabled.\r\n[OK] InnoDB buffer pool \/ data size: 512.0M\/39.7M\r\n[OK] InnoDB buffer pool instances: 1\r\n[!!] InnoDB Used buffer: 5.93% (1943 used\/ 32767 total)\r\n[OK] InnoDB Read buffer efficiency: 99.50% (358717 hits\/ 360535 total)\r\n[!!] InnoDB Write buffer efficiency: 0.00% (0 hits\/ 1 total)\r\n[OK] InnoDB log waits: 0.00% (0 waits \/ 4616 writes)\r\n\r\n-------- AriaDB Metrics -----------------------------------------------------\r\n[--] AriaDB is disabled.\r\n\r\n-------- Replication Metrics -------------------------------------------------\r\n[--] No replication slave(s) for this server.\r\n[--] This is a standalone server..\r\n\r\n-------- Recommendations -----------------------------------------------------\r\nGeneral recommendations:\r\nRun OPTIMIZE TABLE to defragment tables for better performance\r\nMySQL started within last 24 hours - recommendations may be inaccurate\r\nReduce your overall MySQL memory footprint for system stability\r\nWhen making adjustments, make tmp_table_size\/max_heap_table_size equal\r\nReduce your SELECT DISTINCT queries which have no LIMIT clause\r\nVariables to adjust:\r\n*** MySQL's maximum memory usage is dangerously high ***\r\n*** Add RAM before increasing MySQL buffer variables ***\r\ntmp_table_size (&gt; 128M)\r\nmax_heap_table_size (&gt; 128M)<\/pre>\n<div class=\"pdfprnt-buttons pdfprnt-buttons-post pdfprnt-bottom-right\"><a href=\"https:\/\/tst-amo.net.ua\/blog\/index.php?rest_route=wpv2posts2979&print=pdf\" class=\"pdfprnt-button pdfprnt-button-pdf\" target=\"_blank\"><img decoding=\"async\" src=\"https:\/\/tst-amo.net.ua\/blog\/wp-content\/plugins\/pdf-print\/images\/pdf.png\" alt=\"image_pdf\" title=\"View PDF\" \/><\/a><a href=\"https:\/\/tst-amo.net.ua\/blog\/index.php?rest_route=wpv2posts2979&print=print\" class=\"pdfprnt-button pdfprnt-button-print\" target=\"_blank\"><img decoding=\"async\" src=\"https:\/\/tst-amo.net.ua\/blog\/wp-content\/plugins\/pdf-print\/images\/print.png\" alt=\"image_print\" title=\"Print Content\" \/><\/a><\/div>","protected":false},"excerpt":{"rendered":"<p>Mysqltuner \u2014 \u044d\u0442\u043e perl-\u0441\u043a\u0440\u0438\u043f\u0442, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u0430\u043d\u0430\u043b\u0438\u0437\u0438\u0440\u0443\u0435\u0442 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0443 \u0440\u0430\u0431\u043e\u0442\u044b Mysql \u0438 \u0432\u044b\u0434\u0430\u0435\u0442 \u0441\u0432\u043e\u0438 \u0440\u0435\u043a\u043e\u043c\u0435\u043d\u0434\u0430\u0446\u0438\u0438 \u043f\u043e \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438 \u043d\u0430\u0441\u0442\u0440\u043e\u0435\u043a Mysql \u0441\u0435\u0440\u0432\u0435\u0440\u0430. # yum -y install mysqltuner \u0417\u0430\u043f\u0443\u0441\u043a\u0430\u0435\u043c: # mysqltuner &#8211;user root &#8211;pass rootpassword \u041e\u0441\u043e\u0431\u043e\u0435 \u0432\u043d\u0438\u043c\u0430\u043d\u0438\u0435 \u0441\u0442\u043e\u0438\u0442 \u0443\u0434\u0435\u043b\u0438\u0442\u044c \u0441\u0442\u0440\u043e\u043a\u0430\u043c, \u043f\u043e\u043c\u0435\u0447\u0435\u043d\u043d\u044b\u043c \u0441\u0438\u043c\u0432\u043e\u043b\u0430\u043c\u0438 [!!] \u0438 \u0441\u0435\u043a\u0446\u0438\u0438 Recommendations. \u041f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u044b \u0443\u043a\u0430\u0437\u0430\u043d\u043d\u044b\u0435 \u043f\u043e\u0441\u043b\u0435 \u0441\u0442\u0440\u043e\u043a\u0438\u00a0Variables to adjust\u00a0\u043d\u0443\u0436\u043d\u043e \u0438\u0437\u043c\u0435\u043d\u0438\u0442\u044c \u0432 \u0444\u0430\u0439\u043b\u0435 my.cnf, \u0432 \u0441\u043e\u043e\u0442\u0432\u0435\u0442\u0441\u0442\u0432\u0438\u0438 \u0441 \u0440\u0435\u043a\u043e\u043c\u0435\u043d\u0434\u0430\u0446\u0438\u044f\u043c\u0438 &#8230;<\/p>\n<p><a href=\"https:\/\/tst-amo.net.ua\/blog\/?p=2979\" class=\"more-link\">Continue reading &lsquo;Mysqltuner &#8211; \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u044f MySQL&rsquo; &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[48,47,126,15,148],"tags":[],"class_list":["post-2979","post","type-post","status-publish","format-standard","hentry","category-centos","category-linux","category-mariadb","category-mysql","category-mysqltuner"],"_links":{"self":[{"href":"https:\/\/tst-amo.net.ua\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2979"}],"collection":[{"href":"https:\/\/tst-amo.net.ua\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tst-amo.net.ua\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tst-amo.net.ua\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tst-amo.net.ua\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2979"}],"version-history":[{"count":4,"href":"https:\/\/tst-amo.net.ua\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2979\/revisions"}],"predecessor-version":[{"id":2984,"href":"https:\/\/tst-amo.net.ua\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2979\/revisions\/2984"}],"wp:attachment":[{"href":"https:\/\/tst-amo.net.ua\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2979"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tst-amo.net.ua\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2979"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tst-amo.net.ua\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2979"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}