Enlarge IOS vm cache

From: Arseny Sher <a(dot)sher(at)postgrespro(dot)ru>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Enlarge IOS vm cache
Date: 2021-03-09 10:56:18
Message-ID: 87pn08oast.fsf@ars-thinkpad
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

Our customer experienced a significant slowdown on queries involving
Index Only Scan. As it turned out, the problem was constant pin-unpin of
the visibility map page. IOS caches only one vm page, which corresponds
to 8192 * 8 / 2 * 8192 bytes = 256 MB of data; if the table is larger
and the order of access (index) doesn't match the order of data, vm page
will be replaced on each tuple processing. That's costly. Attached
ios.sql script emulates this worst case behaviour. In current master,
select takes

[local]:5432 ars(at)postgres:21052=# explain analyse select * from test order by id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using test_idx on test (cost=0.44..1159381.24 rows=59013120 width=8) (actual time=0.015..9094.532 rows=59013120 loops=1)
Heap Fetches: 0
Planning Time: 0.043 ms
Execution Time: 10508.576 ms

Attached straightforward patch increases the cache to store 64 pages (16
GB of data). With it, we get

[local]:5432 ars(at)postgres:17427=# explain analyse select * from test order by id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using test_idx on test (cost=0.44..1159381.24 rows=59013120 width=8) (actual time=0.040..3469.299 rows=59013120 loops=1)
Heap Fetches: 0
Planning Time: 0.118 ms
Execution Time: 4871.124 ms

(I believe the whole index is cached in these tests)

You might say 16GB is also somewhat arbitrary border. Well, it is. We
could make it GUC-able, but I'm not sure here as the setting is rather
low-level, and at the same time having several dozens of additionally
pinned buffers doesn't sound too criminal, i.e. I doubt there is a real
risk of "no unpinned buffers available" or something (e.g. even default
32MB shared_buffers contain 4096 pages). However, forcing IOS to be
inefficient if the table is larger is also illy. Any thoughts?

(the code is by K. Knizhnik, testing by M. Zhilin and R. Zharkov, I've
only polished the things up)

Attachment Content-Type Size
ios.sql application/x-sql 948 bytes
ios_vm.patch text/x-diff 5.5 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message John Naylor 2021-03-09 11:43:52 Re: [POC] verifying UTF-8 using SIMD instructions
Previous Message Amit Kapila 2021-03-09 10:55:46 Re: [HACKERS] logical decoding of two-phase transactions