Re: Migrated from 8.3 to 9.0 - need to update config (re-post) - Mailing list pgsql-performance
| From | Carlo Stonebanks |
|---|---|
| Subject | Re: Migrated from 8.3 to 9.0 - need to update config (re-post) |
| Date | |
| Msg-id | BLU153-W39A8E3D61C95ACC02BD73596070@phx.gbl Whole thread Raw |
| In response to | Re: Migrated from 8.3 to 9.0 - need to update config (re-post) (Craig Ringer <ringerc@ringerc.id.au>) |
| List | pgsql-performance |
Thanks guys,
So, would you say that transaction pooling has a load-balancing effect because of its granularity compared to session pooling?
I'm concerned about the side-effects of transaction pooling, like the sessiion-level features we would always have to look out for. Wouldn't this require a code review? Just reading UDF Session State=No on this page got my attention: http://wiki.postgresql.org/wiki/PgBouncer
If we go with transaction pooling, will we get any sort of warnings or exceptions when apps and stored pgUDF's are violating transaction pooling features, or will things just quietly go wrong, with one session getting a side-effect from another session's state?
Carlo
So, would you say that transaction pooling has a load-balancing effect because of its granularity compared to session pooling?
I'm concerned about the side-effects of transaction pooling, like the sessiion-level features we would always have to look out for. Wouldn't this require a code review? Just reading UDF Session State=No on this page got my attention: http://wiki.postgresql.org/wiki/PgBouncer
If we go with transaction pooling, will we get any sort of warnings or exceptions when apps and stored pgUDF's are violating transaction pooling features, or will things just quietly go wrong, with one session getting a side-effect from another session's state?
Carlo
> Date: Wed, 14 Sep 2011 09:52:07 +0800
> From: ringerc@ringerc.id.au
> To: stonec.register@sympatico.ca
> CC: kevin.grittner@wicourts.gov; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Migrated from 8.3 to 9.0 - need to update config (re-post)
>
> On 09/14/2011 02:56 AM, Carlo Stonebanks wrote:
>
> > Even for 300 stateful applications that can remain connected for up to a
> > week, continuously distilling data (imports)?
>
> If they're all doing active work all that time you can still benefit
> from a pooler.
>
> Say your server can service 50 connections at optimum speed, and any
> more result in reduced overall throughput. You have 300 apps with
> statements they want to run. Your pooler will basically queue them, so
> at any one time 50 are doing work and 250 are waiting for database
> access. This should _improve_ database throughput by reducing contention
> if 50 worker connections is your sweet spot. However, it will also
> increase latency for service for those workers because they may have to
> wait a while before their transaction runs, even though their
> transaction will complete much faster.
>
> You'd probably want to pool at the transaction level, so once a client
> gets a connection it keeps it for the lifetime of that transaction and
> the connection is handed back to the pool when the transaction commits
> or rolls back.
>
> >> 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? Since the sys admin thinks there's no
> > performance benefit from this, I would like to be clear on why we should
> > do this.
>
> fsync!
>
> If your workload is read-only, it won't help you much. If your workload
> is write-heavy or fairly balanced it'll make a HUGE difference, because
> fsync() on commit won't have to wait for disk I/O, only I/O to the RAID
> card's cache controller.
>
> You can also play with commit_delay and synchronous_commit to trade
> guarantees of data persistence off against performance. Don't mind
> losing up to 5 mins of commits if you lose power? These options are for you.
>
> Whatever you do, do NOT set fsync=off. It should be called "Eat my data
> if anything goes even slightly wrong=on"; it does have legitimate uses,
> but they're not yours.
>
> >> > Can our particular setup benefit from changing the bgwriter values?
> >> Probably not. If you find that your interactive users have periods
> >> where queries seem to "freeze" for a few minutes at a time and then
> >> return to normal levels of performance, you might need to make this
> >> more aggressive.
> >
> > We actually experience this. Once again, remember the overwhelming use
> > of the system is long-running import threads with continuous
> > connections. 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).
>
> This could definitely be checkpointing issues. Enable checkpoint logging.
>
> > What would the bg_writer settings be in this case?
>
> You need to tune it for your workload I'm afraid. See the manual and
> mailing list discussions.
>
> --
> Craig Ringer
> From: ringerc@ringerc.id.au
> To: stonec.register@sympatico.ca
> CC: kevin.grittner@wicourts.gov; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Migrated from 8.3 to 9.0 - need to update config (re-post)
>
> On 09/14/2011 02:56 AM, Carlo Stonebanks wrote:
>
> > Even for 300 stateful applications that can remain connected for up to a
> > week, continuously distilling data (imports)?
>
> If they're all doing active work all that time you can still benefit
> from a pooler.
>
> Say your server can service 50 connections at optimum speed, and any
> more result in reduced overall throughput. You have 300 apps with
> statements they want to run. Your pooler will basically queue them, so
> at any one time 50 are doing work and 250 are waiting for database
> access. This should _improve_ database throughput by reducing contention
> if 50 worker connections is your sweet spot. However, it will also
> increase latency for service for those workers because they may have to
> wait a while before their transaction runs, even though their
> transaction will complete much faster.
>
> You'd probably want to pool at the transaction level, so once a client
> gets a connection it keeps it for the lifetime of that transaction and
> the connection is handed back to the pool when the transaction commits
> or rolls back.
>
> >> 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? Since the sys admin thinks there's no
> > performance benefit from this, I would like to be clear on why we should
> > do this.
>
> fsync!
>
> If your workload is read-only, it won't help you much. If your workload
> is write-heavy or fairly balanced it'll make a HUGE difference, because
> fsync() on commit won't have to wait for disk I/O, only I/O to the RAID
> card's cache controller.
>
> You can also play with commit_delay and synchronous_commit to trade
> guarantees of data persistence off against performance. Don't mind
> losing up to 5 mins of commits if you lose power? These options are for you.
>
> Whatever you do, do NOT set fsync=off. It should be called "Eat my data
> if anything goes even slightly wrong=on"; it does have legitimate uses,
> but they're not yours.
>
> >> > Can our particular setup benefit from changing the bgwriter values?
> >> Probably not. If you find that your interactive users have periods
> >> where queries seem to "freeze" for a few minutes at a time and then
> >> return to normal levels of performance, you might need to make this
> >> more aggressive.
> >
> > We actually experience this. Once again, remember the overwhelming use
> > of the system is long-running import threads with continuous
> > connections. 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).
>
> This could definitely be checkpointing issues. Enable checkpoint logging.
>
> > What would the bg_writer settings be in this case?
>
> You need to tune it for your workload I'm afraid. See the manual and
> mailing list discussions.
>
> --
> Craig Ringer
pgsql-performance by date: