„Сайтът се бави“ почти винаги опира до базата данни. Проверката отнема няколко минути, ако се прави в правилния ред: първо това, което се вижда точно по време на забавянето, после това, което се е трупало със седмици.

Гледа се в момента на проблема

Главното правило: най-ценната информация е достъпна точно тогава, когато всичко е бавно. Първата команда за MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

За PostgreSQL:

sudo -u postgres psql -c "SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY dur DESC;"

Ще видите какво се изпълнява точно сега и от колко време. Обикновено отговорът се вижда веднага: една тежка заявка държи останалите или сто еднакви заявки чакат освобождаване на заключване.

Връзки

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

Ако Max_used_connections се доближава плътно до max_connections, приложението периодично получава отказ „твърде много връзки“. Вдигането на лимита не е първото, което трябва да се направи: всяка връзка заема памет и увеличаването на лимита на сървър, на който не достига памет, само ускорява преминаването към swap. Първо си струва да се разбере защо връзките не се освобождават: обикновено е виновна дълга заявка или липсата на пул от страна на приложението.

Тук е полезно да се погледне и от другата страна: рязкото нарастване на броя връзки може да е следствие не от растеж на популярността, а от масово обръщане на ботове към тежка страница — търсене, филтър на каталог, генератор на отчети.

Бавни заявки

Без журнал за бавни заявки няма как да се продължи. MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

След денонощие:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

PostgreSQL, в postgresql.conf:

log_min_duration_statement = 1000

По-нататък — разширението pg_stat_statements, което веднага показва сумарното време по всяка заявка:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Обърнете внимание: важна е не най-дългата заявка, а тази с най-голямо сумарно време. Заявка от 50 милисекунди, изпълнявана хиляда пъти в минута, вреди повече от десетсекунден отчет веднъж на денонощие.

Настройката, която най-често се забравя

За MySQL и MariaDB това е размерът на буферния пул на InnoDB. Стойността по подразбиране е 128 мегабайта и не се е променяла десетилетия. На сървър, където базата заема няколко гигабайта, това означава постоянно четене от диска вместо от паметта.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Разумен ориентир за отделен сървър за бази е половината от оперативната памет; за сървър, на който редом работят уеб сървър и PHP — една четвърт, с оглед да не започне swap. Само тази настройка обикновено дава повече от седмица оптимизация на заявки.

Две неща, които незабелязано изяждат диска

Двоичните журнали на MySQL. Нужни са за репликация и възстановяване към момент от време, растат постоянно и по подразбиране понякога изобщо не се изтриват. Проверка:

sudo du -sh /var/lib/mysql/*bin.*
mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'"

Ако нямате репликация и не използвате възстановяване към момент от време, срокът на съхранение се слага на разумни няколко денонощия.

WAL журналите в PostgreSQL при забравен слот за репликация. Случай, по-малко известен и по-опасен: ако слот за репликация е създаден, а потребителят вече не се свързва към него, PostgreSQL е длъжен да пази всички журнали от момента на прекъсването — и ще ги пази, докато не свърши дискът.

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

Неактивен слот, който на никого не е нужен, трябва да се изтрие. Това е една от малкото ситуации, в които база данни гарантирано докарва диска до нула.

Размер и растеж

SELECT table_schema, round(sum(data_length+index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;

Полезно е не толкова текущото число, колкото скоростта на изменението му. База, нараснала двойно за месец без съответен растеж на посещаемостта, обикновено означава, че някаква журнална таблица се пише и не се чисти — сесии, история на задачи, журнал със събития на разширение.

Сигурност в два реда

Щом вече сте отворили конзолата на базата, струва си да проверите две неща. Първото — достъпна ли е базата отвън (ss -tulpn | grep 3306): навън тя не бива да гледа почти никога. Второто — акаунти с достъп от всякакъв адрес:

mysql -e "SELECT user, host FROM mysql.user"

Записите с host = '%' означават „свързване е възможно отвсякъде“. Ако при това портът е отворен, между базата и интернет стои само една парола.

Резервни копия

Последното, което си струва да се каже. Наличието на файл с резервно копие не гарантира нищо — гарантира само проверено възстановяване. Дъмп, който никога не е разгръщан, с еднаква вероятност може да се окаже отрязан, направен с грешка при заключване или съдържащ друга база. Това трябва да се провери предварително и на отделна машина, а не в деня, в който копието е потрябвало.

За ежедневното наблюдение стигат три числа: брой връзки, брой бавни заявки за денонощие и размер на базата. И трите се променят бавно и предвидимо, а всяка рязка промяна си струва един поглед. Как изглеждат те на една страница, показва демонстрацията по-долу.