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

Восстановление данных из горячего InnoDB-бэкапа без перезапуска сервера

Каждый человек, который делает бэкапы каких-либо приложений (при копировании чего-либо отличного от обычных файлов) должен знать и понимать, что бэкап — это только половина дела по обеспечению надежности функционирования и защищенности данных сервиса от потери. Вторая, не менее важная, половина — это умение этот бэкап восстановить. По-хорошему, бэкапы надо регулярно восстанавливать в «тестовых» условиях, так сказать, проводить учения :) Чтобы при возникновении необходимости восстановления, не было вопросов типа «а что с этим делать?».

В случае с MyISAM-таблицами все обстоит совсем просто. Если сделать «flush tables with read lock», то их можно даже скопировать на файловом уровне. Хоть официально это и не поддерживается, но чаще всего работает «на ура». С InnoDB-таблицами все намного сложнее…

Для восстановления нам понадобится mysql-сервер той же версии, как и на «боевом» сервере. Если бэкапы делались при помощи снэпшотов и они еще не удалены с сервера, то на «боевом» сервере их восстановить будет даже проще — локально скопировать несколько файлов куда проще, чем доставать из архива и копировать по сети. Итак, для восстановления базы из горячего бэкапа в случае InnoDB-таблиц нам понадобится:

1) Копируем файл конфигурации с рабочего варианта (/etc/my.cnf в /etc/my2.cnf) и заменяем в нем: порт, который слушает mysql (3306) на любой другой (3307, например); сокет, который слушает mysql (/tmp/mysql.sock на /tmp/mysql2.sock); pid-файл (mysql.pid на mysql2.pid); директорию с файлами БД (/var/db/mysql на /var/db/mysql2).

2) Копируем в /var/db/mysql2 файлы из корня бэкапа (т.е. ib_logfile0, ib_logfile1 и др). Копируем из бэкапа директории performance_schema и mysql, а также директорию с базой данных, которую мы хотим восстановить.

3) Проверяем права на директорию /var/db/mysql2. Они должны быть такими же, как и на директорию /var/db/mysql.

4) Запускаем вторую копию mysql командой:

mysqld_safe --defaults-file=/etc/my2.cnf &

5) Запускаем mysqldump с необходимыми ключами (набор которых зависит от размера БД, количества памяти на сервере и т.д.). Если вы не знаете, какие ключи вам нужны, можете запускать его совсем без ключей. Главное — указать сокет:

mysqldump -S /tmp/mysql2.sock database_to_dump > /path/to/save/dump

6) Если дамп прошел успешно — гасим вторую копию mysql командой:

mysqladmin -S /tmp/mysql2.sock shutdown

После всего этого у нас будет дамп базы данных на руках. А с ним уже можно делать все, что душе угодно :)

Спасение сервера от медленных запросов mysql

Медленные запросы к mysql могут возникать по разным причинам: недостаточная производительность процессора, неоптимизированные запросы, криво настроенный сервер, большое количество «случайных» операций чтения и др. Иногда, во имя спасения всего сервера, легче «заставить» сервер отказаться от выполнения долгах запросов. К сожалению, mysql-сервер не умеет ограничивать максимальное время выполнения запроса. Поэтому приходится искать альтернативные пути. А именно, убивать запросы, которые выполняются дольше, чем мы хотим. Конечно, в таком случае работа веб-приложения завершится с ошибкой и, скорее всего, пользователь увидит HTTP 500, но бывают такие случаи, когда лучше отдать одному пользователю HTTP 500, чем всем остальным показать таймаут.

Итак, встречайте: очередной тул от percona — pt-kill! На FreeBSD ставится вот отсюда: /usr/ports/databases/percona-toolkit . Pt-kill — это такая маленькая, но полезная утилитка, которая умеет опрашивать mysql-сервер раз в n секунд и убивать медленные запросы. В общем-то, на этом ее функционал не ограничивается, но в данный момент нас интересует именно эта часть.

Для начала, можно проверить работу pt-kill в «тестовом» режиме. Вместо завершения соединений, он будет просто показывать те запросы, которые подходят под его условия. Для этого надо запустить его примерно такой командой:

pt-kill --busy-time 60 --print

В этом примере он должен выводить в консоль запросы, которые перешагнули порог в 60 секунд. Таймаут, необходимый вам, лучше подбирать самостоятельно исходя из характера нагрузки на сервер. В общем случае для сервера, на котором крутится не только mysql, но и apache, я бы рекомендовал выставлять его таким, чтобы количество процессов веб-сервера не начало расти из-за того, что mysql не успевает отдавать данные.

И, если вы все же уверены в том, что все эти запросы необходимо убивать, можете запустить его следующим образом:

pt-kill --busy-time 60 --print --kill

В таком режиме он будет выводить на экран и убивать по одному медленному запросу за один раз (самому «старому»). Если надо убивать их все сразу (допустим, одновременно их появилось 10 штук), надо указать ключ —victims all. И, наконец, если вы хотите, чтобы он работал в фоне, смело убирайте —print и добавляйте —daemonize.

pt-kill --busy-time 60 --kill -- victims all --daemonize

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

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

Давным-давно, на заре времен, когда сервера были большими, а базы данных маленькими, все нормальные люди бэкапили их просто при помощи 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. Смотрим кто и сколько места там занимает.

Иногда бывает полезно посмотреть какие таблицы находятся в 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-сервер начинаешь задумываться о том, как еще можно оптимизировать его производительность. Так уж случилось, что на рассматриваемом мной сервере внезапно возросла нагрузка в виде запросов на запись в 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 минут работы сервера.