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

Question

[SteepNET]

Добрый день! Настройил 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 едениц

Помогите оптимизировать пожалуйста!

Comments

[FanatPHP]

все эти ошибки - это не настройки сервера, а говнокод в приложении, которое пользуется БД.
Number of on-disk temporary tables в переводе на русский означает "кривые неоптимизированные запросы без индексов"
Refused connections (max_connections limit reached) означает "неоптимальный код, долгое выполнение запросов, возможное создание кучи подключений на один НТТР реквест". Может уйти, если решится п.1
Buffer pool utilization is too low (less 50% for 5m) означает буквально прямым текстом: у сервера-то ресурсов полно, куда больше чем надо для крошечной базки, то есть дело однозначно не в нём

[Денис Юрьев]

FanatPHP, max_connections limit reached еще встречается когда создаются постоянные подключения без надобности, эникейщик написал статью про оптимизацию коннектов, другой эникейщих бездумно повторил

беда всплывает в тот момент, когда становится понятно, что у первого эникейщика блог с трафиком 2 посетителя в месяц, а у второго 2 посетителя в минуту.

[FanatPHP]

Денис Юрьев, тоже верно. На стаковерфлоу самый частый случай - попытка заливать пожар бензином, когда для решения проблемы too many connections рекомендуют использовать persistent connect

[SteepNET]

FanatPHP, Скорее соглашусь с Вами, действительно код менялся то одними то другими, он то работает то нет, но как же рекомендации тюнера? Кним стоит присмотреться?

[Денис Юрьев]

SteepNET, к ним можно присматриваться только когда понимаешь что написано, в других случаях можно взять, свернуть и выбросить.

Так что построчно сиди и изучай каждый пункт что это и как работает.

иначе, например, я даже хз как ты выполнишь вот эти два пункта

query_cache_size (=0)
query_cache_size (> 16M)

PS: еще эти рекомендации исходят из предположения, что на данном сервере кроме mysql запущено ровно ничего. Сомневаюсь что вы отдельный сервак для базы юзаете.

[SteepNET]

Денис Юрьев, Я понимаю это дословно так
query_cache_size либо должен быть выключен либо быть меньше 16М
На сервере порядка 50 сайтов
То что изучать понятное дело, но помощи все же прошу поэтому и сделал вопрос

[FanatPHP]

к каким конкретно?

[SteepNET]

FanatPHP, Да там полно их этих рекомендаций, ко всем!

[FanatPHP]

"Все" - это там чисто общие рекомендации. Вести здоровый образ жизни, заниматься фитнесом.
Я понимаю что тебе неохота разбираться с реальной проблемой, и ты ищешь волшебную палочку, которой ткнуть в одной место - и всё залетает.
Но мы не в сказке, в реальной жизни так не бывает, чтобы была какая-то волшебная кнопка в настройках.
Тебе тюнер пишет главное - адов говнокод насилует базу. А ты все пытаешься бегать вокруг и торговаться - "а можно я не буду насильников отгонять, а просто простыночку поправлю, чтобы базке удобнее было?"

[FanatPHP]

у меня есть своя рекомендация:
включи slow query log и смотри какой сайт косячит
запускай show processlist и смотри чьи запросы чаще всего висят

[FanatPHP]

вот кстати зуб даю на отсечение - show processlist сразу покажет чья собака порылась и кому по шапке давать. и будет в 100 раз информативнее, чем все эти тюнеры вместе взятые

[SteepNET]

FanatPHP, Да я и не спорю, ненадо агресировать , на самом деле сложно рабираться коде когда его пишут все кому не лень там, слоу лог включен, и я передавал эти запросы кодерам этим ,им пофигу на это...,они требуют решения от меня...
# mysql -e "show full processlist\G"| grep -E "Command:"| wc -l
19
# mysql -e "show full processlist\G"| grep -E "Command:"|grep -i "sleep"| wc -l
6
# mysql -e "show full processlist\G"| grep -E "Command:"|grep -iv "sleep"| wc -l
6

| 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |

[FanatPHP]

эээ. ну так спящие тоже надо смотреть. Это значит что кто-то занимает коннект к базе, но ничего не делает

