python example Как я могу ускорить операции обновления/замены в PostgreSQL?



py postgresql (5)

В вашем insert_or_replace . попробуй это:

WHERE EXISTS(SELECT 1 FROM item WHERE key=NEW.key LIMIT 1)

вместо

WHERE EXISTS(SELECT 1 FROM item WHERE key=NEW.key)

Как отмечено в комментариях, это, вероятно, ничего не сделает. Все, что я должен добавить, это то, что вы всегда можете ускорить производительность INSERT / UPDATE, удаляя индексы. Скорее всего, это не то, что вы хотите сделать, если не обнаружите, что ваша таблица переиндексирована, но это, по крайней мере, следует проверить.

У нас есть довольно специфическое приложение, которое использует PostgreSQL 8.3 в качестве бэкэнда хранилища (используя Python и psycopg2). Операции, которые мы выполняем над важными таблицами, в большинстве случаев вставляются или обновляются (редко удаляются или выбираются).

По соображениям здравого смысла мы создали собственный слой, подобный Data Mapper, который работает достаточно хорошо, но у него есть одно большое узкое место - производительность обновлений. Конечно, я не ожидаю, что сценарий обновления / замены будет таким же быстрым, как сценарий «вставка в пустую таблицу», но было бы неплохо стать немного ближе.

Обратите внимание, что эта система свободна от одновременных обновлений

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

  1. Создайте процедуру replace() , которая принимает массив строк для обновления:

    CREATE OR REPLACE FUNCTION replace_item(data item[]) RETURNS VOID AS $$
    BEGIN
        FOR i IN COALESCE(array_lower(data,1),0) .. COALESCE(array_upper(data,1),-1) LOOP
           UPDATE item SET a0=data[i].a0,a1=data[i].a1,a2=data[i].a2 WHERE key=data[i].key;
        END LOOP;
    END;
    $$ LANGUAGE plpgsql
  2. Создайте правило insert_or_replace чтобы все, кроме случайного удаления, становилось многострочным.

    CREATE RULE "insert_or_replace" AS
        ON INSERT TO "item"
        WHERE EXISTS(SELECT 1 FROM item WHERE key=NEW.key)
        DO INSTEAD
            (UPDATE item SET a0=NEW.a0,a1=NEW.a1,a2=NEW.a2 WHERE key=NEW.key);

Оба эти параметра значительно ускоряют обновления, хотя последний немного замедляет вставки:

Multi-row insert           : 50000 items inserted in  1.32 seconds averaging 37807.84 items/s
executemany() update       : 50000 items updated  in 26.67 seconds averaging  1874.57 items/s
update_andres              : 50000 items updated  in  3.84 seconds averaging 13028.51 items/s
update_merlin83 (i/d/i)    : 50000 items updated  in  1.29 seconds averaging 38780.46 items/s
update_merlin83 (i/u)      : 50000 items updated  in  1.24 seconds averaging 40313.28 items/s
replace_item() procedure   : 50000 items replaced in  3.10 seconds averaging 16151.42 items/s
Multi-row insert_or_replace: 50000 items inserted in  2.73 seconds averaging 18296.30 items/s
Multi-row insert_or_replace: 50000 items replaced in  2.02 seconds averaging 24729.94 items/s

Случайные заметки о тестовом прогоне:

  • Все тесты выполняются на том же компьютере, где находится база данных; подключение к localhost.
  • Вставки и обновления применяются к базе данных партиями по 500 элементов, каждый из которых отправляется в своей собственной транзакции ( ОБНОВЛЕНО ).
  • Все тесты обновления / замены использовали те же значения, которые были уже в базе данных.
  • Все данные были экранированы с помощью функции psycopg2 adapt ().
  • Все таблицы усекаются и очищаются перед использованием ( ДОБАВЛЕНО , в предыдущих запусках происходило только усечение)
  • Таблица выглядит так:

    CREATE TABLE item (
        key MACADDR PRIMARY KEY,
        a0 VARCHAR,
        a1 VARCHAR,
        a2 VARCHAR
    )

Итак, реальный вопрос: как я могу ускорить операции обновления / замены? (Я думаю, что эти результаты могут быть «достаточно хорошими», но я не хочу сдаваться, не касаясь ТАКОЙ толпы :)

Кроме того, кто-нибудь намекает на более элегантный replace_item () или свидетельство того, что мои тесты полностью сломаны, было бы очень кстати.

Тестовый скрипт доступен here если вы хотите попытаться воспроизвести. Не забудьте проверить это сначала хотя ... это WorksForMe, но ...

Вам нужно будет отредактировать строку db.connect () в соответствии с вашими настройками.

РЕДАКТИРОВАТЬ

Благодаря andres в #postgresql @ freenode у меня есть еще один тест с обновлением с одним запросом; очень похоже на многострочную вставку (указана выше как update_andres).

UPDATE item
SET a0=i.a0, a1=i.a1, a2=i.a2 
FROM (VALUES ('00:00:00:00:00:01', 'v0', 'v1', 'v2'), 
             ('00:00:00:00:00:02', 'v3', 'v4', 'v5'),
             ...
      ) AS i(key, a0, a1, a2)
WHERE item.key=i.key::macaddr

РЕДАКТИРОВАТЬ

Благодаря merlin83 в #postgresql @ freenode и jug / jwp ниже у меня есть еще один тест с подходом вставки в темп / удаления / вставки (указан как «update_merlin83 (i / d / i)» выше).

INSERT INTO temp_item (key, a0, a1, a2)
    VALUES (
        ('00:00:00:00:00:01', 'v0', 'v1', 'v2'),
        ('00:00:00:00:00:02', 'v3', 'v4', 'v5'),
        ...);

DELETE FROM item
USING temp_item
WHERE item.key=temp_item.key;

INSERT INTO item (key, a0, a1, a2)
    SELECT key, a0, a1, a2
    FROM temp_item;

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

РЕДАКТИРОВАТЬ

andres в #postgresql @ freenode указали, что я должен протестировать с вариантом вставки в temp / update (указан как «update_merlin83 (i / u)» выше).

INSERT INTO temp_item (key, a0, a1, a2)
    VALUES (
        ('00:00:00:00:00:01', 'v0', 'v1', 'v2'),
        ('00:00:00:00:00:02', 'v3', 'v4', 'v5'),
        ...);

UPDATE item
SET a0=temp_item.a0, a1=temp_item.a1, a2=temp_item.a2
FROM temp_item
WHERE item.key=temp_item.key

РЕДАКТИРОВАТЬ

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


Answer #1

Обычный способ сделать это в pg: загрузить необработанные данные, соответствующие целевой таблице, во временную таблицу (без ограничений), используя copy, merge (забавная часть), прибыль.

Я написал функцию merge_by_key специально для этих ситуаций:

http://mbk.projects.postgresql.org/

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


Answer #2

Звучит так, как будто бы вы получили выгоду от использования WAL (Write Ahead Logging) с ИБП для кэширования ваших обновлений между записями на диск.

wal_buffers Этот параметр определяет количество буферов, которые может иметь WAL (запись впереди журнала). Если в вашей базе данных много транзакций записи, установка этого значения немного выше значения по умолчанию может привести к лучшему использованию дискового пространства. Экспериментируйте и решайте. Хорошее начало было бы около 32-64, что соответствует 256-512Кб памяти.

http://www.varlena.com/GeneralBits/Tidbits/perf.html


Answer #3

В Oracle блокировка таблицы определенно поможет. Возможно, вы захотите попробовать это и с PostgreSQL.


Answer #4

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





psycopg2