Mysql грузит диск

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

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

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

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

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

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

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

Команда 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Time=108.52s жуть!

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