Увеличьте скорость записи PostgreSQL за счет вероятной потери данных?

Ответ 1

1M записей, вставленных за 22 минуты, составляет 758 записей в секунду. Каждый INSERT здесь является индивидуальной фиксацией на диске, и в конечном итоге с ним записываются журнал записи и компоненты базы данных. Обычно я ожидаю, что даже хорошее аппаратное обеспечение с кешем с батареей и всем, что вам повезет, ударит 3000 фиксаций в секунду. Таким образом, вы на самом деле не слишком плохи, если это обычное оборудование без такого ускорения записи. Нормальный предел здесь находится в диапазоне от 500 до 1000 комет/секунду в ситуации, в которой вы находитесь, без специальной настройки для этой ситуации.

Как бы то ни было, если вы не можете заставить коммиты включать в себя больше записей, ваши варианты ускорения этого включают:

• Отключите synchronous_commit (уже сделано)

• Увеличить wal_writer_delay. когда synchronous_commit выключен, бланки базы данных написанных каждые 200 мс. Ты можешь сделать что вместо этого несколько секунд если вы хотите, настроив это вверх, он просто увеличивает размер потери данных после сбоя.

• Увеличить wal_buffers до 16 МБ, просто сделать всю операцию больше эффективным.

• Увеличить контрольные точки, сократить о том, как часто регулярные данные записанный на диск. Вы, вероятно, хотите здесь не менее 64. Недостатки - это увеличение использования дискового пространства и более длительное время восстановления после аварии.

• Увеличить shared_buffers. По умолчанию здесь крошечный, обычно 32 МБ. Вы необходимо увеличить количество разделов UNIX памяти, которую система должна выделять. После этого полезные значения обычно > 1/4 от общей ОЗУ, до 8GB. Темп прироста здесь падает выше 256 МБ, увеличение с по умолчанию может быть действительно полезно хотя.

Это в значительной степени. Все, что вы затронули, может помочь, может привести к повреждению данных при сбое; все это полностью безопасно.

Ответ 2

22 минуты для 1 миллиона строк не кажутся такими медленными, особенно если у вас много индексов.

Как вы делаете вставки? Полагаю, вы используете пакетные вставки, а не одну строку за транзакцию.

Поддерживает ли PG какую-то массовую загрузку, например, чтение из текстового файла или предоставление потока данных CSV? Если это так, вам, скорее всего, рекомендуется использовать это.

Пожалуйста, разместите код, который вы используете, чтобы загрузить 1M-записи, и люди посоветуют.

Сообщение:

• Инструкция CREATE TABLE для таблицы, которую вы загружаете в
• Код, который вы используете для загрузки в
• небольшой пример данных (если возможно)

EDIT: Кажется, OP не интересуется массовыми вставками, но делает тест производительности для многих однострочных вставок. Я предполагаю, что каждая вставка находится в своей собственной транзакции.

• Рассмотрите возможность пакетной обработки вставок на стороне клиента, per-w630 > , записывая их во временный файл (надеюсь, долговременно/надежно) и имея демон или некоторый периодический процесс, который асинхронно выполняет пакетную вставку выдающихся записей, в партии разумного размера.
• Этот механизм для каждого устройства на самом деле дает лучшую производительность, по моему опыту, в данных аудита, таких как приложения хранилища данных, где данные сейчас не нужно входить в базу данных. Это также дает непригодности для приложения к базе данных.
• Конечно, у вас обычно будет несколько конечных устройств, создающих аудиторские записи (например, телефонные коммутаторы, почтовые ретрансляторы, серверы веб-приложений), каждый из которых должен иметь свой собственный экземпляр этого механизма, который полностью независим.
• Это действительно "умная" оптимизация, которая вносит множество сложностей в дизайн приложения и имеет множество мест, где могут возникать ошибки. Не реализуйте его, если вы действительно не уверены, что вам это нужно.

Ответ 3

Хорошо, вы не даете нам многого. Но похоже, что вы ищете асинхронные транзакции.

Не упускайте из виду аппаратное обновление - более быстрое оборудование обычно означает более быструю базу данных.

Ответ 4

Я думаю, что проблема не может быть решена только при работе с сервером.

Я обнаружил, что PostgreSQL может фиксировать 3000+ строк в секунду, и сервер и клиент не были заняты, но время прошло. В отличие от этого SQL Server может достигать 5000+ строк в секунду, а Oracle еще быстрее, он может достигать 12000+ в секунду, около 20 полей подряд.

Я предполагаю, что проблема с обоймой - проблема: отправьте строку на сервер и получите ответ от сервера. Оба SQL Server и Oracle поддерживают пакетные операции: отправляют более одной строки в вызове функции и ждут ответа.

