Руководства

База данных на VPS: перенос, настройка, типичные ошибки

· 8 мин чтения
База данных на VPS: перенос, настройка, типичные ошибки

Когда проект «медленно работает», в большинстве случаев виновата не сеть и не процессор, а база данных. И почти всегда это несколько типовых ошибок, а не что-то экзотическое: нет индекса, запросы в цикле, настройки по умолчанию, слабый диск. Разберём, как перенести базу на VPS без потери данных, что настроить сразу после установки, как найти, что именно тормозит, и в каком порядке это чинить. Примеры — для MySQL/MariaDB и PostgreSQL, которые стоят на большинстве серверов.

Перенос базы на VPS

Правильный перенос — это дамп, а не копирование файлов работающей базы. Файлы на диске меняются в момент копирования, и такая «копия» может не открыться вовсе.

MySQL / MariaDB

mysqldump --single-transaction --routines --triggers -u root -p mydb | gzip > mydb.sql.gz

Флаг --single-transaction даёт согласованный снимок для InnoDB без блокировки таблиц — сайт продолжает работать во время дампа. На новом сервере:

mysql -e "CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
zcat mydb.sql.gz | mysql mydb

PostgreSQL

pg_dump -Fc -U app mydb > mydb.dump
pg_restore -U app -d mydb --no-owner mydb.dump

Формат -Fc сжат и позволяет восстанавливать выборочно и в несколько потоков (-j 4).

Три вещи, которые ломаются при переносе

  • Кодировка. Старая база в utf8 (трёхбайтовой), новая — в utf8mb4. Обычно это к лучшему: наконец заработают эмодзи. Но если создать базу в latin1 по умолчанию, русский текст превратится в знаки вопроса. Указывайте кодировку явно при создании.
  • Пользователи и права. Дамп базы не содержит пользователей. Создайте их заново: CREATE USER 'app'@'localhost' IDENTIFIED BY '...'; GRANT ALL ON mydb.* TO 'app'@'localhost';
  • Версии. Дамп с MySQL 5.7 обычно накатывается на 8.0, обратно — нет. PostgreSQL мажорные версии несовместимы по файлам, но дамп переносится в любую сторону. Восстанавливайте в ту же или более новую версию.

Если переезжаете с хостинга целиком, порядок действий с проверкой через hosts до переключения DNS — в статье «Как перенести сайт без простоя».

Настройка после установки: память

Свежеустановленная база рассчитана на то, чтобы запуститься где угодно, а не работать быстро. Ключевой параметр — сколько памяти отдано под кэш данных. То, что помещается в память, читается мгновенно; остальное идёт с диска. Если рабочий объём базы влезает в кэш, скорость вырастает в разы без единой правки кода.

MySQL / MariaDB

Главный параметр — innodb_buffer_pool_size. По умолчанию 128 МБ — этого мало для чего угодно, кроме тестов. В /etc/mysql/mariadb.conf.d/50-server.cnf (или my.cnf):

