Оптимизируйте PostgreSQL для быстрого тестирования

Я переключаюсь на PostgreSQL из SQLite для типичного приложения Rails.

Проблема в том, что работа спецификаций стала медленной с PG.
На SQLite это заняло ~34 секунды, на PG - ~76 секунд, что более чем в 2 раза медленнее.

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

Пара очевидных вещей из головы:

  • RAM Disk (неплохо было бы увидеть хорошую настройку RSpec на OSX)
  • Незаполненные таблицы (можно ли применить их ко всей базе данных, чтобы мне не пришлось менять все сценарии?)

Как вы, наверное, поняли, меня не волнует надежность и все остальное (БД здесь просто пустяк).
Мне нужно максимально использовать возможности PG и сделать это как можно быстрее.

Лучший ответ - в идеале описать приемы для этого, настройки и недостатки этих приемов.

ОБНОВИТЬ: fsync = off + full_page_writes = off только время уменьшилось до ~65 секунд (~-16 секунд). Хорошее начало, но далеко от цели 34.

ОБНОВЛЕНИЕ 2: Я пытался использовать RAM-диск, но прирост производительности был в пределах ошибки. Так что, похоже, оно того не стоит.

ОБНОВЛЕНИЕ 3:* Я нашел самое большое узкое место, и теперь мои спецификации работают так же быстро, как и спецификации SQLite.

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

Чтобы "исправить" это, я открываю транзакцию перед каждым тестом и откатываю ее в конце.

Некоторые цифры для ~700 тестов.

  • Усечение: SQLite - 34 с, PG - 76 с.
  • Транзакция: SQLite - 17 с, PG - 18 с.

Увеличение скорости в 2 раза для SQLite. Увеличение скорости в 4 раза для PG.

Источник

2 ответа

Решение

Во-первых, всегда используйте последнюю версию PostgreSQL. Улучшения производительности всегда идут впереди, так что вы, вероятно, теряете время, если настраиваете старую версию. Например, PostgreSQL 9.2 значительно улучшает скорость TRUNCATE и, конечно, добавляет сканирование только по индексу. Даже небольшие релизы всегда должны сопровождаться; см. политику версий.

Этикет

НЕ помещайте табличное пространство на RAM-диск или другое недолговечное хранилище.

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

Если вы действительно хотите систему на основе виртуального диска, initdb целый новый кластер на виртуальном диске initdb добавление нового экземпляра PostgreSQL на виртуальный диск, чтобы у вас был полностью одноразовый экземпляр PostgreSQL.

Конфигурация сервера PostgreSQL

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

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

Излишне говорить, что вы никогда не должны включать fsync=off в производстве, если вы не используете Pg в качестве временной базы данных для данных, которые вы можете заново сгенерировать из других источников. Если и только если вы делаете, чтобы отключить fsync, можно также включить full_page_writes выкл, так как это больше не приносит никакой пользы. Остерегайтесь этого fsync=off а также full_page_writes применять на уровне кластера, чтобы они влияли на все базы данных в вашем экземпляре PostgreSQL.

Для производства вы можете использовать synchronous_commit=off и установить commit_delay, так как вы получите много тех же преимуществ, что и fsync=off без гигантского риска повреждения данных. У вас есть небольшое окно потери последних данных, если вы включите асинхронную фиксацию - но это все.

Если у вас есть возможность немного изменить DDL, вы также можете использовать UNLOGGED таблицы в Pg 9.1+, чтобы полностью избежать регистрации в WAL и получить реальное увеличение скорости за счет удаления таблиц в случае сбоя сервера. Не существует параметра конфигурации, чтобы сделать все таблицы незаблокированными, это необходимо установить во время CREATE TABLE, В дополнение к хорошему для тестирования, это удобно, если у вас есть таблицы, полные сгенерированных или неважных данных в базе данных, которая в противном случае содержит вещи, которые вы должны быть в безопасности.

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

мелодия shared_buffers чтобы соответствовать вашей рабочей нагрузке. Это зависит от ОС, зависит от того, что еще происходит с вашей машиной, и требует некоторых проб и ошибок. Значения по умолчанию чрезвычайно консервативны. Возможно, вам придется увеличить максимальный предел общей памяти ОС, если вы увеличите shared_buffers на PostgreSQL 9.2 и ниже; 9.3 и выше изменили способ использования общей памяти, чтобы избежать этого.

Если вы используете только пару соединений, которые выполняют много работы, увеличьте work_mem чтобы дать им больше оперативной памяти для игр для сортировки и т.д. Остерегайтесь слишком высокого work_mem установка может вызвать проблемы нехватки памяти, потому что это для каждого сортировки, а не для соединения, поэтому один запрос может иметь много вложенных сортировок. Вы действительно должны увеличить work_mem если вы можете увидеть, что сорта разливаются на диск в EXPLAIN или вошли с log_temp_files настройка (рекомендуется), но более высокое значение может также позволить Pg выбирать более разумные планы.

Как сказал другой автор, здесь целесообразно разместить xlog и основные таблицы / индексы на отдельных жестких дисках, если это возможно. Отдельные разделы довольно бессмысленны, вам действительно нужны отдельные диски. Это разделение имеет гораздо меньше преимуществ, если вы работаете с fsync=off и почти нет, если вы используете UNLOGGED столы.

