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. A

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 9:19 AM, Andres Freund wrote: >> >> > On 2

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 -0700, Andres Freund wrote: > >> >> I'm wr

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 now, planning to post it later today, commi

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. > > -/* see b

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 */ -DEFAULT_BACKEND_FLUSH_AFTER

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 -0700 Subject: [PATCH] Ch

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 results for the following

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 > > > at > > > 128 client coun

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 do

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 > >> > disservice for the maj

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 going up. > > > > I thin

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 disregarding that: Ma

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 scenario. > > > > The "rough

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 mostly avoids it? It's >

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 of this change). I seem > >> to recall you y

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 opposed to reading i

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 being quite passionately arguing that the linux

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 RAM-constrained system, t

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 under

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 be faster. In practice

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 c

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 faster.

Re: [HACKERS] Perf Benchmarking and regression.

2016-06-02 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 distri

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 easy to see massive

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 520

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 shared buffers was c

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 workload > (not the case

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 *_flush_after : TPS = 10925.882396

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 disable it such that it

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 p

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 m

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. backend_

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, checkpoint

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, checkpoint_flush

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 > > > different numbers of "backend_flush_after". >

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 size; with a noticeab

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 >> > different numbers of "backend_flush_after". >>

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 (32*8kb), tps = 10841.178

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 (64*8kb), tps = 11098.702

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 da

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 = 10925.882396 > > > > 2) U

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 do you think would b

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 suggested values to t

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 backend_f

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 = 18613.343529 > > 3) That

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 AddWaitEventToSet(FeBeWaitSet, WL_POSTMASTER_DEATH,

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 di

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: http://www.post

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 Freund Date: Tue Apr

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 de

[HACKERS] Perf Benchmarking and regression.

2016-05-06 Thread Mithun Cy
I tried to do some benchmarking on postgres master head commit 72a98a639574d2e25ed94652848555900c81a799 Author: Andres Freund Date: Tue Apr 26 20:32:51 2016 -0700 CASE : Read-Write Tests when data exceeds shared buffers. Non Default settings and test ./postgres -c shared_buffers=8GB -N 200 -c