Много лет назад я работал с Oracle: пытаясь улучшить производительность записи с помощью OCI, я читал документы и обнаружил, что слишком много круговых поездок снижают производительность. Наконец, я решил это с помощью пакетных операций: отправьте на сервер 128 строк и более, ожидая ответа. Он достиг 12000+ строк в секунду. Если вы не используете партии и отправляете все строки по отдельности (включая wait), она достигла всего около 2000 строк в секунду.

Ответ 5

Вы также должны увеличить checkpoint_segments (например, до 32 или даже выше) и, скорее всего, wal_buffers, а также

Edit:
если это объемная загрузка, вы должны использовать COPY для вставки строк. Это намного быстрее, чем обычные INSERT.

Если вам нужно использовать INSERT, вы рассмотрели возможность использования пакетной (для JDBC) или многострочных вставок?

Ответ 6

Ну, одна вещь, которую вы могли бы сделать для ускорения работы, - это падение индекса, который вы создаете вручную, - ограничение primary key уже автоматически создает уникальный индекс в этом столбце, как вы можете видеть ниже (я тестирую 8.3)

postgres=> CREATE TABLE "user"
postgres-> (
postgres(>   id serial NOT NULL,
postgres(>   username character varying(40),
postgres(>   email character varying(70),
postgres(>   website character varying(100),
postgres(>   created integer,
postgres(>   CONSTRAINT user_pkey PRIMARY KEY (id)
postgres(> )
postgres-> WITH ( OIDS=FALSE );
NOTICE:  CREATE TABLE will create implicit sequence "user_id_seq" for serial column "user.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "user_pkey" for table "user"
CREATE TABLE
postgres=> CREATE INDEX id ON "user" USING btree (id);
CREATE INDEX
postgres=> \d user
                                  Table "stack.user"
  Column  |          Type          |                     Modifiers
----------+------------------------+---------------------------------------------------
 id       | integer                | not null default nextval('user_id_seq'::regclass)
 username | character varying(40)  |
 email    | character varying(70)  |
 website  | character varying(100) |
 created  | integer                |
Indexes:
    "user_pkey" PRIMARY KEY, btree (id)
    "id" btree (id)

Кроме того, рассмотрите возможность изменения wal_sync_method на опцию, использующую O_DIRECT - это не значение по умолчанию для Linux

Ответ 7

1M совершает за 22 минуты, кажется разумным, даже с synchronous_commit = off, но если вы можете избежать необходимости совершать на каждой вставке, вы можете получить намного быстрее, чем это. Я просто попытался вставить 1M (идентичные) строки в вашу примерную таблицу из 10 параллельных авторов, используя команду bulk-insert COPY:

$ head -n3 users.txt | cat -A # the rest of the file is just this another 99997 times
Random J. User^[email protected]^Ihttp://example.org^I100$
Random J. User^[email protected]^Ihttp://example.org^I100$
Random J. User^[email protected]^Ihttp://example.org^I100$
$ wc -l users.txt
100000 users.txt
$ time (seq 10 | xargs --max-procs=10 -n 1 bash -c "cat users.txt | psql insertspeed -c 'COPY \"user\" (username, email, website, created) FROM STDIN WITH (FORMAT text);'")

real    0m10.589s
user    0m0.281s
sys     0m0.285s
$ psql insertspeed -Antc 'SELECT count(*) FROM "user"'
1000000

Ясно, что там всего 10 коммитов, что не совсем то, что вы ищете, но, надеюсь, дает вам какое-то указание скорости, которая может быть возможной, объединяя ваши вставки вместе. Это виртуальная виртуальная машина, работающая под управлением Linux, на довольно обычном домашнем хосте Windows, поэтому не совсем возможное оборудование с наивысшей производительностью.

Чтобы дать меньше фигурок игрушек, у нас есть служба, работающая в производстве, которая имеет единственный поток, который передает данные в Postgres с помощью команды COPY, аналогичной приведенной выше. Он заканчивает партию и фиксируется после определенного количества строк или если транзакция достигает определенного возраста (в зависимости от того, что наступит раньше). Он может поддерживать 11 000 вставок в секунду с максимальной задержкой ~ 300 мс, выполняя ~ 4 фиксации в секунду. Если бы мы затянули максимально допустимый возраст транзакций, мы получили бы больше коммитов в секунду, что уменьшило бы задержку, а также пропускную способность. Опять же, это не на ужасно впечатляющем аппаратном обеспечении.

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

Ответ 8

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

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