База данных на своём сервере: MySQL или PostgreSQL и сколько памяти

База - самая требовательная часть почти любого проекта и одновременно самая уязвимая. Приложение можно развернуть заново из репозитория за десять минут, сервер пересоздать за три, а потерянные данные не восстанавливает никто. При этом на своём сервере база по умолчанию настроена скромно и не защищена ничем, кроме пароля.

Разберём, что выбрать под типовой проект, сколько памяти базе действительно нужно, почему её нельзя выставлять в интернет и как устроены бэкапы, которые не подведут в нужный момент.

MySQL или PostgreSQL

Честный ответ: для большинства проектов разница не критична, и оба варианта отработают годами. Значение имеет то, что вы уже умеете обслуживать.

MySQL и MariaDB. Практически взаимозаменяемы, MariaDB - развитие того же кода. Классический выбор под сайты: WordPress, Bitrix и большинство CMS ориентированы именно на них, инструкций в интернете больше. Проще в первичной настройке.

PostgreSQL. Строже относится к данным и типам, лучше держит сложные запросы и параллельную запись, богаче по возможностям. Стандартный выбор для приложений, которые пишут сами, и обязательный - для 1С в клиент-серверном режиме, где он заменяет платную лицензию MS SQL.

Практическое правило:

• Сайт на готовой CMS - MySQL или MariaDB, потому что так ожидает сама CMS.
• Своё приложение - PostgreSQL, если нет причин выбрать иначе.
• 1С - PostgreSQL, это позволяет не платить за лицензии СУБД.

Что важнее выбора движка: хватает ли базе памяти, лежит ли она на быстром диске, закрыт ли доступ и делаются ли копии. Неправильно настроенный PostgreSQL проигрывает правильно настроенному MySQL с разгромным счётом, и наоборот.

Сколько памяти нужно базе

Главный принцип звучит так: база работает быстро, пока её рабочий набор помещается в память. Рабочий набор - это не вся база, а те данные, к которым обращаются регулярно: свежие заказы, активные пользователи, индексы.

Если этот объём помещается в оперативную память, база отвечает из неё. Как только перестаёт - каждый запрос идёт на диск, и производительность падает в разы. Именно поэтому NVMe для базы важнее, чем для чего-либо ещё: он смягчает падение, когда память кончилась.

Ориентиры для старта:

Отдельно стоит помнить, что база по умолчанию использует лишь малую часть памяти сервера. Обе СУБД поставляются с консервативными настройками, рассчитанными на слабую машину. Один параметр отвечает за размер кеша данных, и его увеличение до разумной доли доступной памяти обычно даёт больший эффект, чем всё остальное вместе взятое. Как считать ресурсы под задачу целиком - в статье как выбрать конфигурацию сервера.

Главное правило: база не смотрит в интернет

Это самая частая и самая дорогая ошибка на своём сервере.

Порты СУБД - 3306 у MySQL и 5432 у PostgreSQL - находятся автоматическими сканерами за часы после запуска. Дальше идёт перебор стандартных учётных записей и слабых паролей. Базу с публичным портом и паролем средней сложности вскрывают не потому, что вы кому-то интересны, а потому что это делается роботом по всему диапазону адресов.

Правильных вариантов три:

• Только локально. Если приложение живёт на том же сервере, база слушает 127.0.0.1 и наружу не выставляется вовсе. Это конфигурация по умолчанию, и её не нужно менять.
• Доступ с конкретного адреса. Если приложение на другом сервере, порт открывается правилом для одного адреса, а не для всех, см. порты на сервере.
• Через SSH-туннель. Для подключения из графического клиента с ноутбука. Открывать порт для этого не нужно.

Плюс базовая гигиена: отдельный пользователь под каждое приложение, права только на нужную базу, сильный пароль, никакого доступа под административной учётной записью из приложения.

На том же сервере или на отдельном

Пока проект один, база живёт рядом с приложением, и это нормально. Вопрос возникает, когда они начинают мешать друг другу.

Держать вместе проще и дешевле: нет сетевой задержки, меньше сущностей, одна машина для обслуживания. Так работает подавляющее большинство сайтов и небольших сервисов.

Разносить имеет смысл, когда:

• база и приложение конкурируют за память, и обоим не хватает;
• нужно масштабировать их независимо;
• к базе обращаются несколько приложений;
• требования к надёжности базы выше, чем к остальному.

Промежуточный вариант перед разносом - просто добавить ресурсов текущему серверу. На виртуальной машине это делается за минуты и часто снимает проблему без усложнения схемы.

Бэкапы: те, что восстанавливаются

Копия базы, которую ни разу не разворачивали, - это предположение, а не бэкап.

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

Снапшот сервера. Копия всего диска целиком. Быстро создаётся и быстро разворачивается, но восстанавливает всю машину, а не отдельную таблицу.

Разумная схема - оба: снапшот на случай отказа машины и регулярный дамп на случай, когда нужно вернуть данные. Общий подход и правило трёх копий разобраны в статье резервное копирование VPS.

Три требования к копиям базы, без которых они не работают:

1. Хранить вне сервера. Копия на том же диске исчезает вместе с ним. 2. Проверять восстановлением. Хотя бы раз в квартал развернуть дамп и убедиться, что он читается. 3. Делать копию перед миграциями. Изменение структуры - самая частая причина обращения к бэкапу.

Своя база или готовый сервис

У провайдеров есть управляемые базы данных, где обновления, репликация и резервное копирование берут на себя. Дороже, но снимает работу.

Своя база на своём сервере выигрывает, когда важна цена, нужна нестандартная настройка или база небольшая и обслуживания почти не требует. Готовый сервис - когда база критична, а администратора в команде нет.

Для типового сайта или небольшого сервиса своя база на том же VPS остаётся самым разумным вариантом: она уже входит в стоимость сервера и требует внимания несколько раз в год.

Частые ошибки

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

Оставляют настройки по умолчанию. Обе СУБД из коробки используют малую долю памяти, и сервер с 16 ГБ работает как сервер с двумя.

Подключаются из приложения под административной учётной записью. Одна ошибка в коде или уязвимость - и доступ ко всем базам сразу.

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

Никогда не проверяют восстановление. Выясняется в худший момент, что дампы пустые или бьются.

Меняют структуру без копии. Миграция, пошедшая не так, без бэкапа означает потерю данных.

Частые вопросы

Что выбрать: MySQL или PostgreSQL? Для сайта на готовой CMS - MySQL или MariaDB, их ожидает сама CMS. Для своего приложения и для 1С - PostgreSQL. Настройка и обслуживание влияют на результат сильнее, чем сам выбор.

Сколько памяти нужно базе данных? Столько, чтобы регулярно используемые данные и индексы помещались в память. Для базы до гигабайта хватает сервера с 2 ГБ, для магазина или CRM берут 4-8 ГБ.

Можно ли держать базу и сайт на одном сервере? Да, так работает большинство проектов. Разносить стоит, когда они конкурируют за память или к базе обращается несколько приложений.

Как безопасно подключиться к базе с ноутбука? Через SSH-туннель. Открывать порт СУБД в интернет ради этого не нужно.

Почему база медленно работает после переноса? Чаще всего потому, что настройки остались по умолчанию и база использует малую часть памяти сервера. Второй по частоте ответ - медленный диск.

Чем дамп отличается от снапшота? Дамп - выгрузка содержимого базы, из него можно восстановить данные выборочно. Снапшот - копия всего диска, он возвращает машину целиком. Для базы нужны оба.

Нужна ли управляемая база данных? Она оправдана, когда база критична, а администрировать её некому. Для типового сайта своя база на том же сервере дешевле и требует внимания несколько раз в год.

Нужен сервер под базу данных? VPS на NVMe от 459 руб в месяц - быстрый диск, память наращивается за минуты, полный root-доступ для своих настроек. Для 1С есть готовые конфигурации с PostgreSQL без лицензий MS SQL.