Наконец, настройте ваши запросы. Убедитесь, что ваш random_page_cost а также seq_page_cost отражать производительность вашей системы, убедитесь, что ваш effective_cache_size правильно и т. д. Используйте EXPLAIN (BUFFERS, ANALYZE) изучить индивидуальные планы запросов и включить auto_explain модуль, чтобы сообщить обо всех медленных запросах. Часто вы можете значительно повысить производительность запросов, просто создав соответствующий индекс или изменив параметры стоимости.

AFAIK нет никакого способа установить всю базу данных или кластер как UNLOGGED, Было бы интересно сделать это. Подумайте об этом в списке рассылки PostgreSQL.

Настройка ОС хоста

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

В Linux вы можете контролировать это с помощью подсистемы виртуальной памяти. dirty_* настройки, как dirty_writeback_centisecs,

Единственная проблема с настройкой параметров обратной записи, чтобы быть слишком слабой, заключается в том, что очистка какой-либо другой программой может также привести к сбросу всех накопленных буферов PostgreSQL, вызывая большие задержки, когда все блокируется при записи. Вы можете облегчить это, запустив PostgreSQL в другой файловой системе, но некоторые сбросы могут быть на уровне устройства или на уровне целого хоста, а не на уровне файловой системы, поэтому вы не можете на это полагаться.

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

В более новых ядрах вы можете убедиться, что vm.zone_reclaim_mode установлен на ноль, так как это может вызвать серьезные проблемы с производительностью систем NUMA (большинство систем в наши дни) из-за взаимодействия с тем, как PostgreSQL управляет shared_buffers,

Настройка запросов и рабочей нагрузки

Это вещи, которые действительно требуют изменения кода; они могут не подойти вам. Некоторые вещи, которые вы могли бы применить.

Если вы не планируете работать с большими транзакциями, начните. Множество небольших транзакций обходятся дорого, поэтому вы должны пакетировать вещи, когда это возможно и практично. Если вы используете асинхронную фиксацию, это менее важно, но все же настоятельно рекомендуется.

По возможности используйте временные таблицы. Они не генерируют трафик WAL, поэтому они намного быстрее для вставок и обновлений. Иногда стоит сложить кучу данных во временную таблицу, манипулировать ею по мере необходимости, а затем выполнить INSERT INTO ... SELECT ... скопировать его в финальный стол. Обратите внимание, что временные таблицы для каждого сеанса; если ваш сеанс заканчивается или вы теряете соединение, то временная таблица исчезает, и никакое другое соединение не может видеть содержимое временных таблиц сеанса.

Если вы используете PostgreSQL 9.1 или новее, вы можете использовать UNLOGGED таблицы для данных, которые вы можете позволить себе потерять, например, состояние сеанса. Они видны в разных сеансах и сохраняются между соединениями. Они усекаются, если сервер отключается из-за нечистоты, поэтому их нельзя использовать ни для чего, что вы не можете создать заново, но они отлично подходят для кэшей, материализованных представлений, таблиц состояний и т. Д.

В общем, не DELETE FROM blah;, использование TRUNCATE TABLE blah; вместо; это намного быстрее, когда вы сбрасываете все строки в таблице. Усекать много таблиц в одном TRUNCATE позвони, если сможешь. Существует предостережение, если вы делаете много TRUNCATES маленьких столиков снова и снова; см.: Postgresql Скорость усечения

Если у вас нет индексов для внешних ключей, DELETEРабота с первичными ключами, на которые ссылаются эти внешние ключи, будет ужасно медленной. Убедитесь, что создали такие индексы, если вы когда-либо ожидали DELETE из ссылочной таблицы (таблиц). Индексы не требуются для TRUNCATE,

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

аппаратные средства

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

Если у вас недостаточно ОЗУ, чем быстрее вы сможете получить память, тем лучше. Даже дешевый SSD имеет огромное значение для вращающейся ржавчины. Не доверяйте дешевым твердотельным накопителям для производства, они часто не защищены от сбоев и могут съесть ваши данные.

Учусь

Книга Грега Смита " Высокая производительность PostgreSQL 9.0" остается актуальной, несмотря на упоминание более старой версии. Это должна быть полезная ссылка.

Присоединяйтесь к общему списку рассылки PostgreSQL и следуйте ему.

Чтение:

Источник

Используйте другую структуру диска:

  • другой диск для $PGDATA
  • другой диск для $PGDATA/pg_xlog
  • другой диск для файлов tem (для базы данных $PGDATA/base//pgsql_tmp) (см. примечание о work_mem)

postgresql.conf:

  • shared_memory: 30% доступной оперативной памяти, но не более 6–8 ГБ. Кажется, было бы лучше иметь меньше разделяемой памяти (2–4 ГБ) для интенсивных рабочих нагрузок при записи
  • work_mem: в основном для выборочных запросов с сортировками / агрегатами. Это для каждого параметра подключения, и запрос может распределять это значение несколько раз. Если данные не помещаются, используется диск (pgsql_tmp). Проверьте "объяснить анализ", чтобы увидеть, сколько памяти вам нужно
  • fsync и synchronous_commit: значения по умолчанию безопасны, но если вы можете допустить потерю данных, вы можете затем отключить
  • random_page_cost: если у вас SSD или быстрый RAID-массив, вы можете уменьшить его до 2,0 (RAID) или даже ниже (1,1) для SSD
  • checkpoint_segments: вы можете подняться выше 32 или 64 и изменить checkpoint_completion_target на 0.9. Более низкое значение обеспечивает более быстрое восстановление после сбоя
Источник
Другие вопросы по тегам