Архив рубрики: mysql

Горячие бэкапы mysql при помощи снэпшотов ZFS в FreeBSD

Backup, mysql, , ,

Давным-давно, на заре времен, когда сервера были большими, а базы данных маленькими, все нормальные люди бэкапили их просто при помощи mysqldump. Получался в результате сего действия вполне себе хороший бэкап, который можно восстановить где угодно. Шло время, объемы баз росли… В какой-то момент придумали mysqlhotcopy для того, чтобы не тратить ресурсы на полное вычитывание базы (с блокировкой таблиц на это время). Он прекрасно справлялся с таблицами myisam, но с innodb ничего поделать не мог. Долгое время «горячий» бэкап innodb-таблиц был возможен только при помощи платного софта, который не все могли себе позволить. Потом добрая Percona выпустила свой бесплатный xtrabackup, который снимал горячие копии InnoDB, XtraDB и MyISAM таблиц. Вроде бы все было хорошо, но… Если ваш сервер не относится к high-end, но очень старается работать также (с минимальными перерывами в обслуживании), то даже бэкап с помощью xtrabackup на продакшене может вызвать небольшой даунтайм — если вы копируете базы размером в 5-10 Гб, а запросы к ним идут хотя бы пару раз в секунду. Конечно, можно сказать, что мир неидеален и надо ставить второй сервер, настраивать репилкацию и снимать копии со слэйва. Но не всегда на это выделяется должное финансирование.

Всю ситуацию может спасти сервер с FreeBSD на ZFS. В FreeBSD поддержка ZFS сделана уже довольно давно и работает она в ней стабильно, хоть и намного медленнее, чем в Solaris. Именно в ZFS есть модный нынче «механизм» снэпшотов (снимков) файловой системы. Причем сделано все так, что наличие большого количества снэпшотов никак не влияет на производительность сервера (кроме, конечно, поедания дискового пространства). Именно при помощи снэпшотов можно делать самые быстрые горячие бэкапы mysql. Но тут всплывает один нюанс: InnoDB сбрасывает «грязные» страницы на диск не сразу, да и мы можем сделать снэпшот как раз в момент записи данных (и чем больше пишем, тем больше шансов это сделать). Т.е. наша база в бэкапе может оказаться с битыми таблицами. Приятного мало.

Можно, конечно, набросать простенький скрипт, который будет делать «flush tables with read lock», потом снэпшот, потом «unlock tables», но лучше воспользоваться уже готовым вариантом. В FreeBSD портировали прекрасную софтинку (или даже скрипт) для автоматического создания снэпшотов — zfstools (искать тут: /usr/ports/sysutils/zfstools).  Ей необходимо передавать всего 2 параметра: название серии снэпшотов и количество снэпшотов, которые необходимо в этой серии хранить. Запускается она так:

/usr/local/sbin/zfs-auto-snapshot hourly 72

Объяснить ей что надо снэпшотить, а что нет, очень просто: необходимо выставить свойство com.sun:auto-snapshot = true для конкретной ФС в zpool (причем этот параметр наследуется всеми «дочерними» ФС). При запуске она просматривает все файловые системы в zpool и снэпшотит те из них, в которых свойство  com.sun:auto-snapshot установлено не в false. В случае снэпшота файловой системы, в которой лежат базы данных (вы же создали под них отдельную ФС, верно?) вместо true необходимо написать mysql. Скрипт автоматически выполнит команду «flush tables with read lock», сделает снэпшот и разблокирует таблицы. Таким образом, скорость выполнения будет напрямую зависеть от количества данных, которые необходимо сбросить на диск при снятии снимка.

Но опытные пользователи админы понимают: снэпшот — не замена бэкапа. Если умрет сервер — умрут и все снэпшоты. Для простого доступа к файлам снэпшотов, в FreeBSD реализован замечательный «скрытый» путь. Если мы сделали снэпшот /var/db/mysql при помощи zfs-auto-snapshot 1 марта 2015 года в 11:00, то получить к нему доступ сможем по пути: /var/db/mysql/.zfs/snapshot/zfs-auto-snap_hourly-2015-03-01-11h00. Таким образом, сразу после снятие снэпшота, его можно скопировать куда угодно каким угодно способом. А для того, чтобы не создавать лишнюю нагрузку на диски, лучше всего копировать оттуда данные при помощи rsync — после первого копирования мы будем перемещать только измененные таблицы, что чаще всего значительно уменьшает время, необходимое на копирование и неплохо снижает нагрузку на диск. И уже после этого, в удаленном месте (это может быть даже виртуалка на обычном PC) снимаем бэкапы.

