PostgreSQL is known for its robustness and performance right out of the box. However, different applications and different scales of data demand fine-tuning of various parameters to achieve optimum performance. One such significant parameter is shared_buffers, which, when configured correctly, can notably enhance the performance of your PostgreSQL database.
Understanding Shared Buffers for unlocking PostgreSQL performance
Unlike other DBMS, PostgreSQL does not use direct IO. PostgreSQL uses its own buffer and kernel buffered IO which means that data is stored twice in memory. This is referred to as double buffering. PostgreSQL’s buffer is called shared_buffers and it defines how much dedicated system memory PostgreSQL will use for cache. By default shared_buffers are set 128MB.
Kernel buffered IO passes through OS cache which results in better performance by reducing disk I/O. If a process needs to retrieve some data from the database, it first looks for the required data inside shared_buffers, if the data is not available in the shared_buffers, a request would go to the OS to get the required data. If the OS cache already has the required data it will pass it on to shared_buffers. If the data is not present even in OS cache, only then will a physical IO occur.
How to change shared_buffers value?
You can change the value of shared_buffers by editing the postgresql.conf file or by following query:
ALTER SYSTEM SET shared_buffers = '512MB';If shared_buffers value is changed, we must restart PostgreSQL for the change to take effect.
Why tune Shared Buffers in PostgreSQL?
Tuning the shared_buffers parameter can bring about several benefits:
- Reduced Disk I/O: shared_buffers helps reduce the need to read data from disk. When data is in memory, it can be accessed much faster than when it has to be retrieved from disk or OS cache.
- Query Response Time: Faster data retrieval translates into quicker query response times.
Why not over-allocate shared_buffers?
While more RAM sounds good, it doesn’t always lead to better performance. It’s important to leave enough free RAM for the operating system and other database tasks like hashing, sorting, vacuuming, or indexing to work well. If shared_buffers take up too much memory, other processes might not get the memory they need, leading to poorer performance. You might also run into errors due to lack of memory. Generally it is advised to keep the value of shared_buffers at 25% of the available memory.
Testing: Evaluating Performance by tuning shared_buffers
We initialize a pgbench database with a scale factor of 500. This will create a database with 50 million records. We initialized this pgbench database on AWS EC2 t3.large instance with 2 VCPUs and 8GB memory.
pgbench -i -s 500We ran the following benchmark, with the number of clients equal to 10, with the number of threads per client equal to 2 and for a duration of 60 seconds.
pgbench -c 10 -j 2 -T 60pgbench (15.4 (Ubuntu 15.4-2.pgdg22.04+1))
starting vacuum…end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 2
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 92887
number of failed transactions: 0 (0.000%)
latency average = 6.460 ms
initial connection time = 18.983 ms
tps = 1548.056963 (without initial connection time)
We achieved a tps(transactions per second) of 1548. We then increased the value of shared_buffers to 1GB, restarted the server and ran the same query.
pgbench -c 10 -j 2 -T 60pgbench (15.4 (Ubuntu 15.4-2.pgdg22.04+1))
starting vacuum…end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 2
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 104548
number of failed transactions: 0 (0.000%)
latency average = 5.739 ms
initial connection time = 20.992 ms
tps = 1742.523183 (without initial connection time)
We can see that the tps increased to 1742 from 1548. We then increased the value of shared_buffers to 25% of available memory i.e. 2GB and ran the same query again.
pgbench -c 10 -j 2 -T 60pgbench (15.4 (Ubuntu 15.4-2.pgdg22.04+1))
starting vacuum…end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 500
query mode: simple
number of clients: 10
number of threads: 2
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 106806
number of failed transactions: 0 (0.000%)
latency average = 5.616 ms
initial connection time = 23.479 ms
tps = 1780.633062 (without initial connection time)
We can see that the value of tps now goes up to 1780. From default shared_buffers value up till 25% of memory tps increases from 1548 to 1780.
Conclusion
Tuning shared_buffers is an important part of this process, as it directly impacts data retrieval speed and query response times.
By allocating an appropriate amount of memory to shared_buffers and regularly monitoring your database’s performance, you can improve the performance of your PostgreSQL database, providing a better user experience for your applications.
