9.4 regression

Hi all,

I recently tried a simple benchmark to see how far 9.4 had come since
8.4, but I discovered that I couldn't get 9.4 to even touch 8.4 for
performance. After checking 9.2 and 9.3 (as per Kevin Grittner's
suggestion), I found that those were fine, so the issue must be in
9.4devel. I used identical configurations for each test, and used
9.1's pgbench to ensure changes in pgbench didn't affect the outcome.
The common config changes were:

max_connections = 500
shared_buffers = 4GB
effective_cache_size = 12GB
random_page_cost = 2.0
cpu_tuple_cost = 0.03
wal_buffers = 32MB
work_mem = 100MB
maintenance_work_mem = 512MB
checkpoint_segments = 32
checkpoint_timeout = 15min
checkpoint_completion_target = 0.8
commit_delay = 50
commit_siblings = 15

System info:
8GB DDR3 RAM (yes, I know the config isn't optimal here)
64-bit Linux Mint 15 (3.8.0 kernel)
ext4

Only build option used was --enable-depend. I did have
--enable-cassert for the shorter 5 min benchmarks, but was removed for
the 30 min tests.

Here are the results:

pgbench -j 80 -c 80 -T 300:

8.4 - 535.990042
9.2 - 820.798141
9.3 - 828.395498
9.4 - 197.851720

pgbench -j 20 -c 20 -T 300:

8.4 - 496.529343
9.2 - 569.626729
9.3 - 575.831264
9.4 - 385.658893

pgbench -j 20 -c 400 -T 300:

8.4 - 580.186868
9.2 - 824.590252
9.3 - 784.638848
9.4 - 524.493308

These were only run for 5 minutes each, but subsequent ones were run
for 30 mins. All tests were run with -s 20.

Given a few key commits Robert Haas directed me to, I narrowed down
the regression to a time period, so benchmarked a few select commits.

pgbench -j 80 -c 80 -T 1800:

8.4: 812.482108
9.4 HEAD: 355.397658
9.4 e5592c (9th July): 356.485625
9.4 537227 (7th July): 365.992518
9.4 9b2543 (7th July): 362.587339
9.4 269e78 (5th July): 359.439143
9.4 8800d8 (5th July): 821.933082
9.4 568d41 (2nd July): 822.991160

269e78 was the commit immediately after 8800d8, so it appears that
introduced the regression.

"Use posix_fallocate() for new WAL files, where available."

Ironically, that was intended to be a performance improvement.

Regards

Thom