При создании временных таблиц в PostgreSQL изменяются до 13 таблиц системного каталога, при этом особенно сильно разрастаются pg_attribute, pg_class, pg_depend и pg_type. Массовое создание и усечение временных таблиц активно применяется в том числе в 1C:ERP. В статье рассматриваются особенности работы с временными таблицами и описано решение проблемы раздувания таблиц системного каталога, реализованное в СУБД Tantor Postgres. Начиная с версии 17.5, при создании и удалении временных таблиц, а также при работе с временными объектами таблицы системного каталога не изменяются. Новый функционал управляется параметром конфигурации enable_temp_memory_catalog, который можно включать на нескольких уровнях, в том числе на уровне сессии, без необходимости перезапуска экземпляра.
Раздувание таблиц системного каталога в PostgreSQL
Создание, удаление и изменение отношений в PostgreSQL приводит к внесению изменений в таблицы системного каталога. При создании таблиц, включая временные, внешние и нежурналируемые, могут автоматически создаваться ограничения целостности и индексы. В общей сложности изменения затрагивают до 13 таблиц системного каталога. Число актуальных версий строк в этих таблицах можно получить с помощью запроса:
Запрос
select 'pg_class' table, pg_total_relation_size('pg_class') size, count(*)
from pg_class union all
select 'pg_attribute', pg_total_relation_size('pg_attribute'), count(*)
from pg_attribute union all
select 'pg_type', pg_total_relation_size('pg_type'), count(*)
from pg_type union all
select 'pg_depend', pg_total_relation_size('pg_depend'), count(*)
from pg_depend union all
select 'pg_inherits', pg_total_relation_size('pg_inherits'), count(*)
from pg_inherits union all
select 'pg_statistic', pg_total_relation_size('pg_statistic'), count(*)
from pg_statistic union all
select 'pg_attrdef', pg_total_relation_size('pg_attrdef'), count(*)
from pg_attrdef union all
select 'pg_collation', pg_total_relation_size('pg_collation'), count(*)
from pg_collation union all
select 'pg_constraint', pg_total_relation_size('pg_constraint'), count(*)
from pg_constraint union all
select 'pg_description', pg_total_relation_size('pg_description'), count(*)
from pg_description union all
select 'pg_index', pg_total_relation_size('pg_index'), count(*) from
pg_index union all
select 'pg_namespace', pg_total_relation_size('pg_namespace'), count(*)
from pg_namespace union all
select 'pg_sequence', pg_total_relation_size('pg_sequence'), count(*)
from pg_sequence;
----------------------
table | size | count
----------------+------------+-------
pg_sequence | 8192 | 0
pg_attrdef | 24576 | 0
pg_inherits | 16384 | 0
pg_namespace | 57344 | 102
pg_constraint | 114688 | 114
pg_index | 65536 | 168
pg_collation | 229376 | 822
pg_description | 565248 | 5233
pg_statistic | 2187264 | 416
pg_class | 542957568 | 428
pg_attribute | 3064348672 | 3195
pg_depend | 1002225664 | 1808
pg_type | 1000480768 | 637
(13 rows)Проведем тест по созданию и удалению временных таблиц и посмотрим как это повлияет на число строк в таблицах системного каталога. В результате мы увидим, какие из таблиц системного каталога раздуваются больше других. Для наблюдения за состоянием буферного кэша установим расширение:
postgres=# create extension pg_buffercache;
CREATE EXTENSIONПри небольшом количестве строк размер этих таблиц может быть значительным. Создадим файл, который сгенерирует скрипт для создания и удаления временных таблиц.
postgres@tantor:~$ cat gen2.pydef main():
for count in [100]:
with open(f'workl1c_{count}.sql', 'w') as f:
for i in range(1, count):
f.write(f"""create temporary table ttt{i} (c int);
drop table ttt{i};
""")
if __name__ == "__main__":
main()Выполним скрипт для создания файла теста с названием workl1c_100.sql:
postgres@tantor:~$ python3 gen2.pyЧтобы таблицы системного каталога не очищались, можно удерживать горизонт базы. Для этого в одной из сессий можно открыть транзакцию и выполнить в ней любое изменение:
postgres=# begin;
create temporary table xt (n int);
BEGIN;
CREATE TABLEАвтовакуум можно не отключать, при удержании горизонта автовакуум не сможет очистить старые версии строк. Для наблюдения за автовакуумом установим параметр:
postgres=# alter system set log_autovacuum_min_duration = '1s';
select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
ALTER SYSTEMЕсли вакуумирование какой-либо таблицы превысит одну секунду, будет создана запись в логе.
Пример параметров конфигурации:
postgres@tantor:~$ cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
max_connections = '1000'
shared_preload_libraries = 'pg_stat_statements'
logging_collector = 'on'
log_filename = 'postgresql-%F.log'
log_autovacuum_min_duration = '1s'Для запуска файла создания временных таблиц используем утилиту pgbench:
postgres@tantor:~$ pgbench -n -T 10000 -c 16 -P 60 -f workl1c_100.sql\
progress: 60.0 s, 19.8 tps, lat 798.487 ms stddev 97.788, 0 failed
progress: 120.0 s, 16.0 tps, lat 1000.391 ms stddev 100.195, 0 failed
...
progress: 2340.0 s, 2.2 tps, lat 7131.160 ms stddev 1289.401, 0 failed
progress: 2400.0 s, 2.2 tps, lat 7343.940 ms stddev 1204.763, 0 failedПостепенное уменьшение tps связано с заполнением буферного кэша. По мере увеличения размеров таблиц системного каталога процесс, создающий временные таблицы, вынужден вытеснять из кэша блоки, то есть буферный кэш оказывается полностью заполненным:
postgres=# select * from pg_buffercache_usage_counts();
usage_count | buffers | dirty | pinned
-------------+---------+-------+--------
0 | 776 | 71 | 0
1 | 833 | 85 | 1
2 | 1134 | 102 | 0
3 | 801 | 26 | 0
4 | 2758 | 28 | 0
5 | 10082 | 139 | 1
(6 rows)Размеры таблиц системного каталога увеличились:
table | size | count
----------------+-----------+-------
pg_class | 161521664 | 431
pg_type | 293175296 | 637
pg_depend | 298180608 | 1836
pg_attribute | 899604480 | 3251
(13 rows)Если остановить тест, немного подождать и запустить его заново, значения tps вернутся к первоначальным показателям — это происходит потому, что после остановки теста bgwriter успевает освободить буферы (можно поразмышлять, какие именно, почему и всегда ли это так):
postgres=# select * from pg_buffercache_usage_counts();
usage_count | buffers | dirty | pinned
-------------+---------+-------+--------
0 | 15946 | 0 | 0
1 | 242 | 81 | 0
2 | 9 | 0 | 0
3 | 9 | 0 | 0
4 | 174 | 25 | 0
5 | 4 | 2 | 0
(6 rows)При этом размеры таблиц системного каталога не уменьшаются, а время выполнения запросов, которые выдают размер, остается прежним. После некоторого времени показатели постепенно уменьшаются:
progress: 60.0 s, 18.9 tps, lat 841.028 ms stddev 95.998, 0 failed
progress: 120.0 s, 15.4 tps, lat 1038.162 ms stddev 107.813, 0 failed
progress: 180.0 s, 13.1 tps, lat 1218.092 ms stddev 119.285, 0 failed
progress: 240.0 s, 11.7 tps, lat 1370.945 ms stddev 97.170, 0 failed
...
progress: 7380.0 s, 1.0 tps, lat 15925.498 ms stddev 2271.770, 0 failed
progress: 7440.0 s, 1.0 tps, lat 16131.646 ms stddev 2646.911, 0 failed
progress: 7500.0 s, 0.9 tps, lat 16450.132 ms stddev 3068.155, 0 failedЧерез некоторое время в журнале появятся сообщения автовакуума и автоанализа. Ниже приведен пример таких сообщений спустя 6000 секунд:
postgres@tantor:~$ cat $PGDATA/log/postgresql*
10:06:58.014 MSK [24548] LOG: automatic analyze of table "postgres.pg_catalog.pg_class"
elapsed: 2.59 s
10:07:27.693 MSK [24551] LOG: automatic vacuum of table "postgres.pg_catalog.pg_attribute": index scans: 0
tuples: 0 removed, 19759720 remain, 19759694 are dead but not yet removable, oldest xmin: 13261300
elapsed: 35.63 s
10:07:29.844 MSK [24551] LOG: automatic analyze of table "postgres.pg_catalog.pg_attribute"
elapsed: 2.15 s
10:07:40.733 MSK [24551] LOG: automatic vacuum of table "postgres.pg_catalog.pg_depend": index scans: 0
removable cutoff: 13261300, which was 5661621 XIDs old when operation ended
elapsed: 10.87 s
10:07:43.996 MSK [24551] LOG: automatic analyze of table "postgres.pg_catalog.pg_depend"
elapsed: 3.26 s
10:07:49.832 MSK [24554] LOG: automatic vacuum of table "postgres.pg_catalog.pg_type": index scans: 0
tuples: 0 removed, 5659547 remain, 5658922 are dead but not yet removable, oldest xmin: 13261300
elapsed: 17.41 s
10:07:52.590 MSK [24551] LOG: automatic vacuum of table "postgres.pg_catalog.pg_class": index scans: 0
elapsed: 8.57 s
10:07:52.891 MSK [24554] LOG: automatic analyze of table "postgres.pg_catalog.pg_type"
elapsed: 3.05 s
10:07:55.511 MSK [24551] LOG: automatic analyze of table "postgres.pg_catalog.pg_class"
elapsed: 2.92 sМы приводим здесь характерный фрагмент диагностического лога. По значению elapsed видно, что автовакуум больше всего времени тратит на обработку таблиц системного каталога — pg_attribute, pg_type, pg_depend. Если в каждом цикле автовакуум тратит примерно одинаковое время на одни и те же таблицы, это означает, что он не может очистить эти таблицы и повторяет попытки впустую, расходуя ресурсы.
Цикл автовакуума и автоанализа системных таблиц в примере длился более 70 секунд, при этом результата не было — автовакуум не мог очистить таблицы из-за удержания горизонта. Циклы автовакуума и автоанализа выполняются последовательно. Задержка между циклами по умолчанию невелика и определяется параметром autovacuum_naptime = 1min.
Набор следующих параметров:
autovacuum_vacuum_cost_delay | 2ms
autovacuum_vacuum_cost_limit | -1
vacuum_cost_delay | 0
vacuum_cost_limit | 200заставляет автовакуум работать с задержками. В нашем примере горизонт удерживается открытой транзакцией, и время работы автовакуума и автоанализа не влияет на сдвиг горизонта базы. Однако при реальной работе автовакуум и автоанализ могут сами удерживать горизонт, что нежелательно.
Настройка автовакуума. Устранение задержки
Если отключить задержку, например, выполнив команды:
postgres=# alter system set autovacuum_vacuum_cost_delay=0;
select pg_reload_conf();то время вакуумирования таблиц уменьшится:
10:15:00.457 MSK [24588] LOG: automatic vacuum of table "postgres.pg_catalog.pg_depend": index scans: 0
elapsed: 6.69 s
10:15:01.999 MSK [24588] LOG: automatic analyze of table "postgres.pg_catalog.pg_depend"
elapsed: 1.54 s
10:15:04.983 MSK [24588] LOG: automatic vacuum of table "postgres.pg_catalog.pg_class": index scans: 0
elapsed: 2.96 s
10:15:31.568 MSK [24595] LOG: automatic vacuum of table "postgres.pg_catalog.pg_type": index scans: 0
elapsed: 6.74 s
10:15:32.864 MSK [24595] LOG: automatic analyze of table "postgres.pg_catalog.pg_type"
elapsed: 1.28 s
10:15:50.476 MSK [24595] LOG: automatic vacuum of table "postgres.pg_catalog.pg_attribute": index scans: 0
elapsed: 17.59 s
10:15:51.878 MSK [24595] LOG: automatic analyze of table "postgres.pg_catalog.pg_attribute"
elapsed: 1.40 sВ приведённом примере время вакуумирования таблицы pg_attribute уменьшилось с 35 до 17 секунд. Однако в обоих случаях, пока удерживается горизонт базы, автовакуум работает впустую — он не может очищать версии строк. Перед запуском автовакуум не способен предсказать, удастся ли ему очистить хоть какую-то часть версий строк. Автовакуум мог бы проверять, сдвинулся ли горизонт базы по сравнению с предыдущим циклом, но на практике циклы автовакуума довольно продолжительны, и горизонт сдвигается лишь незначительно. При этом количество версий строк, вышедших за горизонт, практически не зависит от величины сдвига, поскольку в одной транзакции может быть изменено большое число строк.
Влияние раздувания таблиц системного каталога на скорость создания таблиц
Когда влияние невелико
Раздувание таблиц и индексов системного каталога обычно не влияет на скорость создания и удаления relations:
postgres=# \timing on \\
Timing is on.
postgres=# create temp table aaa (x int);
CREATE TABLE
Time: 15.370 ms
postgres=# create table aaa1 (x int);
CREATE TABLE
Time: 1.750 msТаблицы создаются быстро.
Когда влияние значительно
В ходе теста время выполнения запросов к таблицам системного каталога постепенно увеличивается:
explain (analyze, buffers) SELECT n.nspname, c.relname
ROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE n.nspname <> 'pg_catalog';
-> Bitmap Heap Scan on pg_class c (cost=165.17..491.18 rows=86 width=72) (actual time=179.958..179.967 rows=1 loops=135)
Buffers: shared hit=2707 read=547283 written=5695
Planning Time: 13.527 ms
Execution Time: 24299.326 ms
(17 rows)
Time: 24319.014 ms (00:24.319)После остановки теста:
-> Bitmap Heap Scan on pg_class c (cost=166.25..747.37 rows=161 width=72) (actual time=24.597..24.599 rows=1 loops=135)
Buffers: shared hit=556152 read=106322 dirtied=53 written=187
Planning Time: 3.363 ms
Execution Time: 3324.657 ms
Time: 3333.285 ms (00:03.333)Влияние страничного кэша Linux на скорость доступа к раздутым таблицам системного каталога
Через некоторое время выполнение запросов замедлится:
-> Bitmap Heap Scan on pg_class c (cost=165.78..681.47 rows=141 width=72) (actual time=49.566..49.568 rows=1 loops=135)
Buffers: shared hit=556164 read=106310 written=10
Planning Time: 7.597 ms
Execution Time: 6695.839 ms
Time: 6715.433 ms (00:06.715)Увеличение времени связано с вытеснением страниц файлов индексов и таблиц системного каталога из страничного кэша Linux, содержимое которого невозможно отследить средствами PostgreSQL.
Последствия раздувания таблиц системного каталога
Нежелательность раздувания таблиц системного каталога заключается в том, что физические размеры индексов не уменьшаются даже после снятия удержания горизонта и вакуумирования. Вероятность уменьшения размеров файлов таблиц также низка, поскольку при создании временных таблиц могут создаваться объекты постоянного хранения, строки которых располагаются ближе к концу файлов основного слоя системных таблиц каталога. Однако это сказывается только на занимаемом пространстве и никак не влияет на производительность, поскольку доступ к таблицам системного каталога обычно осуществляется посредством индексом.
До снятия удержания горизонта время выполнения запросов чрезвычайно велико:
postgres=# \timing on \\
Timing is on.
postgres=# explain (analyze, buffers) select 'pg_attribute', pg_total_relation_size('pg_attribute'), count(*) from pg_attribute;
Buffers: shared hit=6 read=598191
Planning Time: 0.084 ms
Execution Time: 39773.733 ms
Time: 39774.720 ms (00:39.775)Обращения к счётчику времени TSC приводят к накладным расходам и увеличивают время выполнения explain(analyse) примерно в три раза. Без таких обращений реальное время выполнения запроса составляет:
postgres=# explain (analyze, buffers, timing off) select 'pg_attribute', pg_total_relation_size('pg_attribute'), count(*) from pg_attribute;
Buffers: shared hit=1 read=598196
Planning Time: 0.065 ms
Execution Time: 12976.249 ms
Time: 12976.864 ms (00:12.977)Размеры таблиц после 10 000 секунд работы теста:
table | size | count
----------------+------------+-------
pg_class | 1041825792 | 425
pg_attribute | 5905104896 | 3171
pg_depend | 1936924672 | 1796
pg_type | 1891008512 | 627
(13 rows)
Time: 30579.073 ms (00:30.579)Пропорция размеров таблиц pg_attribute и pg_class зависит от числа столбцов в создаваемых таблицах. В тесте создавались временные таблицы с одним столбцом фиксированного размера.
До снятия удержания горизонта автовакуум и автоанализ работают без перерывов, пытаясь обработать таблицы системного каталога. После снятия удержания горизонта им удаётся успешно выполнить эту обработку. За работой автовакуума можно наблюдать через представление:
select * from pg_stat_progress_vacuum \gx
-[ RECORD 1 ]--------+------------------
pid | 27131
datid | 5
datname | postgres
relid | 1249
phase | vacuuming indexes
heap_blks_total | 516654
heap_blks_scanned | 516654
heap_blks_vacuumed | 0
index_vacuum_count | 0
max_dead_tuple_bytes | 67108864
dead_tuple_bytes | 12780544
num_dead_item_ids | 29945377
indexes_total | 2
indexes_processed | 0
-[ RECORD 2 ]--------+------------------
pid | 27114
datid | 5
datname | postgres
relid | 1259
phase | vacuuming heap
heap_blks_total | 99573
heap_blks_scanned | 99573
heap_blks_vacuumed | 82095
index_vacuum_count | 1
max_dead_tuple_bytes | 67108864
dead_tuple_bytes | 3081216
num_dead_item_ids | 4280219
indexes_total | 0
indexes_processed | 0При повторных обращениях значение в столбце будет меняться:
heap_blks_vacuumed | 181104Сканирование индексов при вакуумировании:
select pid, relid::regclass, phase, heap_blks_vacuumed from pg_stat_progress_vacuum;
pid | relid | phase | heap_blks_vacuumed
-------+--------------+---------------------+--------------------
27185 | pg_attribute | cleaning up indexes | 510699После снятия удержания горизонта
В журнале отражается, что индексы просканированы за один проход:
12:16:35.631 MSK [27131] LOG: automatic vacuum of table "postgres.pg_catalog.pg_statistic": index scans: 1
system usage: CPU: user: 0.10 s, system: 0.02 s, elapsed: 1.23 s
12:20:38.721 MSK [27114] LOG: automatic vacuum of table "postgres.pg_catalog.pg_depend": index scans: 1
tuples: 12840612 removed, 1796 remain, 0 are dead but not yet removable, oldest xmin: 21822422
index "pg_depend_depender_index": pages: 88958 in total, 88941 newly deleted, 88941 currently deleted, 0 reusable
index "pg_depend_reference_index": pages: 52304 in total, 52284 newly deleted, 52284 currently deleted, 26614 reusable
buffer usage: 1249768 hits, 331656 misses, 331637 dirtied
WAL usage: 707844 records, 330198 full page images, 784190586 bytes
system usage: CPU: user: 17.68 s, system: 8.18 s, elapsed: 315.05 s
12:22:30.224 MSK [27126] LOG: automatic vacuum of table "postgres.pg_catalog.pg_type": index scans: 1
tuples: 8560408 removed, 629 remain, 4 are dead but not yet removable, oldest xmin: 21822422
index "pg_type_oid_index": pages: 23475 in total, 23467 newly deleted, 23467 currently deleted, 5544 reusable
index "pg_type_typname_nsp_index": pages: 8188 in total, 8173 newly deleted, 8173 currently deleted, 0 reusable
buffer usage: 457668 hits, 430004 misses, 429933 dirtied
WAL usage: 692059 records, 429925 full page images, 319674174 bytes
system usage: CPU: user: 24.85 s, system: 12.94 s, elapsed: 414.71 s
12:22:50.901 MSK [27114] LOG: automatic vacuum of table "postgres.pg_catalog.pg_class": index scans: 1
pages: 0 removed, 99573 remain, 99573 scanned (100.00% of total)
tuples: 843 removed, 422 remain, 0 are dead but not yet removable, oldest xmin: 21822427
index scan needed: 99566 pages from table (99.99% of total) had 4280219 dead item identifiers removed
index "pg_class_oid_index": pages: 11739 in total, 11731 newly deleted, 11731 currently deleted, 0 reusable
index "pg_class_relname_nsp_index": pages: 4095 in total, 4086 newly deleted, 4086 currently deleted, 0 reusable
index "pg_class_tblspc_relfilenode_index": pages: 11738 in total, 11731 newly deleted, 11731 currently deleted, 0 reusable
buffer usage: 319841 hits, 226761 misses, 127214 dirtied
WAL usage: 281670 records, 127216 full page images, 230305466 bytes
system usage: CPU: user: 9.48 s, system: 3.44 s, elapsed: 132.17 s
12:27:09.884 MSK [27131] LOG: automatic vacuum of table "postgres.pg_catalog.pg_attribute": index scans: 1
pages: 5955 removed, 510699 remain, 516600 scanned (99.99% of total)
tuples missed: 16066 dead from 277 pages not removed due to cleanup lock contention
index scan needed: 516323 pages from table (99.94% of total) had 29945377 dead item identifiers removed
index "pg_attribute_relid_attnam_index": pages: 122212 in total, 121547 newly deleted, 121547 currently deleted, 0 reusable
index "pg_attribute_relid_attnum_index": pages: 81827 in total, 81328 newly deleted, 81328 currently deleted, 0 reusable
buffer usage: 2461858 hits, 1040521 misses, 935880 dirtied
WAL usage: 1856568 records, 935889 full page images, 1763589809 bytes
system usage: CPU: user: 51.65 s, system: 22.52 s, elapsed: 634.24 s
12:27:44.400 MSK [27185] LOG: automatic vacuum of table "postgres.pg_catalog.pg_attribute": index scans: 0
tuples: 6240 removed, 26 remain, 0 are dead but not yet removable, oldest xmin: 21822428
index scan bypassed: 280 pages from table (0.05% of total) have 16100 dead item identifiers
index "pg_attribute_relid_attnam_index": pages: 122212 in total, 0 newly deleted, 121547 currently deleted, 121547 reusable
index "pg_attribute_relid_attnum_index": pages: 81827 in total, 0 newly deleted, 81328 currently deleted, 81328 reusable
buffer usage: 219551 hits, 189542 misses, 166 dirtied
WAL usage: 170 records, 163 full page images, 498360 bytes
system usage: CPU: user: 1.75 s, system: 1.11 s, elapsed: 13.07 s
12:27:46.339 MSK [27185] LOG: automatic analyze of table "postgres.pg_catalog.pg_attribute"
avg read rate: 121.051 MB/s, avg write rate: 0.040 MB/s
buffer usage: 949 hits, 30044 misses, 10 dirtied
system usage: CPU: user: 0.28 s, system: 0.16 s, elapsed: 1.93 sПосле вакуумирования время выполнения запросов к таблицам системного каталога значительно сокращается:
postgres=# explain (analyze, buffers, timing off) select 'pg_attribute', pg_total_relation_size('pg_attribute'), count(*) from pg_attribute;
QUERY PLAN
----------------------------------------------------------------
Aggregate (cost=327460.66..327460.67 rows=1 width=48) (actual rows=1 loops=1)
Buffers: shared hit=470
-> Index Only Scan using pg_attribute_relid_attnum_index on pg_attribute (cost=0.40..327455.72 rows=1975 width=0) (actual rows=3153 loops=1)
Heap Fetches: 0
Buffers: shared hit=470
Planning Time: 0.068 ms
Execution Time: 1.277 ms
(7 rows)
Time: 1.655 msСканирование индексов таблиц системного каталога будет выполняться не в каждом цикле автовакуума. Если периодически возникает раздувание файлов индексов, смысла в их перестроении нет. Блоки в файлах индексов, не включённые в логическую структуру индекса («дерево»), могут добавляться в эту структуру.
Параметры конфигурации enable_temp_memory_catalog и enable_delayed_temp_file в СУБД Tantor Postgres
Параметр конфигурации enable_temp_memory_catalog, реализованный в СУБД Tantor Postgres, исключает изменение любых таблиц системного каталога при работе с временными таблицами. Параметр присутствует начиная с 17 версии в редакциях Special Edition, Special Edition 1C, а после прохождения сертификации ФСТЭК России параметр присутствует в редакции СУБД Tantor Certified 16.8 в исполнении 1. Параметр можно включить на разных уровнях, в том числе на уровне сессии. Включим на уровне кластера, чтобы упростить тестирование:
postgres=# alter system set enable_temp_memory_catalog = on;
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)Будем удерживать горизонт базы данных:
postgres=# begin;
create table xt (n int);
BEGIN
CREATE TABLEЗапустим тест:
postgres@tantor:~$ pgbench -n -T 10000 -c 16 -P 60 -f workl1c_100.sql
pgbench (17.2)
progress: 60.0 s, 41.9 tps, lat 379.749 ms stddev 35.921, 0 failed
progress: 120.0 s, 41.9 tps, lat 381.668 ms stddev 41.316, 0 failed
progress: 180.0 s, 42.1 tps, lat 380.539 ms stddev 37.818, 0 failed
progress: 240.0 s, 41.8 tps, lat 383.191 ms stddev 39.267, 0 failedПоказатели ТPS при включенном параметре со временем не меняются даже при удержании горизонта базы данных. Скорость создания и удаления временных таблиц примерно в два раза выше (без параметра TPS=23) по сравнению с обычным способом создания временных таблиц, затрагивающим таблицы системного каталога. Размеры таблиц системного каталога не изменяются, и изменения в них не вносятся.
При использовании этого параметра метаданные хранятся в локальной памяти серверного процесса, а не в блоках таблиц системного каталога. При этом не требуется настройка параметров распределения памяти, таких как work_mem и maintenance_work_mem. Скорость доступа к метаданным уже созданной временной таблицы выше, поскольку данные находятся в локальной памяти серверного процесса, и при этом не используется блокировка таблиц и индексов системного каталога, что снижает конкуренцию за ресурсы. Параметр изменяет способ хранения метаданных, однако способ хранения данных во временных объектах и способ доступа к самим данным остаются без изменений, за исключением случаев, когда транзакция затрагивает только временные таблицы. Если же в транзакции не участвуют объекты постоянного хранения, фиксация транзакции происходит быстрее.
Параметр enable_delayed_temp_file
Способ работы с файлами временных таблиц изменяется параметром конфигурации enable_delayed_temp_file, который вместе с параметром enable_temp_memory_catalog был добавлен в СУБД Tantor Postgres версии 17.5 :
postgres@tantor:~$ psql -c "alter system set enable_delayed_temp_file = on;"
ALTER SYSTEM
postgres@tantor:~$ psql -c "select pg_reload_conf();"
pg_reload_conf
----------------
t
(1 row)
postgres@tantor:~$ pgbench -n -T 10000 -c 16 -P 5 -f workl1c_100.sql
pgbench (17.2)
progress: 5.0 s, 47.2 tps, lat 324.453 ms stddev 41.019, 0 failed
progress: 10.0 s, 49.0 tps, lat 324.568 ms stddev 34.198, 0 failed
progress: 15.0 s, 49.2 tps, lat 324.529 ms stddev 41.999, 0 failed
progress: 20.0 s, 50.0 tps, lat 321.942 ms stddev 35.568, 0 failed
progress: 25.0 s, 48.4 tps, lat 328.826 ms stddev 42.060, 0 failedПри включении параметра enable_delayed_temp_file TPS увеличивается с ~42 до ~49.
Параметр конфигурации enable_temp_memory_catalog
В сборке PostgreSQL 16 от 1С ИТС реализован параметр enable_temp_memory_catalog, решающий аналогичную задачу — кэширование метаданных временных таблиц. Название параметра для новой опции в СУБД Tantor Postgres 17 было выбрано таким же, как и в сборке 1С ИТС, однако несмотря на одинаковое название, реализации различаются. Параметр enable_delayed_temp_file в сборке от 1С отсутствует.
Размер патча от «Тантор Лабс», реализующего функционал enable_temp_memory_catalog составляет 217Кб (7377 строк). Изменения внесены в 30 функций и в более чем 30 файлов исходного кода. Для быстрого поиска метаданных в локальной памяти процесса используется индексная структура типа Red-Black tree, что позволяет серверным процессам работать с большим числом временных таблиц. Однако благодаря использованию такой индексной структуры при создании большого (десятки и сотни тысяч) числа временных таблиц в одной сессии скорость их создания замедляется нелинейно.
----------------------
Core Operations and Hooks:
inmem_init(): Initializes the in-memory storage engine, creating shared memory data structures and registering callbacks.
inmem_insert(), inmem_delete(), inmem_update(), inmem_inplace_update(): Hooks that intercept heap operations on temporary tables and redirect them to the in-memory storage engine.
inmem_index_insert(), inmem_index_getnext_tid_merge(), inmem_index_getbitmap(): Hooks that intercept index operations and handle them using the in-memory indexes.
inmem_beginscan(), inmem_getnext(), inmem_hot_search_buffer(): Functions that support heap scans, retrieving data from the in-memory storage.
inmem_index_beginscan(), inmem_index_endscan(), inmem_index_rescan(): Functions that manage index scans on in-memory indexes.
inmem_subxact_cb(), inmem_xcact_cb(): Callbacks that handle transaction and subtransaction events, managing the visibility of changes.
find_relation_entry(): get entry for in-memory relation, if it handled.
GetRelationItemIdxByOidInternal(), GetSnapshotRelationIdxByOid(): get idx in arrays of relation.
tuples_handler_is_empty(): check, that relation have no tuples.
IsInmemHandledRelationId(), IsInmemHandledIndexId(): determine whether to apply in-memory logic.
GetLocalMemoryContext(), GetLocalTransactionMemoryContext(): local contexts for data storing.
generic_is_inmem_tuple(), pg_class_is_inmem_tuple(): check if tuple is in-memory.
inmem_clean_catalog_on_relation_delete(): delete all data about deleting relation from indexes.
delete_pg_depend_entries(), delete_pg_class_entries(), delete_pg_type_entries(): deleting entries from pg_depend, pg_class, pg_type catalogs.
---------------------Буферный кэш не засоряется блоками таблиц системного каталога:
postgres=# select * from pg_buffercache_usage_counts();
usage_count | buffers | dirty | pinned
-------------+---------+-------+--------
0 | 15644 | 0 | 0
1 | 175 | 6 | 0
2 | 135 | 0 | 0
3 | 73 | 0 | 0
4 | 56 | 1 | 0
5 | 301 | 25 | 0
(6 rows)Запрос к таблицам системного каталога выполняется быстро:
postgres=# explain (analyze, buffers, timing off) select 'pg_attribute', pg_total_relation_size('pg_attribute'), count(*) from pg_attribute;
-> Seq Scan on pg_attribute (cost=0.00..89.37 rows=3237 width=0) (actual rows=3237 loops=1)
Buffers: shared hit=57
Planning Time: 0.059 ms
Execution Time: 0.672 ms
Time: 1.446 msРезультат
При создании и удалении временных таблиц в PostgreSQL в таблицы системного каталога вносятся изменения — несмотря на то, что временные таблицы доступны только процессу, в сессии которого они были созданы. Это может привести к раздуванию таблиц системного каталога и дополнительной нагрузке на экземпляр со стороны процессов автовакуума.
Параметр конфигурации enable_temp_memory_catalog, реализованный в СУБД Tantor Postgres 17.5, позволяет сохранять метаданные временных объектов в локальной памяти процесса, который работает с ними, предотвращая изменения в таблицах системного каталога. Параметр конфигурации enable_delayed_temp_file ускоряет работу с временными таблицами, позволяя не создавать файлы временных таблиц, пока хватает памяти локального буфера серверного процесса.
