Под нагрузкой PostgreSQL и MySQL расходятся по узким местам, поэтому один набор параметров для них не работает. Для PostgreSQL решают shared_buffers, work_mem, effective_cache_size и autovacuum; для MySQL InnoDB - innodb_buffer_pool_size, размер redo log и innodb_flush_log_at_trx_commit. Диагностика в обеих системах встроенная: pg_stat_statements и performance_schema. Изменения проверяют нагрузочным тестом на стенде, повторяющем продакшен, а параметры меняют по одному, с мониторингом и готовым откатом.
Ориентир для сервера с 32 ГБ RAM и 100 соединениями: PostgreSQL - shared_buffers 8 ГБ, work_mem 16-32 МБ, effective_cache_size 24 ГБ; MySQL - innodb_buffer_pool_size 24 ГБ, innodb_redo_log_capacity 1-2 ГБ, long_query_time 0.25. Это стартовая точка, а не догма: значения зависят от профиля запросов, размера рабочего набора и типа диска. Порядок действий всегда одинаковый: снять baseline, изменить один параметр, повторить тест, сравнить метрики.
Почему производительность PostgreSQL и MySQL под нагрузкой требует разного подхода
Ключевые архитектурные различия, влияющие на производительность
Обе СУБД работают по MVCC, но версии строк хранят по-разному. PostgreSQL оставляет старые версии прямо в таблице (heap) и освобождает место фоновым процессом VACUUM. Под нагрузкой на UPDATE и DELETE растет число dead tuples, а вместе с ним размер таблиц и индексов.
InnoDB держит предыдущие версии в undo log, очистку выполняет purge-поток. Полного вакуума таблиц не требуется, зато долгая читающая транзакция останавливает purge и раздувает undo tablespace.
Кэш устроен по-разному. PostgreSQL читает блоки по 8 КБ через shared_buffers и опирается на page cache операционной системы: отсюда двойное кэширование и смысл держать shared_buffers заметно ниже объема RAM. InnoDB управляет своим пулом сама, поэтому рабочий набор должен целиком помещаться в innodb_buffer_pool_size, иначе каждый промах уходит на диск.
Журналирование тоже отличается. PostgreSQL пишет WAL сегментами по 16 МБ, частоту контрольных точек задают checkpoint_timeout и max_wal_size. MySQL InnoDB использует redo log, doublewrite buffer и binlog для репликации, а в 8.0.30 и новее размер redo log задает параметр innodb_redo_log_capacity.
Соединения обрабатываются разными моделями: PostgreSQL порождает отдельный процесс на подключение (fork) с ощутимым расходом памяти, поэтому при 500 и более соединениях нужен внешний пулер вроде PgBouncer. MySQL обслуживает клиентов потоками, накладные расходы на соединение ниже, а лимит задает max_connections.
Блокировки: в PostgreSQL читатели не блокируют писателей, эскалации блокировок строк до уровня таблицы нет, а долгие ожидания видны через log_lock_waits. InnoDB блокирует строки и при уровне REPEATABLE READ добавляет gap locks, ожидание ограничено innodb_lock_wait_timeout (по умолчанию 50 секунд), взаимные блокировки разрешает встроенный детектор.
| Архитектурный аспект | PostgreSQL | MySQL (InnoDB) |
|---|---|---|
| Версии строк | heap, очистка через VACUUM и autovacuum | undo log, очистка purge-потоком |
| Кэш данных | shared_buffers плюс page cache ОС | innodb_buffer_pool_size |
| Журнал | WAL | redo log, doublewrite buffer, binlog |
| Обработка соединений | процесс на соединение | поток на соединение |
| Доступ к строке | индекс плюс heap по ctid | кластерный первичный ключ |
| Специальные индексы | GIN, GiST, BRIN, SP-GiST, hash | B-tree, FULLTEXT, функциональные |
Когда какая СУБД выигрывает под нагрузкой
Различия в доступе к строкам задают разные сильные стороны. В InnoDB первичный ключ кластерный: строка лежит в листе PK-дерева, а вторичный индекс хранит значение PK. Точечное чтение по первичному ключу требует одного спуска по дереву. В PostgreSQL вторичный индекс ведет к heap через ctid, и кроме index-only scan почти каждое чтение требует второго обращения к таблице. Для нагрузки из миллионов простых SELECT по ключу это дает MySQL преимущество в пропускной способности.
PostgreSQL выигрывает там, где запрос сложный: многотабличные соединения, CTE, оконные функции, выборки по JSONB через GIN-индекс, геоданные PostGIS, частичные и функциональные индексы. Планировщик умеет выбирать hash join, merge join и параллельные планы, а расширения добавляют типы и индексы, которых в MySQL нет.
По записи картина зависит от профиля. Короткие транзакции с высокой конкурентностью (OLTP) упираются в синхронизацию журнала и блокировки: MySQL здесь предсказуемее из коробки, PostgreSQL требует настройки autovacuum и пула соединений. Аналитические запросы (OLAP) чаще остаются за PostgreSQL.
Проверять выбор стоит на своей схеме и своем профиле: pgbench и sysbench дают ответ за час работы, тогда как синтетические бенчмарки из чужих обзоров ничего не говорят о вашем рабочем наборе.
Настройка PostgreSQL для высокой нагрузки: ключевые параметры
shared_buffers, work_mem, effective_cache_size: формулы и ограничения
shared_buffers по умолчанию 128 МБ. Практическое правило: 25% RAM, а на серверах с 64 ГБ и больше часто достаточно 8-16 ГБ, потому что остальную часть рабочего набора кэширует операционная система. Параметр требует перезапуска.
work_mem выделяется не на соединение, а на каждую операцию сортировки или хеширования в плане. Один запрос может использовать несколько таких узлов, а параллельные воркеры получают свою копию памяти. Отсюда осторожная формула: (RAM - shared_buffers) / (max_connections * 3). При 32 ГБ RAM, shared_buffers 8 ГБ и 100 соединениях выходит около 80 МБ, но безопаснее начать с 16-32 МБ. Значение выше реальной потребности соединений приводит к срабатыванию OOM-killer и падению сервера целиком.
effective_cache_size память не занимает: это подсказка планировщику, сколько данных доступно в кэше ОС и shared_buffers суммарно. Ставят 50-75% RAM. Заниженное значение заставляет планировщик недооценивать кэш и выбирать лишние чтения с диска, завышенное - переоценивать index scan там, где выгоднее последовательное чтение.
Пример для 32 ГБ RAM, 100 соединений, SSD: shared_buffers = 8 ГБ, work_mem = 16 МБ, maintenance_work_mem = 1-2 ГБ, effective_cache_size = 24 ГБ, random_page_cost = 1.1, max_wal_size = 8-16 ГБ, checkpoint_timeout = 15 мин. Для профиля с интенсивной записью полезно поднять wal_buffers, для читающего профиля - взять верхнюю границу effective_cache_size.
Autovacuum и логирование: как не допустить деградации
По умолчанию autovacuum запускается при 20% измененных строк (autovacuum_vacuum_scale_factor = 0.2) с интервалом autovacuum_naptime = 1 мин и тремя воркерами. Для таблицы на 100 млн строк порог 20% означает 20 млн изменений до первого прохода, и за это время таблица успевает раздуться.
Для больших и часто обновляемых таблиц пороги задают точечно: ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_analyze_scale_factor = 0.02, autovacuum_vacuum_cost_delay = 0). Тот же эффект дает общий autovacuum_vacuum_scale_factor = 0.05 при большом числе небольших таблиц. Число воркеров поднимают до 5-10 (autovacuum_max_workers), если баз много, при этом каждый воркер в момент индексации потребляет maintenance_work_mem.
Контроль состояния: pg_stat_user_tables (n_dead_tup, last_autovacuum, last_autoanalyze) и pg_stat_progress_vacuum во время прохода. Отключенный autovacuum ведет к раздуванию, замедлению запросов и риску зацикливания счетчика транзакций.
Для логирования минимальный набор под нагрузкой: log_min_duration_statement = 250ms, log_lock_waits = on, log_temp_files = 0, log_checkpoints = on, log_autovacuum_min_duration = 250ms. Параметр log_temp_files показывает, где именно не хватает work_mem. Для разбора планов подключают auto_explain с log_analyze = on и log_min_duration = 500ms. Значения уровня sighup применяют без перезапуска: ALTER SYSTEM SET log_min_duration_statement = '250ms'; и затем SELECT pg_reload_conf().
Оптимизация MySQL InnoDB buffer pool и других параметров
innodb_buffer_pool_size: как рассчитать и почему 80% RAM
innodb_buffer_pool_size - главный кэш данных и индексов InnoDB, по умолчанию всего 128 МБ. На выделенном сервере под БД ставят 70-80% RAM: при 32 ГБ это 24 ГБ. Если на хосте живут приложение, веб-сервер или вторая СУБД, долю снижают до 50%, чтобы ОС не ушла в swap.
Пул делится на инстансы (innodb_buffer_pool_instances), что снижает конкуренцию на мьютексах: ориентир один инстанс на 1-2 ГБ пула, разумный предел 8-16. Размер пула выравнивается по chunk_size (по умолчанию 128 МБ), поэтому итог может отличаться от заданного числа. Проверить фактическое значение: SHOW VARIABLES LIKE 'innodb_buffer_pool_size'.
Метрики попадания в кэш: Innodb_buffer_pool_read_requests (логические чтения) против Innodb_buffer_pool_reads (физические). Если доля промахов держится выше долей процента при стабильной нагрузке, пул мал для рабочего набора. Полезно смотреть Innodb_buffer_pool_pages_free и счетчик ожиданий свободной страницы Innodb_buffer_pool_wait_free.
Увеличение пула на лету поддерживается: SET GLOBAL innodb_buffer_pool_size = 25769803776; выполняется без остановки сервера, но в момент роста InnoDB перечитывает страницы, поэтому возможен всплеск IO. Слишком большой пул при нехватке памяти приводит к swapping и падению отзывчивости.
Логирование и другие параметры для диагностики
Медленные запросы включают так: slow_query_log = ON, long_query_time = 0.25, log_slow_extra = ON (в 8.0.14 и новее добавляет детали выполнения), log_slow_admin_statements = ON. Параметр log_queries_not_using_indexes включают осторожно: на большом трафике он быстро забивает диск. Для разбора логов подходит pt-query-digest.
innodb_flush_log_at_trx_commit управляет надежностью: 1 - сброс на диск при каждом коммите (значение по умолчанию), 2 - раз в секунду, что ускоряет запись, но при сбое ОС теряется последняя секунда подтвержденных транзакций. Выбор зависит от того, допустима ли потеря этих данных для конкретной системы.
Размер журнала: в 8.0.30 и новее его задает innodb_redo_log_capacity (по умолчанию 100 МБ), в более старых версиях - innodb_log_file_size и innodb_log_files_in_group. Для интенсивной записи ставят 1-4 ГБ суммарно: маленький журнал вызывает частые контрольные точки и всплески IO.
innodb_io_capacity описывает возможности диска: 200 по умолчанию (HDD), 2000-4000 для SSD, верхнюю границу задает innodb_io_capacity_max. Для SSD отключают слияние соседних страниц (innodb_flush_neighbors = 0) и выбирают innodb_flush_method = O_DIRECT на Linux. Дополнительно проверяют table_open_cache, max_connections и thread_cache_size под реальное число клиентов.
Пример под OLTP на 32 ГБ RAM: innodb_buffer_pool_size = 24 ГБ, innodb_buffer_pool_instances = 8-12, innodb_redo_log_capacity = 2 ГБ, innodb_flush_log_at_trx_commit = 1, innodb_io_capacity = 2000, slow_query_log = ON, long_query_time = 0.25, sync_binlog = 1. Готовые конфигурации для нагрузки 10k+ запросов в секунду приведены в руководстве по настройке MySQL под высоконагруженные системы.
Диагностика узких мест: pg_stat_statements и performance_schema
pg_stat_statements: установка и анализ запросов
Расширение включают через shared_preload_libraries = 'pg_stat_statements' с последующим перезапуском, затем в базе выполняют CREATE EXTENSION pg_stat_statements. Дальше представление pg_stat_statements агрегирует запросы по нормализованному тексту: константы заменяются на $1, $2.
Полезные столбцы: query, calls, total_exec_time, mean_exec_time, rows, shared_blks_hit, shared_blks_read, temp_blks_written. Первый запрос для разбора: SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10. Он показывает, кто съедает время суммарно. Высокий mean_exec_time при малом calls указывает на отдельный тяжелый запрос, высокий temp_blks_written - на нехватку work_mem.
В PostgreSQL 13 и новее столбец называется total_exec_time, в более старых версиях имя другое, поэтому перед запросом стоит посмотреть структуру представления. Сброс статистики выполняют функцией pg_stat_statements_reset(), а число вытесненных записей показывает pg_stat_statements_info.
Детализацию одного запроса дает EXPLAIN (ANALYZE, BUFFERS): видно расхождение оценки rows с фактом, тип соединения, попадания в кэш. Блокировки ищут через pg_locks, pg_stat_activity (wait_event_type, wait_event) и pg_blocking_pids(pid). Последовательность проверок от медленных запросов к схеме данных и пулу соединений разобрана в материале о том, как база данных влияет на производительность автоматизированных систем.
performance_schema в MySQL: поиск медленных запросов
В MySQL 8.0 performance_schema включена по умолчанию, но это статический параметр: менять его можно только перезапуском. Основная таблица для разбора нагрузки - performance_schema.events_statements_summary_by_digest. В ней есть DIGEST_TEXT (нормализованный запрос), COUNT_STAR (число выполнений), SUM_TIMER_WAIT и AVG_TIMER_WAIT (в пикосекундах), SUM_ROWS_EXAMINED, SUM_NO_INDEX_USED, SUM_CREATED_TMP_DISK_TABLES.
Запрос для поиска главных потребителей времени: SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000000 AS total_sec, AVG_TIMER_WAIT/1000000000 AS avg_ms FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10. Запросы с SUM_NO_INDEX_USED > 0 и большим COUNT_STAR - кандидаты на индекс, а SUM_CREATED_TMP_DISK_TABLES > 0 указывает на сортировки, не поместившиеся в память.
Схема sys дает готовые срезы: sys.schema_table_statistics, sys.statements_with_full_table_scans, sys.schema_unused_indexes. План запроса разбирают через EXPLAIN ANALYZE (доступен с 8.0.18). Ожидания блокировок смотрят в performance_schema.data_locks и data_lock_waits, сводку - в sys.innodb_lock_waits.
performance_schema потребляет память и добавляет накладные расходы. Если инструмент нужен только для разбора инцидента, часть потребителей отключают: UPDATE performance_schema.setup_consumers SET ENABLED = 'NO' WHERE NAME LIKE 'events_waits%'. После сбора данных потребителей возвращают в исходное состояние.
Нагрузочное тестирование: pgbench и sysbench
pgbench: методика и интерпретация результатов
pgbench входит в поставку PostgreSQL. Инициализация: pgbench -i -s 10 (масштаб 10, около 1 млн строк в pgbench_accounts). Запуск: pgbench -c 10 -j 2 -T 60 -P 10 -M prepared. Ключ -c задает число клиентов, -j - рабочие потоки, -T - длительность в секундах, -P - интервал вывода, -M prepared включает подготовленные выражения.
Итоговая сводка содержит tps (транзакции в секунду), latency average и latency stddev, а также время установки соединений. Читать их нужно вместе: рост tps при неизменной latency означает реальный выигрыш, а рост tps вместе с ростом latency часто говорит о том, что тест уперся в предел по CPU и растет очередь, а не пропускная способность.
Методика сравнения проста: прогрев одним-двумя прогонами, затем три замера по 60 секунд до правки параметров и три после. Если до правки tps держался около 500, а после увеличения shared_buffers вырос до 1200 при той же latency average, эффект подтвержден. Разброс между замерами больше 10% означает, что стенд шумит и вывод делать рано.
Полезные режимы: -S для проверки только чтения, --rate для ограничения нагрузки заданным числом транзакций в секунду, -f со своим скриптом для повторения реального запроса. Реплики тестируют отдельным прогоном и сравнивают tps с мастером. Больше команд для измерения RPS, latency и p95 собрано в руководстве по нагрузочному тестированию серверов и кластеров.
sysbench: подготовка и анализ
sysbench тестирует MySQL через собственный клиент. Подготовка: sysbench oltp_read_write --table-size=1000000 --tables=10 --mysql-host=... prepare. Запуск: sysbench oltp_read_write --threads=10 --time=60 --report-interval=10 run. После теста данные удаляют командой cleanup.
Отчет содержит transactions per second, queries per second, latency average и 95-й процентиль. Процентиль важнее среднего: рост p95 при стабильном среднем означает, что часть запросов уходит в ожидание на блокировках или IO. Профили oltp_point_select и oltp_read_only помогают отделить чтение от записи, а oltp_write_only показывает предел по журналу.
Клиент sysbench лучше запускать с отдельного хоста: на одном сервере генератор нагрузки отбирает CPU у СУБД и искажает цифры. Замеры до и после правки innodb_buffer_pool_size выполняют на одном и том же наборе данных: рост с 800 до 2000 транзакций в секунду при падении p95 говорит о том, что рабочий набор перестал уходить на диск.
Тестирование на продакшене без подготовки перегружает диск и блокировки. Стенд повторяет прод по объему данных, версии СУБД и типу диска, иначе результат переносится неверно.
Влияние индексов, партиционирования и репликации на отзывчивость
Индексы: когда помогают, а когда вредят
B-tree есть в обеих СУБД и покрывает сравнения, сортировку и диапазоны. PostgreSQL добавляет GIN (JSONB, массивы, полнотекстовый поиск), GiST (геометрия, диапазоны), BRIN (большие таблицы с корреляцией данных по физическому порядку), SP-GiST и hash-индексы. В MySQL InnoDB есть B-tree и FULLTEXT, а частичных индексов нет: их заменяют функциональные индексы по вычисляемым столбцам (8.0.13 и новее).
Каждый индекс ускоряет чтение и удорожает запись: при INSERT и UPDATE InnoDB помечает старую запись вторичного индекса удаленной и добавляет новую, а PostgreSQL пишет новую версию записи индекса до прохода autovacuum. На таблице с 10 индексами и 5 тыс. обновлений в секунду лишний индекс дает заметный рост IO и размера базы.
Покрывающие индексы сокращают обращения к таблице: в PostgreSQL это INCLUDE-столбцы, в InnoDB вторичный индекс уже содержит первичный ключ. Порядок эффекта виден на типовом примере: индекс на столбец из условия WHERE переводит запрос с 500 мс на 5 мс, если выборка возвращает доли процента строк. Если запрос читает треть таблицы, последовательное чтение быстрее, и индекс добавит только работы.
Неиспользуемые индексы ищут так: в PostgreSQL pg_stat_user_indexes с idx_scan = 0, в MySQL sys.schema_unused_indexes. Перед удалением индекс проверяют по статистике за полный деловой цикл, включая месячные отчеты. Как ранние решения по схеме определяют дальнейшую стоимость сопровождения, разобрано в статье о том, как проектирование базы данных влияет на администрирование.
Партиционирование и репликация: практические сценарии
PostgreSQL поддерживает декларативное партиционирование по диапазону, списку и хешу (с версии 10), MySQL InnoDB - RANGE, LIST, HASH и KEY. В обеих системах ключ партиционирования обязан входить в каждый уникальный индекс, это ограничение стоит учитывать при проектировании схемы. В партиционированных таблицах InnoDB внешние ключи не поддерживаются.
Практический эффект дает отсечение партиций: таблица заказов на 100 млн строк, разбитая по месяцам, при запросе за последний месяц читает одну партицию вместо всей таблицы. Второй выигрыш - обслуживание: DROP PARTITION удаляет старые данные мгновенно, тогда как DELETE на 10 млн строк порождает журнал, блокировки и работу для VACUUM и purge.
Репликация решает другую задачу - распределение чтения. PostgreSQL использует потоковую физическую репликацию по WAL; синхронный режим (synchronous_commit = on с указанными standby) гарантирует, что коммит не потеряется, но добавляет задержку каждой транзакции. Логическая репликация позволяет передавать отдельные таблицы и работать между разными версиями. MySQL применяет репликацию по binlog с GTID (gtid_mode = ON), полусинхронный режим и Group Replication для отказоустойчивых кластеров.
Чтение с реплик снижает нагрузку на мастер, пока приложение готово к задержке репликации. В PostgreSQL ее показывают столбцы write_lag, flush_lag и replay_lag в pg_stat_replication, в MySQL - SHOW REPLICA STATUS (в 8.0.22 и новее, ранее SHOW SLAVE STATUS). Отставание ломает логику чтения сразу после записи, поэтому такие запросы направляют на мастер. Полусинхронная репликация увеличивает время коммита на мастере, и этот компромисс стоит измерять нагрузочным тестом.
Безопасное внедрение изменений: как не сломать продакшен
Чек-лист перед изменением конфигурации
- Сделать резервную копию и проверить восстановление на отдельном инстансе. Копия без проверки восстановления ничего не гарантирует.
- Зафиксировать текущие значения параметров: в PostgreSQL SELECT name, setting, context FROM pg_settings, в MySQL SHOW VARIABLES LIKE 'innodb%'.
- Проверить, нужен ли перезапуск. В pg_settings столбец context показывает postmaster, sighup или user; в MySQL признак Dynamic указан в документации и в таблице performance_schema.variables_info.
- Проверить правку на стенде с той же версией СУБД и тем же объемом данных.
- Прогнать нагрузочный тест до и после и сравнить tps, latency и p95.
- Применить параметр на реплике и наблюдать не менее 24 часов, включая пиковые часы.
- Применить на мастере в окно низкой нагрузки, имея готовую команду отката.
Часть параметров меняется на лету. В PostgreSQL это ALTER SYSTEM SET work_mem = '32MB'; с последующим SELECT pg_reload_conf() для значений уровня sighup, а shared_buffers и max_connections требуют перезапуска. В MySQL работает SET GLOBAL innodb_buffer_pool_size = 25769803776;, а SET PERSIST сохраняет значение между перезапусками (сброс - RESET PERSIST).
Порядок применения через реплику дает проверку на реальном трафике чтения без риска для записи. Если на реплике метрики ухудшились, мастер не тронут, и откат сводится к возврату параметра.
Мониторинг после изменений
Список метрик для наблюдения после правки: tps и qps, средняя latency и p95, загрузка CPU, дисковые IOPS и время ожидания, число активных соединений, ожидания блокировок, доля попаданий в кэш, размер временных файлов, отставание реплик. Для PostgreSQL дополнительно смотрят n_dead_tup и last_autovacuum, для MySQL - Innodb_buffer_pool_reads и Innodb_row_lock_time.
Сбор удобно вести через Prometheus с postgres_exporter и mysqld_exporter и отображать в Grafana; для MySQL есть Percona Monitoring and Management. Пороговые значения фиксируют до правки: например, откат выполняют, если p95 вырос больше чем на 20% или tps упал больше чем на 10% в течение контрольного окна. Разбор метрик, порогов и готовых конфигураций собран в статье о мониторинге производительности СЭД с Prometheus, Grafana и Zabbix.
Пример реакции по метрикам: после увеличения work_mem latency отчетных запросов снизилась, но выросло потребление памяти процессами. Если рост памяти подошел к пределу хоста, значение возвращают назад, потому что риск OOM дороже выигрыша по latency.
Значения по умолчанию отличаются между версиями PostgreSQL и MySQL, а смысл части параметров менялся после крупных релизов. Примеры в статье даны как ориентир для типового сервера: перед правкой сверяйте фактические значения командой SELECT name, setting FROM pg_settings или SHOW VARIABLES и документацию именно вашей версии.
Практический шаг на сегодня: снять baseline метрик на текущем сервере, поднять стенд с копией данных и прогнать pgbench или sysbench на своем профиле нагрузки. Дальше менять по одному параметру из списка выше и оставлять только те правки, которые подтверждены цифрами до и после.