PostgreSQL 9.3 - Shared Buffers Performance (1)

02.2014 / Category: , / Tags: |

A lot has been said about PostgreSQL shared buffers and performance. As my new desktop box has arrived this week I decided to give it a try and see, how a simple benchmark performs, given various settings of shared_buffers.

The first thing to run the test is to come up with a simple test database. I decided to use a pgbench with a scale factor of 100. This gives a total of 10 mio rows and a database size of roughly 1.5 GB:

The test box we are running on is an AMD FX-8350 (8 cores) with 16 GB of RAM and a 256 GB Samsung 840 SSD.

Running the test

A simple shell script is used to conduct the test:

The goal of the test is to see if the amount of shared memory does have an impact on the performance of the system.

NOTE: In our test the benchmarking program is running on the same node sucking up around 1.5 CPU cores. So, the tests we have here can basically just use up the remaining 6.5 cores. While this does not give performance data, it is still enough to see, which impact changing the size of the shared buffers have.

Here are the results. Buffers are in 8 blocks:

Buffers TPS simple TPS prepared
1024 80529,33 120403,43
4096 80496,44 119709,02
16384 82487,12 124697,52
65536 87791,88 139695,82
131072 92086,75 150641,91
262144 94760,85 155698,34
524288 94708,02 155766,49
1048576 94676,83 156040,41

What we see is a steady increase of the database throughput roughly up to the size of the database itself. Then the curve is flattening out as expected:


It is interesting to see that the impact of not having to ask the operating system for each and every block is actually quite significant.

The same can be said about prepared queries. Avoiding parsing does have a serious impact on performance - especially on short reading transactions. Close to 160k reads to find data in 10 mio rows is actually quite a lot given the fact that we are talking about hardware, which is ways around 1.000 euros including VAT.

0 0 votes
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
Jim Nasby
10 years ago

To me this is just further confirmation that we still can't use any more than 2-8G for shared_buffers. That matches what I've seen at work, where we're using 80 core machines with 512GB, yet still have to set shared_buffers to 8G.

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram