Как разгрузить MYSQL

Ну, если хочется по-максимуму выжать из железа, то можно табличные пространства на raw-разделах держать. Ну и СУБД-шный кэш правильно отстроить. При этом кэш файловой может быть минимальным. Ну и системные накладные расходы, связанные с файловой системой, уменьшаются.

Если дисков несколько, то можно данные как-то пооптимальней раскидать по дискам, WAL-логи держать отдельно от часто используемых таблиц.

... правда, raw-девайсы сейчас не особо используют - слишком хлопотно админить )

А можно чуть подробнее? RAW раздел это который подключается к гипервизору без разметки и «пробрасывается» на виртуальную машину?

да. прокидывешь чистое блочное устройство и без файловой системы отдаешь целиком mysql, но там выигрыш будет мизерным. лучше обзавестись dba, чтобы он отладил запросы, ну и как вариант если база использует ряды в своей логике можно заняться разбитием на партиции самой базы.

https://dev.mysql.com/doc/refman/5.7/en/partitioning-overview.html

Как именно раскидать данные по дискам? Растаскивать отдельные таблицы или целые БД по разным винтам в приделах одного сервера?

Если отталкиваться от мануала https://dev.mysql.com/doc/refman/5.7/en/symbolic-links.html то в моем случае получается, что либо необходимо пересоздать таблицу с директивой DATA DIRECTORY, либо перенести каждую БД в виде папки и создать простую символьную ссылку (опять-же в приделах одного виртуального сервера)?

hyper-v

на винфак выбросить бяку не предлагали?

Как именно раскидать данные по дискам? Растаскивать отдельные таблицы или целые БД по разным винтам в приделах одного сервера?

Цель сего мероприятия - максимально распараллелить ввод-вывод и уменьшить взаимное «трение» :).

К примеру, есть у вас WAL-логи (которые REDO), куда в первую очередь закатываются все изменения. И по мере их заполнения они куда-то архивируются. Ясно, что архивировать лучше на другой диск. Кроме того, и сами WAL-логи лучше держать отдельно - на диске, где находятся редко используемые таблички. Иногда бывает выгодно держать индексы и сами таблицы на разных дисках.

Следует отметить, что вся эта оптимизация требует участия квалифицированного DBA.

... прокидывешь чистое блочное устройство и без файловой системы отдаешь целиком mysql, но там выигрыш будет мизерным...

Тут выигрыш в экономии на файловом кэше и в возможности более рационально использовать оперативную память. Если не raw-девайсы, то direct-io или как-нибудь еще отменять линуксовый файловый кэш для базы данных - лучше память отдать собственному кэшу MySQL.

Если хранить БД на файлах, то существенный момент - это выбор файловой системы.

Пока останавливаюсь на варианте хранить отдельные базы на других винтах средствами символьных ссылок. Протестил на временной базе, вроде бы, работает при условии, если выключить AppArmor (сейчас полетят тапки =)

Как влияет выбор ФС на работу СУБД? Наличие\отсутствие журналирования в самой ФС?

Все это непотребство выглядит примерно так ...

Есть еще один вариант оптимизации :) - уволить текущую команду разработчиков и набрать новых, более грамотных. Если, к примеру, работу с 300-гиговой базой они умудрились запрограммировать c применением какой-нибудь ORM, а не на чистом SQL, то вас уже ничто не спасет.

Пока останавливаюсь на варианте хранить отдельные базы на других винтах средствами символьных ссылок.

Вы какой-то ерундой занимаетесь - какие, на хрен, символьные ссылки??? Если нет соответствующей квалификации, не лезьте в физическую структуру хранения базы данных «ручками». У вас, судя по вашим действиям, квалификации однозначно нет. Да и реальная причина тормозов может быть вовсе не в высокой нагрузке.

ЕДИНСТВЕННЫЙ ВАРИАНТ - поискать грамотного DBA.

Спасибо большое за толковые советы и отсутствие троллинга.

оффтоп:

Хотел уточнить, что почитать, чтобы набраться ума в сфере БД? Чтоб начать вникать в тему с азов.

Увольнять разрабов никто не будет в силу тотальной (нет, АДСКОЙ) экономии и менталитета, по этой же причине не будет и квалифицированного администратора БД, а все проблемы вешаются на меня. И, как Вы верно заметили, соответствующей квалификации у меня нет, но этот пункт я хочу исправить. Прошу прощения, накипело....

что почитать, чтобы набраться ума в сфере БД? Чтоб начать вникать в тему с азов

Ну что тут можно посоветовать... Если речь идет о реляционных базах данных - которые RDBMS - то для начала вам нужно что-нибудь коротенькое, чисто обзорное по SQL. Тут любая книжка из серии «для чайников» подойдет. После этого уже можно охватить тему в более полном объеме - гляньте, например, «MySQL Administrator's Bible» (может, есть и что-то более подходящее - это так... навскидку). Ну а после этого уже документация:
https://dev.mysql.com/doc/
https://dev.mysql.com/doc/refman/5.7/en/
https://dev.mysql.com/doc/refman/8.0/en/
(выберите нужную вам версию - там можно скачать и в PDF-формате)

