Postgres — ОБНОВЛЕНИЕ со временем замедляется

У меня есть таблица, как это (больше столбцов, но они будут делать):

events
+----------+----------------+--------------------+------------------+------------------+---------+
| event_id | user_ipaddress |   network_userid   |  domain_userid   | user_fingerprint | user_id |
+----------+----------------+--------------------+------------------+------------------+---------+
|        1 | 127.0.0.1      | 000d7d9e-f3cb-4a08 | 26dc9870c3572519 |       2199066221 |         |
|        2 | 127.0.0.1      | 000d7d9e-f3cb-4a08 | 26dc9870c3572519 |       2199066221 |         |
|        3 | 127.0.0.1      | 000d7d9e-f3cb-4a08 | 26dc9870c3572519 |       2199066221 |         |
|        4 | 127.0.0.1      | 000d7d9e-f3cb-4a08 | 26dc9870c3572519 |       2199066221 |         |
+----------+----------------+--------------------+------------------+------------------+---------+

Таблица содержит около 1 млн. Записей. Я пытаюсь обновить все записи, чтобы установить user_id,
Я использую очень простой PHP-скрипт для этого.

Я перебираю каждую запись с user_id = NULL а также SELECT из всей таблицы, чтобы найти существующие user_id основанный на user_ipaddress, network_userid, domain_userid и / или user_fingerprint,

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

Запрос выглядит так:

UPDATE events SET user_id = 'abc' WHERE event_id = '1'

SELECT часть супер быстрая (~ 5 мс).
UPDATE Партия начинается быстро (~ 10 мс), но становится медленнее (~ 800 мс) после нескольких сотен обновлений.

Если я подожду минут 10-20, это снова станет быстрым.

Я использую PostgreSQL 9.3.3 на AWS RDS (db.m1.medium) с SSD-хранилищем общего назначения.
У меня есть индексы по всем столбцам в сочетании и индивидуально.

Я играл с FILLFACTOR и в настоящее время он настроен на 70, Я пытался бежать VACUUM FULL events, но я никогда не знаю, закончил ли он (ждал более 1 часа). Тоже попробовал REINDEX TABLE events,

Я единственный, кто использует этот сервер.

Вот EXPLAIN ANALYZE из UPDATE запрос:

Update on events  (cost=0.43..8.45 rows=1 width=7479) (actual time=0.118..0.118 rows=0 loops=1)
->  Index Scan using events_event_id_idx on events  (cost=0.43..8.45 rows=1 width=7479) (actual time=0.062..0.065 rows=1 loops=1)
Index Cond: (event_id = '1'::bpchar)
Total runtime: 0.224 ms

Любые хорошие идеи о том, как я могу держать запрос быстро?

0

Решение

За 10-20 минут, чтобы снова стать быстрым, вы получаете постепенное улучшение?

Вещи, которые я бы проверил:

  • вы создаете новые соединения с каждым обновлением и оставляете их открытыми? Затем они остановятся и закроются когда-нибудь позже.
  • Что делает нагрузка на систему (процессор, память, ввод-вывод)? Я действительно задавался вопросом, может ли экземпляр поддерживать всплески, но я так не думаю.
0

Другие решения

Я просто догадываюсь,
Это потому что ваш первичный ключ это char, а не int. Попробуйте преобразовать ваш первичный ключ в int и посмотрите результат.

Ваш объяснить, проанализировать результат говорит Index Cond: (event_id = '1'::bpchar)

  1. Наилучшим выбором для первичного ключа являются целочисленные типы данных, поскольку целочисленные значения обрабатываются быстрее, чем символьные значения типов данных. Символьный тип данных (как первичный ключ) необходимо преобразовать в эквивалентные значения ASCII перед обработкой.
  2. Извлечение записи на основе первичного ключа будет быстрее в случае целых чисел в качестве первичных ключей, поскольку это будет означать, что на одной странице будет присутствовать больше записей индекса. Таким образом, общее время поиска уменьшается. Также соединения будут быстрее. Но это будет применимо, если ваш запрос использует поиск по кластерному индексу, а не сканирование, и если используется только одна таблица. В случае сканирования без дополнительного столбца будет означать больше строк на одной странице данных.

Индекс SQL — разница между char и int

0

Я обнаружил, что проблема была вызвана файловой системой, выбранной для моего экземпляра RDS.
Я бегал с General Purpose Storage (SSD), По-видимому, он имеет некоторые ограничения ввода / вывода. Таким образом, решение было переключить хранение. Сейчас я бегу Provisioned IOPS Storage и производительность улучшилась мгновенно.

Также решением может быть придерживаться General Purpose Storage (SSD) и увеличьте размер хранилища, так как это также увеличит пределы ввода / вывода.

Прочитайте больше:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html#Concepts.Storage.GeneralSSD

Спасибо за все ответы. И спасибо @Dan и @ArtemGr за то, что они указали мне в этом направлении.

0
По вопросам рекламы [email protected]