У меня есть таблица, как это (больше столбцов, но они будут делать):
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
Любые хорошие идеи о том, как я могу держать запрос быстро?
За 10-20 минут, чтобы снова стать быстрым, вы получаете постепенное улучшение?
Вещи, которые я бы проверил:
Я просто догадываюсь,
Это потому что ваш первичный ключ это char, а не int. Попробуйте преобразовать ваш первичный ключ в int и посмотрите результат.
Ваш объяснить, проанализировать результат говорит Index Cond: (event_id = '1'::bpchar)
Я обнаружил, что проблема была вызвана файловой системой, выбранной для моего экземпляра 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 за то, что они указали мне в этом направлении.