[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
max_connections = 100

PostgreSQL

В postgresql.conf:

shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 16MB
maintenance_work_mem = 256MB
random_page_cost = 1.1

random_page_cost = 1.1 вместо 4 по умолчанию — говорит планировщику, что диск быстрый (SSD/NVMe), и он охотнее использует индексы.

Сколько выделять

ТарифRAMБаза на одном сервере с сайтомБаза на отдельном сервере
Lite, $61 ГБ128–256 МБ — тесно, только для тестов512 МБ
Start, $102 ГБ512 МБ1–1,2 ГБ
Medium, $204 ГБ1–1,5 ГБ2,5–3 ГБ
Premium, $408 ГБ2–3 ГБ5–6 ГБ
Elite, $6016 ГБ4–6 ГБ10–12 ГБ

Главное — не выделить суммарно больше, чем есть физически: система уйдёт в подкачку, и станет хуже, чем было. Ядро при нехватке памяти убивает самый большой процесс, а это почти всегда база. Как считать память по всем компонентам — в статье «Как выбрать тариф VPS».

Диск: почему для базы важен NVMe

База — это интенсивная работа с диском, причём случайная: чтение разрозненных страниц, а не последовательных файлов. Именно на случайном доступе NVMe в несколько раз опережает обычные SSD. Для сайта, который отдаёт страницы из кэша, разница незаметна; для базы с активной записью — заказы, логи, аналитика — ощутима без всяких тестов.

Проверить, упирается ли проект в диск: iostat -x 5 — колонка %util устойчиво выше 60–70% и await больше 10 мс говорят, что узкое место в хранилище. В top это видно по показателю wa.

NVMe стоит на всех тарифах в Финляндии, Нидерландах, Чехии и ряде других европейских локаций; в Германии, Польше, Казахстане и США — SSD. Для базы данных это один из главных критериев выбора страны. Плюс частота процессора: разбор одного тяжёлого запроса упирается в одно ядро, и Xeon Gold 3.0 GHz в Финляндии заметно быстрее E5 2.2 GHz во Франкфурте.

Medium в Финляндии, 3.0 GHz + NVMe — $20/мес   Medium в Нидерландах, NVMe — $20/мес

Что тормозит: четыре типовые причины

1. Нет индексов

Самая частая причина с большим отрывом. Без индекса база при каждом запросе просматривает таблицу целиком. На тысяче записей это незаметно, на сотне тысяч страница открывается секундами. Признак: запрос, который на тестовой базе летал, на боевой встал — данных стало больше, а способ поиска остался перебором.

Смотрите план выполнения: EXPLAIN SELECT ... в обеих СУБД. Если в плане type: ALL (MySQL) или Seq Scan (PostgreSQL) там, где ожидался поиск по условию, — нужен индекс на столбцы из WHERE и JOIN.

Обратная крайность тоже вредна: индекс на каждый столбец замедляет запись, потому что при каждом изменении обновляются все индексы. Ставьте их по фактическим медленным запросам, а не про запас.

2. Запросы в цикле (N+1)

Приложение получает список из ста записей, а затем в цикле делает по запросу на каждую — итого сто один запрос вместо двух. В коде это выглядит аккуратно: цикл по объектам, обращение к связанной сущности. Обнаруживается только подсчётом запросов на страницу: если отладочная панель показывает десятки однотипных запросов, различающихся одним значением, — это оно. Лечится жадной загрузкой связей (JOIN или WHERE id IN (...)), в ORM — with(), select_related(), include.

3. Настройки по умолчанию

Разобрано выше: 128 МБ буфера у MySQL и 128 МБ shared_buffers у PostgreSQL означают, что база почти всё читает с диска. Одна правка конфига даёт кратное ускорение.

4. Диск

Если после первых трёх пунктов iostat всё ещё показывает высокий %util — упёрлись в хранилище. Варианты: NVMe-локация, вынос базы на отдельный сервер или увеличение памяти под кэш, чтобы диск читался реже.

Как найти виновника: журнал медленных запросов

Не гадайте — включите лог. Обычно два-три запроса дают почти всё время.

MySQL / MariaDB:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

Анализ: mysqldumpslow -s t /var/log/mysql/slow.log | head -20 или pt-query-digest.

PostgreSQL:

log_min_duration_statement = 1000

Или расширение pg_stat_statements, которое копит статистику по всем запросам: SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

Порядок действий

  1. Включить журнал медленных запросов и посмотреть, что реально тормозит.
  2. Проверить планы выполнения этих запросов через EXPLAIN и добавить недостающие индексы.
  3. Посчитать запросы на страницу и устранить обращения в цикле.
  4. Настроить память под кэш по фактическому объёму сервера.
  5. Проверить диск через iostat.
  6. И только потом добавлять ресурсы, если этого оказалось мало.

Порядок важен: увеличение тарифа без первых четырёх шагов обычно даёт десятки процентов там, где правка запроса даёт разы.

Когда выносить базу на отдельный сервер

Пока сайт и база живут на одной машине, они конкурируют за память и диск. Вынос базы на отдельный VPS — самый эффективный промежуточный шаг перед выделенным сервером: приложение получает всю память своего сервера, база — всю память своего, а дисковые операции перестают мешать обработке запросов. Два Medium по $20 в одной локации часто работают лучше одного Premium за $40.

Сигналы, что пора: буфер базы уже занимает половину памяти сервера, а %util диска всё равно высокий; или PHP-FPM и база по очереди попадают под OOM-killer. Соединение между серверами — по частной сети провайдера, если она есть, либо через WireGuard; наружу порт базы не открывать. Когда виртуалки мало уже и для отдельной базы — в разборе «VPS или выделенный сервер».

Бэкапы базы

Ежедневный дамп по cron — минимум, без которого всё остальное бессмысленно:

0 3 * * * mysqldump --single-transaction --all-databases | gzip > /var/backups/db-$(date +\%F).sql.gz

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

Коротко

  • Переносите базу дампом, указывайте кодировку явно, заново создавайте пользователей.
  • Первым делом после установки — память под кэш: innodb_buffer_pool_size или shared_buffers. По умолчанию там 128 МБ.
  • Тормозит почти всегда одно из четырёх: нет индекса, запросы в цикле, настройки по умолчанию, диск. Ищите через журнал медленных запросов и EXPLAIN.
  • Для базы выбирайте NVMe и высокую частоту: Финляндия, Чехия, Нидерланды.
  • Выросли — выносите базу на отдельный VPS раньше, чем идти на выделенный сервер.

Читайте также