В Oracle к документации относятся весьма серьезно - она просто замечательная. В свое время я тему RDBMS просто по оракловой документации освоил - только не по MySQL, а по Oracle Database. Кстати, эту документацию тоже имеет смысл глянуть - там есть достаточно общие разделы.

Ну а что касается вашей текущей проблемы, то тут прежде всего нужно посмотреть, не встречаются ли в процессе работы БД «тяжелые» запросы, выполняемые слишком медленно. Тут будет полезен «Slow Query Log». Если таковые запросы найдутся, то нужно посмотреть план их выполнения (Optimization -> Understanding the Query Execution Plan). Может, каких индексов не хватает или SQL-запросы можно как-то более оптимально переформулировать. Еще надо глянуть, сколько SQL-запросов выполняется при конкретных клиентских операциях - может, там какие дурные циклы есть, которые можно в один-два запроса переделать.
Вот с этого нужно начинать разборки, когда возникают «тормоза» в работе базы данных.

Где ты там нагруженность увидел?
Можно даже забрать часть ресурсов у этого сервера и отдать другим виртуалкам, которым это нужно.

Перейти на последнюю версию mysql, там наверняка получше с производительностью

Верно для Mariadb 10.1

Поиграть с

innodb_flush_method=fsync

Выставить

innodb_default_row_format=dynamic
innodb_file_format=Barracuda

Попробовать

row format compressed, чем черт не шутит
ALTER TABLE price DELAY_KEY_WRITE = 1; опасно но прикольно
innodb_buffer_pool_size=300M # Как можно больше
innodb_flush_log_at_trx_commit=2 # 1 надежность, 2 скорость, но риск потерять данные за несколько секунд
innodb_log_file_size=150M При большой записи побольше, но не слишком

В некоторых случаях может помочь партиционирование

Если уж совсем дебри, то ssd с

mkfs.xfs -i size=1024 -s size=4096 /dev/sdc1 с опцией
http://xfs.org/docs/xfsdocs-xml-dev/XFS_User_Guide//tmp/en-US/html/ch04s08.html
http://xfs.org/docs/xfsdocs-xml-dev/XFS_User_Guide//tmp/en-US/html/ch05s06.html

800Гб SSD

задержка чтения: сред. 3.68 мс

usage: сред. 45.97 %

Тебя обманули, это ни разу не ssd. Стандартные цифры для sas

Про slow логи и explain тебе уже всё объяснили. Разве что для начала можно прогнать mysqltuner, он обычно дело говорит

Чисто теоретически, может быть высокая нагрузка на гипервизор или паршивый и изношенный ssd, а на гипервизоре не работает trim, но это маловероятно. Слишком уж типичные для sas цифры

3) Вариант горизонтального шардинга ПОКА был отброшен,

А партиционирование по текущей дате?

Разве что для начала можно прогнать mysqltuner, он обычно дело говорит

router@calypso:~$ apt-cache show mysqltuner | grep ^Homepage
Homepage: http://mysqltuner.com

Слишком уж типичные для sas цифры

Типичные и главное ровные. Это точно НЕ ssd

А партиционирование по текущей дате?

если с партиционированием не прокатит, хотя бы убедись что отдельные таблицы живут в отдельных файлах ( innodb_file_per_table ). Если сейчас не так, то после изменения настройки потребуется экспорт/импорт

Насчет типичности... Для SAS 3-4мс - это идеал, к которому нужно стремиться, и который редко получается достичь.
А у него 3000 IOPS на чтение и 24000 IOPS на запись. Это точно не SAS.

А у него 3000 IOPS на чтение и 24000 IOPS на запись. Это точно не SAS.

Да, согласен. Не заметил график с iops'ами, 2 оси Y зло

Всем спасибо большое за ответы и советы, буду изучать тему. На серваке действительно ssd, виртуалка привязана к отдельной сетевухе. Партициирование не используется.

Привожу конфиг Mysql:

# The Percona Server 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-server.conf.d/

[mysqld]
# CLIENT #
bind-address                   = 0.0.0.0
port                           = 3306
server-id                      = 2

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
#pid-file                       = /var/lib/mysql/mysql.pid

# SAFETY #
max-allowed-packet             = 32M
max-connect-errors             = 1000000
skip-name-resolve

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin.log
expire-logs-days               = 1
sync-binlog                    = 1
max-binlog-size                = 200M
binlog-format                  = row

# CACHES AND LIMITS #
tmp-table-size                 = 100M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max_connections                = 5000
max-connections                = 5000
thread-cache-size              = 100
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 10240
wait_timeout                   = 30
interactive_timeout            = 30
max_execution_time             = 300000

# INNODB #
innodb-flush-method            = O_DIRECT
innodb_flush_log_at_trx_commit  = 2
innodb-log-files-in-group      = 2
innodb-log-file-size           = 6000M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 34G
innodb-status-output           = off

# LOGGING #
log-error                      = /var/log/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/log/mysql/mysql-slow.log

gtid-mode=on
enforce-gtid-consistency=true