Postgresql: To learn the effect of shared buffers on the query performance

Objective:
To learn the effect of shared buffers on the query performance.
Here we will have pre/post data collection..
1) psql output of various object and query stats
2) OS free-k, vmstat & proc stats

Data Model setup for test:
create table test_shrbuf as
with t as
(
select generate_series(0,1000000) lvl
)
select t.lvl
,(mod(t.lvl,100)+date '1/1/2022') as dob
,lpad('A',(mod(t.lvl,20)+(20-mod(t.lvl,20))),chr(65+mod((t.lvl/26),26))) as hosptl
,substr(md5(random()::text),15) as fn
,substr(md5(random()::text),15) as ln
,'UKCO'||chr(65+mod(t.lvl,26))||chr(65+mod((t.lvl/26),26)) as POSCODE
from t;
create index test_shrbuf_idx_lvl on test_shrbuf(lvl);

Note: Please refer to previous blog for statistics on the table & index creation.

Current Setting:
show shared_buffers;
 shared_buffers
----------------
 128MB
(1 row)

select name,setting from pg_settings where name in ('shared_buffers','huge_pages','shared_memory_type','effective_cache_size','huge_page_size','min_parallel_table_scan_size','min_parallel_index_scan_size') order by 1;
             name             | setting
------------------------------+---------
 effective_cache_size         | 524288 <<< 4GB which is my machine's total ram size
 huge_pages                   | try
 huge_page_size               | 0
 min_parallel_index_scan_size | 64
 min_parallel_table_scan_size | 1024
 shared_buffers               | 16384 <<< 128MB
 shared_memory_type           | mmap
(7 rows)

psql setup: timing on and pager off

postgres=# \pset pager off
Pager usage is off.
postgres=# \timing
Timing is on.
postgres=#
Table & index size:
                                      List of relations
 Schema |    Name     | Type  |  Owner   | Persistence | Access method |  Size  | Description
--------+-------------+-------+----------+-------------+---------------+--------+-------------
 public | test_shrbuf | table | postgres | permanent   | heap          | 104 MB |
(1 row)
                                                 List of relations
 Schema |        Name         | Type  |  Owner   |    Table    | Persistence | Access method | Size  | Description
--------+---------------------+-------+----------+-------------+-------------+---------------+-------+-------------
 public | test_shrbuf_idx_lvl | index | postgres | test_shrbuf | permanent   | btree         | 21 MB |
(1 row)

Test results:

#	scenario	shared_buffers	query planning time	query execution time	seq_scan	seq_scan_delta	seq_tup_read	seq_tup_read_delta	heap_blks_read	heap_blks_read_delta	heap_blks_hit	heap_blks_hit_delta	shared_blks_hit	shared_blks_hit_delta	shared_blks_read	shared_blks_read_delta	vmstat inf	VmPeak	VmSize	VmHWM	VmRSS	free -k(shared)	free -k (buff/cache)
1	Pre query launch	128MB	NA	NA	23	0	8000008	0	126596	0	33423	0	13488	0	26674	0	vm free: 3340532	403404 kB	403376 kB	19064 kB	19064 kB	24332	336076
2	Query run: Test1 explain analyze select * from test_shrbuf where poscode='UKCOPA';	128MB	0.889 ms	365.444 ms	26	3	9000009	1000001	139930	13334	33423	0	13596	108	40011	13337	vm free: 3336196	NO change	NO change	NO change	NO change	25360	337148	shared and buffer inc a bit
3	Query run: Test2 explain analyze select * from test_shrbuf where poscode='UKCOPA';	128MB	0.070 ms	404.173 ms	29	3	10000010	1000001	153168	13238	33519	96	13692	96	53249	13238	vm free: 3335304	NO change	NO change	NO change	NO change	26128	337948	shared and buffer inc a bit
4	Query run: Test3 explain analyze select * from test_shrbuf where poscode='UKCOPA';	128MB	0.062 ms	366.712 ms	32	3	11000011	1000001	166310	13142	33711	192	13884	192	66391	13142	vm free: 3334240	NO change	NO change	NO change	NO change	26896	339052	shared and buffer inc a bit
5	Pre query launch	1GB	NA	NA	32	0	11000011	0	166310	0	33711	0	13884	0	66391	0	vm free: 3306680	1354924 kB	1354896 kB	49312 kB	49312 kB	54028	366004	shared and buffer inc a bit
6	explain analyze select * from test_shrbuf where poscode='UKCOPA';	1GB	1.223 ms	574.786 ms	35	3	12000012	1000001	179644	13334	33711	0	13987	103	79728	13337	vm free: 3199144	NO change	NO change	NO change	NO change	161512	473888	shared and buffer inc a lot (almost equal to table size)
7	explain analyze select * from test_shrbuf where poscode='UKCOPA';	1GB	0.065 ms	369.067 ms	38	3	13000013	1000001	179644	0	47045	13334	27321	13334	79728	0	vm free: 3198904	NO change	NO change	NO change	NO change	161512	473920	buff inc a bit
8	explain analyze select * from test_shrbuf where poscode='UKCOPA';	1GB	0.061 ms	461.891 ms	41	3	14000014	1000001	179644	0	60379	13334	40655	13334	79728	0	vm free: 3198656	NO change	NO change	NO change	NO change	161512	473932	buff inc a bit
9	explain analyze select * from test_shrbuf where poscode='UKCOPA';	1GB	0.079 ms	363.798 ms	44	3	15000015	1000001	179644	0	73713	13334	53989	13334	79728	0	vm free: 3198688	NO change	NO change	NO change	NO change	161512	473960	buff inc a bit

