Re: [PERFORM] SSI slows down over time

On 14/04/2014 4:30 PM, Ryan Johnson wrote: FYI, here's a plot of performance over time. Each point in the graph is throughput (in tps) over a 10-second measurement (~20 minutes total), against a 12 WH TPC-C dataset with 24 clients banging on

Re: [PERFORM] Tuning Postgres for Single connection use

Terrific -- thanks Gavin and Jeff! That's incredibly helpful for a n00b like me! On Mon, Apr 14, 2014 at 5:29 PM, Gavin Flower wrote: > In this list, please bottom post! > > I've added potentially useful advice below. > > > On 15/04/14 11:39, Nick Eubank wrote: > > Thanks Gavin -- would LOVE t

Re: [PERFORM] Tuning Postgres for Single connection use

In this list, please bottom post! I've added potentially useful advice below. On 15/04/14 11:39, Nick Eubank wrote: Thanks Gavin -- would LOVE to. Sadly I'm in a weird situation where my hardware is not under my control, so I'm stuck making the best of what I have. Next time though! :) On Mo

Re: [PERFORM] Tuning Postgres for Single connection use

On Mon, Apr 14, 2014 at 2:46 PM, Nick Eubank wrote: > Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, > etc. for a database that DOESN'T anticipate concurrent connections and that > is doing lots of aggregate functions on large tables? All the advice I > can find online on t

Re: [PERFORM] Tuning Postgres for Single connection use

On 04/14/2014 05:46 PM, Nick Eubank wrote: Any rules of thumb for |work_mem|, |maintenance_work_mem|, |shared_buffer|, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions on large tables? All the advice I can find online on tuning

Re: [PERFORM] Tuning Postgres for Single connection use

Thanks Gavin -- would LOVE to. Sadly I'm in a weird situation where my hardware is not under my control, so I'm stuck making the best of what I have. Next time though! :) On Monday, April 14, 2014, Gavin Flower wrote: > On 15/04/14 09:46, Nick Eubank wrote: > > Any rules of thumb for work_mem,

Re: [PERFORM] Tuning Postgres for Single connection use