в принципе, 19 одновременных коединений это не так много
я бы мониторил по крону, когда количество растёт, скажем за сотню, и скидывал дамп show processlist

[ThunderCat]

SteepNET, По описанию похоже что периодически у вас в базу прилетает масса запросов, а в остальное время сервер плюет в потолок. Возможно некоторые сайты используют что-то типа грабберов или другие функции, записывающие большой массив информации в короткий промежуток времени. В этом ключе логично поставить мониторинг запросов или, как предложил FanatPHP, отмониторить что и когда грузит систему. А так тыкать пальцем в небо бесполезно.

[ThunderCat]

SteepNET, Как вариант, если есть такая возможность, можно просмотреть кроны с сайтов, которые выполняются и проверить именно эти временные промежутки на загрузку базы.

[Антон]

Не Битрикс ли у вас?)
Если он, то innodb_flush_log_at_trx_commit ставить надо 0, вопреки рекомендации
innodb_buffer_pool_size поставь 4
max_connections пока 120 сделай

[FanatPHP]

Антон, я могу понять первую, хотя и с оговорками, но при чем здесь вторая и третья?
Скажем, как поможет постоянная ошибка too many connections?

[SteepNET]

Антон, Битрикс есть но не на BitrixENV естественно а просто как развернут но один сайт ,все остальные обычый NGINX+FPM , увеличил оперативку вчера на 4 гб, урезал немного Buffer pool , включил perfomance схему на mysql ,урезал пару буферов по рекомендации mysqltuner, вроде бы алерты пропали пока что....
Но ThunderCat заметил верно, как начинается обход SEO бота сайтов , то караул, нагрузка зашкаливает за 12 едениц,
Сейчас у нас трафик исходящий превышает норму и доходит до 15 мбит\с , вроде бы как то определил что на яндекс идет трафик может кто по своему опыту подсказать кто чем пользуется для таких целей, нужно точно понять какие IP жрут трафик больше всего..

[Денис Юрьев]

SteepNET,

трафик исходящий превышает норму и доходит до 15 мбит\с

ща прослезиться хочется, у меня на одном проекте трафик на сервак с бд к 10гбит/с приближается периодически

[SteepNET]

Денис Юрьев, Учитывая что стоит защита от DDos с лимитом трафика 5мбит\с за каждый превышенный 1мбит\с платится 250 рублей ,то в день порядка 150 рублей уходит на это..

Что касается Ваших слез то локлаьный трафик кторый составляет очень больших цифр сюда я не приплюсовываю что бы Вас не смешить...
Серверов у нас несколько и ммежду ними идет трафик по локальной сети а не по внешней...

[Денис Юрьев]

SteepNET, вы уверены что вам нужна защита от ддос? вас прям ддосят так люто?
судя по тому, что мы видим - у вас там не ддос, а кривые запросы в базу

[Денис Юрьев]

SteepNET,
вот вам трафик обычного коммерческого среднестатистического проекта без преферанса и куртизанок

никаких ддосов

[d'Ivan]

От "DDoS" поисковиков еще кэширование помогает.

[Антон]

FanatPHP, переформулируйте вопрос, а то я вообще ничего не понял

[Антон]

SteepNET, может мне показалось, но странно ведь искать ip, которые жрут ваши ресурсы. Нужно оптимизировать код, при чем комплексно. Почему спросил про Битрикс - потому что я увидел, что ваши значения высталвены в аккурат, как пишут в доках Битрикса.
Я и сам временами настраиваю связки isp manager и БД и никогда не делал это за раз.
Только - базовая настройка, ждем какое то время, немного подгоняем некоторые значения, и опаять наблюдаем...

[SteepNET]

Антон, Про IP это я спросит, так , попутнок как говориться...
Да,Битрикс есть но он болтается так , можно его не брать в расчет..
Битрикс разварачиваю в черьез только на BitrixENV ,остальное все муть..

Настройка nginx+php-fpm+mysql это да ,тема щикотливая ,поэтому были проблемы и создал вопрос, сейчас вроде все стабилизировалось