Observation: In 128MB scenario, the postgresql had no clue about where it is reading data from. May be the data came in from FS cache (so why the performance was better, but still postgresql belived it fetched the blocks from disk; for which I will repeat the test with vm reboot) vs in the secondset of sceanrio, the postgresql was able to track the performane difference.

Update - 3Apr2023

To prove if the FS cache is playing its part with respect to 128MB memory scenario. I reran the same test after a server reboot. As expected there is a sharp raise in buffer cache size after warm up or first fetch of the table & the query performance was poor that time. In the next test it was back to a performance of 1GB test. See the result below...

#	scenario	shared_buffers	query planning time	query execution time	seq_scan	seq_scan_delta	seq_tup_read	seq_tup_read_delta	heap_blks_read	heap_blks_read_delta	heap_blks_hit	heap_blks_hit_delta	shared_blks_hit	shared_blks_hit_delta	shared_blks_read	shared_blks_read_delta	vmstat inf	VmPeak	VmSize	VmHWM	VmRSS	free -k(shared)	free -k (buff/cache)
1	Pre query launch	128MB	NA	NA	44		15000015		179644		73713		53989		79728		vm free: 3446184|vm cache: 229708	403404 kB	403376 kB	19068 kB	19068 kB	24300	232156
2	Test Round 1: explain analyze select * from test_shrbuf where poscode='UKCOPA';	128MB	2.252 ms	679.897 ms	47	3	16000016	1000001	192978	13334	73713	0	54092	103	93065	13337	vm free: 3235340|vm cache: 436224	no change	no change	no change	no change	24300	438328	cache increased by 200MB, may be the table is cached?
3	Test Round 2: explain analyze select * from test_shrbuf where poscode='UKCOPA';	128MB	0.069 ms	366.576 ms	50	3	17000017	1000001	206216	13238	73809	96	54188	96	106303	13238	vm free: 3232896|vm cache: 437276	no change	no change	no change	no change	26128	439388	The cache increase in test1 - warmed up the memory

Also a round of test after dropping the cache with the below command:

echo 1 > /proc/sys/vm/drop_caches

cache size after the command: 186716

#

scenario

shared_buffers

query planning time

query execution time

seq_scan

seq_scan_delta

seq_tup_read

seq_tup_read_delta

heap_blks_read

heap_blks_read_delta

heap_blks_hit

heap_blks_hit_delta

shared_blks_hit

shared_blks_hit_delta

shared_blks_read

shared_blks_read_delta

vmstat inf

VmPeak

VmSize

VmHWM

VmRSS

free -k(shared)

free -k (buff/cache)

5

Test Round 2 - drop cache: explain analyze select * from test_shrbuf where poscode='UKCOPA';

128MB

0.100 ms

474.190 ms

59

20000020

1000001

245354

12950

74673

384

55052

384

145441

12950

vm free: 3417120|vm cache:233156

no change

no change

no change

no change

29000

232780

See how warm the cache is after test (cache size): 232780

Performance has deteriorated (after cache drop).

This concludes our test on the shared buffer influence on query performance.

But remember postgresql statistics (pg_stat_statements & pg_statio_user_tables) are misleading in this situation!!!!

So as far as I am concerned, if the VM is a dedicated DB VM, we should allocate the memory to the postgresql db itself, rather than leaving them for FS cache. Since postgres knows better to manage its cache rather than FS, also see postgresql metrics are going into trouble.

Thanks