Таблицы в буфере InnoDB. Смотрим кто и сколько места там занимает.

mysql

Иногда бывает полезно посмотреть какие таблицы находятся в innodb-буфере (размер которого задается директивой innodb_buffer_pool_size). Исходя из того, что буфер Innodb работает по алгоритму LRU (least recently used), по содержимому буфера мы можем судить о наиболее часто используемых таблицах. Это может пригодиться, например, для оптимизации его размера (когда у нас не выделенный mysql-сервер). А можно и любопытства ради туда заглядывать :)

Итак, для того, чтобы узнать кто у вас там прописался и сколько процентов этого самого буфера он съедает, необходимо выполнить всего навсего один запрос (пример приведен для mysql 5.6):

USE information_schema;
SET @page_size = @@innodb_page_size;
SET @bp_pages = @@innodb_buffer_pool_size/@page_size;
 
SELECT P.TABLE_NAME, P.PAGE_TYPE,
CASE WHEN P.INDEX_NAME IS NULL THEN NULL WHEN P.TABLE_NAME LIKE '`SYS_%' THEN P.INDEX_NAME WHEN P.INDEX_NAME <> 'PRIMARY' THEN 'SECONDARY' ELSE 'PRIMARY' END AS INDEX_TYPE,
COUNT(DISTINCT P.PAGE_NUMBER) AS PAGES,
ROUND(100*COUNT(DISTINCT P.PAGE_NUMBER)/@bp_pages,2) AS PCT_OF_BUFFER_POOL,
CASE WHEN P.TABLE_NAME IS NULL THEN NULL WHEN P.TABLE_NAME LIKE 'SYS_%' THEN NULL ELSE ROUND(100*COUNT(DISTINCT P.PAGE_NUMBER)/CASE P.INDEX_NAME WHEN 'PRIMARY' THEN TS.DATA_LENGTH/@page_size ELSE TS.INDEX_LENGTH/@page_size END, 2) END AS PCT_OF_INDEX
FROM INNODB_BUFFER_PAGE AS P
JOIN INNODB_SYS_TABLES AS T ON P.SPACE = T.SPACE
JOIN TABLES AS TS ON T.NAME = CONCAT(TS.TABLE_SCHEMA, '/', TS.TABLE_NAME)
WHERE TS.TABLE_SCHEMA <> 'mysql'
GROUP BY TABLE_NAME, PAGE_TYPE, INDEX_TYPE;

На выходе будет примерно такая табличка (в данном случае были выполнены запросы только к таблице test.foo):

+--------------+-------------------+------------+-------+--------------------+--------------+
| TABLE_NAME   | PAGE_TYPE         | INDEX_TYPE | PAGES | PCT_OF_BUFFER_POOL | PCT_OF_INDEX |
+--------------+-------------------+------------+-------+--------------------+--------------+
| NULL         | FILE_SPACE_HEADER | NULL       |     1 |               0.00 |         NULL |
| NULL         | IBUF_BITMAP       | NULL       |     1 |               0.00 |         NULL |
| NULL         | INODE             | NULL       |     1 |               0.00 |         NULL |
| `test`.`foo` | INDEX             | PRIMARY    |  2176 |               3.32 |        98.37 |
| `test`.`foo` | INDEX             | SECONDARY  |  2893 |               4.41 |        88.47 |
+--------------+-------------------+------------+-------+--------------------+--------------+

Расчет размера innodb_log_file_size

mysql

С ростом нагрузки на mysql-сервер начинаешь задумываться о том, как еще можно оптимизировать его производительность. Так уж случилось, что на рассматриваемом мной сервере внезапно возросла нагрузка в виде запросов на запись в innodb-таблицы. Любые данные, которые пишутся в innodb, сперва записываются в лог innodb (ib_logfile0 и ib_logfile1) и только потом сбрасываются на диск. Если вдруг скорость записи строк в БД высокая, а файлы лога маленькие — серверу приходится чаще сбрасывать изменения на диск. При этом, мы получаем не последовательный доступ к диску (как при записи одного файла), а случайный — данные пишутся в разные таблицы, в разные БД, которые обычно разбросаны по диску.

