Fixed xloginsert_locks for 9.4
I did a fair dive into double-checking the decision to just leave
xloginsert_locks fixed at 8 for 9.4. My conclusion: good call, move
along. Further improvements beyond what the 8-way split gives sure are
possible. But my guess from chasing them a little is that additional
places will pop up as things that must also be tweaked, before you'll
see those gains turn significant.
I'd like to see that box re-opened at one point. But if we do that, I'm
comfortable that could end with a xloginsert_locks that tunes itself
reasonably on large servers in the end, similar to wal_buffers. There's
nothing about this that makes feel like it needs a GUC. I barely needed
an exposed knob to do this evaluation.
= Baseline =
I rolled back a few commits to just before the GUC was removed and
tested against that point in git time. Starting with the 4 client test
case Heikki provided, the fastest runs on my 24 core server looked like
this:
tps = 56.691855 (including connections establishing)
Repeat runs do need to drop the table and rebuild, because eventually AV
kicks in on things in a big way, and then your test is toast until it's
done. Attached is what I settled on for a test harness. Nothing here
was so subtle I felt a more complicated harness was needed.
Standard practice for me is to give pgbench more workers when worrying
about any scalability tests. That gives a tiny improvement, to where
this is typical with 4 clients and 4 workers:
tps = 60.942537 (including connections establishing)
Increasing to 24 clients plus 24 workers gives roughly the same numbers,
suggesting that the bottleneck here is certainly not the client count,
and that the suggestion of 4 was high enough:
tps = 56.731581 (including connections establishing)
Decreasing xloginsert_locks to 1, so back to the original problem, the
rate normally looks like this instead:
tps = 25.384708 (including connections establishing)
So the big return you get just fine with the default tuning; great. I'm
happy to see it ship like this as good enough for 9.4.
= More locks =
For the next phase, I stuck to 24 clients and 24 workers. If I then
bump up xloginsert_locks to something much larger, there is an
additional small gain to be had. With 24 locks, so basically ever
client has their own, instead of 57-60 TPS, I managed to get as high as
this:
tps = 66.790968 (including connections establishing)
However, the minute I get into this territory, there's an obvious
bottleneck shift going on in there too. The rate of creating new
checkpoint segments becomes troublesome as one example, with messages
like this:
LOG: checkpoints are occurring too frequently (1 second apart)
HINT: Consider increasing the configuration parameter
"checkpoint_segments".
When 9.4 is already giving a more than 100% gain on this targeted test
case, I can't see that chasing after maybe an extra 10% is worth having
yet another GUC around. Especially when it will probably take multiple
tuning steps before you're done anyway; we don't really know the rest of
them yet; and when we do, we probably won't need a GUC to cope with them
in the end anyway.
--
Greg Smith greg.smith@crunchydatasolutions.com
Chief PostgreSQL Evangelist - http://crunchydatasolutions.com/
On Thu, Oct 2, 2014 at 5:08 PM, Greg Smith
<greg.smith@crunchydatasolutions.com> wrote:
When 9.4 is already giving a more than 100% gain on this targeted test case,
I can't see that chasing after maybe an extra 10% is worth having yet
another GUC around. Especially when it will probably take multiple tuning
steps before you're done anyway; we don't really know the rest of them yet;
and when we do, we probably won't need a GUC to cope with them in the end
anyway.
Agreed. I think that prior to 9.4, the logging performance of Postgres
was very competitive when compared to other systems. At this stage,
it's probably extremely fast by any standard. Amit's work on only
WAL-logging the modified portion of UPDATEs helps here too.
I tend to believe that the next big round of performance gains can be
had by working on the buffer manager, and B-Tree indexes. At some
point we should work on prefix compression within B-Tree leaf pages.
We should also work on adding "abbreviated keys" to B-Tree internal
pages. Doing so should almost remove the benefit of using the C
locale, because most comparisons needed for index scans can use
comparisons implemented as nothing more than a memcmp() (note that
internal pages have values that are naturally heterogeneous, so this
will work well).
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-10-02 20:08:33 -0400, Greg Smith wrote:
I did a fair dive into double-checking the decision to just leave
xloginsert_locks fixed at 8 for 9.4. My conclusion: good call, move along.
Further improvements beyond what the 8-way split gives sure are possible.
But my guess from chasing them a little is that additional places will pop
up as things that must also be tweaked, before you'll see those gains turn
significant.
Thanks for doing this.
I'd like to see that box re-opened at one point. But if we do that, I'm
comfortable that could end with a xloginsert_locks that tunes itself
reasonably on large servers in the end, similar to wal_buffers. There's
nothing about this that makes feel like it needs a GUC. I barely needed an
exposed knob to do this evaluation.= Baseline =
I rolled back a few commits to just before the GUC was removed and tested
against that point in git time. Starting with the 4 client test case Heikki
provided, the fastest runs on my 24 core server looked like this:tps = 56.691855 (including connections establishing)
Repeat runs do need to drop the table and rebuild, because eventually AV
kicks in on things in a big way, and then your test is toast until it's
done. Attached is what I settled on for a test harness. Nothing here was so
subtle I felt a more complicated harness was needed.Standard practice for me is to give pgbench more workers when worrying about
any scalability tests. That gives a tiny improvement, to where this is
typical with 4 clients and 4 workers:tps = 60.942537 (including connections establishing)
Increasing to 24 clients plus 24 workers gives roughly the same numbers,
suggesting that the bottleneck here is certainly not the client count, and
that the suggestion of 4 was high enough:tps = 56.731581 (including connections establishing)
Decreasing xloginsert_locks to 1, so back to the original problem, the rate
normally looks like this instead:tps = 25.384708 (including connections establishing)
So the big return you get just fine with the default tuning; great. I'm
happy to see it ship like this as good enough for 9.4.= More locks =
For the next phase, I stuck to 24 clients and 24 workers. If I then bump up
xloginsert_locks to something much larger, there is an additional small gain
to be had. With 24 locks, so basically ever client has their own, instead
of 57-60 TPS, I managed to get as high as this:tps = 66.790968 (including connections establishing)
However, the minute I get into this territory, there's an obvious bottleneck
shift going on in there too. The rate of creating new checkpoint segments
becomes troublesome as one example, with messages like this:LOG: checkpoints are occurring too frequently (1 second apart)
HINT: Consider increasing the configuration parameter
"checkpoint_segments".When 9.4 is already giving a more than 100% gain on this targeted test case,
I can't see that chasing after maybe an extra 10% is worth having yet
another GUC around. Especially when it will probably take multiple tuning
steps before you're done anyway; we don't really know the rest of them yet;
and when we do, we probably won't need a GUC to cope with them in the end
anyway.
I've modified the test slightly, by having the different backends insert
into different relations. Even on my measly 5 year old workstation I
*do* see quite a bit more than 10%.
psql -f /tmp/prepare.sql && pgbench -n -f /tmp/fooinsert.sql -c 64 -j 64 -T 10
on a 2x E5520 server (2 sockets a 4 cores a 2 threads)
with the following configuration:
-c shared_buffers=2GB
-c wal_level=hot_standby
-c full_page_writes=off
-c checkpoint_segments=400
-c fsync=off (io system here is abysmally bad)
-c synchronous_commit=off
#define NUM_XLOGINSERT_LOCKS 1
tps = 52.711939 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 8
tps = 286.496054 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 16
tps = 346.113313 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 24
tps = 363.242111 (including connections establishing)
I'd not be surprised at all if you'd see bigger influence on a system
with 4 sockets.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On 10/3/14, 8:26 AM, Andres Freund wrote:
#define NUM_XLOGINSERT_LOCKS 1
tps = 52.711939 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 8
tps = 286.496054 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 16
tps = 346.113313 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 24
tps = 363.242111 (including connections establishing)
Just to clarify: that 10% number I threw out was meant as a rough
estimate for a system with the default configuration, which is all that
I tested. It seemed like people would likely need to tune all the usual
things like checkpoint_segments, shared_buffers, etc. as well before
seeing much better. You did all that, and sure enough the gain went up;
thanks for confirming my guess.
I still don't think that means this needs a GUC for 9.4. Look at that
jump from 1 to 8. The low-hanging fruit here hasn't just been knocked
off. It's been blended into a frozen drink, poured into a glass, and
had a little paper umbrella put on top. I think that's enough for 9.4.
But, yes, let's see if we can add delivery to the side of the pool in
the next version too.
--
Greg Smith greg.smith@crunchydatasolutions.com
Chief PostgreSQL Evangelist - http://crunchydatasolutions.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-10-03 10:07:39 -0400, Gregory Smith wrote:
On 10/3/14, 8:26 AM, Andres Freund wrote:
#define NUM_XLOGINSERT_LOCKS 1
tps = 52.711939 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 8
tps = 286.496054 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 16
tps = 346.113313 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 24
tps = 363.242111 (including connections establishing)Just to clarify: that 10% number I threw out was meant as a rough estimate
for a system with the default configuration, which is all that I tested. It
seemed like people would likely need to tune all the usual things like
checkpoint_segments, shared_buffers, etc. as well before seeing much better.
You did all that, and sure enough the gain went up; thanks for confirming my
guess.I still don't think that means this needs a GUC for 9.4. Look at that jump
from 1 to 8. The low-hanging fruit here hasn't just been knocked off. It's
been blended into a frozen drink, poured into a glass, and had a little
paper umbrella put on top. I think that's enough for 9.4. But, yes, let's
see if we can add delivery to the side of the pool in the next version too.
So 25% performance on a relatively small machine improvements aren't
worth a GUC? That are likely to be larger on a bigger machine?
I utterly fail to see why that's a service to our users.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Oct 3, 2014 at 04:11:30PM +0200, Andres Freund wrote:
On 2014-10-03 10:07:39 -0400, Gregory Smith wrote:
On 10/3/14, 8:26 AM, Andres Freund wrote:
#define NUM_XLOGINSERT_LOCKS 1
tps = 52.711939 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 8
tps = 286.496054 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 16
tps = 346.113313 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 24
tps = 363.242111 (including connections establishing)Just to clarify: that 10% number I threw out was meant as a rough estimate
for a system with the default configuration, which is all that I tested. It
seemed like people would likely need to tune all the usual things like
checkpoint_segments, shared_buffers, etc. as well before seeing much better.
You did all that, and sure enough the gain went up; thanks for confirming my
guess.I still don't think that means this needs a GUC for 9.4. Look at that jump
from 1 to 8. The low-hanging fruit here hasn't just been knocked off. It's
been blended into a frozen drink, poured into a glass, and had a little
paper umbrella put on top. I think that's enough for 9.4. But, yes, let's
see if we can add delivery to the side of the pool in the next version too.So 25% performance on a relatively small machine improvements aren't
worth a GUC? That are likely to be larger on a bigger machine?I utterly fail to see why that's a service to our users.
Well, I think the issue is that having a GUC that can't reasonably be
tuned by 95% of our users is nearly useless. Few users are going to run
benchmarks to see what the optimal value is.
I remember Informix had a setting that had no description except "try
different values to see if it helps performance" --- we don't want to do
that.
What if we emit a server message if the setting is too low? That's how
we handle checkpoint_segments.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Oct 3, 2014 at 1:40 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Fri, Oct 3, 2014 at 04:11:30PM +0200, Andres Freund wrote:
On 2014-10-03 10:07:39 -0400, Gregory Smith wrote:
On 10/3/14, 8:26 AM, Andres Freund wrote:
#define NUM_XLOGINSERT_LOCKS 1
tps = 52.711939 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 8
tps = 286.496054 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 16
tps = 346.113313 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 24
tps = 363.242111 (including connections establishing)Just to clarify: that 10% number I threw out was meant as a rough
estimate
for a system with the default configuration, which is all that I
tested. It
seemed like people would likely need to tune all the usual things like
checkpoint_segments, shared_buffers, etc. as well before seeing muchbetter.
You did all that, and sure enough the gain went up; thanks for
confirming my
guess.
I still don't think that means this needs a GUC for 9.4. Look at that
jump
from 1 to 8. The low-hanging fruit here hasn't just been knocked
off. It's
been blended into a frozen drink, poured into a glass, and had a little
paper umbrella put on top. I think that's enough for 9.4. But, yes,let's
see if we can add delivery to the side of the pool in the next version
too.
So 25% performance on a relatively small machine improvements aren't
worth a GUC? That are likely to be larger on a bigger machine?I utterly fail to see why that's a service to our users.
Well, I think the issue is that having a GUC that can't reasonably be
tuned by 95% of our users is nearly useless. Few users are going to run
benchmarks to see what the optimal value is.I remember Informix had a setting that had no description except "try
different values to see if it helps performance" --- we don't want to do
that.What if we emit a server message if the setting is too low? That's how
we handle checkpoint_segments.--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com+ Everyone has their own god. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Not all GUC need to be straight forward to tune.
If the gains are worthy I don't see any reason not to have it.
On Fri, Oct 3, 2014 at 03:00:56PM -0300, Arthur Silva wrote:
I remember Informix had a setting that had no description except "try
different values to see if it helps performance" --- we don't want to do
that.What if we emit a server message if the setting is too low? That's how
we handle checkpoint_segments.Not all GUC need to be straight forward to tune.
If the gains are worthy I don't see any reason not to have it.
Every GUC add complexity to the system because people have to understand
it to know if they should tune it. No GUC is zero-cost.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Oct 3, 2014 at 02:07:45PM -0400, Bruce Momjian wrote:
On Fri, Oct 3, 2014 at 03:00:56PM -0300, Arthur Silva wrote:
I remember Informix had a setting that had no description except "try
different values to see if it helps performance" --- we don't want to do
that.What if we emit a server message if the setting is too low? That's how
we handle checkpoint_segments.Not all GUC need to be straight forward to tune.
If the gains are worthy I don't see any reason not to have it.Every GUC add complexity to the system because people have to understand
it to know if they should tune it. No GUC is zero-cost.
Please see my blog post about the cost of adding GUCs:
http://momjian.us/main/blogs/pgblog/2009.html#January_10_2009
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Oct 3, 2014 at 3:10 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Fri, Oct 3, 2014 at 02:07:45PM -0400, Bruce Momjian wrote:
On Fri, Oct 3, 2014 at 03:00:56PM -0300, Arthur Silva wrote:
I remember Informix had a setting that had no description except
"try
different values to see if it helps performance" --- we don't want
to do
that.
What if we emit a server message if the setting is too low?
That's how
we handle checkpoint_segments.
Not all GUC need to be straight forward to tune.
If the gains are worthy I don't see any reason not to have it.Every GUC add complexity to the system because people have to understand
it to know if they should tune it. No GUC is zero-cost.Please see my blog post about the cost of adding GUCs:
http://momjian.us/main/blogs/pgblog/2009.html#January_10_2009
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com+ Everyone has their own god. +
That's true Bruce (nice post, it was a good reading).
But how can we ignore 25%+ improvements (from 8 to 24)?
At very least we should delivery some pretty good defaults.
Bruce Momjian <bruce@momjian.us> writes:
On Fri, Oct 3, 2014 at 03:00:56PM -0300, Arthur Silva wrote:
Not all GUC need to be straight forward to tune.
If the gains are worthy I don't see any reason not to have it.
Every GUC add complexity to the system because people have to understand
it to know if they should tune it. No GUC is zero-cost.
In particular, the cost of putting this one back would be documenting
what it does and how to tune it. As mentioned upthread, we're not
following that Informix precedent ;-)
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Oct 3, 2014 at 03:30:35PM -0300, Arthur Silva wrote:
Every GUC add complexity to the system because people have to understand
it to know if they should tune it. No GUC is zero-cost.Please see my blog post about the cost of adding GUCs:
http://momjian.us/main/blogs/pgblog/2009.html#January_10_2009
That's true Bruce (nice post, it was a good reading).
But how can we ignore 25%+ improvements (from 8 to 24)?
At very least we should delivery some pretty good defaults.
Well, checkpoint_segments was a similar case where we couldn't give good
tuning advice so we went with a server log file warning if it needed to
be increased --- this might be a similar case.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 10/03/2014 09:42 PM, Bruce Momjian wrote:
On Fri, Oct 3, 2014 at 03:30:35PM -0300, Arthur Silva wrote:
Every GUC add complexity to the system because people have to understand
it to know if they should tune it. No GUC is zero-cost.Please see my blog post about the cost of adding GUCs:
http://momjian.us/main/blogs/pgblog/2009.html#January_10_2009
That's true Bruce (nice post, it was a good reading).
But how can we ignore 25%+ improvements (from 8 to 24)?
At very least we should delivery some pretty good defaults.Well, checkpoint_segments was a similar case where we couldn't give good
tuning advice so we went with a server log file warning if it needed to
be increased --- this might be a similar case.
I have no idea how to decide at runtime whether it should be increased
or not. If that was feasible, we probably could make it tune itself on
the fly - it's not like checkpoint_segments where you need more disk
space if you increase it.
I stand by my decision to make it a #define, at least until someone
voices their objection in the form of a documentation patch.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Oct 3, 2014 at 2:49 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
I stand by my decision to make it a #define, at least until someone voices
their objection in the form of a documentation patch.
I think that's exactly right. If we knew users should tune this, then
we might be able to make it self-tuning, which would be best of all.
At the least, we'd have useful information to provide to people who
want to change it. However, if we *can't* provide tuning advice, then
all making it a GUC does is give users a knob that says "turning this
might make things better! or worse!". Adding such knobs does little
harm individually, but in the aggregate it makes for a product that is
mysterious, hard to use, and hard to maintain and improve.
In practice, I suspect few people will get the amount of benefit that
Andres saw in his tests. You have to be xloginsert-bound, and a lot
of workloads aren't. And if they were before, they aren't now that we
have 8 xloginsert slots by default.
My suspicion, which I think is what Andres was getting at as well, is
that you'll want more slots if you have more CPUs. One way to measure
the effectiveness of a given value (and maybe even auto-tune) would be
to count how often you run out of slots. Of course, you'd need some
countervailing form of pressure based on the effort that you're
spending locking however many you have rather than some smaller
number. You can't just measure the upside without measuring the
downside.
The xlog slots use the kind of algorithm that I quite frankly hate
even when it works. It avoids one kind of loss but in a way that's
not scalable; as you crank up the number of slots you start to incur
cost elsewhere. Getting any more scalability beyond that point will
require doing something fundamentally different, or at least that's my
guess. So I feel like we've put our finger in the dike more than
anything, but that still beats letting the dike collapse.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Oct 3, 2014 at 2:20 PM, Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Oct 3, 2014 at 2:49 PM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:I stand by my decision to make it a #define, at least until someone voices
their objection in the form of a documentation patch.I think that's exactly right. If we knew users should tune this, then
we might be able to make it self-tuning, which would be best of all.
At the least, we'd have useful information to provide to people who
want to change it. However, if we *can't* provide tuning advice, then
all making it a GUC does is give users a knob that says "turning this
might make things better! or worse!". Adding such knobs does little
harm individually, but in the aggregate it makes for a product that is
mysterious, hard to use, and hard to maintain and improve.
100% agree. It's a very simple standard: if you provide a performance
affecting GUC pleease provide guidelines to end user regarding the
tradeoffs or performance interactions being managed. Failure to do
this causes an interesting problem; let's take the case of shared
buffers for example which isn't explained very well. Failure to
properly document or explain the interactions causes the user to make
invalid assumptions about the setting (more memory = faster!) or take
obsolete advice (Tom Lane said in 1997 that 128mb is about right) as
gospel. This has been a long running gripe of mine.
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-10-03 12:40:21 -0400, Bruce Momjian wrote:
On Fri, Oct 3, 2014 at 04:11:30PM +0200, Andres Freund wrote:
On 2014-10-03 10:07:39 -0400, Gregory Smith wrote:
On 10/3/14, 8:26 AM, Andres Freund wrote:
#define NUM_XLOGINSERT_LOCKS 1
tps = 52.711939 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 8
tps = 286.496054 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 16
tps = 346.113313 (including connections establishing)
#define NUM_XLOGINSERT_LOCKS 24
tps = 363.242111 (including connections establishing)Just to clarify: that 10% number I threw out was meant as a rough estimate
for a system with the default configuration, which is all that I tested. It
seemed like people would likely need to tune all the usual things like
checkpoint_segments, shared_buffers, etc. as well before seeing much better.
You did all that, and sure enough the gain went up; thanks for confirming my
guess.I still don't think that means this needs a GUC for 9.4. Look at that jump
from 1 to 8. The low-hanging fruit here hasn't just been knocked off. It's
been blended into a frozen drink, poured into a glass, and had a little
paper umbrella put on top. I think that's enough for 9.4. But, yes, let's
see if we can add delivery to the side of the pool in the next version too.So 25% performance on a relatively small machine improvements aren't
worth a GUC? That are likely to be larger on a bigger machine?I utterly fail to see why that's a service to our users.
Well, I think the issue is that having a GUC that can't reasonably be
tuned by 95% of our users is nearly useless. Few users are going to run
benchmarks to see what the optimal value is.
Sure. And the default loooks to be a good one. So it's not bad that they
don't tune it further.
But. How are we ever going to be able to tune it further without
feedback from actual production usage?
It's possible to convince customers to play with a performance
influencing parameter and see how the results are. Even in
production. It's near impossible to do so if that requires to download
source packages, change a define in some .c file, rebuild the packages,
distribute them to the respective servers. And then continue to do so
for every release thereafter.
Not only is that a *significant* amount of work. It often involves a
different set of people (sysadmin, not dba-ish people). And often
complicated procedures to allow patching the source code at all.
I remember Informix had a setting that had no description except "try
different values to see if it helps performance" --- we don't want to do
that.
I'm pretty sure we can come up with a better description than that.
What if we emit a server message if the setting is too low? That's how
we handle checkpoint_segments.
I don't think it's realistically possible in this case. The
instrumentation we'd need to add would be too expensive for something
running as frequently as this.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Andres Freund <andres@2ndquadrant.com> writes:
On 2014-10-03 12:40:21 -0400, Bruce Momjian wrote:
Well, I think the issue is that having a GUC that can't reasonably be
tuned by 95% of our users is nearly useless. Few users are going to run
benchmarks to see what the optimal value is.
It's possible to convince customers to play with a performance
influencing parameter and see how the results are. Even in
production.
I'm a bit dubious that people will be willing to experiment in production
with a GUC that requires a database restart to change.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-10-03 17:55:19 -0400, Tom Lane wrote:
Andres Freund <andres@2ndquadrant.com> writes:
On 2014-10-03 12:40:21 -0400, Bruce Momjian wrote:
Well, I think the issue is that having a GUC that can't reasonably be
tuned by 95% of our users is nearly useless. Few users are going to run
benchmarks to see what the optimal value is.It's possible to convince customers to play with a performance
influencing parameter and see how the results are. Even in
production.I'm a bit dubious that people will be willing to experiment in production
with a GUC that requires a database restart to change.
I've convinced customers to restart databases with several different
shared_buffers settings... So I'm pretty sure it's possible, in *some*
cases, for xloginsert_slots.
And even if it's just test/pre-production machines - they're not going
to benchmark settings they can't reasonably set in production.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Oct 3, 2014 at 11:58:14PM +0200, Andres Freund wrote:
On 2014-10-03 17:55:19 -0400, Tom Lane wrote:
Andres Freund <andres@2ndquadrant.com> writes:
On 2014-10-03 12:40:21 -0400, Bruce Momjian wrote:
Well, I think the issue is that having a GUC that can't reasonably be
tuned by 95% of our users is nearly useless. Few users are going to run
benchmarks to see what the optimal value is.It's possible to convince customers to play with a performance
influencing parameter and see how the results are. Even in
production.I'm a bit dubious that people will be willing to experiment in production
with a GUC that requires a database restart to change.I've convinced customers to restart databases with several different
shared_buffers settings... So I'm pretty sure it's possible, in *some*
cases, for xloginsert_slots.And even if it's just test/pre-production machines - they're not going
to benchmark settings they can't reasonably set in production.
Do we really want to expose a setting a few of us _might_ ask customers
to change? I don't think so --- create a custom build if you want that.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-10-03 18:08:56 -0400, Bruce Momjian wrote:
On Fri, Oct 3, 2014 at 11:58:14PM +0200, Andres Freund wrote:
On 2014-10-03 17:55:19 -0400, Tom Lane wrote:
Andres Freund <andres@2ndquadrant.com> writes:
On 2014-10-03 12:40:21 -0400, Bruce Momjian wrote:
Well, I think the issue is that having a GUC that can't reasonably be
tuned by 95% of our users is nearly useless. Few users are going to run
benchmarks to see what the optimal value is.It's possible to convince customers to play with a performance
influencing parameter and see how the results are. Even in
production.I'm a bit dubious that people will be willing to experiment in production
with a GUC that requires a database restart to change.I've convinced customers to restart databases with several different
shared_buffers settings... So I'm pretty sure it's possible, in *some*
cases, for xloginsert_slots.And even if it's just test/pre-production machines - they're not going
to benchmark settings they can't reasonably set in production.Do we really want to expose a setting a few of us _might_ ask customers
to change?
They also will try that themselves. Our customers aren't a horde of dumb
people. Some of them are willing to try things if they hit scalability
problesm. And *lots* of people hit scalability problems with
postgres. In fact I've seen big users migrate away from postgres because
of them.
And it's not like this only affects absurd cases. Even a parallel
restore will benefit.
I don't think so --- create a custom build if you want that.
Won't work in the majority of real world cases.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
