Оптимизация MySQL (Mariadb)
Прошу помочь с настройкой параметров MySQL, т.к. при повышении трафика сайт начинает "подвисать", что проявляется в долгой реакции на клик по любой ссылке в рамках сайта. А именно, страница загружается от 10 сек. до минуты, потом само приходит в норму). Иногда, во время того, как сайт "думает" появляется ошибка о превышении времени ожидания ответаот сайта, после чего все приходит в норму.
Пришел к выводу, что это MySQL, потому что система в моменты зависаний не загружена. Загрузка ОЗУ в эти моменты 1,8/4 GB, процессоры ~25%. Параметры VPS: 4 ядра, 4GB ОЗУ, 2GB SWAP На VPS установлен LAMP.
Зашел в настройки /etc/mysql.my.cnf, там следующее:
#
[client-server]
[mysqld]
max_connections = 250
# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/
Смотрел файлы в диреториях
!includedir /etc/mysql/conf.d/ и !includedir /etc/mysql/mariadb.conf.d/
Ничего внятного там тоже не обнаружил.
Mysqltuner, говорит следующее:
>> MySQLTuner 1.7.2 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 10.1.44-MariaDB-0ubuntu0.18.04
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ---------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(0B)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[!!] Log file /var/log/mysql/error.log is empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] /var/log/mysql/error.log doesn't contain any warning.
[OK] /var/log/mysql/error.log doesn't contain any error.
[--] 0 start(s) detected in /var/log/mysql/error.log
[--] 0 shutdown(s) detected in /var/log/mysql/error.log
-------- Storage Engine Statistics --------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHE+SEQUENCE
[--] Data in MyISAM tables: 41M (Tables: 12)
[--] Data in InnoDB tables: 400K (Tables: 19)
[OK] Total fragmented tables: 0
-------- Security Recommendations ---------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'root@localhost' has no password set.
[--] There are 612 basic passwords in the list.
-------- CVE Security Recommendations -----------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics --------------------------------------------------------------------
[--] Up for: 2d 23h 13m 42s (3M q [12.212 qps], 301K conn, TX: 11G, RX: 251M)
[--] Reads / Writes: 86% / 14%
[--] Binary logging is disabled
[--] Physical Memory : 3.9G
[--] Max MySQL memory : 1.0G
[--] Other process memory: 343.1M
[--] Total buffers: 328.0M global + 2.8M per thread (250 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 724.6M (18.37% of installed RAM)
[OK] Maximum possible memory usage: 1.0G (26.15% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memoavailable
[OK] Slow queries: 0% (0/3M)
[OK] Highest usage of available connections: 56% (141/250)
[OK] Aborted connections: 0.00% (1/301230)
[!!] name resolution is active : a reverse name resolution is made for each nconnection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 41.0% (1M cached / 3M selects)
[OK] Query cache prunes per day: 0
[!!] Sorts requiring temporary tables: 23% (5K temp sorts / 24K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 44% (4K on disk / 9K total)
[OK] Thread cache hit rate: 94% (15K created / 301K connections)
[OK] Table cache hit rate: 90% (120 open / 132 opened)
[OK] Open file limit used: 2% (90/4K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
-------- Performance schema ---------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ---------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 4 thread(s).
[--] Using default value is good enough for your version (10.1.44-MariaDB-0ubu0.18.04.1)
-------- MyISAM Metrics -------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (16M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/18.8M
[OK] Read Key buffer hit rate: 100.0% (65M cached / 8K reads)
[OK] Write Key buffer hit rate: 100.0% (1M cached / 1M writes)
-------- InnoDB Metrics -------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/400.0K
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/10M should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[!!] InnoDB Read buffer efficiency: 89.13% (2443 hits/ 2741 total)
[!!] InnoDB Write Log efficiency: 37.21% (16 hits/ 43 total)
[OK] InnoDB log waits: 0.00% (0 waits / 27 writes)
-------- AriaDB Metrics -------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.8% (417M cached / 781K reads)
-------- TokuDB Metrics -------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics -------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- RocksDB Metrics ------------------------------------------------------------------------
[--] RocksDB is disabled.
-------- Spider Metrics -------------------------------------------------------------------------
[--] Spider is disabled.
-------- Connect Metrics ------------------------------------------------------------------------
[--] Connect is disabled.
-------- Galera Metrics -------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics --------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.
-------- Recommendations ------------------------------------------------------------------------
General recommendations:
Set up a Password for user with the following SQL statement ( SET PASSWOROR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Configure your accounts with ip or subnets only, then update your configuion with skip-name-resolve=1
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
sort_buffer_size (> 2M)
read_rnd_buffer_size (> 256K)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
performance_schema = ON enable PFS
innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 ouffer pool size (=64M) if possible.
innodb_buffer_pool_instances (=1)
Знаю, что на mysqltuner не стоит 100% полагаться и для каждой системы все настройки индивидуальны. Но их я высчитывать не умею (пробовал делать как описано здесь, но ничего не получилось. Прошу помочь тех, кто разбирается в оптимизации MySQL. Спасибо.