Обсуждение: Re: Migrated from 8.3 to 9.0 - need to update config (re-post)
Carlo Stonebanks wrote: >> max_connections = 300 > Too high. Both throughput and latency should improve with correct > use of a connection pooler. > Even for 300 stateful applications that can remain connected for > up to a week, continuously distilling data (imports)? Absolutely. A good connection pooler will be able to hold those 300 *client* connections, and maintain a much smaller set of connections to the database. It will notice when a client connection is requesting the start of a database transaction. If there is an idle database connection it will route the requests there; otherwise it will put that client connection in a queue. When a database transaction is committed, a waiting client connection (if any) will be assigned to its database connection. Every benchmark I've seen shows that this will improve both throughput and latency over the approach of releasing a "thundering herd" of requests against the server. Picture a meat counter with four butchers behind it, and few spinning devices to slice meat. If customers queue up, and the butchers call on people as they are ready, things go better than if each butcher tries to take on one- fourth of the customers at a time and constantly switch between one order and another to try to make incremental progress on all of them. > a sys admin raised it from 100 when multiple large projects were > loaded and the server refused the additional connections. Whoever is making these decisions needs more training. I suggest Greg Smith's book: http://www.postgresql.org/docs/books/ (Full disclosure, I was a technical reviewer of the book and got a free copy.) > you want the controller configured for write-back (with automatic > switch to write-through on low or failed battery, if possible). For performance or safety reasons? You get better performance with write-back. If you can't rely on the battery, then write-back is not safe and you need to use write- through. > Since the sys admin thinks there's no performance benefit from > this, I would like to be clear on why we should do this. If you can get him to change it back and forth for performance testing, it is easy enough to prove. Write a client application which inserts on row per database transaction. A nice, simple, short row -- like containing one integer column with no indexes. Have the external application create the table and do a million inserts. Try this with both cache settings. It's best not to issue a BEGIN and COMMIT at all. Don't loop in a function or a DO block, because that creates an implicit transaction. > Every now and then the imports behave as if they are suddenly > taking a deep breath, slowing down. Sometimes, so much we cancel > the import and restart (the imports pick up where they left off). > > What would the bg_writer settings be in this case? I'm not sure what that is based on information so far, so it's unclear whether background writer settings would help; but on the face of it my bet would be that it's a context switching storm or swapping, and the connection pool would be the better solution. Those poor butchers are just overwhelmed.... -Kevin
Ok, connection pooler it is. As I understand it, even if there are no idle connections available we'll get the benefit of putting a turnstile on the butcher's door.
> Date: Tue, 13 Sep 2011 16:13:00 -0500
> From: Kevin.Grittner@wicourts.gov
> To: pgsql-performance@postgresql.org; stonec.register@sympatico.ca
> Subject: RE: [PERFORM] Migrated from 8.3 to 9.0 - need to update config (re-post)
>
> Carlo Stonebanks wrote:
>
> >> max_connections = 300
> > Too high. Both throughput and latency should improve with correct
> > use of a connection pooler.
>
> > Even for 300 stateful applications that can remain connected for
> > up to a week, continuously distilling data (imports)?
>
> Absolutely.
>
> A good connection pooler will be able to hold those 300 *client*
> connections, and maintain a much smaller set of connections to the
> database. It will notice when a client connection is requesting the
> start of a database transaction. If there is an idle database
> connection it will route the requests there; otherwise it will put
> that client connection in a queue. When a database transaction is
> committed, a waiting client connection (if any) will be assigned to
> its database connection.
>
> Every benchmark I've seen shows that this will improve both
> throughput and latency over the approach of releasing a "thundering
> herd" of requests against the server. Picture a meat counter with
> four butchers behind it, and few spinning devices to slice meat.
> If customers queue up, and the butchers call on people as they are
> ready, things go better than if each butcher tries to take on one-
> fourth of the customers at a time and constantly switch between one
> order and another to try to make incremental progress on all of
> them.
>
> > a sys admin raised it from 100 when multiple large projects were
> > loaded and the server refused the additional connections.
>
> Whoever is making these decisions needs more training. I suggest
> Greg Smith's book:
>
> http://www.postgresql.org/docs/books/
>
> (Full disclosure, I was a technical reviewer of the book and got a
> free copy.)
>
> > you want the controller configured for write-back (with automatic
> > switch to write-through on low or failed battery, if possible).
>
> For performance or safety reasons?
>
> You get better performance with write-back. If you can't rely on
> the battery, then write-back is not safe and you need to use write-
> through.
>
> > Since the sys admin thinks there's no performance benefit from
> > this, I would like to be clear on why we should do this.
>
> If you can get him to change it back and forth for performance
> testing, it is easy enough to prove. Write a client application
> which inserts on row per database transaction. A nice, simple,
> short row -- like containing one integer column with no indexes.
> Have the external application create the table and do a million
> inserts. Try this with both cache settings. It's best not to
> issue a BEGIN and COMMIT at all. Don't loop in a function or a DO
> block, because that creates an implicit transaction.
>
> > Every now and then the imports behave as if they are suddenly
> > taking a deep breath, slowing down. Sometimes, so much we cancel
> > the import and restart (the imports pick up where they left off).
> >
> > What would the bg_writer settings be in this case?
>
> I'm not sure what that is based on information so far, so it's
> unclear whether background writer settings would help; but on the
> face of it my bet would be that it's a context switching storm or
> swapping, and the connection pool would be the better solution.
> Those poor butchers are just overwhelmed....
>
> -Kevin
>
>
I also ordered the book as soon as you mentioned - the title alone was enough to sell me on it! The book won't be for the errant sys admin who increased the connections, it's for me - I'll use it to whack the sys admin on the head. Thanks fo rthe tip, the author owes you a beer - as do I.
Will the book recommend any particular connection pooler product, or is it inappropriate to ask for a recommendation on the forum?
Carlo
> From: Kevin.Grittner@wicourts.gov
> To: pgsql-performance@postgresql.org; stonec.register@sympatico.ca
> Subject: RE: [PERFORM] Migrated from 8.3 to 9.0 - need to update config (re-post)
>
> Carlo Stonebanks wrote:
>
> >> max_connections = 300
> > Too high. Both throughput and latency should improve with correct
> > use of a connection pooler.
>
> > Even for 300 stateful applications that can remain connected for
> > up to a week, continuously distilling data (imports)?
>
> Absolutely.
>
> A good connection pooler will be able to hold those 300 *client*
> connections, and maintain a much smaller set of connections to the
> database. It will notice when a client connection is requesting the
> start of a database transaction. If there is an idle database
> connection it will route the requests there; otherwise it will put
> that client connection in a queue. When a database transaction is
> committed, a waiting client connection (if any) will be assigned to
> its database connection.
>
> Every benchmark I've seen shows that this will improve both
> throughput and latency over the approach of releasing a "thundering
> herd" of requests against the server. Picture a meat counter with
> four butchers behind it, and few spinning devices to slice meat.
> If customers queue up, and the butchers call on people as they are
> ready, things go better than if each butcher tries to take on one-
> fourth of the customers at a time and constantly switch between one
> order and another to try to make incremental progress on all of
> them.
>
> > a sys admin raised it from 100 when multiple large projects were
> > loaded and the server refused the additional connections.
>
> Whoever is making these decisions needs more training. I suggest
> Greg Smith's book:
>
> http://www.postgresql.org/docs/books/
>
> (Full disclosure, I was a technical reviewer of the book and got a
> free copy.)
>
> > you want the controller configured for write-back (with automatic
> > switch to write-through on low or failed battery, if possible).
>
> For performance or safety reasons?
>
> You get better performance with write-back. If you can't rely on
> the battery, then write-back is not safe and you need to use write-
> through.
>
> > Since the sys admin thinks there's no performance benefit from
> > this, I would like to be clear on why we should do this.
>
> If you can get him to change it back and forth for performance
> testing, it is easy enough to prove. Write a client application
> which inserts on row per database transaction. A nice, simple,
> short row -- like containing one integer column with no indexes.
> Have the external application create the table and do a million
> inserts. Try this with both cache settings. It's best not to
> issue a BEGIN and COMMIT at all. Don't loop in a function or a DO
> block, because that creates an implicit transaction.
>
> > Every now and then the imports behave as if they are suddenly
> > taking a deep breath, slowing down. Sometimes, so much we cancel
> > the import and restart (the imports pick up where they left off).
> >
> > What would the bg_writer settings be in this case?
>
> I'm not sure what that is based on information so far, so it's
> unclear whether background writer settings would help; but on the
> face of it my bet would be that it's a context switching storm or
> swapping, and the connection pool would be the better solution.
> Those poor butchers are just overwhelmed....
>
> -Kevin
>
>
From: Carlo Stonebanks [mailto:stonec.register@sympatico.ca] Sent: Tuesday, September 13, 2011 9:27 PM To: Performance support Postgresql Subject: Re: Migrated from 8.3 to 9.0 - need to update config (re-post) ________________________________________ Ok, connection pooler it is. As I understand it, even if there are no idle connections available we'll get the benefit ofputting a turnstile on the butcher's door. I also ordered the book as soon as you mentioned - the title alone was enough to sell me on it! The book won't be for theerrant sys admin who increased the connections, it's for me - I'll use it to whack the sys admin on the head. Thanks forthe tip, the author owes you a beer - as do I. Will the book recommend any particular connection pooler product, or is it inappropriate to ask for a recommendation on theforum? Carlo I'd start with the pg_bouncer: very simple to setup, reliable, no "extra" functionality, which seems by your message youdon't need. Igor Neyman
On Wed, Sep 14, 2011 at 03:40:07PM -0400, Igor Neyman wrote: > > > From: Carlo Stonebanks [mailto:stonec.register@sympatico.ca] > Sent: Tuesday, September 13, 2011 9:27 PM > To: Performance support Postgresql > Subject: Re: Migrated from 8.3 to 9.0 - need to update config (re-post) > > > > ________________________________________ > Ok, connection pooler it is. As I understand it, even if there are no idle connections available we'll get the benefitof putting a turnstile on the butcher's door. > I also ordered the book as soon as you mentioned - the title alone was enough to sell me on it! The book won't be for theerrant sys admin who increased the connections, it's for me - I'll use it to whack the sys admin on the head. Thanks forthe tip, the author owes you a beer - as do I. > > Will the book recommend any particular connection pooler product, or is it inappropriate to ask for a recommendation onthe forum? > > Carlo > > > I'd start with the pg_bouncer: very simple to setup, reliable, no "extra" functionality, which seems by your message youdon't need. > > Igor Neyman +1 for pg_bouncer being easy to setup and use and being robust. We also use pgpool here but its is a much bigger beast and I suspect that you do not need its bells and whistles. Regards, Ken