Архив за месяц: Март 2015

Горячие бэкапы 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 |
+--------------+-------------------+------------+-------+--------------------+--------------+

Открытие документов Excel в разных окнах (в разных процессах)

registry, Windows

Иногда приходится решать задачи, которые админскими не назовешь, но пользователи сами решить не могут. Один из таких примеров — открытие документов Excel в разных процессах. По умолчанию, Excel 2010 не позволяет расположить рядом на одном экране 2 открытых документа. Также, невозможно разместить 2 документа одновременно на двух мониторах. Исключение составляют те случаи, когда первый документ мы открыли «как обычно» двойным кликом по нему, затем запустили еще одну копию Excel и открыли второй документ в нем. А между тем, эта фича довольно полезна: если у вас «упадет» excel из-за кривых VBA-скриптов — закроются не все документы, а только упавший. Да и  легче сравнивать и переносить данные из одного документа в другой.

К счастью, решение этой проблемы, по крайней мере для Office 2010 существует. Необходимо всего-то изменить парочку записей в реестре:

1. Открываем regedit (Win+R->regedit->Enter);

2. Переходим в HKEY_CLASSES_ROOT\Excel.Sheet.12\shell\Open\command;

3. Кликаем дважды на ключе (Default) справа и изменяем строку на такую (т.е. в конце строки меняем /dde на /e «%1»:

"C:\Program Files\Microsoft Office\Office14\EXCEL.EXE" /e "%1"

4. Переименовываем ключ command в command2;

5. Переименовываем раздел ddexec в ddexec2.

6. Пользуемся :)

Excel 2010 separate processes

Если захочется открывать так не только xlsx файлы, но и xls — достаточно проделать эти же операции с веткой HKEY_CLASSES_ROOT\Excel.Sheet.8\shell\Open\command

Расчет размера 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 минут работы сервера.