1. Introduction
In OLAP(Online Analytical Processing) workload, PostgreSQL server often reads large data from one table. So buffer cache for table containing large data is effective. In this short article, I show what I found on effect of buffer cache for accelarating query which reads large data from one table in PostgreSQL.
2. Prerequisites
Version of PostgreSQL: PostgreSQL13.1
Experimental Setting: Windows10, Intel Core i7-6700T CPU 2.7GHz(4 Cores), 32GB memory, 1TB 5,400rpm HDD, shared_buffers = 20GB
3. What I looked up
(1) Effect of PostgreSQL buffer cache
(2) Buffer cache architecture
(3) Discussion on improvement of buffer chache archtecture between PostgreSQL developers
(4) Effect of buffer cache by slight source change
4. How I looked up
(1) Effect of PostgreSQL buffer cache
By simple experiment
(2) Buffer cache architecture
By source reading
(3) Discussion on improvement of buffer chache archtecture between PostgreSQL developers
By search for pgsql-hackers
(4) Effect of buffer cache by slight source change
By simple experiment
5. What I found
(1) Effect of PostgreSQL buffer cache
For query which reads large data from one table in PostgreSQL, PostgreSQL buffer cache doesn’t effect so much. Performance of the query after the first depends on OS cache.
(2) PostgreSQL buffer cache architecture
For read-only queries, PostgreSQL manages pages which PostgreSQL keeps in buffer cache with a ringbuffer. For query which reads large data from one table, the number for pages in the ring buffer(so called ringbuffer size) is 32(256KB). So almost pages read with such query are not kept in buffer cache.
(3) Discussion on improvement of buffer cache archtecture between PostgreSQL developers
There is a discussion whether or not to make ringbuffer size configurable[2] using GUC parameters or storage parameter per table, etc.
(4) Effect of buffer cache by slight source change
By make ringbuffer size equal to shared_buffers, some query response becomes 1/5 of the before.
Hereinafter, I show the detail of what I looked up and what I found.
5.1 Effect of buffer cache
(1) Connect to PostgreSQL server using psql and execute the following commands
\timing on
create database test_cache;
\c test_cache
set max_parallel_workers_per_gather=4;
create table t1(col00 float8, col01 float8, col02 float8, col03 float8, col04 float8, col05 float8, col06 float8, col07 float8, col08 float8, col09 float8);
select setseed(0.5);
insert into t1 select random() as col00, random() as col01, random() as col02, random() as col03, random() as col04, random() as col05, random() as col06, random() as col07, random() as col08, random() as col09 from generate_series(1, 150000000, 1);
(2) By \d+ meta command confirm table data size
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
——–+——+——-+———-+————-+——-+————-
public | t1 | table | postgres | permanent | 15 GB |
(1 row)
(3) Execute the follow two commands 3 times in comanndline. In the following, “myclearcache” is my program to clear OS’s cache. “myclearcache” reads a file which data size is equal with total memory size of my PC.
psql -U postgres -c “explain (verbose, analyze, buffers) select avg(col00 + col01 + col02 + col03 + col04 + col05 + col06 + col07 + col08 + col09) from t1”
myclearcache
In my setting, the second response and the thid response were about 300s and the PostgreSQL’s cache hit rate is less than 1%.
(4) When I removed “myclearcache” from the above two commands, they were response 15s.
5.2 PostgreSQL buffer cache architecture
Structure of PostgreSQL buffer cache is hash table. The memory area of PostgreSQL buffer cache is allocated in shared memory area. I found these facts from the following source file. https://github.com/postgres/postgres/blob/master/src/backend/storage/buffer/buf_table.c
For read-only queries, PostgreSQL selects a strategy of managing pages which PostgreSQL keeps in buffer cache, so called buffer access strategry. For query which reads large data from one table, the buffer access strategry is BAS_BULKREAD which manages pages which PostgreSQL keeps in buffer cache with a 32-length ringbuffer. I found these facts from the init() function in following first source file and GetAccessStrategy() function called from init() and GetBufferFromRing() function in following second source file.
https://github.com/postgres/postgres/blob/master/src/backend/access/heap/heapam.c
https://github.com/postgres/postgres/blob/master/src/backend/storage/buffer/freelist.c
the number for pages in the ring buffer(so called ringbuffer size) is 32(256KB). So almost pages read with such query are not kept in buffer cache.
5.3 Discussion on improvement of buffer cache archtecture between PostgreSQL developers
There is a discussion whether or not to make ringbuffer size configurable[2] using GUC parameters or storage parameter per table, etc.
5.4 Effect of buffer cache by slight source change
To find effect of buffer cache by expanding ringbuffer size(not to make practical module), I performed the follwing simple experiment.
(1) For making ringbuffer size equal to shared_buffers, I changed the source of GetAccessStrategy() and build postgres module.
Before:
case BAS_BULKREAD:
ring_size = NBuffers;
…
ring_size = Min(NBuffers / 8, ring_size);
After:
case BAS_BULKREAD:
ring_size = NBuffers;
…
ring_size = Min(NBuffers, ring_size);
In the above code, NBuffers is set by shared_buffers the following source file.
https://github.com/postgres/postgres/blob/master/src/backend/utils/misc/guc.c
(2) I found the response of the 5.1(3) query using (1) module was about 50s and the PostgreSQL buffer cache hit rate is 100%. I don’t understand that this response 50s is larger than 5.1 (4) response 15s. But I found that postgres read some data from Windows pagefile and I guess this reading from pagefile is a cause of the difference.
6. Impression
I think it is usefull to make ringbuffer size configurable per table since I can make a table which data is kept in memory area even if OS cache is cleared by the another application behavior.
7. References
[1] pgsql-hackers, Make ringbuffer threshold and ringbuffer sizes configurable?
https://www.postgresql.org/message-id/flat/20200206040026.trjzsmdsbl4gu2b6%40alap3.anarazel.de
[2] Understanding caching in Postgres – An in-depth guide
https://madusudanan.com/blog/understanding-postgres-caching-in-depth/
[3] Inside the PostgreSQL Shared Buffer Cache
https://www.2ndquadrant.com/wp-content/uploads/2019/05/Inside-the-PostgreSQL-Shared-Buffer-Cache.pdf