Для того, чтобы знать какой размер лог-файлов должен быть, неплохо было бы узнать сколько данных пишется в БД за какой-нибудь промежуток времени. Это можно сделать, выполнив следующие запросы в консоли mysql:

mysql> pager grep sequence
PAGER set to 'grep sequence'
mysql> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 84 3836410803
1 row in set (0.06 sec)
1 row in set (1 min 0.00 sec)
Log sequence number 84 3838334638
1 row in set (0.05 sec)

Выполнять эти запросы желательно в момент пиковой нагрузки на сервер
В результате выполнения мы получим два числа. Log sequence number — это число байт, записанных в лог транзакций. Таким образом, мы можем вычислить объем информации, записываемой в минуту в лог:

  3838334638-3836410803=1923835

Т.е. в этом примере в лог записано почти 2 Мб за минуту. По некоторым рекомендациям, размера лога должно быть достаточно для хранения данных максимум за час работы сервера. Т.е. в этом случае размер одного лог-файла стоит сделать 64 Мб. Два лог-файла смогут вместить 60 минут работы сервера.

Тюнинг MySQL в ISPmanager 4. Не сохраняются изменения.

ispmanager, mysql, Панели управления хостингом, , ,

Довелось мне недавно столкнуться с панелью ISPManager 4, установленной на CentOS 6.5. Это был довольно слабый сервер на десктопном железе, но при этом количество сайтов на нем переваливало за сотню. Всего лишь 2 SATA-винчестера по 500 Гб в зеркале и 32 Гб SSD для баз mysql. Оперативной памяти было всего-то 8 Гб, но этого должно быть достаточно для работы сервера при грамотной настройке. А вот с грамотной настройкой были проблемы и по этой причине Load Average скакал от 4 до 60. В общем-то, 4 — это нормальная цифра для 4-ядерного процессора, но 60…

Первое, за что я решил взяться — это тюнинг MySQL. Открыл рабочий конфиг и взялся за голову: это был дефолтный конфиг из типовых настроек для «маленького» сервера, т.е. my-small.cnf:

# Example MySQL config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld daemon
# doesn't use much resources.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (using the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
server-id = 1

# Uncomment the following if you want to log updates
#log-bin=mysql-bin

# binary logging format - mixed recommended
#binlog_format=mixed

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

Как видим из конфига, ничего хорошего с таким сервером при >100 сайтов просто не могло быть: все кэши минимальные, кэш запросов не указан, а значит выключен, thread_cache_size не установлен, а значит равен нулю и т.д. Помимо этого, лимит открытых файлов на сервере равнялся 1024. Первым делом поднят был именно он (до 80000), после этого были подкручены table_open_cache, query_cache_size, thread_cache_size и некоторое количество других буферов. Настало время перезапуска сервера для применения настроек. Перезапуск производился средствами панели и каково же было мое удивление, когда вместо своего конфига я опять увидел дефолтный!

Как водится, поиск в гугле на тему подмены конфига ничего не дал. Беглый осмотр вики производителя панели выдал только информацию по добавлению mysql-сервера в панель. Пришлось покопаться в самой панели и внезапно обнаружить, что при рестарте mysql-сервера, происходит копирование все того же my-small.cnf в my.cnf! Оставим вопросы о целесообразности такого решения разработчикам: есть у меня стойкое ощущение, что многие владельцы (язык не поворачивается называть этих людей администраторами) этих панелей не знают что такое ssh и для чего он вообще нужен, если все есть в панели.

Таким образом, одним из возможных решений этой проблемы, является внесение изменений в my-small.cnf (в центоси он лежит тут: /usr/share/mysql), который и будет скопирован в /etc/my.cnf.

А как же советы по настройке? Настройка параметров — дело индивидуальное и сильно зависит от самого сервера, количества сайтов, количества Innodb и myisam таблиц и т.д.