4

I have a server with 4GB of RAM and PostgreSQL 9.0.3 on Centos 5. I'm using pgbench and pgbench-tools to measure performance using two pgbench-tools queries: select and tpc-b.

With default settings of postgresql.conf and using a select query, I get the following results:

Scale: 1, 10, 100, 1000.
Transactions per second: 10000, 8800, 7500, 100.

(the number of records of the table is scale*100000)

I increased the option shared_buffer to 256MB (previously was 32 MB) and I get the following results:

Scale: 1, 10, 100, 1000.
Transactions per second: 10000, 8000, 3200, 30

Why is the performance is so low compared to the first test when the scale is 100 or more in the second benchmark? The only thing I have done was increase the memory.

Caleb
  • 11,583
  • 4
  • 35
  • 49
doctore
  • 143
  • 1
  • 4
  • 2
    How often did you run that test? Run it at least three times to see if it gives consistent results. There might be some background task running. – Eelke Apr 29 '11 at 18:42
  • Thanks for your reply. Each test has released 3 times (SETTIMES = 3 in the config file of pgbench-tools). I've also launched, for the default configuration, a test with autovacuum on and other with autovacuum off, and the same to change the parameter shared_buffers. So I made ​​2 benchmarks with 3 tests (6 total) with default configuration, and the same after increasing shared_buffer. –  Apr 29 '11 at 21:03
  • What is `sysctl kernel.shmmax` on your system? Is it greater than the `shared_buffers` value? – idleyoungman Apr 30 '11 at 03:48
  • The only reasonable cause I can think off is that the query planner is making some bad decisions when the data set gets big. Have you set the effective cache size? It should be OS cache size + shared buffer size. Notice this setting is a planner hint it doesn't change memory usage it only will change the planners assumptions about what data is likely to be in memory. – Eelke Apr 30 '11 at 06:01
  • Hi Idleyoungman, yes the shmmax value is higher (536870912). If I had a smaller value, PostgreSQL will not start. – doctore Apr 30 '11 at 07:46
  • Hi Eelke, in an earlier test to the two tests that indicated in the first message, the memory configuration was: shared_buffers = 512 MB; maintenance_work_mem = 8MB; effective_cache_size = 1GB; work_mem = 2MB; And the results were similar, even slightly worse. For that reason, I did tests changing options one by one, to know what was the cause of poor performance. And "the winner" is shared_buffers... – doctore Apr 30 '11 at 07:58

1 Answers1

1

The only thing that consistently makes pgbench slower when you increase shared_buffers is a debugging option called assertion checking. If you do this in psql:

show debug_assertions;

And it's on, your build has this problem, and the results you're seeing are expected. You need a new install that's not enabled for assertion debugging to make that go away.

Otherwise, if you didn't do all this more than once, I wouldn't be so sure the cause here was the shared_buffers change. As one example, if autovacuum happens to run at the point your large scale database is running, it will slow test results. A checkpoint starting as the test begins will too. Turn off autovacuum to eliminate it from the test, and turn on log_checkpoints to see if it's interfering.

A third possibility, and this I'm almost sure you're suffering from to some degree, is that things moving around on disk can cause a 50% slowdown in results. Disks are about twice as fast on their early parts as their later ones, and as you run pgbench repeatedly the data will move to the slower parts as you go. I end up rebuilding the whole filesystem in order to get repeatable results, only way to be sure you're starting at the same point on the drive. This doesn't impact the results at the smaller scales because those all fit in RAM.

When I see a performance change after touching a configuration parameter, I always try the original configuration again to make sure that was the cause. I'm often surprised to discover that the test just gets slower each time you run it, with this disk location speed difference being one source for that issue.

Greg Smith
  • 959
  • 5
  • 7
  • Thanks for your answer. All tests were performed with and without autovacuum, and each test was run 3 times (SETTIMES = 3 in the config file of pgbench-tools). With shared_buffers = 128 Mb, the result are similar to default configuration, but when I increase this option to 256 Mb, the performance is more lower with scale = 100 or greater. – doctore May 13 '11 at 11:23
  • Sorry, I forgot to comment: debug_assertions = off – doctore May 13 '11 at 13:00