Введение
В данной заметке разберем как запустить скрипт для анализа работы MySQL под названием MySQLTuner, скрипт проанализирует работу и в конце даст нам рекомендации которые могут нам помочь улучшить работу и стабильность MySQL
Запуск MySQLTuner
Перед тем как запускать скрипт MySQLTuner для детального сбора информации, ваш сервер должен проработать хотя бы одни сутки без выключений и редактирования конфигурации MySQL.
1. Скачиваем сам скрипт и дополнения к нему:
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv2. Запускаем его:
perl mysqltuner.plНиже пример вывода от скрипта, обратить внимание следует на [!!] и на сноску в самом низу Recommendations:
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 25.9M (Tables: 61)
[OK] Total fragmented tables: 0
[OK] Currently running supported MySQL version 10.5.23-MariaDB-0+deb11u1
-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file doesn't exist
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Views Metrics -----------------------------------------------------------------------------
-------- Triggers Metrics --------------------------------------------------------------------------
-------- Routines Metrics --------------------------------------------------------------------------
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'zbx_monitor'@% does not specify hostname restrictions.
[!!] There is no basic password file list!
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 18d 16h 26m 49s (155K q [0.096 qps], 9K conn, TX: 187M, RX: 98M)
[--] Reads / Writes: 56% / 44%
[--] Binary logging is disabled
[--] Physical Memory : 964.6M
[--] Max MySQL memory : 861.2M
[--] Other process memory: 0B
[--] Total buffers: 417.0M global + 2.9M per thread (151 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 431.7M (44.76% of installed RAM)
[!!] Maximum possible memory usage: 861.2M (89.28% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/155K)
[OK] Highest usage of available connections: 3% (5/151)
[OK] Aborted connections: 0.00% (0/9208)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 62% (10K on disk / 16K total)
[OK] Thread cache hit rate: 66% (3K created / 9K connections)
[OK] Table cache hit rate: 99% (101K hits / 101K requests)
[OK] table_definition_cache (400) is greater than number of tables (254)
[OK] Open file limit used: 0% (52/32K)
[OK] Table locks acquired immediately: 100% (227 immediate / 227 locks)
-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is not installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--] +-- Total MyISAM Tables : 0
[--] +-- Total MyISAM indexes : 0B
[--] +-- KB Size :128.0M
[--] +-- KB Used Size :23.3M
[--] +-- KB used :18.2%
[--] +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--] +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 128.0M ) under limit for 64 bits architecture: (17179869184.0G )
[OK] InnoDB buffer pool / data size: 128.0M / 25.9M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 96.0M * 1 / 128.0M should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.95% (3723645 hits / 3725384 total)
[!!] InnoDB Write Log efficiency: 1556.04% (43289 hits / 2782 total)
[OK] InnoDB log waits: 0.00% (0 waits / 46071 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K
[!!] Aria pagecache hit rate: 87.3% (80K cached / 10K reads)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Restrict Host for 'zbx_monitor'@'%' to 'zbx_monitor'@LimitedIPRangeOrLocalhost
RENAME USER 'zbx_monitor'@'%' TO 'zbx_monitor'@LimitedIPRangeOrLocalhost;
Reduce your overall MySQL memory footprint for system stability
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance schema should be activated for better diagnostics
Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
Be careful, increasing innodb_log_file_size / innodb_log_files_in_group means higher crash recovery mean time
Variables to adjust:
skip-name-resolve=ON
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
performance_schema=ON
innodb_log_file_size should be (=32M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
innodb_log_buffer_size (> 16M)Параметры указанные после строки Variables to adjust нужно добавлять в файл /etc/mysql/my.cnf, а после перезагрузить службу MySQL
systemctl restart mysqlПеред внесением изменений в конфигурацию рекомендую сделать бекап базы и конфига /etc/mysql/my.cnf (cp /etc/mysql/my.cnf ~/my.cnf.backup), так же рекомендую добавлять изменения не все сразу, что бы понять какое из них приводит к проблемам.
После правок конфигурации mysql даем поработать еще день и запускаем скрипт еще раз, ниже вывод скрипта после внесения некоторых рекомендаций при первом запуске:
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 26.0M (Tables: 61)
[OK] Total fragmented tables: 0
[OK] Currently running supported MySQL version 10.5.23-MariaDB-0+deb11u1
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql/mysql_error.log exists
[--] Log file: /var/log/mysql/mysql_error.log (9K)
[OK] Log file /var/log/mysql/mysql_error.log is not empty
[OK] Log file /var/log/mysql/mysql_error.log is smaller than 32 MB
[OK] Log file /var/log/mysql/mysql_error.log is readable.
[OK] /var/log/mysql/mysql_error.log doesn't contain any warning.
[OK] /var/log/mysql/mysql_error.log doesn't contain any error.
[--] 4 start(s) detected in /var/log/mysql/mysql_error.log
[--] 1) 2024-06-05 14:32:17 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 2) 2024-06-05 14:06:27 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 3) 2024-06-05 14:02:20 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 4) 2024-06-05 13:57:48 0 [Note] /usr/sbin/mariadbd: ready for connections.
[--] 3 shutdown(s) detected in /var/log/mysql/mysql_error.log
[--] 1) 2024-06-05 14:32:17 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 2) 2024-06-05 14:06:27 0 [Note] /usr/sbin/mariadbd: Shutdown complete
[--] 3) 2024-06-05 14:02:19 0 [Note] /usr/sbin/mariadbd: Shutdown complete
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Views Metrics -----------------------------------------------------------------------------
-------- Triggers Metrics --------------------------------------------------------------------------
-------- Routines Metrics --------------------------------------------------------------------------
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 620 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 23h 44m 2s (17K q [0.201 qps], 1K conn, TX: 29M, RX: 7M)
[--] Reads / Writes: 64% / 36%
[--] Binary logging is disabled
[--] Physical Memory : 964.6M
[--] Max MySQL memory : 610.6M
[--] Other process memory: 0B
[--] Total buffers: 433.0M global + 2.9M per thread (25 max threads)
[--] Performance_schema Max memory usage: 104M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 545.9M (56.59% of installed RAM)
[OK] Maximum possible memory usage: 610.6M (63.30% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/17K)
[OK] Highest usage of available connections: 12% (3/25)
[OK] Aborted connections: 0.00% (0/1519)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 50% (991 on disk / 1K total)
[OK] Thread cache hit rate: 86% (205 created / 1K connections)
[OK] Table cache hit rate: 93% (11K hits / 12K requests)
[OK] table_definition_cache (400) is greater than number of tables (254)
[OK] Open file limit used: 1% (56/3K)
[OK] Table locks acquired immediately: 100% (816 immediate / 816 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 104.0M
[--] Sys schema is not installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--] +-- Total MyISAM Tables : 0
[--] +-- Total MyISAM indexes : 0B
[--] +-- KB Size :128.0M
[--] +-- KB Used Size :23.3M
[--] +-- KB used :18.2%
[--] +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--] +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 128.0M ) under limit for 64 bits architecture: (17179869184.0G )
[OK] InnoDB buffer pool / data size: 128.0M / 26.0M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 32.0M * 1/128.0M should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.73% (639767 hits / 641508 total)
[!!] InnoDB Write Log efficiency: 356.88% (3319 hits / 930 total)
[OK] InnoDB log waits: 0.00% (0 waits / 4249 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K
[!!] Aria pagecache hit rate: 92.8% (13K cached / 1K reads)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
MySQL was started within the last 24 hours: recommendations may be inaccurate
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
innodb_log_buffer_size (> 32M)Ну и на последок:
ПРЕДУПРЕЖДЕНИЕ
Для вас важно полностью понимать каждое изменение, которое вы вносите в сервер базы данных MySQL. Если вы не понимаете части выходных данных скрипта или если вы не понимаете рекомендаций, вам следует проконсультироваться со знающим администратором базы данных или системным администратором, которому вы доверяете. Всегда тестируйте свои изменения в промежуточных средах и всегда помните, что улучшения в одной области могут отрицательно повлиять на MySQL в других областях.
Полезные ссылки:
Анализируемые параметры MySQLTuner
Вывод
В данной заметке разобрали пример работы с утилитой MySQLTuner
Что бы не потерять инструкцию рекомендую подписаться на мой личный блог-канал в Telegram в котором я делюсь в формате заметок своими знаниями и интересным из мира IT, спасибо за потраченное время на чтение и доброго времени суток!)