Re: [HACKERS] Perf Benchmarking and regression.

2016-06-10 Thread Andres Freund
On 2016-06-09 17:19:34 -0700, Andres Freund wrote: > On 2016-06-09 14:37:31 -0700, Andres Freund wrote: > > I'm writing a patch right now, planning to post it later today, commit > > it tomorrow. > > Attached. And pushed. Thanks to Michael for noticing the missing addition of header file hunk.

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-09 Thread Michael Paquier
On Fri, Jun 10, 2016 at 9:42 AM, Andres Freund wrote: > On 2016-06-10 09:41:09 +0900, Michael Paquier wrote: >> On Fri, Jun 10, 2016 at 9:37 AM, Andres Freund wrote: >> > On 2016-06-10 09:34:33 +0900, Michael Paquier wrote: >> >> On Fri, Jun 10, 2016 at

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-09 Thread Andres Freund
On 2016-06-10 09:41:09 +0900, Michael Paquier wrote: > On Fri, Jun 10, 2016 at 9:37 AM, Andres Freund wrote: > > On 2016-06-10 09:34:33 +0900, Michael Paquier wrote: > >> On Fri, Jun 10, 2016 at 9:19 AM, Andres Freund wrote: > >> > On 2016-06-09 14:37:31

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-09 Thread Michael Paquier
On Fri, Jun 10, 2016 at 9:37 AM, Andres Freund wrote: > On 2016-06-10 09:34:33 +0900, Michael Paquier wrote: >> On Fri, Jun 10, 2016 at 9:19 AM, Andres Freund wrote: >> > On 2016-06-09 14:37:31 -0700, Andres Freund wrote: >> >> I'm writing a patch right

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-09 Thread Andres Freund
On 2016-06-10 09:34:33 +0900, Michael Paquier wrote: > On Fri, Jun 10, 2016 at 9:19 AM, Andres Freund wrote: > > On 2016-06-09 14:37:31 -0700, Andres Freund wrote: > >> I'm writing a patch right now, planning to post it later today, commit > >> it tomorrow. > > > > Attached. >

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-09 Thread Michael Paquier
On Fri, Jun 10, 2016 at 9:19 AM, Andres Freund wrote: > On 2016-06-09 14:37:31 -0700, Andres Freund wrote: >> I'm writing a patch right now, planning to post it later today, commit >> it tomorrow. > > Attached. -/* see bufmgr.h: OS dependent default */ -

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-09 Thread Andres Freund
On 2016-06-09 14:37:31 -0700, Andres Freund wrote: > I'm writing a patch right now, planning to post it later today, commit > it tomorrow. Attached. >From d86fc0c966efe544b1926652196059539966b137 Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Thu, 9 Jun 2016 17:15:42

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-09 Thread Andres Freund
On 2016-06-08 23:00:15 -0400, Noah Misch wrote: > On Sun, May 29, 2016 at 01:26:03AM -0400, Noah Misch wrote: > > On Thu, May 12, 2016 at 10:49:06AM -0400, Robert Haas wrote: > > > On Thu, May 12, 2016 at 8:39 AM, Ashutosh Sharma > > > wrote: > > > > Please find the test

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-08 Thread Noah Misch
On Sun, May 29, 2016 at 01:26:03AM -0400, Noah Misch wrote: > On Thu, May 12, 2016 at 10:49:06AM -0400, Robert Haas wrote: > > On Thu, May 12, 2016 at 8:39 AM, Ashutosh Sharma > > wrote: > > > Please find the test results for the following set of combinations taken > > >

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Andres Freund
On 2016-06-03 20:41:33 -0400, Noah Misch wrote: > Disabling just backend_flush_after by default works for me, so let's do that. > Though I would not elect, on behalf of PostgreSQL, the risk of enabling > {bgwriter,checkpoint,wal_writer}_flush_after by default, a reasonable person > may choose to

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Noah Misch
On Fri, Jun 03, 2016 at 03:17:06PM -0400, Robert Haas wrote: > On Fri, Jun 3, 2016 at 2:20 PM, Andres Freund wrote: > >> > I'm inclined to give up and disable backend_flush_after (not the rest), > >> > because it's new and by far the "riskiest". But I do think it's a > >> >

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Andres Freund
On 2016-06-03 15:17:06 -0400, Robert Haas wrote: > On Fri, Jun 3, 2016 at 2:20 PM, Andres Freund wrote: > >> I've always heard that guideline as "roughly 1/4, but not more than > >> about 8GB" - and the number of people with more than 32GB of RAM is > >> going to just keep

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Robert Haas
On Fri, Jun 3, 2016 at 2:20 PM, Andres Freund wrote: >> I've always heard that guideline as "roughly 1/4, but not more than >> about 8GB" - and the number of people with more than 32GB of RAM is >> going to just keep going up. > > I think that upper limit is wrong. But even

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Andres Freund
On 2016-06-03 13:47:58 -0400, Robert Haas wrote: > On Fri, Jun 3, 2016 at 1:43 PM, Andres Freund wrote: > >> I really don't get it. There's nothing in any set of guidelines for > >> setting shared_buffers that I've ever seen which would cause people to > >> avoid this

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Robert Haas
On Fri, Jun 3, 2016 at 1:43 PM, Andres Freund wrote: >> I really don't get it. There's nothing in any set of guidelines for >> setting shared_buffers that I've ever seen which would cause people to >> avoid this scenario. > > The "roughly 1/4" of memory guideline already

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Andres Freund
On 2016-06-03 13:42:09 -0400, Tom Lane wrote: > Robert Haas writes: > > On Fri, Jun 3, 2016 at 12:39 PM, Andres Freund wrote: > >> Note that other operating systems like windows and freebsd *alreaddy* > >> write back much more aggressively (independent

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Andres Freund
On 2016-06-03 13:33:31 -0400, Robert Haas wrote: > On Fri, Jun 3, 2016 at 12:39 PM, Andres Freund wrote: > > On 2016-06-03 12:31:58 -0400, Robert Haas wrote: > >> Now, what varies IME is how much total RAM there is in the system and > >> how frequently they write that data, as

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Tom Lane
Robert Haas writes: > On Fri, Jun 3, 2016 at 12:39 PM, Andres Freund wrote: >> Note that other operating systems like windows and freebsd *alreaddy* >> write back much more aggressively (independent of this change). I seem >> to recall you yourself

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Robert Haas
On Fri, Jun 3, 2016 at 12:39 PM, Andres Freund wrote: > On 2016-06-03 12:31:58 -0400, Robert Haas wrote: >> Now, what varies IME is how much total RAM there is in the system and >> how frequently they write that data, as opposed to reading it. If >> they are on a tightly

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Andres Freund
On 2016-06-03 12:31:58 -0400, Robert Haas wrote: > Now, what varies IME is how much total RAM there is in the system and > how frequently they write that data, as opposed to reading it. If > they are on a tightly RAM-constrained system, then this situation > won't arise because they won't be

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Robert Haas
On Fri, Jun 3, 2016 at 2:09 AM, Andres Freund wrote: > On 2016-06-03 01:57:33 -0400, Noah Misch wrote: >> > Which means that transactional workloads that are bigger than the OS >> > memory, or which have a non-uniform distribution leading to some >> > locality, are likely to

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Andres Freund
On 2016-06-03 09:24:28 -0700, Andres Freund wrote: > This unstable performance issue, with the minute-long stalls, is the > worst and most frequent production problem people hit with postgres in > my experience, besides issues with autovacuum. Ignoring that is just > hurting our users. Oh, and a

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Andres Freund
On 2016-06-03 10:48:18 -0400, Noah Misch wrote: > On Thu, Jun 02, 2016 at 11:09:22PM -0700, Andres Freund wrote: > > > Today's defaults for *_flush_after greatly smooth and accelerate > > > performance > > > for one class of plausible workloads while greatly slowing a different > > > class > > >

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Fabien COELHO
Hello Noah, The usual PostgreSQL handling of a deeply workload-dependent performance feature is to disable it by default. That's what I'm inclined to do here, for every GUC the feature added. Sophisticated users will nonetheless fully exploit this valuable mechanism in 9.6. I don't think

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Noah Misch
On Thu, Jun 02, 2016 at 11:09:22PM -0700, Andres Freund wrote: > On 2016-06-03 01:57:33 -0400, Noah Misch wrote: > > > Which means that transactional workloads that are bigger than the OS > > > memory, or which have a non-uniform distribution leading to some > > > locality, are likely to be

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-03 Thread Andres Freund
On 2016-06-03 01:57:33 -0400, Noah Misch wrote: > > Which means that transactional workloads that are bigger than the OS > > memory, or which have a non-uniform distribution leading to some > > locality, are likely to be faster. In practice those are *hugely* more > > likely than the uniform

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-02 Thread Noah Misch
On Wed, Jun 01, 2016 at 03:33:18PM -0700, Andres Freund wrote: > On 2016-05-31 16:03:46 -0400, Robert Haas wrote: > > On Fri, May 27, 2016 at 12:37 AM, Andres Freund wrote: > > > I don't think the situation is quite that simple. By *disabling* backend > > > flushing it's also

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-01 Thread Andres Freund
On 2016-06-01 15:33:18 -0700, Andres Freund wrote: > Cpu: i7-6820HQ > Ram: 24GB of memory > Storage: Samsung SSD 850 PRO 1TB, encrypted > postgres -c shared_buffers=6GB -c backend_flush_after=128 -c > max_wal_size=100GB -c fsync=on -c synchronous_commit=off > pgbench -M prepared -c 16 -j 16 -T

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-01 Thread Andres Freund
On 2016-05-31 16:03:46 -0400, Robert Haas wrote: > On Fri, May 27, 2016 at 12:37 AM, Andres Freund wrote: > > I don't think the situation is quite that simple. By *disabling* backend > > flushing it's also easy to see massive performance regressions. In > > situations where

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-31 Thread Robert Haas
On Fri, May 27, 2016 at 12:37 AM, Andres Freund wrote: > I don't think the situation is quite that simple. By *disabling* backend > flushing it's also easy to see massive performance regressions. In > situations where shared buffers was configured appropriately for the

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-28 Thread Noah Misch
On Thu, May 12, 2016 at 10:49:06AM -0400, Robert Haas wrote: > On Thu, May 12, 2016 at 8:39 AM, Ashutosh Sharma > wrote: > > Please find the test results for the following set of combinations taken at > > 128 client counts: > > > > 1) Unpatched master, default

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-26 Thread Andres Freund
Hi, On May 26, 2016 9:29:51 PM PDT, Ashutosh Sharma wrote: >Hi All, > >As we have seen the regression of more than 45% with >"*backend_flush_after*" >enabled and set to its default value i.e. 128KB or even when it is set >to >some higher value like 2MB, i think we should

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-26 Thread Ashutosh Sharma
Hi All, As we have seen the regression of more than 45% with "*backend_flush_after*" enabled and set to its default value i.e. 128KB or even when it is set to some higher value like 2MB, i think we should disable it such that it does not impact the read write performance and here is the attached

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-14 Thread Fabien COELHO
These raw tps suggest that {backend,bgwriter}_flush_after should better be zero for this kind of load.Whether it should be the default is unclear yet, because as Andres pointed out this is one kind of load. FWIW, I don't think {backend,bgwriter} are the same here. It's primarily backend that

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-14 Thread Andres Freund
On 2016-05-14 18:49:27 +0200, Fabien COELHO wrote: > > Hello, > > > Please find the results for the following 3 scenarios with unpatched master: > > > > 1. Default settings for *_flush_after : TPS = *10677.662356* > > 2. backend_flush_after=0, rest defaults : TPS = *18452.655936* > > 3.

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-14 Thread Fabien COELHO
Hello, Please find the results for the following 3 scenarios with unpatched master: 1. Default settings for *_flush_after : TPS = *10677.662356* 2. backend_flush_after=0, rest defaults : TPS = *18452.655936* 3. backend_flush_after=0, bgwriter_flush_after=0, wal_writer_flush_after=0,

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-14 Thread Ashutosh Sharma
Hi, Please find the results for the following 3 scenarios with unpatched master: 1. Default settings for *_flush_after : TPS = *10677.662356* 2. backend_flush_after=0, rest defaults : TPS = *18452.655936* 3. backend_flush_after=0, bgwriter_flush_after=0, wal_writer_flush_after=0,

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-13 Thread Amit Kapila
On Fri, May 13, 2016 at 11:13 PM, Andres Freund wrote: > > On 2016-05-13 10:20:04 -0400, Robert Haas wrote: > > On Fri, May 13, 2016 at 7:08 AM, Ashutosh Sharma wrote: > > > Following are the performance results for read write test observed with > > >

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-13 Thread Andres Freund
On 2016-05-13 14:43:15 -0400, Robert Haas wrote: > On Fri, May 13, 2016 at 1:43 PM, Andres Freund wrote: > > I just want to emphasize what we're discussing here is a bit of an > > extreme setup. A workload that's bigger than shared buffers, but smaller > > than the OS's cache

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-13 Thread Robert Haas
On Fri, May 13, 2016 at 1:43 PM, Andres Freund wrote: > On 2016-05-13 10:20:04 -0400, Robert Haas wrote: >> On Fri, May 13, 2016 at 7:08 AM, Ashutosh Sharma >> wrote: >> > Following are the performance results for read write test observed with >> >

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-13 Thread Andres Freund
On 2016-05-13 10:20:04 -0400, Robert Haas wrote: > On Fri, May 13, 2016 at 7:08 AM, Ashutosh Sharma > wrote: > > Following are the performance results for read write test observed with > > different numbers of "backend_flush_after". > > > > 1) backend_flush_after = 256kb

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-13 Thread Robert Haas
On Fri, May 13, 2016 at 7:08 AM, Ashutosh Sharma wrote: > Following are the performance results for read write test observed with > different numbers of "backend_flush_after". > > 1) backend_flush_after = 256kb (32*8kb), tps = 10841.178815 > 2) backend_flush_after = 512kb

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-13 Thread Ashutosh Sharma
Hi, Following are the performance results for read write test observed with different numbers of "*backend_flush_after*". 1) backend_flush_after = *256kb* (32*8kb), tps = *10841.178815* 2) backend_flush_after = *512kb* (64*8kb), tps = *11098.702707* 3) backend_flush_after = *1MB* (128*8kb), tps

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-12 Thread Fabien COELHO
I'm getting increasingly unhappy about the checkpoint flush control. I saw major regressions on my parallel COPY test, too: Yes, I'm concerned too. A few thoughts: - focussing on raw tps is not a good idea, because it may be a lot of tps followed by a sync panic, with an unresponsive

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-12 Thread Andres Freund
On 2016-05-12 10:49:06 -0400, Robert Haas wrote: > On Thu, May 12, 2016 at 8:39 AM, Ashutosh Sharma > wrote: > > Please find the test results for the following set of combinations taken at > > 128 client counts: > > > > 1) Unpatched master, default *_flush_after : TPS =

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-12 Thread Andres Freund
On 2016-05-12 11:27:31 -0400, Robert Haas wrote: > On Thu, May 12, 2016 at 11:13 AM, Andres Freund wrote: > > Could you run this one with a number of different backend_flush_after > > settings? I'm suspsecting the primary issue is that the default is too low. > > What values

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-12 Thread Robert Haas
On Thu, May 12, 2016 at 11:13 AM, Andres Freund wrote: > Could you run this one with a number of different backend_flush_after > settings? I'm suspsecting the primary issue is that the default is too low. What values do you think would be good to test? Maybe provide 3 or 4

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-12 Thread Andres Freund
Hi, On 2016-05-12 18:09:07 +0530, Ashutosh Sharma wrote: > Please find the test results for the following set of combinations taken at > 128 client counts: Thanks. > *1)* *Unpatched master, default *_flush_after :* TPS = 10925.882396 Could you run this one with a number of different

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-12 Thread Robert Haas
On Thu, May 12, 2016 at 8:39 AM, Ashutosh Sharma wrote: > Please find the test results for the following set of combinations taken at > 128 client counts: > > 1) Unpatched master, default *_flush_after : TPS = 10925.882396 > > 2) Unpatched master, *_flush_after=0 : TPS =

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-12 Thread Ashutosh Sharma
Hi, Please find the test results for the following set of combinations taken at 128 client counts: *1)* *Unpatched master, default *_flush_after :* TPS = 10925.882396 *2) Unpatched master, *_flush_after=0 :* TPS = 18613.343529 *3)* *That line removed with #if 0, default *_flush_after :* TPS

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-11 Thread Robert Haas
On Wed, May 11, 2016 at 12:51 AM, Ashutosh Sharma wrote: > I am extremely sorry for the delayed response. As suggested by you, I have > taken the performance readings at 128 client counts after making the > following two changes: > > 1). Removed

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-10 Thread Ashutosh Sharma
Hi Andres, I am extremely sorry for the delayed response. As suggested by you, I have taken the performance readings at 128 client counts after making the following two changes: *1).* Removed AddWaitEventToSet(FeBeWaitSet, WL_POSTMASTER_DEATH, -1, NULL, NULL); from pq_init(). Below is the git

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-10 Thread Andres Freund
Hi, On 2016-05-06 21:21:11 +0530, Mithun Cy wrote: > I will try to run the tests as you have suggested and will report the same. Any news on that front? Regards, Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-06 Thread Mithun Cy
On Fri, May 6, 2016 at 8:35 PM, Andres Freund wrote: > Also, do you see read-only workloads to be affected too? Thanks, I have not tested with above specific commitid which reported performance issue but At HEAD commit 72a98a639574d2e25ed94652848555900c81a799 Author: Andres

Re: [HACKERS] Perf Benchmarking and regression.

2016-05-06 Thread Andres Freund
Hi, Thanks for benchmarking! On 2016-05-06 19:43:52 +0530, Mithun Cy wrote: > 1. # first bad commit: [ac1d7945f866b1928c2554c0f80fd52d7f92] Make idle > backends exit if the postmaster dies. > this made performance to drop from > > 15947.21546 (15K +) to 13409.758510 (arround 13K+). Let's