- Автор темы
- #1
По мере увеличения числа инстансов PostgreSQL оптимизация вычислительных ресурсов становится все более актуальной. Рассмотрим простую, но эффективную оптимизацию — prepared statements. В статье расскажу о некоторых особенностях custom и generic планов в PostgreSQL при выполнении prepared statements.
Prepared statements и их планы выполнения
По мере миграции данных в PostgreSQL растет и нагрузка. Поэтому «толстости» и тонкости настройки производительности и устройства СУБД вообще начинают приобретать все большее значение.В OLTP-системах, где много быстрых и сравнительно простых запросов, одна из простейших оптимизаций — использование prepared statements.
Документация прямо говорит:
При создании prepared statement’а запрос разбирается, анализируется и трансформируется — это происходит один раз. При последующих выполнениях Postgres строит план и выполняет запрос.
Для планирования тоже существует оптимизация. Документация говорит следующее:
Все вроде бы понятно, generic-план позволяет снизить накладные расходы на планирование, а custom-планы могут быть более оптимальными, так как учитывают значения параметров.
Заглянем немного внутрь.
Поведение запроса к несекционированной таблице
Рассмотрим, как будет себя вести запрос к несекционированной таблице:create table mediumtab as select n,'Medium table record '||n as v from generate_series(1,1e6) s(n);
create unique index on mediumtab(n);
analyze mediumtab;
Запрос:
prepare s1 as select max(md5(v)) from mediumtab where n between $1::numeric and $2::numeric;
Выполним запрос несколько раз:
execute s1(60,70);
После шестого выполнения должен появиться generic-план. Его можно увидеть в дампе memory context’а с помощью вызова из отладчика call MemoryContextStatsDetail(TopMemoryContext,30000,20000,1. Есть способ проще увидеть, какой generic- или custom-план используется.
Действительно, видим custom-план:
testdb=# explain execute s1(60,70);
QUERY PLAN
-----------------------------------------------------------------------------------------
Aggregate (cost=8.70..8.71 rows=1 width=32)
-> Index Scan using mediumtab_n_idx on mediumtab (cost=0.42..8.64 rows=11 width=26)
Index Cond: ((n >= '60'::numeric) AND (n <= '70'::numeric))
В PostgreSQL появилась опция GENERIC_PLAN команды EXPLAIN, можем посмотреть, как выглядит generic-план для нашего запроса:
testdb=# explain (generic_plan) select max(md5(v)) from mediumtab where n between $1::numeric and $2::numeric;
QUERY PLAN
---------------------------------------------------------------------------------------------
Aggregate (cost=226.43..226.44 rows=1 width=32)
-> Index Scan using mediumtab_n_idx on mediumtab (cost=0.42..201.43 rows=5000 width=26)
Index Cond: ((n >= $1) AND (n <= $2))
Сделаю небольшое отступление и покажу, как можно посмотреть план выполняющегося запроса. Находим PID нашего backend’а:
testdb=# select pg_backend_pid();
pg_backend_pid
----------------
30356
Подключаемся к нему отладчиком:
# gdb --readnow -p 30356
Ставим breakpoint на функцию ExecProcNode:
(gdb) b ExecProcNode
Breakpoint 1 at 0x5555558a6b95: ExecProcNode. (66 locations)
...
(gdb) c
Continuing.
Запускаем запрос:
testdb=# execute s1(10,30);
После остановки на breakpoint’е печатаем backtrace:
Breakpoint 1, ExecProcNode (node=0x555556153538) at executor/./build/../src/include/executor/executor.h:270
(gdb) bt
#0 ExecProcNode (node=0x555556153538) at executor/./build/../src/include/executor/executor.h:270
#1 ExecutePlan (execute_once=<optimized out>, dest=0x55555607a658, direction=<optimized out>, numberTuples=0, sendTuples=<optimized out>, operation=CMD_SELECT, use_parallel_mode=<optimized out>,
planstate=0x555556153538, estate=0x555556153320) at executor/./build/../src/backend/executor/execMain.c:1670
#2 standard_ExecutorRun (queryDesc=0x5555560962a8, direction=<optimized out>, count=0, execute_once=<optimized out>) at executor/./build/../src/backend/executor/execMain.c:365
...
#14 0x0000555555a8bea9 in exec_simple_query (query_string=0x5555560274e0 "execute s1(10,30);") at tcop/./build/../src/backend/tcop/postgres.c:1276
...
Далее вызываем заранее подготовленный скрипт:
# cat printplan.gdb
set $s=NewExplainState()
call ExplainBeginOutput($s)
call ExplainPrintPlan($s,$a)
call ExplainEndOutput($s)
set print elements 10240
p ((ExplainState *)$s)->str->data
Для этого в отладчике выполняем команды. Переменной $а присваиваем значение параметра queryDesc функции standard_ExecutorRun из полученного backtrace:
(gdb) set $a=0x5555560962a8
(gdb) source printplan.gdb
$1 = 0x5555561671d0 "Aggregate (cost=8.95..8.96 rows=1 width=32)\n -> Index Scan using mediumtab_n_idx on mediumtab (cost=0.42..8.85 rows=21 width=26)\n Index Cond: ((n >= '10'::numeric) AND (n <= '30'::numeric))\n"
Вернемся к custom- и generic-планам. В нашем примере PostgreSQL для небольших диапазонов будет всегда выбирать custom-план.
Функция choose_custom_plan позволяет выбрать, какой план использовать:
…
avg_custom_cost = plansource->total_custom_cost / plansource->num_custom_plans;
/*
* Prefer generic plan if it's less expensive than the average custom
* plan. (Because we include a charge for cost of planning in the
* custom-plan costs, this means the generic plan only has to be less
* expensive than the execution cost plus replan cost of the custom
* plans.)
*
* Note that if generic_cost is -1 (indicating we've not yet determined
* the generic plan cost), we'll always prefer generic at this point.
*/
if (plansource->generic_cost < avg_custom_cost)
return false;
В ней в том числе есть сравнение средней стоимости custom-планов и generic-плана.
Для выполняемого запроса с такими параметрами custom-план всегда будет иметь меньшую стоимость:
execute s1(10,20);
1054 avg_custom_cost = plansource->total_custom_cost / plansource->num_custom_plans;
(gdb)
1066 if (plansource->generic_cost < avg_custom_cost)
(gdb) p avg_custom_cost
$1 = 13.484999999999999
(gdb) p plansource->generic_cost
$2 = 226.435
Попробуем подобрать параметры так, чтобы увеличить среднюю стоимость custom-плана:
testdb=# explain execute s1(10,12000);
QUERY PLAN
----------------------------------------------------------------------------------------------
Aggregate (cost=534.83..534.84 rows=1 width=32)
-> Index Scan using mediumtab_n_idx on mediumtab (cost=0.42..475.15 rows=11936 width=26)
Index Cond: ((n >= '10'::numeric) AND (n <= '12000'::numeric))
(3 rows)
Через несколько выполнений Postgres начнет использовать generic-план:
testdb=# explain execute s1(10,12000);
QUERY PLAN
---------------------------------------------------------------------------------------------
Aggregate (cost=226.43..226.44 rows=1 width=32)
-> Index Scan using mediumtab_n_idx on mediumtab (cost=0.42..201.43 rows=5000 width=26)
Index Cond: ((n >= $1) AND (n <= $2))
Postgres для нового prepared-запроса при первых пяти выполнениях использует custom-план, пересчитывает и запоминает его среднюю стоимость. На шестое выполнение строится generic-план. Какой план выбрать — решается при сравнении стоимостей.
Если Postgres выбирает custom-план, он также пересчитывает среднюю стоимость.
Если с помощью каких-то кривых значений параметров средняя стоимость custom-плана станет больше стоимости generic-плана, Postgres не будет больше выбирать custom-план. Поэтому его средняя стоимость меняться не будет.
В примере видно, что, несмотря на наличие generic-плана, Postgres выбирает (идентичный) custom-план — соответственно, вызывает планировщик при каждом выполнении, пока средняя стоимость custom-планов меньше стоимости generic-плана.
Рассмотрим еще один пример:
testdb=# prepare s2 as select v from mediumtab where n=$1::numeric;
PREPARE
После пятого выполнения Postgres выберет generic-план:
testdb=# explain execute s2(23);
QUERY PLAN
----------------------------------------------------------------------------------
Index Scan using mediumtab_n_idx on mediumtab (cost=0.42..8.44 rows=1 width=26)
Index Cond: (n = $1)
(2 rows)
testdb=# explain execute s2(145);
QUERY PLAN
----------------------------------------------------------------------------------
Index Scan using mediumtab_n_idx on mediumtab (cost=0.42..8.44 rows=1 width=26)
Index Cond: (n = $1)
(2 rows)
Если подключиться отладчиком к backend’y, поставить breakpoint на choose_custom_plan и продвинуться к строке, где сравниваются стоимости generic- и custom-планов, увидим, что на этот раз generic-план предпочтительнее custom-плана:
(gdb) p plansource->generic_cost
$1 = 8.4425000000000008
(gdb) p avg_custom_cost
$2 = 13.442500000000001
Поведение запроса к секционированной таблице
Рассмотрим запрос к секционированной таблице. Создадим таблицу с 1000 секций:create table bigtab(n numeric,v text) partition by range (n);
do
$$
declare
cnt integer;
v varchar(200);
begin
for i in 0..999 loop
v:= concat('create table bigtab',i,' partition of bigtab for values from (',i*1e4,') to (',(i+1)*1e4,')');
execute v;
end loop;
end;
$$
;
insert into bigtab select generate_series(1, 1e7-1),'Initial value '||generate_series(1, 1e7-1);
create unique index on bigtab(n);
analyze bigtab;
Выполним шесть раз запрос:
prepare s1 as select v from bigtab where n=$1::numeric;
Postgres построил generic-план, но используется custom-план:
testdb=# explain execute s1(123);
QUERY PLAN
-------------------------------------------------------------------------------------
Index Scan using bigtab0_n_idx on bigtab0 bigtab (cost=0.29..8.30 rows=1 width=18)
Index Cond: (n = '123'::numeric)
(2 rows)
Посмотрим на сравнение средней стоимости custom- и generic-планов из отладчика, как делали в предыдущем примере:
(gdb) b choose_custom_plan
Breakpoint 1 at 0x555555bdbdf0: choose_custom_plan. (2 locations)
...
(gdb) p plansource->generic_cost
$1 = 8307.4999999998381
(gdb) p avg_custom_cost
$2 = 15.802499999999998
Как бы выглядел generic-план? Установим plan_cache_mode=force_generic_plan и выполним запрос еще раз:
testdb=# set plan_cache_mode=force_generic_plan;
SET
testdb=# explain execute s1(123);
QUERY PLAN
---------------------------------------------------------------------------------------------
Append (cost=0.29..8307.50 rows=1000 width=22)
Subplans Removed: 999
-> Index Scan using bigtab0_n_idx on bigtab0 bigtab_1 (cost=0.29..8.30 rows=1 width=18)
Index Cond: (n = $1)
Видим, что сработал partition prunning (Subplans Removed: 999). Действительно, нам нужна только одна секция. Похоже, что стоимость generic-плана — это сумма стоимостей входящих в него субпланов. Полностью generic-план можно посмотреть в PostgreSQL 16:
testdb=# explain (generic_plan) select v from bigtab where n=$1::numeric;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Append (cost=0.29..8307.50 rows=1000 width=22)
-> Index Scan using bigtab0_n_idx on bigtab0 bigtab_1 (cost=0.29..8.30 rows=1 width=18)
Index Cond: (n = $1)
-> Index Scan using bigtab1_n_idx on bigtab1 bigtab_2 (cost=0.29..8.30 rows=1 width=20)
Index Cond: (n = $1)
...
-> Index Scan using bigtab998_n_idx on bigtab998 bigtab_999 (cost=0.29..8.30 rows=1 width=22)
Index Cond: (n = $1)
-> Index Scan using bigtab999_n_idx on bigtab999 bigtab_1000 (cost=0.29..8.30 rows=1 width=22)
Index Cond: (n = $1)
Действительно, стоимость каждого из 1 000 субпланов — 8,30, общая стоимость generic-плана — 8307,5, что в 1 000 раз больше. Получается, что Postgres никогда не выберет generic-план для запроса по уникальному значению.
Рассмотрим запрос по диапазону значений к той же таблице:
prepare s1(numeric,numeric) as select v from bigtab where n between $1::numeric and $2::numeric;
Postgres определил стоимость generic-плана равной 9535,00.
testdb=# explain (generic_plan) select v from bigtab where n between $1::numeric and $2::numeric;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Append (cost=0.29..9535.00 rows=50000 width=22)
-> Index Scan using bigtab0_n_idx on bigtab0 bigtab_1 (cost=0.29..9.29 rows=50 width=18)
Index Cond: ((n >= $1) AND (n <= $2))
...
Попробуем подобрать значения параметров таким образом, чтобы стоимость custom-плана получилась больше, чем стоимость generic-плана:
testdb=# explain execute s1(100,4e5);
QUERY PLAN
------------------------------------------------------------------------------------------------
Append (cost=0.00..10957.80 rows=399901 width=21)
-> Seq Scan on bigtab0 bigtab_1 (cost=0.00..213.98 rows=9900 width=18)
Filter: ((n >= '100'::numeric) AND (n <= '400000'::numeric))
-> Seq Scan on bigtab1 bigtab_2 (cost=0.00..224.00 rows=10000 width=20)
Filter: ((n >= '100'::numeric) AND (n <= '400000'::numeric))
-> Seq Scan on bigtab2 bigtab_3 (cost=0.00..224.00 rows=10000 width=20)
Filter: ((n >= '100'::numeric) AND (n <= '400000'::numeric))
...
-> Seq Scan on bigtab39 bigtab_40 (cost=0.00..224.00 rows=10000 width=21)
Filter: ((n >= '100'::numeric) AND (n <= '400000'::numeric))
-> Index Scan using bigtab40_n_idx on bigtab40 bigtab_41 (cost=0.29..8.30 rows=1 width=21)
Index Cond: ((n >= '100'::numeric) AND (n <= '400000'::numeric))
Через несколько выполнений с такими параметрами средняя стоимость custom-планов становится больше стоимости generic-плана — и Postgres переключается на generic-план:
testdb=# explain execute s1(100,4e5);
QUERY PLAN
-------------------------------------------------------------------------------------------------
Append (cost=0.29..9535.00 rows=50000 width=22)
Subplans Removed: 959
-> Index Scan using bigtab0_n_idx on bigtab0 bigtab_1 (cost=0.29..9.29 rows=50 width=18)
Index Cond: ((n >= $1) AND (n <= $2))
-> Index Scan using bigtab1_n_idx on bigtab1 bigtab_2 (cost=0.29..9.29 rows=50 width=20)
Index Cond: ((n >= $1) AND (n <= $2))
...
-> Index Scan using bigtab39_n_idx on bigtab39 bigtab_40 (cost=0.29..9.29 rows=50 width=21)
Index Cond: ((n >= $1) AND (n <= $2))
-> Index Scan using bigtab40_n_idx on bigtab40 bigtab_41 (cost=0.29..9.29 rows=50 width=21)
Index Cond: ((n >= $1) AND (n <= $2))
План изменился, generic-план использует Index Scan по всем секциям, тогда как custom-план использует Seq Scan для всех секций, кроме последней.
Посмотрим, сколько буферов прочитает Postgres при выполнении каждого из планов.
Воспользуемся для этого утилитой perf, создадим пробы для функций ReadBufferExtended, query_planner, standard_ExecutorStart:
# perf probe -v -x /usr/lib/postgresql/16/bin/postgres query_planner
# perf probe -v -x /usr/lib/postgresql/16/bin/postgres ReadBufferExtended spcNode='+0(%di)':"u32" dbNode='+4(%di)':"u32" relNode='+8(%di)':"u32" forkNum=%si:u32 blockNum=%dx:u32
# perf probe -x /usr/lib/postgresql/16/bin/postgres standard_ExecutorStart 'queryDesc->sourceText:string'
Выполним наш запрос сначала с custom-планом, запишем срабатывания проб:
# perf record -e probe_postgres:ReadBufferExtended,probe_postgres:query_planner,probe_postgres:standard_ExecutorStart -p <backend PID>
# perf script | grep -c ReadBufferExtended
3199
# perf script | grep -A10000 standard_ExecutorStart | grep -c ReadBufferExtended
2953
Получилось, что 2 953 буфера было прочитано при выполнении custom-плана и 246 буферов — при планировании. Повторим то же самое для generic-плана:
# perf script | grep -c ReadBufferExtended
4113
При выполнении generic-плана было прочитано на 914 буферов больше, то есть выполнение custom-плана оказалось экономичнее, несмотря на запуск планировщика. *
Как мы знаем из документации:
Вернемся к запросу по уникальному значению select v from bigtab where n=$1::numeric и посмотрим, что произойдет, если установить plan_cache_mode=force_generic_plan:
testdb=# explain analyze execute s1(123);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.29..8307.50 rows=1000 width=22) (actual time=0.073..0.179 rows=1 loops=1)
Subplans Removed: 999
-> Index Scan using bigtab0_n_idx on bigtab0 bigtab_1 (cost=0.29..8.30 rows=1 width=18) (actual time=0.038..0.075 rows=1 loops=1)
Index Cond: (n = $1)
Planning Time: 0.567 ms
Execution Time: 0.310 ms
(6 rows)
А это выполнение с plan_cache_mode=auto:
testdb=# explain analyze execute s1(123);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using bigtab0_n_idx on bigtab0 bigtab (cost=0.29..8.30 rows=1 width=18) (actual time=0.095..0.183 rows=1 loops=1)
Index Cond: (n = '123'::numeric)
Planning Time: 0.168 ms
Execution Time: 0.334 ms
(4 rows)
Хотя планировщик не запускается для generic-плана, расходы на планирование выше, чем при использовании custom-плана.
Сделаем простой нагрузочный тест и посчитаем, сколько запросов будет выполнено при дефолтном (мы уже знаем, что для нашего запроса будет использоваться custom план) значении plan_cache_mode и при plan_cache_mode=force_generic_plan:
$ cat ./runload.sh
runload(){
psql -d testdb <<EOF
do
\$\$
declare
r text;
k numeric;
begin
-- set plan_cache_mode=force_generic_plan;
loop
for k in $1*10000..($i+1)*10000
loop
select v into r from bigtab where n=k;
commit;
end loop;
end loop;
end;
\$\$
;
EOF
}
for ((i=0;i<6;i++))
do
runload 1 &
done
# perf stat -e probe_postgres:query_planner,probe_postgres:standard_ExecutorStart -a sleep 10
Performance counter stats for 'system wide':
737,339 probe_postgres:query_planner
737,339 probe_postgres:standard_ExecutorStart
10.004186030 seconds time elapsed
Теперь раскомментируем set plan_cache_mode=force_generic_plan, перезапустим скрипт:
# perf stat -e probe_postgres:query_planner,probe_postgres:standard_ExecutorStart -a sleep 10
Performance counter stats for 'system wide':
0 probe_postgres:query_planner
35,970 probe_postgres:standard_ExecutorStart
10.004506034 seconds time elapsed
При plan_cache_mode=force_generic_plan производительность уменьшилась в 20 раз. БД размещена на виртуальной машине на лаптопе, важны не полученные числа, но соотношение между ними.
Замечания об используемом в скрипте PL/pgSQL блоке. Запрос “select v into r from bigtab where n=k” преобразуется интерпретатором PL/pgSQL в prepared statement, поэтому параметр plan_cache_mode влияет на его выполнение.
Так как весь блок выполняется в одной виртуальной транзакции, пришлось добавить commit, чтобы каждое выполнение исследуемого запроса происходило в отдельной транзакции. Это важно для дальнейшего исследования и делает выполнение запроса похожим на то, как если бы он выполнялся каким-нибудь приложением (а не в блоке PL/pgSQL в цикле).
Попробуем проанализировать ожидания. Для этого при запущенной нагрузке выберем один из backend’ов, запустим трассировку ожиданий:
# PID=63155 && timeout 10 bpftrace ./trace.bt $(./wait_addr.sh $PID) $PID -p $PID -o waits.out
Для трассировки воспользуемся утилитой bpftrace. В PostgreSQL информация о текущем ожидании хранится по адресу, который находится в переменной my_wait_event_info.
Так как для каждого backend’а адрес свой, перед запуском трассировки необходимо его получить с помощью отладчика. Например, использовав такой скрипт:
# cat ./wait_addr.sh
wa=$(gdb -p $1 <<EOF 2>/dev/null | grep uint | cut -d" " -f6
p my_wait_event_info
quit
EOF
)
echo $wa
Трассировать ожидания будем следующим скриптом:
# cat trace.bt
uprobe:$3:standard_ExecutorStart /pid==$2/
{
printf("%s\n",str(uptr(*(arg0+16))));
@executor_start=nsecs;
}
uretprobe:$3:standard_ExecutorEnd /pid==$2/
{
if(@executor_start!=0){
printf("Execution time: %d ns\n",nsecs-@executor_start);
delete(@executor_start);
}
}
watchpoint:$1:8:rw
{
if (*uptr($1)!=0) {
@wait_event=*uptr($1);
@wait_start=nsecs;
}
else if(@wait_event!=0){
printf("#WAIT %d %ld ns %s\n",@wait_event,nsecs-@wait_start,ustack(7));
delete(@wait_event);
delete(@wait_start);
}
}
BEGIN{
@script_start=nsecs;
}
END{
printf("Script run time: %ld ms\n",(nsecs-@script_start)/1e6);
clear(@script_start);
clear(@executor_start);
clear(@wait_event);
clear(@wait_start);
printf("Exiting\n");
}
Пример запуска трассировки для backend’а с PID’ом 69984 в течение 10 секунд:
# PID=69984 && timeout 10 bpftrace ./trace.bt $(./wait_addr.sh $PID) $PID -p $PID -o waits.out
Обработаем полученный файл скриптом waits.pl. Скрипт парсит полученный файл, добавляет имена событий ожидания и сворачивает стеки функций для улучшения читаемости.
Скрипт написан на языке Perl, обращается к БД с помощью psql, для получения имен событий ожидания использует функцию waitevent_by_number расширения events.
Скрипт и расширение (равно как и другие упомянутые в статье скрипты) можно найти здесь: https://github.com/khafizovtim/postgres-scripts.
Вывод скрипта:
...
select v from bigtab where n=k
Execution time: 84999 ns
#WAIT 16777277 LWLock_LockManager 15000ns
LWLockAcquire+271<-LockAcquireExtended+402<-LockRelationOid+105<-AcquireExecutorLocks+120<-GetCachedPlan+450<-_SPI_execute_plan+568<-SPI_execute_plan_with_paramlist+150
#WAIT 16777277 LWLock_LockManager 37000ns
LWLockAcquire+271<-LockAcquireExtended+402<-LockRelationOid+105<-AcquireExecutorLocks+120<-GetCachedPlan+450<-_SPI_execute_plan+568<-SPI_execute_plan_with_paramlist+150
#WAIT 16777277 LWLock_LockManager 20000ns
LWLockAcquire+271<-LockAcquireExtended+402<-LockRelationOid+105<-AcquireExecutorLocks+120<-GetCachedPlan+450<-_SPI_execute_plan+568<-SPI_execute_plan_with_paramlist+150
#WAIT 16777277 LWLock_LockManager 36000ns
LWLockAcquire+271<-LockAcquireExtended+402<-LockRelationOid+105<-AcquireExecutorLocks+120<-GetCachedPlan+450<-_SPI_execute_plan+568<-SPI_execute_plan_with_paramlist+150
#WAIT 16777277 LWLock_LockManager 22000ns
LWLockAcquire+271<-LockAcquireExtended+402<-LockRelationOid+105<-AcquireExecutorLocks+120<-GetCachedPlan+450<-_SPI_execute_plan+568<-SPI_execute_plan_with_paramlist+150
#WAIT 16777277 LWLock_LockManager 11000ns
LWLockAcquire+271<-LockAcquireExtended+402<-LockRelationOid+105<-AcquireExecutorLocks+120<-GetCachedPlan+450<-_SPI_execute_plan+568<-SPI_execute_plan_with_paramlist+150
#WAIT 16777277 LWLock_LockManager 12000ns
LWLockAcquire+271<-LockAcquireExtended+402<-LockRelationOid+105<-AcquireExecutorLocks+120<-GetCachedPlan+450<-_SPI_execute_plan+568<-SPI_execute_plan_with_paramlist+150
select v from bigtab where n=k
Execution time: 72999 ns
#WAIT 16777277 LWLock_LockManager 41000ns
LWLockAcquire+271<-LockAcquireExtended+402<-LockRelationOid+105<-AcquireExecutorLocks+120<-GetCachedPlan+450<-_SPI_execute_plan+568<-SPI_execute_plan_with_paramlist+150
#WAIT 16777277 LWLock_LockManager 12000ns
LWLockAcquire+271<-LockAcquireExtended+402<-LockRelationOid+105<-AcquireExecutorLocks+120<-GetCachedPlan+450<-_SPI_execute_plan+568<-SPI_execute_plan_with_paramlist+150
Script run time: 9715 ms
Exiting
*************************************
Wait events summary:
Timeout_SpinDelay 1.48ms
LWLock_LockManager 2006.34ms
Total wait time 2007.83ms
Видно, что время ожиданий состоит из ожиданий lightweight lock’ов на LockManager. Ожидания возникают на стадии планирования.
Рассмотрим процесс получения блокировок подробнее. Выполним наш prepared statement, подключимся отладчиком к backend’у:
testdb=# prepare s1 as select v from bigtab where n=$1::numeric;
PREPARE
testdb=# set plan_cache_mode=force_generic_plan;
SET
...
testdb=# execute s1(123);
v
-------------------
Initial value 123
В отладчике поставим breakpoint на AcquireExecutorLocks:
(gdb) b AcquireExecutorLocks
Breakpoint 1 at 0x555555be6040: file utils/cache/./build/../src/backend/utils/cache/plancache.c, line 1750.
(gdb) c
Continuing.
Breakpoint 1, AcquireExecutorLocks (stmt_list=0x5555568a83d8, acquire=acquire@entry=true) at utils/cache/./build/../src/backend/utils/cache/plancache.c:1750
1750 utils/cache/./build/../src/backend/utils/cache/plancache.c: No such file or directory.
(gdb) bt
#0 AcquireExecutorLocks (stmt_list=0x5555568a83d8, acquire=acquire@entry=true) at utils/cache/./build/../src/backend/utils/cache/plancache.c:1750
#1 0x0000555555be7452 in CheckCachedPlan (plansource=0x5555560913e0) at utils/cache/./build/../src/backend/utils/cache/plancache.c:833
#2 GetCachedPlan (plansource=0x5555560913e0, boundParams=0x5555560932f0, owner=0x0, queryEnv=0x0) at utils/cache/./build/../src/backend/utils/cache/plancache.c:1167
#3 0x000055555584eb1b in ExecuteQuery (pstate=<optimized out>, stmt=<optimized out>, intoClause=0x0, params=<optimized out>, dest=0x55555607a658, qc=0x7fffffffd8a0)
at commands/./build/../src/backend/commands/prepare.c:196
Здесь видно, что блокировки берутся из AcquireExecutorLocks, которую вызывает CheckCachedPlan (комментарий из исходников: see if the CachedPlanSource’s generic plan is valid).
Посмотрим, на что и в каком режиме Postgres берет блокировки при выполнении запроса, добавим пробы:
# perf probe -v -x /usr/lib/postgresql/16/bin/postgres LWLockAcquire 'lock->tranche:u32' 'mode:u32'
# perf probe -v -x /usr/lib/postgresql/16/bin/postgres LockRelationOid 'relid:u32' 'lockmode:u32'
Запустим запись срабатывания проб:
# perf record -e probe_postgres:LWLockAcquire,probe_postgres:LockRelationOid -p 106920
Выполним запрос и посмотрим, что получилось. Видно, что число вызовов LockRelationOid чуть больше числа секций таблицы bigtab:
# perf script |egrep -c "LockRelationOid"
1004
# perf script
...
postgres 106920 [006] 963309.293774: probe_postgres:LockRelationOid: (555555a73820) relid_u32=112685 lockmode_u32=1
postgres 106920 [006] 963309.293776: probe_postgres:LWLockAcquire: (555555a71e50) tranche=61 mode_u32=0
postgres 106920 [006] 963309.293779: probe_postgres:LockRelationOid: (555555a73820) relid_u32=112690 lockmode_u32=1
postgres 106920 [006] 963309.293781: probe_postgres:LWLockAcquire: (555555a71e50) tranche=61 mode_u32=0
postgres 106920 [006] 963309.293784: probe_postgres:LockRelationOid: (555555a73820) relid_u32=112695 lockmode_u32=1
postgres 106920 [006] 963309.293787: probe_postgres:LWLockAcquire: (555555a71e50) tranche=61 mode_u32=0
postgres 106920 [006] 963309.293790: probe_postgres:LockRelationOid: (555555a73820) relid_u32=112700 lockmode_u32=1
Действительно, Postgres берет блокировки на все секции таблицы в режиме AccessShareLock. Блокировки удерживаются на время выполнения запроса:
lockdefs.h: #define AccessShareLock 1 /* SELECT */
Режим AccessShareLock не предполагает конкуренции, проблемы возникают на LWLock’ах. В приведенном выводе perf script видно, что перед вызовом LockRelationOid всегда идет вызов LWLockAcquire.
Посмотрим, что такое tranche=61. Трассировка событий ожидания показала, что это LockManager:
lwlock.h:
typedef enum BuiltinTrancheIds
{
LWTRANCHE_XACT_BUFFER = NUM_INDIVIDUAL_LWLOCKS,
LWTRANCHE_COMMITTS_BUFFER,
LWTRANCHE_SUBTRANS_BUFFER,
LWTRANCHE_MULTIXACTOFFSET_BUFFER,
LWTRANCHE_MULTIXACTMEMBER_BUFFER,
LWTRANCHE_NOTIFY_BUFFER,
LWTRANCHE_SERIAL_BUFFER,
LWTRANCHE_WAL_INSERT,
LWTRANCHE_BUFFER_CONTENT,
LWTRANCHE_REPLICATION_ORIGIN_STATE,
LWTRANCHE_REPLICATION_SLOT_IO,
LWTRANCHE_LOCK_FASTPATH,
LWTRANCHE_BUFFER_MAPPING,
LWTRANCHE_LOCK_MANAGER,
...
lwlocknames.h:#define NUM_INDIVIDUAL_LWLOCKS 48
Так и есть, enumeration начинается со значения 48 - NUM_INDIVIDUAL_LWLOCKS, поэтому LWTRANCHE_LOCK_MANAGER == 61. Режимы взятия LWLock’а перечислены в enum’е:
lwlock.h:
typedef enum LWLockMode
{
LW_EXCLUSIVE,
LW_SHARED,
LW_WAIT_UNTIL_FREE, /* A special mode used in PGPROC->lwWaitMode,
* when waiting for lock to become free. Not
* to be used as LWLockAcquire argument */
} LWLockMode;
В нашем случае Postgres перед взятием блокировки на каждую секцию таблицы берет LWLock на LWTRANCHE_LOCK_MANAGER в LW_EXCLUSIVE-режиме, что и вызывает ожидания.
Вместо заключения
Generic-планы улучшают производительность и экономят ресурсы CPU. Несмотря на это, Postgres в простых ситуациях может упорно выбирать custom-план, а в случае секционированных таблиц с большим числом секций использование generic-планов может негативно сказаться на производительности.В случае очевидных проблем можно корректировать поведение PostgreSQL с помощью настройки plan_cache_mode для конкретных запросов на уровне сессии.
* В PostgreSQL 17 для sequential scan’ов появился Streaming I/O (многоблочное чтение). Поэтому число прочитанных буферов можно оценить с помощью проб на функции StartReadBuffer для одноблочных чтений и StartReadBuffers для многоблочных:
# perf probe -v -x $PGBINARY StartReadBuffers 'operation->rel->rd_locator.relNumber:u32' 'blockNum:u32' 'nblocks[0]:u32'
#perf probe -v -x $PGBINARY StartReadBuffer 'operation->rel->rd_locator.relNumber:u32' 'bloc