Mysql грузит диск

12 3
D
На сайте с 25.08.2011
Offline
61
1242

Здравствуйте!

MariaDB периодически загружает диск на 100%, сайты начинают медленно открываться.

VPS у хостера IHOR. 6 cpu, 8 ram, SSD диск.

Сам хостер советует переезд на физически выделенный сервер, чтобы диск был полностью под мои запросы. Но и ценник в 2 раза дороже. Тут вопрос, а поможет ли? Возможно что-то не так с конфигами, или можно добавить оперативки к VPS и получится разгрузить диск?

Размещено около 20 сайтов на Wordpress. Посещаемость 14к уников в сутки.

информационные сайты на WP. Только страницы, картинки.

У одного сайта размер БД 14 ГБ. Второй 2 ГБ. Остальные до 500 мб.

Таблицы InnoDB. Все сайты кешируются плагином d-wp.

Конфиг mariadb справлялся, пока не разрослась база у большого сайта.

Также прикрепляю скрин теста диска на чтение (с отключенным mysql). Около 120мб/с.

memlock
skip-name-resolve=off
skip-external-locking
key_buffer_size = 2600M
max_allowed_packet = 16M
table_open_cache = 4000
myisam_sort_buffer_size = 16M
thread_cache_size = 16
query_cache_size = 0
query_cache_type = 0
thread_concurrency = 8
tmp_table_size=1G
max_heap_table_size=1G
bulk_insert_buffer_size=1G
## Per-Thread Buffers * (max_connections) = total per-thread mem usage
thread_stack = 256K #default: 32bit: 192K, 64bit: 256K
sort_buffer_size = 512K #default: 2M, larger may cause perf issues
read_buffer_size = 512K #default: 128K, change in increments of 4K
read_rnd_buffer_size = 512K #default: 256K
join_buffer_size = 512K #default: 128K
binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries
#

#innodb_use_native_aio = 0
innodb_file_per_table = 1
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4
innodb_stats_on_metadata=OFF
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 512M

max_connections=130
max_user_connections=100
wait_timeout=100
interactive_timeout=50
jpg atop.jpg
jpg htop.jpg
jpg 185877.jpg
LEOnidUKG
На сайте с 25.11.2006
Offline
1551
#1

Надо выяснить причину т.е. посмотреть, что за запросы.

Кэширования будет кэшировать что-то готовое, но сначала надо надо это готовое где-то взять.

Может у вас там woocom с модулем фильтрации с 5000 сайтов и 1000 разных характеристик. И надо заменять модуля.

Так сказать, не вариант.

---------- Добавлено 20.08.2019 в 21:17 ----------

Также прикрепляю скрин теста диска на чтение (с отключенным mysql). Около 120мб/с.

В смысле вырубленные сайты и такая нагрузка? У вас там скачка музыки/файлов?