On 15/04/14 09:46, Nick Eubank wrote: Any rules of thumb for |work_mem|, |maintenance_work_mem|, |shared_buffer|, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions on large tables? All the advice I can find online on tuning (this

[PERFORM] HFS+ pg_test_fsync performance

I was given anecdotal information regarding HFS+ performance under OSX as being unsuitable for production PG deployments and that pg_test_fsync could be used to measure the relative speed versus other operating systems (such as Linux). In my performance lab, I have a number of similarly equipped Li

[PERFORM] Tuning Postgres for Single connection use

Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions on large tables? All the advice I can find online on tuning (this

Re: [PERFORM] SSI slows down over time

On 14/04/2014 10:14 AM, Kevin Grittner wrote: Ryan Johnson wrote: every time I shut down a database and bring it back up, SSI seems to go slower. There's one thing to rule out up front -- that would be a long-lived prepared transacti

Re: [PERFORM] Getting query plan alternatives from query planner?

On 04/14/2014 09:36 PM, Stefan Keller wrote: Who's the elephant in the room who is reluctant to introduce explicit hints? Please read some of the previous discussions on this. Like this, in this very same thread: http://www.postgresql.org/message-id/15381.1395410...@sss.pgh.pa.us I'd like t

Re: [PERFORM] Getting query plan alternatives from query planner?

Hi Craig and Shawn I fully agree with your argumentation. Who's the elephant in the room who is reluctant to introduce explicit hints? -S. 2014-04-14 17:35 GMT+02:00 Craig James : > Shaun Thomas wrote: > >> >>> these issues tend to get solved through optimization fences. Reorganize a qu

Re: [PERFORM] Checkpoint distribution

On Mon, Apr 14, 2014 at 9:42 AM, Borodin Vladimir wrote: > 14 апр. 2014 г., в 19:11, Jeff Janes написал(а): > > > During the writing phase of the checkpoint, PostgreSQL passes the dirty > data to the OS. At the end, it then tells the OS to make sure that that > data has actually reached disk.

Re: [PERFORM] Checkpoint distribution

14 апр. 2014 г., в 19:11, Jeff Janes написал(а): > On Mon, Apr 14, 2014 at 2:46 AM, Borodin Vladimir wrote: > Hi all. > > I’m running PostgreSQL 9.3.4 and doing stress test of the database with > writing only load. The test plan does 1000 transactions per second (each of > them does several u

[PERFORM] Approach to Data Summary and Analysis

I have several related tables that represent a call state. Let's think of these as phone calls to simplify things. Sometimes I need to determine the last time a user was called, the last time a user answered a call, or the last time a user completed a call. The basic schema is something like this:

Re: [PERFORM] SSI slows down over time

Ryan Johnson wrote: > what factors might cause a prepared transaction to exist in the > first place? As part of a "distributed transaction" using "two phase commit" a PREPARE TRANSACTION statement would have had to run against PostgreSQL: http://www.postgresql.org/docs/current/interactive/sql-p

Re: [PERFORM] Getting query plan alternatives from query planner?

Shaun Thomas wrote: > >> these issues tend to get solved through optimization fences. >>> Reorganize a query into a CTE, or use the (gross) OFFSET 0 trick. >>> How are these nothing other than unofficial hints? >>> >> Yeah, the cognitive dissonance levels get pretty high around this >> issue. S

Re: [PERFORM] Checkpoint distribution

On Mon, Apr 14, 2014 at 2:46 AM, Borodin Vladimir wrote: > Hi all. > > I’m running PostgreSQL 9.3.4 and doing stress test of the database with > writing only load. The test plan does 1000 transactions per second (each of > them does several updates/inserts). The problem is that checkpoint is not

Re: [PERFORM] Getting query plan alternatives from query planner?

I don't know how anyone else feels about this, as I don't think I've seen this ever suggested, but my ideal would be a way to configure the database to recognize specific queries and to have a way of influencing its plan choice for that query. I'm intentionally wording that last part vaguely, a

Re: [PERFORM] Getting query plan alternatives from query planner?

Shaun Thomas wrote: > these issues tend to get solved through optimization fences. > Reorganize a query into a CTE, or use the (gross) OFFSET 0 trick. > How are these nothing other than unofficial hints? Yeah, the cognitive dissonance levels get pretty high around this issue. Some of the same p

Re: [PERFORM] SSI slows down over time

On 14/04/2014 10:14 AM, Kevin Grittner wrote: Ryan Johnson wrote: every time I shut down a database and bring it back up, SSI seems to go slower. There's one thing to rule out up front -- that would be a long-lived prepared transaction. Please post the output of these queries: select versio

Re: [PERFORM] SSI slows down over time

Ryan Johnson wrote: > every time I shut down a database and bring it back up, SSI seems > to go slower. There's one thing to rule out up front -- that would be a long-lived prepared transaction. Please post the output of these queries: select version(); show max_prepared_transactions; select *

Re: [PERFORM] Ye olde slow query

Sorry for the delay; back on this, and thanks for the response. On Mar 11, 2014, at 6:23 PM, Tom Lane wrote: > "Murphy, Kevin" writes: >> Synopsis: 8-table join with one "WHERE foo IN (...)" condition; works OK >> with fewer >> than 5 items in the IN list, but at N=5, the planner starts using a

Re: [PERFORM] SSI slows down over time

On 09/04/2014 5:21 PM, Bruce Momjian wrote: On Mon, Apr 7, 2014 at 10:38:52AM -0400, Ryan Johnson wrote: The two * entries were produced by runs under SI, and confirm that the rest of the system has not been slowing down nearly as much as SSI. SI throughput dropped by 5% as the database quadrup

[PERFORM] SSI slows down over time

Hi all, (Referred here from pgsql-performance) tl;dr: every time I shut down a database and bring it back up, SSI seems to go slower. In order to avoid thousands of SSI aborts due to running out of shared memory, I've had to set max_predicate_locks to several thousand (2000 is tolerable, 8000