Re: 9.4 regression

On Wed, Aug 7, 2013 at 12:42 PM, Thom Brown <thom(at)linux(dot)com> wrote:
> On 7 August 2013 17:54, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
>> On Wed, Aug 7, 2013 at 11:21 AM, Thom Brown <thom(at)linux(dot)com> wrote:
>>> 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:
>>
>> ...
>>
>>> 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.
>>
>> Would it be possible for you to download, compile, and run the test
>> program as described and located in this email:
>>
>> http://www.postgresql.org/message-id/CAKuK5J1AcML-1cGBhnRzED-vh4oG+8HkmFhy2ECa-8JBJ-6qbQ@mail.gmail.com
>
> I shall do after the 2 x 1 hour benchmarks are complete.
>
>> I also wonder if there is a problem with the 3.8.0 kernel specifically.
>
> Well my laptop has the same kernel (and also 64-bit Linux Mint), so
> took 3 quick sample benchmarks on those two commits, and I get the
> following (all without --enable-cassert):
>
> 269e78: 1162.593665 / 1158.644302 / 1153.955611
> 8800d8: 2446.087618 / 2443.797252 / 2321.876431
>
> And running your test program gives the following (again, just on my laptop):
>
> 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.6380s
> method: posix_fallocate. 1 open/close iterations, 1 rewrite in 0.3204s
> method: glibc emulation. 1 open/close iterations, 1 rewrite in 0.6274s
> method: classic. 2 open/close iterations, 1 rewrite in 1.2908s
> method: posix_fallocate. 2 open/close iterations, 1 rewrite in 0.6596s
> method: glibc emulation. 2 open/close iterations, 1 rewrite in 1.2666s
> method: classic. 5 open/close iterations, 1 rewrite in 3.1419s
> method: posix_fallocate. 5 open/close iterations, 1 rewrite in 1.5930s
> method: glibc emulation. 5 open/close iterations, 1 rewrite in 3.1516s
> method: classic. 10 open/close iterations, 1 rewrite in 6.2912s
> method: posix_fallocate. 10 open/close iterations, 1 rewrite in 3.2626s
> method: glibc emulation. 10 open/close iterations, 1 rewrite in 6.3667s
> method: classic. 100 open/close iterations, 1 rewrite in 67.4174s
> method: posix_fallocate. 100 open/close iterations, 1 rewrite in 37.8788s
> method: glibc emulation. 100 open/close iterations, 1 rewrite in 55.0714s

OK. That's interesting, and a good data point.

One thing you could try manually disabling the use of posix_fallocate in 269e78.
After running ./configure --stuff-here
edit src/include/pg_config.h and comment out the following line (on or
around line 374)
#define HAVE_POSIX_FALLOCATE 1

*then* build postgresql and see if the performance hit is still there.

--
Jon