LINUX.ORG.RU
ФорумAdmin

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

 , , ,


0

3

Есть нагруженная БД на mysql, которую нужно ускорить. Перенос чтения на слейв особо не поможет, так-как преимущественно идет запись. Слейв играет роль резервного сервера и с него снимаются бэкапы. Виртуалка висит на отдельном сетевом интерфейсе, между клиентами и БД локалка 1Гбит. Винт используется только этой одной виртуальной машиной.

Виртуалка представляет из себя следующее: Ubuntu server 18 (крутится на hyper-v), Percona server 5.7, Движок innodb, 50Гб озу, 16 ядер, 800Гб SSD

Размер базы 300гб+бинарные логи. Нагрузка по чуть-чуть будет расти, т.к. увеличивается количество данных.

Собственно вопрос, какие есть варианты уменьшить нагрузку на БД и увеличить время отклика не прибегая к изменению в коде клиентов? Возможно ли руками раскидать файлы нагруженных таблиц mysql по 2-3 SSD дискам в приделах одной виртуалки, а потом создать символьные ссылки в рабочий каталог mysql? Знаю, что изврат, но все же.

1) Пробовал перебросить нагрузку по чтению на слейв используя ProxySQL, по словам разработчиков прироста не получили.

2) Если использовать Percona Cluster (mysql master-master), то все равно нужен будет балансировщик? Либо придется пилить на уровне кода клиентского приложения? Насколько такой вариант стабильный и надежный? (Видел отзывы 2-3 летней давности, что часто падает репликация) Будет ли прирост производительности? Какие подводные камни?

3) Вариант горизонтального шардинга ПОКА был отброшен, т.к. требует изменений в коде приложения, чтоб оно само знало куда что писать и где какие куски таблиц находятся.

Все это непотребство выглядит примерно так https://c.radikal.ru/c12/1903/a7/ab8b6ba64e1e.png



Последнее исправление: Pleaselogin (всего исправлений: 1)

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

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

vinvlad ★★
()
Ответ на: комментарий от vinvlad

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

Pleaselogin
() автор топика
Ответ на: комментарий от Pleaselogin

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

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

Anoxemian ★★★★★
()
Последнее исправление: Anoxemian (всего исправлений: 1)
Ответ на: комментарий от vinvlad

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

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

Pleaselogin
() автор топика

hyper-v

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

superuser ★★★★☆
()
Ответ на: комментарий от Pleaselogin

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

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

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

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

vinvlad ★★
()
Ответ на: комментарий от Anoxemian

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

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

vinvlad ★★
()
Ответ на: комментарий от vinvlad

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

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

Pleaselogin
() автор топика

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

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

vinvlad ★★
()
Ответ на: комментарий от Pleaselogin

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

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

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

vinvlad ★★
()
Ответ на: комментарий от vinvlad

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

оффтоп:

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

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

Pleaselogin
() автор топика
Ответ на: комментарий от Pleaselogin

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

Ну что тут можно посоветовать... Если речь идет о реляционных базах данных - которые 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-запросов выполняется при конкретных клиентских операциях - может, там какие дурные циклы есть, которые можно в один-два запроса переделать.
Вот с этого нужно начинать разборки, когда возникают «тормоза» в работе базы данных.

vinvlad ★★
()

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

bigbit ★★★★★
()

Перейти на последнюю версию 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

ism ★★★
()
Последнее исправление: ism (всего исправлений: 4)

800Гб SSD

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

usage: сред. 45.97 %

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

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

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

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

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

router ★★★★★
()
Ответ на: комментарий от router

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

router@calypso:~$ apt-cache show mysqltuner | grep ^Homepage
Homepage: http://mysqltuner.com
router ★★★★★
()
Ответ на: комментарий от router

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

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

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

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

router ★★★★★
()
Последнее исправление: router (всего исправлений: 1)
Ответ на: комментарий от router

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

bigbit ★★★★★
()
Последнее исправление: bigbit (всего исправлений: 1)
Ответ на: комментарий от bigbit

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

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

router ★★★★★
()
Ответ на: комментарий от router

Всем спасибо большое за ответы и советы, буду изучать тему. На серваке действительно 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

Pleaselogin
() автор топика
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.