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

Спасение сервера от медленных запросов 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 минут работы сервера.

Объединение двух сетей с одинаковой адресацией через интернет

Объединить две сети через интернет довольно легко. Создаем site-to-site vpn удобным нам способом и пользуемся. А если надо объединить две сети, в которых совпадают диапазоны используемых адресов? Например, две сети с адресацией 192.168.0.0/24. Поначалу может показаться, что идея глупая и это никому не надо. Но есть для этого и практическое применение — обеспечение бесперебойной работы инфраструктуры офиса на время переезда в другое место.

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

Второе требование — достаточно широкий интернет-канал с низкими задержками. Желательно все это выполнить в пределах одного интернет-провайдера.

И, естественно, третьим требованием будет 2 сервера/компьютера с двумя нормальными сетевыми картами в каждом.

Если все требования выполнены, можно приступать к установке Debian. Именно на этой платформе мы будем реализовывать виртуальный свитч. Описывать установку debian бессмысленно, мануалов в сети предостаточно, да и визард вполне понятен.

После установки debian, ставим пакеты bridge-utils и tinc. Создаем директории /etc/tinc/mynetwork и /etc/tinc/mynetwork/hosts, где mynetwork — имя вашего соединения. В директории /etc/tinc/mynetwork создаем конфигурационный файл tinc.conf и вписываем туда следующие параметры:

Name = segment1
Mode = switch
ConnectTo = segment2

На втором, соответственно:

Name = segment2
Mode = switch
ConnectTo = segment1

В таком режиме, как можно догадаться, tinc будет работать как обычный свитч: пересылаться будут только те пакеты, которые направлены к хостам в другом сегменте. Для этого tinc будет регулярно рассылать ARP-запросы и поддерживать в актуальном состоянии таблицу MAC-адресов. Если же нужна пересылка абсолютно всего трафика — можно воспользоваться режимом «hub», что собственно и соответствует логике работы обычного хаба.

После того, как созданы конфигурационные файлы, надо сгенерировать ключи для установки защищенного соединения. Для этого выполняем команду:

tincd -n mynetwork -K

Эта команда генерирует private key и public key и сохраняет их в указанном месте (по умолчанию, в директории /etc/tinc/mynetwork хранится private key, а в директории /etc/tinc/mynetwork/hosts — public key. Имя публичного ключа совпадает с именем, заданным в tinc.conf параметром Name. Редактируем файл с публичным ключом и вписываем в первой строке (до начала —— BEGIN RSA PUBLIC KEY):

Address = 1.2.3.4

, где 1.2.3.4 — внешний адрес этого хоста. После этого копируем файлы из директории /etc/tinc/mynetwork/hosts с одного хоста на другой.

В директории /etc/tinc/mynetwork создаем скрипт tinc-up с содержимым:

#!/bin/sh

ifconfig $INTERFACE 0.0.0.0
brctl addif bridge $INTERFACE
ifconfig $INTERFACE up

Этот скрипт будет запускаться при старте демона tincd. Для автоматической установки соединения при старте системы, необходимо вписать имя сети (mynetwork) в файл /etc/tinc/nets.boot.

И, наконец, последнее, что необходимо сделать для того, чтобы все работало — создать, собственно, мост и сделать так, чтобы при старте системы он создавался автоматически. Для этого создаем скрипт /etc/tinc/createbridge со следующим содержимым:

#!/bin/sh

brctl addbr bridge
ifconfig bridge 192.168.0.2 netmask 255.255.255.0
ifconfig eth1 0.0.0.0
brctl addif bridge eth1
ifconfig eth1 up

, где 192.168.0.2 — адрес хоста в сети, а eth1 — интерфейс, к которому подключена локальная сеть. Делаем скрипт исполняемым и добавляем в /etc/network/interfaces в параметры интерфейса eth1 строку:

post-up /etc/tinc/createbridge

Перезагружаем оба сервера и проверяем наличие соединения между хостами. Если соединение не установлено — смотрим логи и проверяем наличие интернета. Если не помогло — читаем мануал :)