Re: 9.4 regression - Mailing list pgsql-hackers

On 2013-08-07 20:23:55 +0100, Thom Brown wrote:
> >>> 269e78 was the commit immediately after 8800d8, so it appears that
> >>> introduced the regression.
> >>>
> >>> "Use posix_fallocate() for new WAL files, where available."
> >>
> >> This is curious. Could you either run a longer test before/after the
> >> commit or reduce checkpoint_timeout to something like 3min?
> >
> > Okay, I'll rerun the test for both those commits at 1 hour each with
> > checkpoint_timeout set at 3mins, but with all other configuration
> > settings the same
> 
> Results
> (checkpoint_timeout = 3min)
> 
> pgbench -j 80 -c 80 -T 3600
> 
> 269e78: 606.268013
> 8800d8: 779.583129

Ok, so the performance difference is lower. But, it seems to be still to
high to be explaineable by performance differences during
initialization/fallocate.
In a very quick test, I don't see the same on my laptop. I am currently
travelling and I don't have convenient access to anything else.

Could you:
- run filefrag on a couple of wal segments of both clusters after the test and post the results here?
- enable log_checkpoints, post the lines spat out together with the results
- run pgbench without reinitializing the cluster/pgbench tables inbetween?

Basically, I'd like to know whether its the initialization that's slow
(measurable because we're regularly creating new files because of a too
low checkpoint_segments) or whether it's the actual writes.

> Jon, here are the test results you asked for:
> 
> $ for i in 1 2 5 10 100; do ./test_fallocate foo $i 1; done
> method: classic. 1 open/close iterations, 1 rewrite in 0.9157s
> method: posix_fallocate. 1 open/close iterations, 1 rewrite in 0.5314s
> method: glibc emulation. 1 open/close iterations, 1 rewrite in 0.6018s
> method: classic. 2 open/close iterations, 1 rewrite in 1.1417s
> method: posix_fallocate. 2 open/close iterations, 1 rewrite in 0.6505s
> method: glibc emulation. 2 open/close iterations, 1 rewrite in 1.8900s
> method: classic. 5 open/close iterations, 1 rewrite in 3.6490s
> method: posix_fallocate. 5 open/close iterations, 1 rewrite in 1.9841s
> method: glibc emulation. 5 open/close iterations, 1 rewrite in 3.1053s
> method: classic. 10 open/close iterations, 1 rewrite in 5.7562s
> method: posix_fallocate. 10 open/close iterations, 1 rewrite in 3.2015s
> method: glibc emulation. 10 open/close iterations, 1 rewrite in 7.1426s
> method: classic. 100 open/close iterations, 1 rewrite in 64.9483s
> method: posix_fallocate. 100 open/close iterations, 1 rewrite in 36.3748s
> method: glibc emulation. 100 open/close iterations, 1 rewrite in 64.8386s

Ok, this seems to indicate that fallocate works nicely. Jon, wasn't
there a version of the test that rewrote the file afterwards?

Greetings,

Andres Freund

-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services