Параметр конфигурации work_mem в PostgreSQL в Linux
Я должен оптимизировать запросы, настраивая основные параметры конфигурации сервера PostgreSQL. В документации я наткнулся на work_mem параметр. Затем я проверил, как изменение этого параметра повлияет на производительность моего запроса (используя сортировку). Я измерил время выполнения запроса с различными work_mem Настройки и были очень разочарованы.
Таблица, по которой я выполняю свой запрос, содержит 10 000 000 строк, и для сортировки необходимо 430 МБ данных. (Sort Method: external merge Disk: 430112kB).
С work_mem = 1MB, EXPLAIN вывод:
Total runtime: 29950.571 ms (sort takes about 19300 ms).
Sort (cost=4032588.78..4082588.66 rows=19999954 width=8)
(actual time=22577.149..26424.951 rows=20000000 loops=1)
Sort Key: "*SELECT* 1".n
Sort Method: external merge Disk: 430104kB
С work_mem = 5MB:
Total runtime: 36282.729 ms (sort: 25400 ms).
Sort (cost=3485713.78..3535713.66 rows=19999954 width=8)
(actual time=25062.383..33246.561 rows=20000000 loops=1)
Sort Key: "*SELECT* 1".n
Sort Method: external merge Disk: 430104kB
С work_mem = 64MB:
Total runtime: 42566.538 ms (sort: 31000 ms).
Sort (cost=3212276.28..3262276.16 rows=19999954 width=8)
(actual time=28599.611..39454.279 rows=20000000 loops=1)
Sort Key: "*SELECT* 1".n
Sort Method: external merge Disk: 430104kB
Кто-нибудь может объяснить, почему производительность ухудшается? Или предложить какие-либо другие методы, чтобы ускорить выполнение запросов путем изменения параметров сервера?
Мой запрос (я знаю, что он не оптимален, но я должен сравнить этот запрос):
SELECT n
FROM (
SELECT n + 1 AS n FROM table_name
EXCEPT
SELECT n FROM table_name) AS q1
ORDER BY n DESC;
Полный план выполнения:
Sort (cost=5805421.81..5830421.75 rows=9999977 width=8) (actual time=30405.682..30405.682 rows=1 loops=1)
Sort Key: q1.n
Sort Method: quicksort Memory: 25kB
-> Subquery Scan q1 (cost=4032588.78..4232588.32 rows=9999977 width=8) (actual time=30405.636..30405.637 rows=1 loops=1)
-> SetOp Except (cost=4032588.78..4132588.55 rows=9999977 width=8) (actual time=30405.634..30405.634 rows=1 loops=1)
-> Sort (cost=4032588.78..4082588.66 rows=19999954 width=8) (actual time=23046.478..27733.020 rows=20000000 loops=1)
Sort Key: "*SELECT* 1".n
Sort Method: external merge Disk: 430104kB
-> Append (cost=0.00..513495.02 rows=19999954 width=8) (actual time=0.040..8191.185 rows=20000000 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..269247.48 rows=9999977 width=8) (actual time=0.039..3651.506 rows=10000000 loops=1)
-> Seq Scan on table_name (cost=0.00..169247.71 rows=9999977 width=8) (actual time=0.038..2258.323 rows=10000000 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..244247.54 rows=9999977 width=8) (actual time=0.008..2697.546 rows=10000000 loops=1)
-> Seq Scan on table_name (cost=0.00..144247.77 rows=9999977 width=8) (actual time=0.006..1079.561 rows=10000000 loops=1)
Total runtime: 30496.100 ms
2 ответа
Я разместил ваш план запроса на веб-сайте объяснения.depesz.com, посмотрите.
Оценки планировщика запросов в некоторых местах ужасно неверны. Ты бегал? ANALYZE относительно недавно?
Прочитайте главы в руководстве по статистике, используемой планировщиком и постоянными стоимости планировщика. Обратите особое внимание на главы о random_page_cost а также default_statistics_target,
Я бы попробовал:
ALTER TABLE diplomas ALTER COLUMN number SET STATISTICS 1000;
ANALYZE diplomas;
Учитывая ваши 10 миллионов строк, вы можете даже пойти выше. Это зависит от распределения ваших данных и фактического запроса. Эксперимент. По умолчанию 100, макс. это 10000.
Для базы данных такого размера, только 1 или 5 МБ work_mem как правило, не достаточно. Прочтите вики-страницу Postgres на Tuning Postgres, на которую ссылается @aleroot.
Поскольку ваш запрос требует 430104 КБ памяти на диске в соответствии с EXPLAIN выходной, вы должны установить work_mem что-то вроде 500 МБ или более, чтобы разрешить сортировку в памяти. Представление данных в памяти требует большего пространства, чем представление на диске. Вы можете быть заинтересованы в том, что Том Лэйн недавно опубликовал по этому вопросу.
Увеличение work_mem совсем немного, как вы пытались, не сильно поможет или даже может замедлить, по-видимому. Не уверен, как объяснить замедление. Может быть, дополнительный work_mem не привыкает вообще или только изначально, будучи недостаточным, но отнимает от другого выделения памяти.
Если ваша память ограничена, не устанавливайте work_mem слишком высоко, или вы можете голодать конкурирующих ресурсов. Только установите его достаточно высоко временно для этого запроса:
SET work_mem = '500MB';
Сбросьте его до значения по умолчанию впоследствии:
RESET work_mem;
Или, еще лучше, используйте SET LOCAL установить его только для текущей транзакции. Лучше всего выполнить все это в одной транзакции в любом случае.
SET search_path='tmp';
-- Generate some data ...
-- DROP table tmp.table_name ;
-- CREATE table tmp.table_name ( n INTEGER NOT NULL PRIMARY KEY);
-- INSERT INTO tmp.table_name(n) SELECT generate_series(1,1000);
-- DELETE FROM tmp.table_name WHERE random() < 0.05 ;
Запрос на исключение эквивалентен следующей форме НЕ СУЩЕСТВУЕТ, которая генерирует здесь другой план запроса (но с теми же результатами) (что-то с 9.0.1)
-- EXPLAIN ANALYZE
WITH q1 AS (
SELECT 1+tn.n AS n
FROM table_name tn
WHERE NOT EXISTS (
SELECT * FROM table_name nx
WHERE nx.n = tn.n+1
)
)
SELECT q1.n
FROM q1
ORDER BY q1.n DESC;
(также возможна версия с рекурсивным CTE:-)
РЕДАКТИРОВАТЬ: планы запроса. все для 100К записей с удалением 0,2 %
Исходный запрос:
------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=36461.76..36711.20 rows=99778 width=4) (actual time=2682.600..2682.917 rows=222 loops=1)
Sort Key: q1.n
Sort Method: quicksort Memory: 22kB
-> Subquery Scan q1 (cost=24984.41..26979.97 rows=99778 width=4) (actual time=2003.047..2682.036 rows=222 loops=1)
-> SetOp Except (cost=24984.41..25982.19 rows=99778 width=4) (actual time=2003.042..2681.389 rows=222 loops=1)
-> Sort (cost=24984.41..25483.30 rows=199556 width=4) (actual time=2002.584..2368.963 rows=199556 loops=1)
Sort Key: "*SELECT* 1".n
Sort Method: external merge Disk: 3512kB
-> Append (cost=0.00..5026.57 rows=199556 width=4) (actual time=0.071..1452.838 rows=199556 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..2638.01 rows=99778 width=4) (actual time=0.067..470.652 rows=99778 loops=1)
-> Seq Scan on table_name (cost=0.00..1640.22 rows=99778 width=4) (actual time=0.063..178.365 rows=99778 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..2388.56 rows=99778 width=4) (actual time=0.014..429.224 rows=99778 loops=1)
-> Seq Scan on table_name (cost=0.00..1390.78 rows=99778 width=4) (actual time=0.011..143.320 rows=99778 loops=1)
Total runtime: 2684.840 ms
(14 rows)
НЕ СУЩЕСТВУЕТ-версия с CTE:
----------------------------------------------------------------------------------------------------------------------
Sort (cost=6394.60..6394.60 rows=1 width=4) (actual time=699.190..699.498 rows=222 loops=1)
Sort Key: q1.n
Sort Method: quicksort Memory: 22kB
CTE q1
-> Hash Anti Join (cost=2980.01..6394.57 rows=1 width=4) (actual time=312.262..697.985 rows=222 loops=1)
Hash Cond: ((tn.n + 1) = nx.n)
-> Seq Scan on table_name tn (cost=0.00..1390.78 rows=99778 width=4) (actual time=0.013..143.210 rows=99778 loops=1)
-> Hash (cost=1390.78..1390.78 rows=99778 width=4) (actual time=309.923..309.923 rows=99778 loops=1)
-> Seq Scan on table_name nx (cost=0.00..1390.78 rows=99778 width=4) (actual time=0.007..144.102 rows=99778 loops=1)
-> CTE Scan on q1 (cost=0.00..0.02 rows=1 width=4) (actual time=312.270..698.742 rows=222 loops=1)
Total runtime: 700.040 ms
(11 rows)
НЕ СУЩЕСТВУЕТ-версия без CTE
--------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=6394.58..6394.58 rows=1 width=4) (actual time=692.313..692.625 rows=222 loops=1)
Sort Key: ((1 + tn.n))
Sort Method: quicksort Memory: 22kB
-> Hash Anti Join (cost=2980.01..6394.57 rows=1 width=4) (actual time=308.046..691.849 rows=222 loops=1)
Hash Cond: ((tn.n + 1) = nx.n)
-> Seq Scan on table_name tn (cost=0.00..1390.78 rows=99778 width=4) (actual time=0.014..142.781 rows=99778 loops=1)
-> Hash (cost=1390.78..1390.78 rows=99778 width=4) (actual time=305.732..305.732 rows=99778 loops=1)
-> Seq Scan on table_name nx (cost=0.00..1390.78 rows=99778 width=4) (actual time=0.007..143.783 rows=99778 loops=1)
Total runtime: 693.139 ms
(9 rows)
Мой вывод заключается в том, что версии "НЕ СУЩЕСТВУЮТ" заставляют Postgres создавать лучшие планы.