✅ Трастовых площадок под размещение статей и ссылок. Опыт 12 лет! ( https://searchengines.guru/ru/forum/675690 ) ⭐ Купить вечные трастовые ссылки для сайта ( https://getmanylinks.ru/?srh ) ⭐ Ускорение ваших сайтов (WP, Opencart и др.) + Настройка сервера ( https://searchengines.guru/ru/forum/997205 )
D
На сайте с 25.08.2011
Offline
61
#2

Лог медленных запросов сегодня вёл весь день.

Команда mysqldumpslow -s at -t 10

Reading mysql slow query log from /var/log/mariadb/mysql-slow.log

Count: 1 Time=108.52s (108s) Lock=0.00s (0s) Rows_sent=0.0 (0), Rows_examined=681092.0 (681092), admin_donsergios[admin_donsergios]@localhost
SELECT ID FROM wp_posts WHERE to_ping <> 'S' AND post_status = 'S'

Count: 1 Time=84.52s (84s) Lock=0.00s (0s) Rows_sent=500.0 (500), Rows_examined=43680.0 (43680), admin_donsergios[admin_donsergios]@localhost
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'S' ) LEFT JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE N=N AND (
wp_postmeta.post_id IS NULL
OR
( mt1.meta_key = 'S' AND mt1.meta_value != 'S' )
) AND wp_posts.post_type = 'S' AND ((wp_posts.post_status = 'S')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT N, N

Count: 5 Time=73.68s (368s) Lock=0.00s (0s) Rows_sent=240202.0 (1201010), Rows_examined=580173.8 (2900869), admin_donsergios[admin_donsergios]@localhost
SELECT
p.ID,
p.post_author,
p.post_status,
p.post_name,
p.post_parent,
p.post_type,
p.post_date,
p.post_date_gmt,
p.post_modified,
p.post_modified_gmt,
p.comment_count
FROM
wp_posts p
WHERE
p.post_password = 'S'
AND p.post_type = 'S'
AND p.post_status = 'S'
AND YEAR(p.post_date_gmt) = N
AND MONTH(p.post_date_gmt) = N
AND p.ID NOT IN (N)

ORDER BY
p.post_date_gmt DESC

Count: 1 Time=71.40s (71s) Lock=0.00s (0s) Rows_sent=10.0 (10), Rows_examined=25852.0 (25852), pristav_admin[pristav_admin]@[194.67.221.129]
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE N=N AND (
wp_term_relationships.term_taxonomy_id IN (N)
) AND wp_posts.post_type = 'S' AND (wp_posts.post_status = 'S') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT N, N

Count: 208 Time=63.35s (13176s) Lock=0.34s (71s) Rows_sent=10.0 (2080), Rows_examined=125114.1 (26023726), admin_donsergios[admin_donsergios]@localhost
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE N=N AND (
wp_term_relationships.term_taxonomy_id IN (N)
) AND wp_posts.post_type = 'S' AND ((wp_posts.post_status = 'S')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT N, N

Count: 166 Time=62.69s (10406s) Lock=0.03s (4s) Rows_sent=0.0 (0), Rows_examined=13601.2 (2257801), 13users@2hosts
UPDATE wp_posts SET post_status='S' WHERE post_status='S' AND post_date_gmt < 'S'

Count: 4 Time=55.63s (222s) Lock=0.00s (0s) Rows_sent=500.0 (2000), Rows_examined=81871.5 (327486), news_donsergios[news_donsergios]@localhost
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE N=N AND wp_posts.post_type = 'S' AND ((wp_posts.post_status = 'S')) ORDER BY wp_posts.post_date DESC LIMIT N, N

Count: 10 Time=47.49s (474s) Lock=0.00s (0s) Rows_sent=1.0 (10), Rows_examined=151531.0 (1515310), 2users@localhost
SELECT post_modified_gmt FROM wp_posts WHERE post_status = 'S' AND post_type IN ('S', 'S', 'S') ORDER BY post_modified_gmt DESC LIMIT N

Count: 1 Time=44.16s (44s) Lock=0.00s (0s) Rows_sent=1.0 (1), Rows_examined=339978.0 (339978), admin_donsergios[admin_donsergios]@localhost
SELECT post_date FROM wp_posts WHERE post_status = 'S' AND post_type IN ('S', 'S', 'S') ORDER BY post_date_gmt DESC LIMIT N

Count: 3 Time=34.70s (104s) Lock=0.00s (0s) Rows_sent=1.0 (3), Rows_examined=157239.7 (471719), news_donsergios[news_donsergios]@localhost
SELECT COUNT(*) FROM wp_term_relationships, wp_posts WHERE wp_posts.ID = wp_term_relationships.object_id AND post_status = 'S' AND post_type IN ('S') AND term_taxonomy_id = N

Ещё вывод mysqltuner

--------------------

[--] Up for: 10h 9m 42s (4M q [135.160 qps], 46K conn, TX: 36G, RX: 2G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory : 7.8G
[--] Max MySQL memory : 8.0G
[--] Other process memory: 949.7M
[--] Total buffers: 7.7G global + 2.2M per thread (130 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 7.7G (99.40% of installed RAM)
[!!] Maximum possible memory usage: 8.0G (102.19% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (481/4M)
[OK] Highest usage of available connections: 23% (31/130)
[OK] Aborted connections: 0.08% (37/46578)
[OK] Query cache is disabled by default due to mutex contention on multiprocesso r machines.
[!!] Sorts requiring temporary tables: 123% (1M temp sorts / 1M sorts)
[!!] Joins performed without indexes: 878
[!!] Temporary tables created on disk: 86% (618K on disk / 717K total)
[OK] Thread cache hit rate: 99% (41 created / 46K connections)
[!!] Table cache hit rate: 2% (442 open / 16K opened)
[OK] Open file limit used: 10% (104/1K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M 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: 6 thread(s).
[--] Using default value is good enough for your version (5.5.60-MariaDB)

-------- MyISAM Metrics -------------------------------------------------------- --------------------
[!!] Key buffer used: 29.8% (812M used / 2B cache)
[OK] Key buffer size / total MyISAM indexes: 2.5G/2.6G
[OK] Read Key buffer hit rate: 99.9% (863M cached / 859K reads)
[OK] Write Key buffer hit rate: 97.8% (118K cached / 116K writes)

-------- InnoDB Metrics -------------------------------------------------------- --------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 4.0G/19.0G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 512.0M * 2/4.0G shoul d be equal 25%
[OK] InnoDB buffer pool instances: 4
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.95% (8109091797 hits/ 8113402090 total)
[OK] InnoDB Write log efficiency: 99.71% (24562641 hits/ 24632966 total)
[OK] InnoDB log waits: 0.00% (0 waits / 70325 writes)

-------- AriaDB Metrics -------------------------------------------------------- --------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.0% (54M cached / 565K 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: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ------------------------------------------------------- --------------------
General recommendations:
Control warning line(s) into /var/log/mariadb/mariadb.log file
Control error line(s) into /var/log/mariadb/mariadb.log file
Restrict Host for user@% to user@SpecificDNSorIp
MySQL was started within the last 24 hours - recommendations may be inaccura te
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Adjust your join queries to always utilize indexes
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: http://*******/1mi7c4C
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/op timizing-table_open_cache/
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 (1024) variable
should be greater than table_open_cache (442)
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
sort_buffer_size (> 512K)
read_rnd_buffer_size (> 512K)
join_buffer_size (> 512.0K, or always use indexes with JOINs)
table_open_cache (> 442)
innodb_buffer_pool_size (>= 19.0G) if possible.


---------- Добавлено 20.08.2019 в 21:31 ----------

LEOnidUKG:
Надо выяснить причину т.е. посмотреть, что за запросы.

В смысле вырубленные сайты и такая нагрузка? У вас там скачка музыки/файлов?

Нет, информационные сайты на WP. Только страницы.

lonelywoolf
На сайте с 23.12.2013
Offline
151
#3
innodb_buffer_pool_size (>= 19.0G) if possible.

Ну и какой объем ваших БД? Естественно, что они не влезают в память и читаются с диска.

Вообще, крайне рекомендуется второй диск поставить в RAID, особенно при такой нагрузке.

В целом, можете вынести базы на SSD - проблема обращения к диску вас беспокоить перестанет.

Ну, а так - у вас там все базы большие? или много маленьких? Если много маленьких - наращивайте ресурсы. Если пара больших БД, а остальные мелкие - разбираться надо именно с ними.

---------- Добавлено 21.08.2019 в 01:26 ----------

P.S> ну, а вообще тест вы выполняли на линейные чтение/запись, арндомные чтение/запись у вас в пределах нормы для HDD. Даже не плохи, я бы сказал.

Платный и бесплатный хостинг с защитой от DDoS (http://aquinas.su)
D
На сайте с 25.08.2011
Offline
61
#4
lonelywoolf:

В целом, можете вынести базы на SSD - проблема обращения к диску вас беспокоить перестанет.

Получается под объем баз в 19 Гб нужно 19 Гб оперативки, если не переносить на отдельный винт базы?

Если подключать второй диск - надо на физически выделенный сервер переехать? И какую конфигурацию выбирать - SSD+SSD, на одном базы, на другом всё остальное? Планируется увеличение базы до 100 Гб на одном сайте

LEOnidUKG
На сайте с 25.11.2006
Offline
1551
#5
Получается под объем баз в 19 Гб нужно 19 Гб оперативки, если не переносить на отдельный винт базы?

В идеале да, но такая роскошь для 14К не обоснована.

D
На сайте с 25.08.2011
Offline
61
#6
LEOnidUKG:
В идеале да, но такая роскошь для 14К не обоснована.

Остается вариант с выносом на отдельный диск, переездом на дедик сервер.

Калькулятор здесь смотрю

https://www.ihor.ru/dedic#cfgMTE7ODswOzE7MjswOzA7MDszMDsxOzA7MTsxOzI7MTswOzU7MDswOzE7MDswOzE7MTA7MTAw

Если базы будут на отдельном диске - сколько оперативки тогда брать, 4 Гб хватит? ssd будет за оперативку отдуваться получается.

И вариант файлы+система на HDD, а только база на ssd получится ли удачным?

baas
На сайте с 17.09.2012
Offline
121
#7

Оперативка не так дорого, я бы лучше вывел бы все в оперативку чем на ссд.

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

Настройка BSD систем. (https://www.fryaha.ru) Знание сила, незнание Рабочая сила!
LEOnidUKG
На сайте с 25.11.2006
Offline
1551
#8

Ребята! А может сначала выяснить, ПОЧЕМУ идёт такое поведение? А то я вижу тут все такие хостеры: ДА ДА, покупайте выделенный сервер! Побольше характеристик! Касса вон там за углом.

lonelywoolf
На сайте с 23.12.2013
Offline
151
#9

Нужно уже бюджет огласить.

Нет, берите диски в RAID. Т.е. 2 HDD + 2 SSD. Оперативки по максимуму. Ну и т.п. Вам, по идее, консультация в мессенджере нужна. Создайте тему в разделе "хостинг", со ссылкой на эту, пусть хостеры предлагают и обсуждают, а там вы уже и поймете, что вам нужно.

---------- Добавлено 21.08.2019 в 15:03 ----------

LEOnidUKG:
А может сначала выяснить, ПОЧЕМУ идёт такое поведение?

Что там выяснять? У него запрос по большим БД. Раз товарищ решил нарастить ресурсы - пусть наращивает. Ну вы же понимаете, что случайное чтение по 100 ГБ базе не будет быстрым по определению? А ещё пока идёт выборка по такой базе - будут тормозить и более мелкие.

Да там нужно и индексы смотреть, и вообще запросы и т.п. Но 100 Гбайт и мгновенно - как-то не вяжется. С учетом, что в InnoDB 19 гигабайт, а запрос поди читает все строки из базы - ну, чего ж там уже дёргаться то. Там надо или переделывать всё или железо менять на то, которое хоть как-то будет тащить. Если это ИМ - однозначно менять железо.

baas
На сайте с 17.09.2012
Offline
121
#10

Запросы в базе не оптимизированы.

Time=108.52s жуть!

Все что больше одной секунды это зло! )))

12 3

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий