Оптимизировать MariaDB?

Добрый день! Настройил Zabbix на мониторинг сервера втом числе на мониторинг MariaDB 10.3 идет не в сосатве с ISPManager 5.5 Lite ,ставилась отдельно.
Дак вот мониторинг кидает алерты:
Problem: MySQL: Number of on-disk temporary tables created per second is high (over 10 for 5m)
Problem: MySQL: Refused connections (max_connections limit reached)
Problem: MySQL: Buffer pool utilization is too low (less 50% for 5m)


Сделал дигностику при помощи mysqltuner

mysqltuner
>> MySQLTuner 1.7.21 - Major Hayden
>> Bug reports, feature requests, and downloads at mysqltuner.pl
>> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.3.28-MariaDB-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/lib/mysql/mysql_error.log exists
[--] Log file: /var/lib/mysql/mysql_error.log(4M)
[OK] Log file /var/lib/mysql/mysql_error.log is not empty
[OK] Log file /var/lib/mysql/mysql_error.log is smaller than 32 Mb
[OK] Log file /var/lib/mysql/mysql_error.log is readable.
[!!] /var/lib/mysql/mysql_error.log contains 22018 warning(s).
[!!] /var/lib/mysql/mysql_error.log contains 108 error(s).
[--] 13 start(s) detected in /var/lib/mysql/mysql_error.log
[--] 1) 2021-04-15 11:31:51 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2021-04-15 11:08:05 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2021-04-12 16:37:37 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2021-04-12 15:07:55 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2021-04-02 13:06:44 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2021-04-01 18:53:47 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2021-04-01 18:40:11 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2021-04-01 18:38:05 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2021-04-01 15:59:01 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2021-04-01 15:58:48 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 12 shutdown(s) detected in /var/lib/mysql/mysql_error.log
[--] 1) 2021-04-15 11:31:47 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2021-04-15 11:08:01 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2021-04-12 16:37:34 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2021-04-12 15:07:51 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2021-04-02 13:06:42 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2021-04-01 18:53:46 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2021-04-01 18:40:10 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2021-04-01 18:38:04 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2021-04-01 15:59:00 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2021-04-01 15:58:47 0 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 5.1G (Tables: 1313)
[--] Data in InnoDB tables: 4.8G (Tables: 3044)
[OK] Total fragmented tables: 0

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2h 26m 59s (5M q [651.674 qps], 375K conn, TX: 48G, RX: 793M)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory : 15.5G
[--] Max MySQL memory : 43.6G
[--] Other process memory: 0B
[--] Total buffers: 10.7G global + 134.7M per thread (250 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 43.7G (281.91% of installed RAM)
[!!] Maximum possible memory usage: 43.6G (281.06% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (5/5M)
[!!] Highest connection usage: 100% (251/250)
[OK] Aborted connections: 0.33% (1237/375749)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 45.4% (3M cached / 8M selects)
[!!] Query cache prunes per day: 5770401
[OK] Sorts requiring temporary tables: 0% (553 temp sorts / 239K sorts)
[!!] Joins performed without indexes: 599
[!!] Temporary tables created on disk: 92% (101K on disk / 109K total)
[OK] Thread cache hit rate: 76% (88K created / 375K connections)
[!!] Table cache hit rate: 0% (1K open / 207K opened)
[!!] table_definition_cache(400) is lower than number of tables(4516)
[OK] Open file limit used: 2% (336/16K)
[OK] Table locks acquired immediately: 100% (96K immediate / 96K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.9% (12M used / 67M cache)
[OK] Key buffer size / total MyISAM indexes: 64.0M/311.7M
[OK] Read Key buffer hit rate: 99.6% (12M cached / 53K reads)
[!!] Write Key buffer hit rate: 17.8% (5K cached / 960 writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 10.0G/4.8G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.9375 %): 48.0M * 2/10.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 80 for 8 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.99% (2618062923 hits/ 2618238636 total)
[!!] InnoDB Write Log efficiency: 27.5% (9900 hits/ 36003 total)
[OK] InnoDB log waits: 0.00% (0 waits / 45903 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[!!] Aria pagecache hit rate: 94.7% (1M cached / 101K reads)
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/lib/mysql/mysql_error.log file
Control error line(s) into /var/lib/mysql/mysql_error.log file
Restrict Host for 'zbx_monitor'@% to 'zbx_monitor'@LimitedIPRangeOrLocalhost
RENAME USER 'zbx_monitor'@'%' TO 'zbx_monitor'@LimitedIPRangeOrLocalhost;
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Reduce or eliminate persistent connections to reduce connection usage
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer...
(specially the conclusions at the bottom of the page).
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table...
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (16681) variable
should be greater than table_open_cache (1024)
Performance schema should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
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:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 250)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (=0)
query_cache_type (=0)
query_cache_size (> 16M)
join_buffer_size (> 2.0M, or always use indexes with JOINs)
table_open_cache (> 1024)
table_definition_cache(400) > 4516 or -1 (autosizing if supported)
performance_schema = ON enable PFS
innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=10)


Мой server.cnf

server.cnf
[mysqld]
max_connections = 250
slow_query_log=ON
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 8
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
transaction-isolation = READ-COMMITTED
query_cache_type = 1
query_cache_size=16M
query_cache_limit=4M
key_buffer_size=64M
join_buffer_size=2M
sort_buffer_size=4M
tmp_table_size=512M
max_heap_table_size=512M
thread_cache_size = 4
table_open_cache = 1024
max_allowed_packet = 128M
sql_mode = ""


На сервере 15 гб оперативки и 16 ядер, диски SSD
В среднем LoadAverage порядка 5-8 едениц

Помогите оптимизировать пожалуйста!
  • Вопрос задан
  • 566 просмотров
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы