Недавно в компании, где работал друг, весь день кричали продавцы.
Мой друг разработчик и не очень хорошо знает БД.Он сказал, что с их приложением на самом деле проблем нет, но обработка очень медленная каждый день, когда наступает пиковый период, всевозможные перегрузки, и куча запросы не могут быть выполнены. У них нет выделенного администратора баз данных, и они хотят обратиться ко мне за помощью.
Закончив работу, я открыл их базу данных, взглянул на нее и был ошеломлен: в базе данных настроены все параметры по умолчанию. Затем я дал им случайную модификацию и сказал им перезапустить базу данных. На второй день он сообщил: «Брат, ты потрясающий. То, что ты сделал вчера, заставило нашу базу данных внезапно стать быстрее».
Я сказал: я ничего не делал, я просто скорректировал параметры по умолчанию в соответствии со значением опыта, что очень поспешно! Все ваши параметры имеют значения по умолчанию из коробки. Явно не для производства.
Параметры, которые PostgreSQL должен настроить
max_connections
Максимально допустимое количество клиентских подключений. Размер этого параметра имеет некоторую связь с work_mem. Чем выше конфигурация, тем больше памяти можно использовать в системе. Обычно можно настроить сотни подключений.Если необходимо использовать тысячи подключений, рекомендуется настроить пул подключений, чтобы уменьшить накладные расходы.
shared_buffers
PostgreSQL использует собственный буфер, а также использует ядро операционной системы Linux для буферизации кэша ОС. Это означает, что данные хранятся в памяти дважды, сначала в буфере PostgreSQL, а затем в буфере ядра ОС. В отличие от других баз данных, PostgreSQL не обеспечивает прямой ввод-вывод, поэтому это также известно как двойная буферизация. Буферы PostgreSQL называютсяshared_buffer, рекомендуется установить его на 1/4 физической памяти. Фактическая конфигурация зависит от конфигурации оборудования и рабочей нагрузки.Если у вас большой объем памяти и вы хотите буферизовать некоторые данные в памяти, вы можете продолжать увеличивать размер.shared_buffer.
Effective_cache_size
Этот параметр в основном используется для оптимизатора Postgre Query. Одним из предположений, что является допустимым размером дискового кэша, доступного для одного запроса, является предполагаемое значение, которое не занимает системную память. Поскольку оптимизатор должен быть оценен стоимость, более высокое значение, скорее всего, будет использовать индексное сканирование, а более низкие значения могут использовать последовательные сканирования. Общее значение Это значение установлено на 1/2 памяти - это нормальная консервативная настройка, а 3/4, установленные на память - это относительно рекомендуемое значение. Просмотр статистики операционной системы через бесплатную команду, вы можете лучше оценить это значение.
[pg@e22 ~]$ free -g
total used free shared buff/cache available
Mem: 62 2 5 16 55 40
Swap: 7 0 7
work_mem
Этот параметр в основном используется для объема памяти, используемого внутренними операциями сортировки и хеш-таблицами перед записью во временные файлы, увеличиваяwork_memПараметр позволит PostgreSQL выполнять большую сортировку в памяти. этот параметр иmax_connectionsЕсть некоторые отношения, скажем, вы установили его на 30 МБ, 40 пользователей, одновременно выполняющих сортировку запросов, скоро будут использовать 1,2 ГБ реальной памяти. При этом для сложных запросов может выполняться несколько операций сортировки и хеширования, например, в сортировке слиянием задействовано 8 таблиц, что требует в 8 раз большеwork_mem.
Как показано в примере ниже, среда использует 4 МБwork_mem, который используется при выполнении операций сортировкиSort Methodдаexternal merge Disk.
kms=> explain (analyze,buffers) select * from KMS_BUSINESS_HALL_TOTAL order by buss_query_info;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Merge (cost=262167.99..567195.15 rows=2614336 width=52) (actual time=2782.203..5184.442 rows=3137204 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=68 read=25939, temp read=28863 written=28947
-> Sort (cost=261167.97..264435.89 rows=1307168 width=52) (actual time=2760.566..3453.783 rows=1045735 loops=3)
Sort Key: buss_query_info
Sort Method: external merge Disk: 50568kB
Worker 0: Sort Method: external merge Disk: 50840kB
Worker 1: Sort Method: external merge Disk: 49944kB
Buffers: shared hit=68 read=25939, temp read=28863 written=28947
-> Parallel Seq Scan on kms_business_hall_total (cost=0.00..39010.68 rows=1307168 width=52) (actual time=0.547..259.524 rows=1045735 loops=3)
Buffers: shared read=25939
Planning Time: 0.540 ms
Execution Time: 5461.516 ms
(14 rows)
Когда мы изменим параметры на 512 МБ, мы увидимSort Methodсталquicksort Memory, который становится сортировкой по памяти.
kms=> set work_mem to "512MB";
SET
kms=> explain (analyze,buffers) select * from KMS_BUSINESS_HALL_TOTAL order by buss_query_info;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=395831.79..403674.80 rows=3137204 width=52) (actual time=7870.826..8204.794 rows=3137204 loops=1)
Sort Key: buss_query_info
Sort Method: quicksort Memory: 359833kB
Buffers: shared hit=25939
-> Seq Scan on kms_business_hall_total (cost=0.00..57311.04 rows=3137204 width=52) (actual time=0.019..373.067 rows=3137204 loops=1)
Buffers: shared hit=25939
Planning Time: 0.081 ms
Execution Time: 8419.994 ms
(8 rows)
maintenance_work_mem
Указывает максимальный объем памяти, используемый операциями обслуживания, такими как (очистка, создание индекса и изменение таблицы, добавление внешнего ключа) Значение по умолчанию — 64 МБ. Так как в нормально работающей БД большого количества одновременных операций такого рода не будет, можно задать большее значение для повышения скорости очистки и создания внешних ключей для индексов.
postgres=# set maintenance_work_mem to "64MB";
SET
Time: 1.971 ms
postgres=# create index idx1_test on test(id);
CREATE INDEX
Time: 7483.621 ms (00:07.484)
postgres=# set maintenance_work_mem to "2GB";
SET
Time: 0.543 ms
postgres=# drop index idx1_test;
DROP INDEX
Time: 133.984 ms
postgres=# create index idx1_test on test(id);
CREATE INDEX
Time: 5661.018 ms (00:05.661)
Вы можете видеть, что при использовании 64 МБ по умолчанию для создания индекса скорость составляет 7,4 секунды, а после установки 2 ГБ скорость создания составляет 5,6 секунды.
wal_sync_method
То, как PostgreSQL принудительно фиксирует данные в журнале WAL после каждой транзакции. можно использоватьpg_test_fsyncкоманда для тестирования на вашей ОС,fdatasyncявляется методом по умолчанию в Linux. Как показано ниже, моя среда протестированаfdatasyncЕще скорость. Неподдерживаемые методы, такие как fsync_writethrough, показывают n/a напрямую.
postgres=# show wal_sync_method ;
wal_sync_method
-----------------
fdatasync
(1 row)
[pg@e22 ~]$ pg_test_fsync -s 3
3 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.
Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 4782.871 ops/sec 209 usecs/op
fdatasync 4935.556 ops/sec 203 usecs/op
fsync 3781.254 ops/sec 264 usecs/op
fsync_writethrough n/a
open_sync 3850.219 ops/sec 260 usecs/op
Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 2469.646 ops/sec 405 usecs/op
fdatasync 4412.266 ops/sec 227 usecs/op
fsync 3432.794 ops/sec 291 usecs/op
fsync_writethrough n/a
open_sync 1929.221 ops/sec 518 usecs/op
Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
1 * 16kB open_sync write 3159.780 ops/sec 316 usecs/op
2 * 8kB open_sync writes 1944.723 ops/sec 514 usecs/op
4 * 4kB open_sync writes 993.173 ops/sec 1007 usecs/op
8 * 2kB open_sync writes 493.396 ops/sec 2027 usecs/op
16 * 1kB open_sync writes 249.762 ops/sec 4004 usecs/op
Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different
descriptor.)
write, fsync, close 3719.973 ops/sec 269 usecs/op
write, close, fsync 3651.820 ops/sec 274 usecs/op
Non-sync'ed 8kB writes:
write 400577.329 ops/sec 2 usecs/op
wal_buffers
Размер буфера журнала транзакций, в который PostgreSQL записывает записи WAL перед сбросом буфера на диск. В PostgreSQL версии 12 значение по умолчанию равно -1, которое равно 1/32 от shared_buffers. Это значение можно установить вручную, если автоматический выбор слишком велик или слишком мал. Обычно считается установленным на 16 МБ.
synchronous_commit
Клиент выполняет фиксацию и ждет, пока WAL будет записан на диск, прежде чем вернуть клиенту статус успешного выполнения. Можно установить на on, remote_apply, remote_write, local, off и другие значения. По умолчанию включено. Если установлено значение off, sync_commit будет закрыт, и клиент вернется сразу после отправки, не дожидаясь сброса записи на диск. Если в этот момент произойдет сбой экземпляра PostgreSQL, последние несколько асинхронных коммитов будут потеряны.
default_statistics_target
PostgreSQL использует статистику для создания планов выполнения. Статистику можно собирать с помощью команды «Анализ» вручную или с помощью автоматического анализа, запускаемого процессом автоочистки.default_statistics_targetПараметр указывает уровень детализации, с которым эти статистические данные собираются и регистрируются. Значение по умолчанию 100 подходит для большинства рабочих нагрузок, для очень простых запросов может быть полезно меньшее значение, а для сложных запросов (особенно к большим таблицам) большее значение может быть более приемлемым. Чтобы не быть одним размером для всех, ALTER TABLE .. ALTER COLUMN .. SET STATISTICS можно использовать для переопределения уровня детализации по умолчанию для сбора статистики для определенных столбцов таблицы.
checkpoint_timeout, max_wal_size, min_wal_size, checkpoint_completion_target
Прежде чем разобраться с этими двумя параметрами, давайте сначала рассмотрим несколько операций, запускающих контрольные точки.
- Выполнить команду контрольной точки напрямую
- Выполнять команды, требующие контрольных точек (например, pg_start_backup, Создать базу данных, pg_ctl stop/start и т. д.)
- Настроенное количество времени (checkpoint_timeout) было достигнуто с момента последней контрольной точки.
- Количество WAL, созданных с момента последней контрольной точки (max_wal_size)
При значении по умолчанию контрольная точка будет иметь значение checkpoint_timeout=5min. То есть срабатывает каждые 5 минут. а такжеmax_wal_sizeПараметр представляет собой максимальный объем журнала с упреждающей записью (WAL), который увеличивается между автоматическими контрольными точками. По умолчанию 1 ГБ, если он превышает 1 ГБ, произойдет контрольная точка. Это мягкий предел. В особом случае, например, при кратковременной высокой нагрузке на систему, журнал может достигать 1 ГБ за несколько секунд, эта скорость значительно превышает значение checkpoint_timeout, и размер каталога pg_wal резко увеличивается. На данный момент мы можем видеть похожие предупреждения из журнала.
LOG: checkpoints are occurring too frequently (9 seconds apart)
HINT: Consider increasing the configuration parameter "max_wal_size".
LOG: checkpoints are occurring too frequently (2 seconds apart)
HINT: Consider increasing the configuration parameter "max_wal_size".
Так что настройте max_wal_size разумно, чтобы избежать частых контрольных точек. Обычно рекомендуется устанавливать значение более 16 ГБ, но конкретная настройка должна соответствовать рабочей нагрузке.
Параметр min_wal_size указывает, что пока использование диска WAL остается ниже этого значения, при выполнении контрольной точки старые файлы WAL всегда возвращаются для использования в будущем, а не удаляются напрямую.
Запись контрольных точек не завершается сразу, и PostgreSQL распределяет все операции контрольной точки в течение определенного периода времени. Этот период времени определяется параметромcheckpoint_completion_targetКонтроль, это дробь, по умолчанию 0,5. То есть операция записи на диск завершается при коэффициенте 0,5 между двумя контрольными точками. При маленьком значении процесс контрольной точки будет быстрее записывать на диск, а при большом — медленнее. Обычно рекомендуется установить значение 0,9, чтобы растянуть написание контрольных точек. Но недостатком является то, что когда происходит сбой, это влияет на время восстановления.
Используйте инструмент PGTune для настройки параметров
Для компаний, таких как друзья, без специалистов по администрированию баз данных, я обычно рекомендую им использовать PGTune для настройки параметров, этот инструмент представляет собой онлайн-программу,Связь. Настройка проста, вам просто нужно знать версию вашей базы данных, тип ОС, объем памяти, объем ЦП, тип диска, количество подключений и тип приложения. Вы можете легко получить некоторые рекомендуемые значения параметров.
Используйте postgresqltuner для оптимизации параметров
Конечно, мы также можем использовать инструмент postgresqltuner для оптимизации параметров.Автор сказал, что он был вдохновлен mysqltuner, который был написан сценарием perl.
Это программное обеспечение также очень простое в использовании, просто загрузите его, распакуйте и запустите скрипт.
postgresqltuner.pl --host=dbhost --database=testdb --user=username --password=qwerty
Результат выполнения следующий:
Эта программа более профессиональна, чем PGTune, она также выводит некоторые конфигурации операционной системы, в то же время оценивая адекватность параметров памяти исходя из текущей загрузки базы данных, аналогично Advisor.
Суммировать
Наконец, давайте сделаем концовку.Конфигурация системы по умолчанию подходит только для самостоятельной игры, не подходит для использования «из коробки». По-прежнему необходимо настроить соответствующие параметры в соответствии с профессиональным опытом администратора баз данных.Если нет профессиональной детской обуви администратора баз данных, вы также можете использовать скрипт PGTune или postgresqltuner для выполнения некоторых оптимизаций.После завершения этих оптимизаций производительность системы будет значительно улучшен.
Справочная документация
Tuning Your PostgreSQL Server
Tuning PostgreSQL Database Parameters to Optimize Performance