Re: Simple postgresql.conf wizard

One of the long-terms goals I'm working toward is wrapping a "wizard"
interface around the tuning guidelines described by
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server now that
those have gone through a fair amount of peer review. Attached is a first
simple version of such a wizard, implemented in Python. Right now what it
does is look the amount of memory in your system and adjust shared_buffers
and effective_cache_size. So if we started with this stock configuration:

shared_buffers = 32MB # min 128kB or
#effective_cache_size = 128MB

And run it like this (from my system with 8GB of RAM):
./pg-generate-conf /home/gsmith/data/guc/postgresql.conf

You'd get a new file with these lines in it printed to standard out:

# shared_buffers = 32MB # min 128kB or
#effective_cache_size = 128MB
shared_buffers 1931MB # pg_generate_conf wizard 2008-11-01
effective_cache_size 5793MB # pg_generate_conf wizard 2008-11-01

While I've got a pretty clear vision for what I'm doing with this next and
will kick off a pgfoundry project real soon, I wanted to throw this out as
a WIP for feedback at this point. I was ultimately hoping to one day have
something like this shipped as a contrib/ module to address the constant
requests for such a thing. I know it would be unreasonable to expect
something in this state to make it into the 8.4 contrib at this late hour.
But since it's independant of the core database stuff I figured I'd make
it available right at the wire here today in the off chance that did seem
a reasonable proposition to anybody. It is already a big improvement over
no tuning at all, and since it's a simple to change script I will rev this
based on feedback pretty fast now that the most boring guts are done.

Possible feedback topics:

-Setting the next round of values requires asking the user for some input
before making recommendations. Is it worth building a curses-based
interface to updating the values? That would be really helpful for people
with only ssh access to their server, but barring something like WCurses
I'm not sure it would help on Windows.

-How about a GUI one with Python's Tkinter interface? Now Windows isn't a
problem, but people using ssh aren't going to be as happy.

-I'm not sure if there's any useful replacement for the os.sysconf
interface I'm using to grab the memory information on the popular Windows
Python ports. Some of the other projects I looked at that tried to
abstract that OS interaction more didn't seem much better here (i.e. the
PSI library which doesn't support Windows either)

-Stepping back a bit from this particular code, is something in Python
like this ever going to be appropriate to ship as a contrib module?
There seems to be a bit more traction in this community for using Perl for
such things; I might do a Perl port of this one day but that's not going
to happen soon.

I think that's enough flametastic material now, and I do plan to join in
on patch review in penance for the disruption I've introduced here. The
next specific things I'm doing with this regardless is making it read and
respect the min/max values for settings as well as the rest of the
information avaialable from pg_settings. I eventually want to support all
the syntax suggested for pg_generate_config described at
http://wiki.postgresql.org/wiki/GUCS_Overhaul but for the moment I'm not
being so ambitious. Some of that is aimed at making a pg_generate_conf
that is capable of replacing the sample postgresql.conf file, which is a
couple of steps away from where I'm at right now.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Greg Smith wrote:
> While I've got a pretty clear vision for what I'm doing with this next
> and will kick off a pgfoundry project real soon, I wanted to throw
> this out as a WIP for feedback at this point. I was ultimately hoping
> to one day have something like this shipped as a contrib/ module to
> address the constant requests for such a thing. I know it would be
> unreasonable to expect something in this state to make it into the 8.4
> contrib at this late hour. But since it's independant of the core
> database stuff I figured I'd make it available right at the wire here
> today in the off chance that did seem a reasonable proposition to
> anybody. It is already a big improvement over no tuning at all, and
> since it's a simple to change script I will rev this based on feedback
> pretty fast now that the most boring guts are done.
I've picked this up to look at it. Will give feedback once I have a
python installed.

-Setting the next round of values requires asking the user for some
input before making recommendations. Is it worth building a
curses-based interface to updating the values? That would be really
helpful for people with only ssh access to their server, but barring
something like WCurses I'm not sure it would help on Windows.

I'd start with command-line switches, e.g.

config --memory=32GB --type=DW --size=500GB --connections=20
> -I'm not sure if there's any useful replacement for the os.sysconf
> interface I'm using to grab the memory information on the popular
> Windows Python ports. Some of the other projects I looked at that
> tried to abstract that OS interaction more didn't seem much better
> here (i.e. the PSI library which doesn't support Windows either)
I think in initial versions we should just get the info from the admin.
I've explored the issues around getting OS-independant accurate system
stats, and they are many.

-Stepping back a bit from this particular code, is something in Python
like this ever going to be appropriate to ship as a contrib module?
There seems to be a bit more traction in this community for using Perl
for such things; I might do a Perl port of this one day but that's not
going to happen soon.

If you do a python version, others will write versions in other
languages. I personally don't really care; Perl's main advantage is
that it's pre-installed on more OSes than Python is.

Anyway, more feedback once I get it running.

--Josh

On Sun, 2 Nov 2008, Josh Berkus wrote:

> I'd start with command-line switches, e.g.
> config --memory=32GB --type=DW --size=500GB --connections=20

That seems reasonable, I think I'll push a fancier UI on the backburner
then and just spec out an options interface like this one.

> I think in initial versions we should just get the info from the admin. I've
> explored the issues around getting OS-independant accurate system stats, and
> they are many.

I'm aware how thorny a perfect solution is here. One thing that's nice
about Python is that there are two interfaces for getting system
information built-in, the os.sysconf I used already and the the
distutils.sysconfig one, aimed more at C-level module writers. Far as
I've been able to tell it's not that hard to get something that works on
most platforms to auto-detect memory and architecture, and I've realized
the latter is kind of important because it determines how big you can make
some things on 32-bit platforms.

After some digging I see there isn't any good interface built-in for
Windows, but it's not hard to ask a DDL for the information. I think it's
reasonable to try and detect total memory+bit width, allow overriding
that, and if detection fails and nothing was specified to error out.
Should make a good balance of reasonable automatic behavior in a lot of
cases, while still allowing the admin to adjust. There's a completely
valid use-case for that even not considering detection failures, where
you're building a config file on a system other than the one it's being
deployed onto.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Sun, 2008-11-02 at 17:10 -0800, Josh Berkus wrote:
> Greg Smith wrote:
>
> -Stepping back a bit from this particular code, is something in Python
> like this ever going to be appropriate to ship as a contrib module?

I think python should be as good as any relatively widespread language.

Unless we require all contrib to be in C.

> There seems to be a bit more traction in this community for using Perl
> for such things; I might do a Perl port of this one day but that's not
> going to happen soon.

The advantage of python over perl is that anybody who knows at least one
programming language is able to learn basic python in an afternoon.

> If you do a python version, others will write versions in other
> languages.

Yeah, if python is not accepted as contrib, then it can probably be
rewritten in C once it has stabilized enough.

> I personally don't really care; Perl's main advantage is
> that it's pre-installed on more OSes than Python is.

I think most (if not all) modern OS's standard setup includes both perl
and python. Except of course windows which probably includes neither.

------------------------------------------
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training

On Sat, 2008-11-01 at 22:02 -0400, Greg Smith wrote:

> Possible feedback topics:
>
> -Setting the next round of values requires asking the user for some input
> before making recommendations. Is it worth building a curses-based
> interface to updating the values? That would be really helpful for people
> with only ssh access to their server, but barring something like WCurses
> I'm not sure it would help on Windows.
>
> -How about a GUI one with Python's Tkinter interface? Now Windows isn't a
> problem, but people using ssh aren't going to be as happy.

You could have it in two parts - an agent running on the server, started
over ssh and GUI interface running on users workstation which talk to
said agent.

And I'd suggest you use wxPython for GUI part if you want a relatively
modern look.

------------------------------------------
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training

Greg Smith wrote:
> One of the long-terms goals I'm working toward is wrapping a "wizard"
> interface around the tuning guidelines described by
> http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server now that
> those have gone through a fair amount of peer review. Attached is a
> first simple version of such a wizard, implemented in Python. Right now
> what it does is look the amount of memory in your system and adjust
> shared_buffers and effective_cache_size. So if we started with this
> stock configuration:

Thats really nice. I have been playing with that idea also (and in
python) but not really having time to do more then some simple tests.
So its nice to see we have something to base development on.

Thx for starting!
Regards
Tino

Greg Smith wrote:
> On Sun, 2 Nov 2008, Josh Berkus wrote:
>
>> I'd start with command-line switches, e.g.
>> config --memory=32GB --type=DW --size=500GB --connections=20
>
> That seems reasonable, I think I'll push a fancier UI on the backburner
> then and just spec out an options interface like this one.
>
>> I think in initial versions we should just get the info from the
>> admin. I've explored the issues around getting OS-independant
>> accurate system stats, and they are many.
>
> I'm aware how thorny a perfect solution is here. One thing that's nice
> about Python is that there are two interfaces for getting system
> information built-in, the os.sysconf I used already and the the
> distutils.sysconfig one, aimed more at C-level module writers. Far as
> I've been able to tell it's not that hard to get something that works on
> most platforms to auto-detect memory and architecture, and I've realized
> the latter is kind of important because it determines how big you can
> make some things on 32-bit platforms.
>
> After some digging I see there isn't any good interface built-in for
> Windows, but it's not hard to ask a DDL for the information. I think
> it's reasonable to try and detect total memory+bit width, allow
> overriding that, and if detection fails and nothing was specified to
> error out. Should make a good balance of reasonable automatic behavior
> in a lot of cases, while still allowing the admin to adjust. There's a
> completely valid use-case for that even not considering detection
> failures, where you're building a config file on a system other than the
> one it's being deployed onto.

Let me know if you need help to write whatever's needed to detect that
stuff on Windows. It shouldn't be too hard, as long as you can live with
a couple of lines of win32-specific code.. :-)

//Magnus

Hannu Krosing wrote:
>> If you do a python version, others will write versions in other
>> languages.
>
> Yeah, if python is not accepted as contrib, then it can probably be
> rewritten in C once it has stabilized enough.

It could. The question is if it makes sense to write something like this
in C, really ;) It might get slightly more portable, at the expense of a
lot more work.

I see no reason why we should on principle reject a python based program
from contrib. We already have stuff there in shellscript which is
actually *less* portable... As long as it's not a core utility needed to
get postgresql working, I think it's fine.

>> I personally don't really care; Perl's main advantage is
>> that it's pre-installed on more OSes than Python is.
>
> I think most (if not all) modern OS's standard setup includes both perl
> and python. Except of course windows which probably includes neither.

Windows ships with neither of the two languages (and you *really* don't
want to write it in vbscript or jscript which is what it does ship with
- other than .BAT). But they both have easy installers you can use to
get it in there - I don't see this as any difference between the two.

And I'll second the comment that I think most reasonably modern
platforms will ship with both of them. AFAIK, many of the newer linuxen
use python based stuff as part of the core installer functionality, for
example.

//Magnus

On 2008-11-02, at 02:02, Greg Smith wrote:

>
> Possible feedback topics:
>
> -Setting the next round of values requires asking the user for some
> input before making recommendations. Is it worth building a curses-
> based interface to updating the values? That would be really
> helpful for people with only ssh access to their server, but barring
> something like WCurses I'm not sure it would help on Windows.
>
> -How about a GUI one with Python's Tkinter interface? Now Windows
> isn't a problem, but people using ssh aren't going to be as happy.
create interface , and module/library - that doesn't 1:1 reflect GUI -
and you'll be able to create interface in any language/toolkit.
>
>
Idea's great, I certainly know many ppl would love to see something
like that. Cos so far, whole 'adjust setting in postgresql.conf' thing
is a guess work. It would be even more good to put in some work into
auto tuning of pgsql itself, with manual override in
postgresql.conf :) (kinda like mssql).

Hannu Krosing escreveu:
> On Sat, 2008-11-01 at 22:02 -0400, Greg Smith wrote:
>
>> Possible feedback topics:
>>
>> -Setting the next round of values requires asking the user for some input
>> before making recommendations. Is it worth building a curses-based
>> interface to updating the values? That would be really helpful for people
>> with only ssh access to their server, but barring something like WCurses
>> I'm not sure it would help on Windows.
>>
>> -How about a GUI one with Python's Tkinter interface? Now Windows isn't a
>> problem, but people using ssh aren't going to be as happy.
>>
>
> You could have it in two parts - an agent running on the server, started
> over ssh and GUI interface running on users workstation which talk to
> said agent.
>
> And I'd suggest you use wxPython for GUI part if you want a relatively
> modern look.
>

Or the GUI could be a part of pgAdmin.

--
[]s
Dickson S. Guedes
Administrador de Banco de Dados
Projeto Colmeia - Florianópolis, SC
(48) 3322-1185, ramal: 26
http://planeta.postgresql.org.br
http://makeall.wordpress.com

On Mon, 3 Nov 2008, Hannu Krosing wrote:

> And I'd suggest you use wxPython for GUI part if you want a relatively
> modern look.

wxPython is GPL licensed and not popular enough to be available on a lot
of systems already. I've spent enough time fighting with installing
wxWidgets (for pgAdmin) to know that I really don't want to push that
install onto end-users, and due to the license it's impractical to just
bundle in some situations.

There's no need for a fancy look here anyway, the only one I ever
envisioned was a simple grid showing the recommendations the user could
update before accepting. In general here, if it doesn't ship with the
stock Python, there would have to be a really, really compelling reason to
use any external library that adds more dependencies.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Mon, 2008-11-03 at 12:42 -0500, Greg Smith wrote:
> On Mon, 3 Nov 2008, Hannu Krosing wrote:
>
> > And I'd suggest you use wxPython for GUI part if you want a relatively
> > modern look.
>
> wxPython is GPL licensed and not popular enough to be available on a lot
> of systems already.

Wikipedia says thus about wxPython
----------------------------------

Being a wrapper, wxPython uses the same free software licence used by
wxWidgets (LGPL) —which is approved by Free Software Foundation and Open
Source Initiative.

and wxWindows itself seems to be even more relaxed
--------------------------------------------------

http://www.wxwidgets.org/about/newlicen.htm

wxWidgets is currently licensed under the "wxWindows Licence" pending
approval of the "wxWidgets Licence" which will be identical apart from
the name.

The wxWindows Licence is essentially the L-GPL (Library General Public
Licence), with an exception stating that derived works in binary form
may be distributed on the user's own terms. This is a solution that
satisfies those who wish to produce GPL'ed software using wxWidgets, and
also those producing proprietary software.

> I've spent enough time fighting with installing
> wxWidgets (for pgAdmin) to know that I really don't want to push that
> install onto end-users, and due to the license it's impractical to just
> bundle in some situations.

I don't think that GPL forbids bundling.

> There's no need for a fancy look here anyway, the only one I ever
> envisioned was a simple grid showing the recommendations the user could
> update before accepting. In general here, if it doesn't ship with the
> stock Python, there would have to be a really, really compelling reason to
> use any external library that adds more dependencies.

I think that though tkInter is kind-of included wit the standard python
distribution, it is not always installed by default, or more exactly,
tcl/tk is often not installed.

In that regard I think that using curses is safest bet.

> --
> * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
>
--
------------------------------------------
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training

On Sun, 2 Nov 2008, Josh Berkus wrote:

> I'd start with command-line switches, e.g.
> config --memory=32GB --type=DW --size=500GB --connections=20

Attached version takes all its input via command line switches. If you
don't specify an explict number of connections, it also implements setting
max_connections via some of the logic from your calcfactors spreadsheet.
I think using more of the ideas from there will drive me crazy unless I
put that into a text file it pulls in instead of a bunch of if statements.
I'm not sure if that much of the complexity of that larger model is
warranted at this point; could use a second opinion on that.

Here's the syntax it accepts now:

$ ./pg-generate-conf --help
Usage: pg-generate-conf [options]

Options:
--version show program's version number and exit
-h, --help show this help message and exit
-i INPUTCONFIG, --input-config=INPUTCONFIG
Input configuration file
-o OUTPUTCONFIG, --output-config=OUTPUTCONFIG
Output configuration file, defaults to standard output
-M TOTALMEMORY, --memory=TOTALMEMORY
Total system memory, will attempt to detect if
unspecified
-T DBTYPE, --type=DBTYPE
Database type, defaults to Mixed, valid options are
DSS, OLTP, Web, Mixed, Desktop
-c CONNECTIONS, --connections=CONNECTIONS
Maximum number of expected connections, default
depends on database type
-D, --debug Enable debugging mode

I've realized I need to duplicate all the memory setting parsing stuff
from the GUC code (as well as the code in SHOW that picks a reasonable
output size) as my next step here, that will be in my next update to this
program.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Greg,

> Attached version takes all its input via command line switches. If you
> don't specify an explict number of connections, it also implements
> setting max_connections via some of the logic from your calcfactors
> spreadsheet.

OK, I'll review. What follows is a review of the *previous* version,
because I'm currently on the road and didn't see your message to
-hackers. Some of the information in the review will still be relevant;
for one thing, I've simplified the "what color is your application"
logic to a few calculations.

----------------------------

Review of simple_config.py:

1) don't bother with os.sysconf, or make it optional and error-trap it.
Instead, solicit the following information from the user:
-- Available RAM
-- Expected Database Size (to nearest 10x)
-- Type of Application
-- Web
-- Data Warehouse
-- Mixed
-- Desktop
-- Operating System [Linux/Windows/OSX/Solaris/FreeBSD/other]

From the above, you can derive all necessary calculations for the
basics. In the advanced version, we'll also want to ask:
-- Memory used by other applications on the system?
-- Analyze queries for performance?
-- SSL?
-- Production vs. Development status?
-- How many connections?
-- Logging setup:
Syslog
Analyze Performance
Private log with weekly rotation

2) It's completely unnecessary to account for OS overhead. This can and
should be taken into account as part of the calculations for other
figures. For example, my 1/4 and 3/4 calculations ignore OS overhead.
You only need to reduce Available RAM when the server will be running
something else, like a J2EE server or multiple databases.

3) You need to provide a whole bunch more values. shared_buffers and
effective_cache_size isn't nearly enough. We should also provide, based
on these calculations, and by database type.

(I'm happy to argue out the figures below. They are simply based on my
direct turning experience with a variety of databases and could probably
use more tweaking for the general case.)

web / oltp
listen_addresses = '*'
max_connections = 200
shared_buffers = 1/4 AvRAM
effective_cache_size = 3/4 AvRAM
work_mem = AvRAM / max_connections, round down
maint_work_mem = AvRAM / 16, round up
wal_buffers = 8mb
autovacuum = on
max_fsm_pages = DBsize / PageSize / 8
checkpoint_segments = 8
default_statistics_target = 10
constraint_exclusion = off

DW:
listen_addresses = '*'
max_connections = 20
shared_buffers = 1/4 AvRAM
effective_cache_size = 3/4 AvRAM
work_mem = AvRAM / max_connections / 2, round down
maint_work_mem = AvRAM / 8, round up
wal_buffers = 32mb
autovacuum = off
max_fsm_pages = DBsize / PageSize / 32*
(unless LoadSize is known)
checkpoint_segments = 64
default_statistics_target = 400
constraint_exclusion = on

Mixed:
listen_addresses = '*'
max_connections = 80
shared_buffers = 1/4 AvRAM
effective_cache_size = 3/4 AvRAM
work_mem = AvRAM / max_connections / 2, round down
maint_work_mem = AvRAM / 16, round up
wal_buffers = 8mb
autovacuum = on
max_fsm_pages = DBsize / PageSize / 8
checkpoint_segments = 16
default_statistics_target = 100
constraint_exclusion = on

Desktop:
listen_addresses = 'localhost'
max_connections = 5
shared_buffers = 1/16 AvRAM
effective_cache_size = 1/4 AvRAM
work_mem = AvRAM / 32, round down
maint_work_mem = AvRAM / 16, round up
wal_buffers = 1mb
autovacuum = on
max_fsm_pages = DBsize / PageSize / 8
checkpoint_segments = 3
default_statistics_target = 10
constraint_exclusion = off

4) Because this comes up so often, we should output to a seperate file
a set of sysctl.conf lines to support SysV memory, depending on OS.

Josh Berkus <josh(at)agliodbs(dot)com> writes:

> DW:
> default_statistics_target = 400
> Mixed:
> default_statistics_target = 100

You, my friend, are certifiably insane.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

Gregory Stark wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>
>> DW:
>> default_statistics_target = 400
>> Mixed:
>> default_statistics_target = 100
>
> You, my friend, are certifiably insane.

Hmmm? Why? I've used those settings in the field, fairly frequently.
I was actually wondering if we should raise the default for web as well,
but decided to let it alone.

Actually, I think a DW should begin at 400; often it needs to go up to
1000, but I don't think a script should do that.

--Josh

Greg,

BTW, I think this is still in enough flux that we really ought to make
it a pgfoundry project. I don't think we'll have anything ready for 8.4
contrib.

--Josh

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Gregory Stark wrote:
>> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>>> DW:
>>> default_statistics_target = 400
>>> Mixed:
>>> default_statistics_target = 100
>>
>> You, my friend, are certifiably insane.

> Hmmm? Why? I've used those settings in the field, fairly frequently.

Even though we all agree default_statistics_target = 10 is too low,
proposing a 40X increase in the default value requires more evidence
than this. In particular, the prospect of a 1600-fold increase in
the typical cost of eqjoinsel() is a mite scary.

regards, tom lane

A statistic target of 400 fir a specific column may make sense but
even then I would recommend monitoring performance to ensure it
doesn't cause problems. As a global setting it's, IMHO, ridiculous.

Even for the smaller data types (except boolean and "char") and array
of 400 will be large enough to be toasted. Planning queries will
involve many more disk I/Os than some of those queries end up taking
themselves. Even for stats which are already cached there are some
algorithms in the planner known to be inefficient for large arrays.

It may make sense for specific skewed columns with indexes on them,
but keep in mind postgres needs to consult the statistics on any
column referenced in a qual even if there are no indexes and for most
data distributions do fine with a target of 10.

I think we all agree the default may need to be raised but until there
is some data we have little basis to recommend anything specific.

I would suggest starting from the basis that "mixed" (with a
conservative memory setting) is the same as "Postgres default".
Perhaps (probably) the defaults should be changed but we shouldn't
have two different tools with different (drastically different!) ideas
for the same situation.

greg

On 13 Nov 2008, at 07:46 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:

> Gregory Stark wrote:
>> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>>> DW:
>>> default_statistics_target = 400
>>> Mixed:
>>> default_statistics_target = 100
>> You, my friend, are certifiably insane.
>
> Hmmm? Why? I've used those settings in the field, fairly
> frequently. I was actually wondering if we should raise the default
> for web as well, but decided to let it alone.
>
> Actually, I think a DW should begin at 400; often it needs to go up
> to 1000, but I don't think a script should do that.
>
> --Josh
>

Gregory Stark wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> writes:
>
>> DW:
>> default_statistics_target = 400
>> Mixed:
>> default_statistics_target = 100
>
> You, my friend, are certifiably insane.

I almost fell off the chair because of that comment, but after I stopped
laughing and actually looked at those values, it doesn't seem that
unreasonable. Arbitrary, sure, but not insane. Or do I need stronger
glasses?

A lot of people have suggested raising our default_statistics target,
and it has been rejected because there's some O(n^2) behavior in the
planner, and it makes ANALYZE slower, but it's not that crazy.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> A lot of people have suggested raising our default_statistics target,
> and it has been rejected because there's some O(n^2) behavior in the
> planner, and it makes ANALYZE slower, but it's not that crazy.

I think everyone agrees it ought to be raised. Where the rubber meets
the road is deciding just *what* to raise it to. We've got no
convincing evidence in favor of any particular value.

If someone actually wanted to put some effort into this, I'd suggest
taking some reasonably complex benchmark (maybe TPCH or one of the DBT
series) and plotting planner runtime for each query as a function of
statistics_target, taking care to mark the breakpoints where it shifted
to a better (or worse?) plan due to having better stats.

regards, tom lane

Tom Lane wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> A lot of people have suggested raising our default_statistics target,
>> and it has been rejected because there's some O(n^2) behavior in the
>> planner, and it makes ANALYZE slower, but it's not that crazy.
>
> I think everyone agrees it ought to be raised. Where the rubber meets
> the road is deciding just *what* to raise it to. We've got no
> convincing evidence in favor of any particular value.
>
> If someone actually wanted to put some effort into this, I'd suggest
> taking some reasonably complex benchmark (maybe TPCH or one of the DBT
> series) and plotting planner runtime for each query as a function of
> statistics_target, taking care to mark the breakpoints where it shifted
> to a better (or worse?) plan due to having better stats.

Yeah, that would be a good starting point. After we have some data to
work with, we could also look into making the planner faster with large
samples.

Another idea would be to take a large sample in ANALYZE, but if the
distribution looks "regular enough", store less samples in the
Most-Common-Values list and fewer histograms, to make the planning faster.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> Another idea would be to take a large sample in ANALYZE, but if the
> distribution looks "regular enough", store less samples in the
> Most-Common-Values list and fewer histograms, to make the planning faster.

Yeah, some flexibility at that end might not be a bad idea either.

regards, tom lane

On 2008-11-13, at 19:33, Greg Stark wrote:

> A statistic target of 400 fir a specific column may make sense but
> even then I would recommend monitoring performance to ensure it
> doesn't cause problems. As a global setting it's, IMHO, ridiculous.
>

If that's the situation, me thinks you guys have to start thinking
about some sort of automated way to increase this param per column as
needed.
Is there any way planner could actually tell, that it would do better
job with more stats for certain column ?

On Thu, Nov 13, 2008 at 3:20 PM, Grzegorz Jaskiewicz
<gj(at)pointblue(dot)com(dot)pl> wrote:
> If that's the situation, me thinks you guys have to start thinking about
> some sort of automated way to increase this param per column as needed.
> Is there any way planner could actually tell, that it would do better job
> with more stats for certain column ?

Other systems do it. For example, Oracle tracks column usage and
attempts to determine the optimal statistics for that column (based on
the queries that used it) on an iterative basis. We don't track
column usage at all, so that option wouldn't be quite that easy to
implement. Though, there are certain things ANALYZE would be able to
determine with a little help, such as knowing to collect more samples
for columns it finds extremely skewed data in.

There are other things that could be done as well... so the answer is, yes.

--
Jonah H. Harris, Senior DBA
myYearbook.com

On Thu, 13 Nov 2008, Josh Berkus wrote:

> don't bother with os.sysconf, or make it optional and error-trap it.

Right, I've moved in that direction in the updated rev I already
sent--memory is an input value, but if you leave it out it tries to guess.
Just need a bit more error trapping around that I think, then I can add
Windows support too. A goal I don't think is unreachable here is to give
a useful middle of the road tuning on most platforms if you just run it
but don't tell it anything.

> In the advanced version, we'll also want to ask...

I'm pretty focused right now only on getting a simple version solidified,
to keep the scope under control. Thanks for the more focused parameter
suggestions. The spreadsheet version I had from you was a bit too
complicated to work with, this reformulation is much easier to automate.

> Because this comes up so often, we should output to a seperate file a
> set of sysctl.conf lines to support SysV memory, depending on OS.

Agreed, will add that to the required feature list.

> BTW, I think this is still in enough flux that we really ought to make
> it a pgfoundry project. I don't think we'll have anything ready for 8.4
> contrib.

I find your lack of faith disturbing. I'll have another rev that
incorporates all your feedback done within a week. There are some pretty
hairy patches still active in this final 'Fest. I think I'll have the
simple version feature complete, documented, and have already done a round
of polishing at least a week or two before all that work wraps up.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Thu, 2008-11-13 at 20:33 +0100, Greg Stark wrote:

> A statistic target of 400 fir a specific column may make sense but
> even then I would recommend monitoring performance to ensure it
> doesn't cause problems. As a global setting it's, IMHO, ridiculous.
>
> Even for the smaller data types (except boolean and "char") and array
> of 400 will be large enough to be toasted. Planning queries will
> involve many more disk I/Os than some of those queries end up taking
> themselves. Even for stats which are already cached there are some
> algorithms in the planner known to be inefficient for large arrays.
>
> It may make sense for specific skewed columns with indexes on them,
> but keep in mind postgres needs to consult the statistics on any
> column referenced in a qual even if there are no indexes and for most
> data distributions do fine with a target of 10.

Your factual comments are accurate, but for Josh's stated target of Data
Warehousing, a stats target of 400 is not unreasonable in some cases.
What you forget to mention is that sample size is also determined by
stats target and for large databases this can be a more important
consideration than the points you mention.

In any case, saying that somebody is certifiably insane in a public
forum is at best questionable. I would like to see the comment
withdrawn.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

Tom,

> Even though we all agree default_statistics_target = 10 is too low,
> proposing a 40X increase in the default value requires more evidence
> than this. In particular, the prospect of a 1600-fold increase in
> the typical cost of eqjoinsel() is a mite scary.

It's a *completely* acceptable tradeoff for a *data warehousing*
database, where queries take multiple seconds to execute even under the
best plans ... and minutes or hours for the worst. And that's what I'm
proposing a value of 400 for, if you read my posting rather than just
Greg's outraged response.

(and yes, I've done this for multiple *production* data warehouses and
the results have been good)

For a web database, keep it at 10. It might turn out that an increase
to 25 or 50 would benefit even web applications, but we don't yet have
the testing resources to determine that.

Of *course* it would be better for the DBA to go through and set
statistics column-by-column. But few will.

--Josh

Simon,

> In any case, saying that somebody is certifiably insane in a public
> forum is at best questionable. I would like to see the comment
> withdrawn.

Thanks for defending me. I think Greg threw that at me because he knows
I'm very difficult to offend, though. I assume that Greg wouldn't make a
post like that to other members of the community.

--Josh

On Thu, 13 Nov 2008, Josh Berkus wrote:

>> Even though we all agree default_statistics_target = 10 is too low,
>> proposing a 40X increase in the default value requires more evidence
>> than this. In particular, the prospect of a 1600-fold increase in
>> the typical cost of eqjoinsel() is a mite scary.
>
> It's a *completely* acceptable tradeoff for a *data warehousing* database,
> where queries take multiple seconds to execute even under the best plans ...
> and minutes or hours for the worst. And that's what I'm proposing a value of
> 400 for

The idea that planning time is trivial compared with query runtime in a
data warehouse application is certainly true. I remain a bit concerned
about making the target so large for everyone just because they picked
that option though. I'd hate to see somebody who doesn't quite understand
what that term implies get their plan times exploding.

Since Josh's latest parameter model takes a database size as an input,
perhaps a reasonable way to proceed here is to put the DW model into size
tiers. Something like this:

DW default_statistics_target:

db size setting
<1GB 10
1GB-10GB 50
10GB-100GB 100
100GB-1TB 200
>1TB 400

Going along with my idea that this tool should produce a reasonable result
with minimal data, I was thinking of making the database size default to
10GB if there isn't any input given there. That would give someone who
specified DW but nothing else a result of 100, which seems a less
controversial setting.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

> listen_addresses = '*'

This doesn't seem like a good thing to autogenerate from a security
perspective. I think we should not attempt to guess the user's
requirements in this area.

> max_fsm_pages = DBsize / PageSize / 8

Isn't this moot for 8.4?

...Robert

On Thu, 2008-11-13 at 18:07 -0500, Greg Smith wrote:
> On Thu, 13 Nov 2008, Josh Berkus wrote:

> Since Josh's latest parameter model takes a database size as an input,
> perhaps a reasonable way to proceed here is to put the DW model into size
> tiers. Something like this:
>
> DW default_statistics_target:
>
> db size setting
> <1GB 10
> 1GB-10GB 50
> 10GB-100GB 100
> 100GB-1TB 200
> >1TB 400
>
> Going along with my idea that this tool should produce a reasonable result
> with minimal data, I was thinking of making the database size default to
> 10GB if there isn't any input given there. That would give someone who
> specified DW but nothing else a result of 100, which seems a less
> controversial setting.
>

Why are we building wizards for settings that will be configured by
experts? I thought the idea here was:

Simple postgresql.conf wizard

If you are running a DW you are beyond the point of this tool are you
not?

Joshua D. Drake

> --
> * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
>
--

On Thu, 2008-11-13 at 17:21 -0500, Greg Smith wrote:
> On Thu, 13 Nov 2008, Josh Berkus wrote:

> > BTW, I think this is still in enough flux that we really ought to make
> > it a pgfoundry project. I don't think we'll have anything ready for 8.4
> > contrib.
>
> I find your lack of faith disturbing. I'll have another rev that
> incorporates all your feedback done within a week. There are some pretty
> hairy patches still active in this final 'Fest. I think I'll have the
> simple version feature complete, documented, and have already done a round
> of polishing at least a week or two before all that work wraps up.
>

I agree if we stick to the actual point (simple postgresql.conf wizard)
then we shouldn't have any worry of getting it in.

Joshua D. Drake

> --
> * Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD
>
--

Josh Berkus wrote:

> Thanks for defending me. I think Greg threw that at me because he knows
> I'm very difficult to offend, though. I assume that Greg wouldn't make a
> post like that to other members of the community.

I would print it and frame it to hang somewhere in the office ... or
maybe get some business cards with it.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thu, 13 Nov 2008, Robert Haas wrote:

>> listen_addresses = '*'
>
> This doesn't seem like a good thing to autogenerate from a security
> perspective. I think we should not attempt to guess the user's
> requirements in this area.

Yeah, I don't want to be the guy who flips the switch for being less
secure by default. Particularly because it's unlikely to do anything by
itself--need some changes to pg_hba.conf in most cases. However, not
setting listen_addresses to something useful is a common newbie problem.
I was thinking of producing a warning to standard error with some
suggestions if listen_addresses isn't set to the usual '*', but not
actually changing the setting.

>> max_fsm_pages = DBsize / PageSize / 8
>
> Isn't this moot for 8.4?

At some point this is going to target earlier versions as well so it's
good to have that intelligence in the app, even if it ends up not being a
setting that is altered.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:

> Your factual comments are accurate, but for Josh's stated target of Data
> Warehousing, a stats target of 400 is not unreasonable in some cases.
> What you forget to mention is that sample size is also determined by
> stats target and for large databases this can be a more important
> consideration than the points you mention.

Even for data warehousing I would not recommend setting it as a *default*
statistics target, at least not without verifying that it doesn't cause any
problems.

I would certainly consider 400 reasonable for specific columns. But the
default statistics target controls how large a histogram to store for *every*
column. Even columns never used by any clauses or used by clauses which do not
have any indexes on them.

Actually a plausible argument could be made that for data warehousing
databases in particular large values of default_statistics_target are
especially damaging. Queries on these databases are likely to have a large
number of clauses which are not indexed and a large number of joins with
complex join clauses.

Not every data warehouse query runs for hours, what I'm afraid of is
potentially the first time someone pops up complaining how Postgres sucks
because it randomly takes minutes to plan their queries. Only to find it's
retrieving kilobytes of data from toasted statistics arrays and performing n^2
comparisons of that data.

> In any case, saying that somebody is certifiably insane in a public
> forum is at best questionable. I would like to see the comment
> withdrawn.

I'm not too nervous that Josh might have actually thought I thought he was
really insane. (Or for that matter that anyone else reading it might have
thought so.)

On the other hand what does occur to me in retrospect is that I regret that I
didn't think about how I was disparaging the importance of mental illness and
hope nobody took offense for that reason.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!

>> In any case, saying that somebody is certifiably insane in a public
>> forum is at best questionable. I would like to see the comment
>> withdrawn.
>
> I'm not too nervous that Josh might have actually thought I thought he was
> really insane. (Or for that matter that anyone else reading it might have
> thought so.)
>
> On the other hand what does occur to me in retrospect is that I regret that I
> didn't think about how I was disparaging the importance of mental illness and
> hope nobody took offense for that reason.

I hope so too, but I think we're taking this way too seriously.

...Robert

Jonah H. Harris wrote:
> On Thu, Nov 13, 2008 at 3:20 PM, Grzegorz Jaskiewicz
> <gj(at)pointblue(dot)com(dot)pl> wrote:
>> If that's the situation, me thinks you guys have to start thinking about
>> some sort of automated way to increase this param per column as needed.
>> Is there any way planner could actually tell, that it would do better job
>> with more stats for certain column ?
>
> Other systems do it. For example, Oracle tracks column usage and
> attempts to determine the optimal statistics for that column (based on
> the queries that used it) on an iterative basis. We don't track
> column usage at all, so that option wouldn't be quite that easy to
> implement. Though, there are certain things ANALYZE would be able to
> determine with a little help, such as knowing to collect more samples
> for columns it finds extremely skewed data in.

That kind of feedback loops are a bit dangerous. For starters, it would
mean that your test system would behave differently than your production
system, just because you run different queries on it. There's also all
kinds of weird dynamic behaviors that could kick in. For example, a
query could run fine for the first few hundred times, but then the
analyzer notices that a certain column is being accessed frequently and
decides to increase the stats target for it, which changes the plan, for
worse. Usually the new plan would be better, but the planner isn't perfect.

> There are other things that could be done as well... so the answer is, yes.

Yes, just have to be careful..

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Fri, 2008-11-14 at 02:21 +0000, Gregory Stark wrote:

> On the other hand what does occur to me in retrospect is that I regret
> that I didn't think about how I was disparaging the importance of
> mental illness and hope nobody took offense for that reason.

Your comments surprise me because you mentioned to me privately that you
disliked on-list bullies.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, Nov 14, 2008 at 8:10 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Fri, 2008-11-14 at 02:21 +0000, Gregory Stark wrote:
>
>> On the other hand what does occur to me in retrospect is that I regret
>> that I didn't think about how I was disparaging the importance of
>> mental illness and hope nobody took offense for that reason.
>
> Your comments surprise me because you mentioned to me privately that you
> disliked on-list bullies.

It hardly seems like bullying to me - a tongue-in-cheek humorous
remark to someone many of us, including Greg, have known and worked
with for years. If it were made to a newbie to the community, that
would be another matter.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

On Fri, 2008-11-14 at 08:57 +0000, Dave Page wrote:
> On Fri, Nov 14, 2008 at 8:10 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> >
> > On Fri, 2008-11-14 at 02:21 +0000, Gregory Stark wrote:
> >
> >> On the other hand what does occur to me in retrospect is that I regret
> >> that I didn't think about how I was disparaging the importance of
> >> mental illness and hope nobody took offense for that reason.
> >
> > Your comments surprise me because you mentioned to me privately that you
> > disliked on-list bullies.
>
> It hardly seems like bullying to me - a tongue-in-cheek humorous
> remark to someone many of us, including Greg, have known and worked
> with for years. If it were made to a newbie to the community, that
> would be another matter.

I was not the one complaining about bullying comments.

It seems a strange moderation policy that allows bald insults from some
people but not others. And stranger still that you think you should leap
to the defence of any person making them. If the comments were meant so
lightly, it would seem easy to withdraw them also, with a smile.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, Nov 14, 2008 at 9:48 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Fri, 2008-11-14 at 08:57 +0000, Dave Page wrote:
>> On Fri, Nov 14, 2008 at 8:10 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> >
>> > On Fri, 2008-11-14 at 02:21 +0000, Gregory Stark wrote:
>> >
>> >> On the other hand what does occur to me in retrospect is that I regret
>> >> that I didn't think about how I was disparaging the importance of
>> >> mental illness and hope nobody took offense for that reason.
>> >
>> > Your comments surprise me because you mentioned to me privately that you
>> > disliked on-list bullies.
>>
>> It hardly seems like bullying to me - a tongue-in-cheek humorous
>> remark to someone many of us, including Greg, have known and worked
>> with for years. If it were made to a newbie to the community, that
>> would be another matter.
>
> I was not the one complaining about bullying comments.

No, but you drew the comparison.

> It seems a strange moderation policy that allows bald insults from some
> people but not others. And stranger still that you think you should leap
> to the defence of any person making them. If the comments were meant so
> lightly, it would seem easy to withdraw them also, with a smile.

No stranger than you leaping to demand an apology. My point was, that
I didn't see an deliberate insult, and by the sound of it neither did
Josh. I saw a humorous response that was unfortunately missing a
smiley to make the intent clear for those that haven't been in these
parts for years. I would have thought that you too would know Greg
well enough to know that he wasn't trying to be rude but was making a
joke.

Anyhoo, I don't think this is worth wasting any more bandwidth so I'll
shut up now.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

Josh Berkus wrote:
> Greg,
>
> BTW, I think this is still in enough flux that we really ought to make
> it a pgfoundry project. I don't think we'll have anything ready for 8.4
> contrib.

[Been trying to find the right point to post this reply.]

Is it only me that thinks this should be a service on the website too
(or even first)? Fill in web form, click button, get sample
postgresql.conf (with comments) back.

Add a tick-box asking if we can keep a copy of their answers and you
might get some useful usage info too.

--
Richard Huxton
Archonet Ltd

On Fri, 2008-11-14 at 02:21 +0000, Gregory Stark wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>
> > Your factual comments are accurate, but for Josh's stated target of Data
> > Warehousing, a stats target of 400 is not unreasonable in some cases.
> > What you forget to mention is that sample size is also determined by
> > stats target and for large databases this can be a more important
> > consideration than the points you mention.
>
> Even for data warehousing I would not recommend setting it as a *default*
> statistics target, at least not without verifying that it doesn't cause any
> problems.

I agree with you that it would be likely misused by many people, simply
because the term "data warehouse" is a very loosely defined concept.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Fri, Nov 14, 2008 at 2:52 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> Other systems do it. For example, Oracle tracks column usage and
>> attempts to determine the optimal statistics for that column (based on
>> the queries that used it) on an iterative basis. We don't track
>> column usage at all, so that option wouldn't be quite that easy to
>> implement. Though, there are certain things ANALYZE would be able to
>> determine with a little help, such as knowing to collect more samples
>> for columns it finds extremely skewed data in.
>
> That kind of feedback loops are a bit dangerous. For starters, it would mean
> that your test system would behave differently than your production system,
> just because you run different queries on it. There's also all kinds of
> weird dynamic behaviors that could kick in. For example, a query could run
> fine for the first few hundred times, but then the analyzer notices that a
> certain column is being accessed frequently and decides to increase the
> stats target for it, which changes the plan, for worse. Usually the new plan
> would be better, but the planner isn't perfect.

Oracle already thought of that a long time ago, which is why the plan
has to come out better for it to take effect. As for bad plans, you
obviously haven't used Postgres in production enough to deal with it
continually changing plans for the worse due to index bloat, data
skew, phase of the moon, etc. :)

--
Jonah H. Harris, Senior DBA
myYearbook.com

dpage(at)pgadmin(dot)org ("Dave Page") writes:
> On Fri, Nov 14, 2008 at 8:10 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>
>> On Fri, 2008-11-14 at 02:21 +0000, Gregory Stark wrote:
>>
>>> On the other hand what does occur to me in retrospect is that I regret
>>> that I didn't think about how I was disparaging the importance of
>>> mental illness and hope nobody took offense for that reason.
>>
>> Your comments surprise me because you mentioned to me privately that you
>> disliked on-list bullies.
>
> It hardly seems like bullying to me - a tongue-in-cheek humorous
> remark to someone many of us, including Greg, have known and worked
> with for years. If it were made to a newbie to the community, that
> would be another matter.

It's pretty much like the "you'd have to be on crack to do that!",
erm, crack.
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/rdbms.html
Zaphod's just zis guy, you know?

Jonah H. Harris wrote:
> Oracle already thought of that a long time ago, which is why the plan
> has to come out better for it to take effect.

Huh? We would never willingly choose a worse plan, of course, but the
point is that what looks like a better plan, with a smaller cost
estimate, is sometimes actually worse.

> As for bad plans, you
> obviously haven't used Postgres in production enough to deal with it
> continually changing plans for the worse due to index bloat, data
> skew, phase of the moon, etc. :)

You're right, I haven't, but yes I know that's a problem. We've chatted
about that with Greg sometimes. It would be nice to have more stable
plans. My favorite idea is to stop using the current relation size in
the planner, and use the value snapshotted at ANALYZE instead. That way,
the planner would be completely deterministic, based on the statistics.
Then, we could have tools to snapshot the statistics, move them to a
test system, store them, revert back to old statistics etc.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Fri, Nov 14, 2008 at 10:50 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> Oracle already thought of that a long time ago, which is why the plan
>> has to come out better for it to take effect.
>
> Huh? We would never willingly choose a worse plan, of course, but the point
> is that what looks like a better plan, with a smaller cost estimate, is
> sometimes actually worse.

Oracle bases it on cost and elapsed execution time.

>> As for bad plans, you
>> obviously haven't used Postgres in production enough to deal with it
>> continually changing plans for the worse due to index bloat, data
>> skew, phase of the moon, etc. :)
>
> You're right, I haven't, but yes I know that's a problem. We've chatted
> about that with Greg sometimes. It would be nice to have more stable plans.
> My favorite idea is to stop using the current relation size in the planner,
> and use the value snapshotted at ANALYZE instead. That way, the planner
> would be completely deterministic, based on the statistics. Then, we could
> have tools to snapshot the statistics, move them to a test system, store
> them, revert back to old statistics etc.

Yes, plan stability would be a Good Thing(tm) IMO.

--
Jonah H. Harris, Senior DBA
myYearbook.com

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> You're right, I haven't, but yes I know that's a problem. We've chatted
> about that with Greg sometimes. It would be nice to have more stable
> plans. My favorite idea is to stop using the current relation size in
> the planner, and use the value snapshotted at ANALYZE instead.

Sorry, that's a nonstarter. We used to act that way and it was
completely fatal to plan quality in very many typical scenarios.

IMHO, the only thing worse than an unstable plan is a stable one.

regards, tom lane

On Fri, Nov 14, 2008 at 11:43 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> IMHO, the only thing worse than an unstable plan is a stable one.

Your opinion contradicts the majority of the industry then, I'm
afraid. Like query hints, people are sometimes smarter than the
optimizer.

--
Jonah H. Harris, Senior DBA
myYearbook.com

"Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:
> On Fri, Nov 14, 2008 at 11:43 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> IMHO, the only thing worse than an unstable plan is a stable one.

> Your opinion contradicts the majority of the industry then, I'm
> afraid. Like query hints, people are sometimes smarter than the
> optimizer.

And, very often, they're not --- or more likely, they were smarter than
the optimizer last year, but now conditions have changed. Failing to
adapt to new conditions is exactly the problem with query hints, and
in general with any insistence that plans should be "stable".

regards, tom lane

On Fri, Nov 14, 2008 at 12:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> And, very often, they're not --- or more likely, they were smarter than
> the optimizer last year, but now conditions have changed. Failing to
> adapt to new conditions is exactly the problem with query hints, and
> in general with any insistence that plans should be "stable".

Well, at least they didn't have to wait a year to fix the problem.

Similarly, whether or not the plan changed due to bad hints or bad
plans, detecting the change is relatively easy, so I don't really see
an argument based on *why* the plan failed. In my, and many others
opinion, if you decide to take your query plan into your own hands,
it's your problem if it fails. I do agree that hints are a little too
nice and simple, and generally get people into trouble because they're
hard-coded in an app, tend to cause issues later, and are then
difficult to track down. Oracle solved that years ago as well, which
is why they support more advanced plan stability features than just
hints.

However, given the number of large-scale OLTP sites I've been to, I
can tell you from experience that post-ANALYZE plan changes wreak
complete havoc on a system and in many cases, bring it to its knees.
In those cases, the proper query plan is well-known, and a hint (or
some other form of plan stability) is all that would be required to
prevent it from happening.

This is pretty off-topic for this thread, so I'll postpone the
discussion for 8.5.

--
Jonah H. Harris, Senior DBA
myYearbook.com

Tom Lane wrote:
> "Jonah H. Harris" <jonah(dot)harris(at)gmail(dot)com> writes:
>> On Fri, Nov 14, 2008 at 11:43 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> IMHO, the only thing worse than an unstable plan is a stable one.
>
>> Your opinion contradicts the majority of the industry then, I'm
>> afraid. Like query hints, people are sometimes smarter than the
>> optimizer.
>
> And, very often, they're not --- or more likely, they were smarter than
> the optimizer last year, but now conditions have changed. Failing to
> adapt to new conditions is exactly the problem with query hints, and
> in general with any insistence that plans should be "stable".

Those are both very simplistic views. Yes, the planner often knows
better. Yes, the DBA also often knows better.

Stable plans are by no means a silver bullet. If a table suddenly
changes dramatically in size, you certainly do want to replan your
queries. The typical scenario that people want to avoid with stable
plans is where a table grows slowly, until it reaches a critical point,
and the planner chooses a different plan, and the new plan happens to be
very bad. The scary thing about that is that it can happen at an
unpredictable time, and it can be really hard to convince the planner to
choose the old plan again.

It's a tradeoff, for sure. Ideally the planner would be infallible.
Failing that, it would be nice to provide the DBA some tools to control
when new plans are taken into use.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Thu, Nov 13, 2008 at 11:53 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> A lot of people have suggested raising our default_statistics target,
>> and it has been rejected because there's some O(n^2) behavior in the
>> planner, and it makes ANALYZE slower, but it's not that crazy.
>
> I think everyone agrees it ought to be raised. Where the rubber meets
> the road is deciding just *what* to raise it to. We've got no
> convincing evidence in favor of any particular value.
>
> If someone actually wanted to put some effort into this, I'd suggest
> taking some reasonably complex benchmark (maybe TPCH or one of the DBT
> series) and plotting planner runtime for each query as a function of
> statistics_target, taking care to mark the breakpoints where it shifted
> to a better (or worse?) plan due to having better stats.

Almost there... I have a MSA70 plugged into the DL380 I have from HP
and I'm trying to find time to get my scripts updated to deal with how
tools have changed over the years... I'm updating the DBT-2 (tpc-c
kit) I have first

Regards,
Mark

On Fri, 14 Nov 2008, Richard Huxton wrote:

> Is it only me that thinks this should be a service on the website too
> (or even first)? Fill in web form, click button, get sample
> postgresql.conf (with comments) back.

Sounds familiar...ah, here it is:
http://archives.postgresql.org/pgsql-performance/2007-06/msg00377.php

I'm ignoring the temptation of working on the UI first and instead staying
focused on getting the parameter model slimmed down to a readable set of
code. Ports to other interfaces should follow--I've already got two
people who want to build alternate ones lined up.

To give you an idea how overdiscussed this general topic is, I just sent a
message to Josh suggesting we might put database size into tiers and set
some parameters based on that. Guess what? That was his idea the last
time around, I subconsciously regurgitated it:
http://archives.postgresql.org/pgsql-performance/2007-06/msg00602.php

> Add a tick-box asking if we can keep a copy of their answers and you
> might get some useful usage info too.

It's rare I work with a company that wants their internal use of
PostgreSQL to be public knowledge. Putting a collection flag on the page
is just going to spook such commercial users, even if it defaults to off.
The privacy issues are one reason I put the web-based port far down
relative to my priorities; another is that I don't do much web application
development. But if somebody else wants to run with that, fine by me.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Greg,

> To give you an idea how overdiscussed this general topic is, I just sent
> a message to Josh suggesting we might put database size into tiers and
> set some parameters based on that. Guess what? That was his idea the
> last time around, I subconsciously regurgitated it:
> http://archives.postgresql.org/pgsql-performance/2007-06/msg00602.php

Heh, no wonder it sounded good.

However, after a year more of experience, I'd suggest that we solicit
specific type from the user rather than determining it strictly from
database size. The specific elements of a "DW" use-case aren't
necessarily tied to size. They are:
* data comes in in large batches rather than individual rows
* small numbers of users
* large complex queries

For example, right now, I'm refactoring a database which is only 15GB,
but is definitely DW behavior, so we want to keep max_connections to <
20 and turn autovaccum off.

So I think we should ask the user what kind of DB they have (*with* docs
which explain what the types mean) and fall back to testing by size if
the info is not supplied.

Regarding the level of default_stats_target, it sounds like people agree
that it ought to be raised for the DW use-case, but disagree how much.
If that's the case, what if we compromize at 50 for "mixed" and 100 for
DW? That should allay people's fears, and we can tinker with it when we
have more data.

--Josh

Simon, Stark, others:

> It seems a strange moderation policy that allows bald insults from some
> people but not others. And stranger still that you think you should leap
> to the defence of any person making them. If the comments were meant so
> lightly, it would seem easy to withdraw them also, with a smile.

Let's not get carried away with this. It is important for us to speak
up when someone uses forceful language (for the sake of the archives),
and very important when it's used with someone who's not a native
English speaker or is a newbie.

However, having made a comment to tone things down, it's best to drop
it. Especially when the person targeted is an American and a core team
member who isn't actually going to be insulted. An apology is only
really necessary if someone is hurt.

--Josh

Mark Wong wrote:
> On Thu, Nov 13, 2008 at 11:53 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> >> A lot of people have suggested raising our default_statistics target,
> >> and it has been rejected because there's some O(n^2) behavior in the
> >> planner, and it makes ANALYZE slower, but it's not that crazy.
> >
> > I think everyone agrees it ought to be raised. Where the rubber meets
> > the road is deciding just *what* to raise it to. We've got no
> > convincing evidence in favor of any particular value.
> >
> > If someone actually wanted to put some effort into this, I'd suggest
> > taking some reasonably complex benchmark (maybe TPCH or one of the DBT
> > series) and plotting planner runtime for each query as a function of
> > statistics_target, taking care to mark the breakpoints where it shifted
> > to a better (or worse?) plan due to having better stats.
>
> Almost there... I have a MSA70 plugged into the DL380 I have from HP
> and I'm trying to find time to get my scripts updated to deal with how
> tools have changed over the years... I'm updating the DBT-2 (tpc-c
> kit) I have first

Yes, please test something. I am tired if us saying we need to increase
default_statistics_target, but because we don't know the magic number,
we do nothing release after release.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

> Yes, please test something. I am tired if us saying we need to increase
> default_statistics_target, but because we don't know the magic number,
> we do nothing release after release.

I think the idea that there IS a magic number is the problem.

No amount of testing is ever going to refute the argument that, under
some other workload, a different value might better.

But that doesn't amount to a reason to leave it the way it is.

...Robert

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-
> owner(at)postgresql(dot)org] On Behalf Of Robert Haas
> Sent: Wednesday, November 19, 2008 5:05 PM
> To: Bruce Momjian
> Cc: Mark Wong; Tom Lane; Heikki Linnakangas; Gregory Stark; Josh
> Berkus; Greg Smith; pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] Simple postgresql.conf wizard
>
> > Yes, please test something. I am tired if us saying we need to
> increase
> > default_statistics_target, but because we don't know the magic
> number,
> > we do nothing release after release.
>
> I think the idea that there IS a magic number is the problem.
>
> No amount of testing is ever going to refute the argument that, under
> some other workload, a different value might better.
>
> But that doesn't amount to a reason to leave it the way it is.

Perhaps a table of experimental data could serve as a rough guideline.

"Dann Corbit" <DCorbit(at)connx(dot)com> writes:
>> I think the idea that there IS a magic number is the problem.
>>
>> No amount of testing is ever going to refute the argument that, under
>> some other workload, a different value might better.
>>
>> But that doesn't amount to a reason to leave it the way it is.

> Perhaps a table of experimental data could serve as a rough guideline.

The problem is not that anyone wants to leave it the way it is.
The problem is that no one has done even a lick of work to identify
a specific number that is demonstrably better than others -- on *any*
scale. How about fewer complaints and more effort?

regards, tom lane

On Nov 19, 2008, at 11:51 PM, Tom Lane wrote:
> "Dann Corbit" <DCorbit(at)connx(dot)com> writes:
>>> I think the idea that there IS a magic number is the problem.
>>>
>>> No amount of testing is ever going to refute the argument that,
>>> under
>>> some other workload, a different value might better.
>>>
>>> But that doesn't amount to a reason to leave it the way it is.
>
>> Perhaps a table of experimental data could serve as a rough
>> guideline.
>
> The problem is not that anyone wants to leave it the way it is.
> The problem is that no one has done even a lick of work to identify
> a specific number that is demonstrably better than others -- on *any*
> scale. How about fewer complaints and more effort?

Is there even a good way to find out what planning time was? Is there
a way to gather that stat for every query a session runs?

The thought occurs to me that we're looking at this from the wrong
side of the coin. I've never, ever seen query plan time pose a
problem with Postgres, even without using prepared statements. Anyone
who actually cares that much about plan time is certainly going to
use prepared statements, which makes the whole plan time argument
moot (plan time, not parse time, but of course stats_target doesn't
impact parsing at all).

What I *have* seen, on many different databases, was problems with
bad plans due to default_stats_target being too low. Most of the time
this was solved by simply setting them to 1000. The only case where I
backed down from that and went with like 100 was a database that had
150k tables.

We've been talking about changing default_stats_target for at least 2
or 3 years now. We know that the current value is causing problems.
Can we at least start increasing it? 30 is pretty much guaranteed to
be better than 10, even if it's nowhere close to an ideal value. If
we start slowly increasing it then at least we can start seeing where
people start having issues with query plan time.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Decibel! <decibel(at)decibel(dot)org> writes:
> The thought occurs to me that we're looking at this from the wrong
> side of the coin. I've never, ever seen query plan time pose a
> problem with Postgres, even without using prepared statements.

That tells more about the type of queries you tend to run than about
whether there's an issue in general.

> Anyone
> who actually cares that much about plan time is certainly going to
> use prepared statements,

This is simply false. There's a significant performance hit caused
by using prepared statements in many cases where the planner needs
to know the parameter values in order to make good decisions.

regards, tom lane

Decibel! <decibel(at)decibel(dot)org> writes:

> Is there even a good way to find out what planning time was? Is there a way to
> gather that stat for every query a session runs?

\timing
explain select ...

> The thought occurs to me that we're looking at this from the wrong side of the
> coin. I've never, ever seen query plan time pose a problem with Postgres, even
> without using prepared statements.

I certainly have seen plan times be a problem. I wonder if you have too and
just didn't realize it. With a default_stats_target of 1000 you'll have
hundreds of kilobytes of data to slog through to plan a moderately complex
query with a few text columns. Forget about prepared queries, I've seen plan
times be unusable for ad-hoc interactive queries before.

> We've been talking about changing default_stats_target for at least 2 or 3
> years now. We know that the current value is causing problems. Can we at least
> start increasing it? 30 is pretty much guaranteed to be better than 10, even
> if it's nowhere close to an ideal value. If we start slowly increasing it then
> at least we can start seeing where people start having issues with query plan
> time.

How would you see anything from doing that? We only hear from people who have
problems so we only see half the picture. You would have no way of knowing
whether your change has helped or hurt anyone.

In any case I don't see "we know that the current value is causing problems"
as a reasonable statement. It's the *default* stats target. There's a reason
there's a facility to raise the stats target for individual columns.

As Dann said, "the idea that there IS a magic number is the problem". *Any*
value of default_stats_target will "cause" problems. Some columns will always
have skewed data sets which require unusually large samples, but most won't
and the system will run faster with a normal sample size for that majority.

The question is what value represents a good trade-off between the costs of
having large stats targets -- longer analyze, more data stored in
pg_statistics, more vacuuming of pg_statistics needed, longer plan times --
and the benefits of having larger stats targets -- fewer columns which need
raised stats targets.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

> -----Original Message-----
> From: Greg Stark [mailto:greg(dot)stark(at)enterprisedb(dot)com] On Behalf Of
> Gregory Stark
> Sent: Tuesday, November 25, 2008 5:06 PM
> To: Decibel!
> Cc: Tom Lane; Dann Corbit; Robert Haas; Bruce Momjian; Mark Wong;
> Heikki Linnakangas; Josh Berkus; Greg Smith; pgsql-
> hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] Simple postgresql.conf wizard

[snip]

> As Dann said, "the idea that there IS a magic number is the problem".
> *Any*
> value of default_stats_target will "cause" problems. Some columns will
> always
> have skewed data sets which require unusually large samples, but most
> won't
> and the system will run faster with a normal sample size for that
> majority.

No, it was somebody smarter than me who said that.

My idea was to create some kind of table which shows curves for
different values and then users will have some sort of basis for
choosing.
Of course, the guy who has 40 tables in his join with an average of 7
indexes on each table (each table containing millions of rows) and a
convoluted WHERE clause will have different needs than someone who has
simple queries and small data loads. The quality of the current
statistical measures stored will also affect the intelligence of the
query preparation process, I am sure. I do have a guess that larger and
more expensive queries can probably benefit more from larger samples
(this principle is used in sorting, for instance, where the sample I
collect to estimate the median might grow as {for instance} the log of
the data set size).

P.S.
I also do not believe that there is any value that will be the right
answer. But a table of data might be useful both for people who want to
toy with altering the values and also for those who want to set the
defaults. I guess that at one time such a table was generated to
produce the initial estimates for default values.
[snip]

"Dann Corbit" <DCorbit(at)connx(dot)com> writes:
> I also do not believe that there is any value that will be the right
> answer. But a table of data might be useful both for people who want to
> toy with altering the values and also for those who want to set the
> defaults. I guess that at one time such a table was generated to
> produce the initial estimates for default values.

Sir, you credit us too much :-(. The actual story is that the current
default of 10 was put in when we first implemented stats histograms,
replacing code that kept track of only a *single* most common value
(and not very well, at that). So it was already a factor of 10 more
stats than we had experience with keeping, and accordingly conservatism
suggested not boosting the default much past that.

So we really don't have any methodically-gathered evidence about the
effects of different stats settings. It wouldn't take a lot to convince
us to switch to a different default, I think, but it would be nice to
have more than none.

regards, tom lane

On Tue, 2008-11-25 at 20:33 -0500, Tom Lane wrote:
> "Dann Corbit" <DCorbit(at)connx(dot)com> writes:
> > I also do not believe that there is any value that will be the right
> > answer. But a table of data might be useful both for people who want to
> > toy with altering the values and also for those who want to set the
> > defaults. I guess that at one time such a table was generated to
> > produce the initial estimates for default values.
>
> Sir, you credit us too much :-(.

Better than not enough :)

> So we really don't have any methodically-gathered evidence about the
> effects of different stats settings. It wouldn't take a lot to convince
> us to switch to a different default, I think, but it would be nice to
> have more than none.

I don't this is not empirical but really, 150 is very reasonable. Let's
just set it to that by default and be done with it. It won't hurt
anything and if they need more than that, they are already investigating
either via the lists or via a vendor anyway.

Joshua D. Drake

>
> regards, tom lane
>
--
PostgreSQL
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

On Tue, Nov 25, 2008 at 8:38 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> I don't this is not empirical but really, 150 is very reasonable. Let's
> just set it to that by default and be done with it. It won't hurt
> anything and if they need more than that, they are already investigating
> either via the lists or via a vendor anyway.

Agreed.

--
Jonah H. Harris, Senior DBA
myYearbook.com

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Tuesday, November 25, 2008 5:33 PM
> To: Dann Corbit
> Cc: Gregory Stark; Decibel!; Robert Haas; Bruce Momjian; Mark Wong;
> Heikki Linnakangas; Josh Berkus; Greg Smith; pgsql-
> hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] Simple postgresql.conf wizard
>
> "Dann Corbit" <DCorbit(at)connx(dot)com> writes:
> > I also do not believe that there is any value that will be the right
> > answer. But a table of data might be useful both for people who
want
> to
> > toy with altering the values and also for those who want to set the
> > defaults. I guess that at one time such a table was generated to
> > produce the initial estimates for default values.
>
> Sir, you credit us too much :-(. The actual story is that the current
> default of 10 was put in when we first implemented stats histograms,
> replacing code that kept track of only a *single* most common value
> (and not very well, at that). So it was already a factor of 10 more
> stats than we had experience with keeping, and accordingly
conservatism
> suggested not boosting the default much past that.
>
> So we really don't have any methodically-gathered evidence about the
> effects of different stats settings. It wouldn't take a lot to
> convince
> us to switch to a different default, I think, but it would be nice to
> have more than none.

I do have a statistics idea/suggestion (possibly useful with some future
PostgreSQL 9.x or something):
It is a simple matter to calculate lots of interesting univarate summary
statistics with a single pass over the data (perhaps during a vacuum
full).
For instance with numerical columns, you can calculate mean, min, max,
standard deviation, skew, kurtosis and things like that with a single
pass over the data.
Here is a C++ template I wrote to do that:
http://cap.connx.com/tournament_software/STATS.HPP
It also uses this template:
http://cap.connx.com/tournament_software/Kahan.Hpp
which is a high-accuracy adder. These things could easily be rewritten
in C instead of C++.

Now, if you store a few numbers calculated in this way, it can be used
to augment your histogram data when you want to estimate the volume of a
request. So (for instance) if someone asks for a scalar that is ">
value" you can look to see what percentage of the tail will hang out in
that neck of the woods using standard deviation and the mean.

I have another, similar idea (possibly useful someday far off in the
future) that I think may have some merit. The idea is to create a
"statistical index". This index is updated whenever data values are
modified in any way. For scalar/ordinal values such as float, integer,
numeric it would simply store and update a statistics accumulator (a
small vector of a few items holding statistical moments, counts and
sums) for the column of interest. These indexes would be very small
and inexpensive to {for instance} memory map.

For categorical values (things like 'color' or 'department') we might
store the count for the number of items that correspond to a hash in our
statistical index. It would give you a crude "count distinct" for any
item -- the only caveat being that more than one item could possibly
have the same hash code (we would also keep a count of null items). If
the count needed to be exact, we could generate a perfect hash for the
data or store each distinct column value in the categorical index with
its hash. The size of such an index would depend on the data so that
bit or char='m'/'f' for male/female or 'y'/'n' for yes/no indexes would
contain just two counts and a column that is unique would have one hash
paired with the number one for each row of the table (a regular unique
index would clearly be better in such a case, but such distributions
could easily arise). The value of an index like this is that it is good
where the normal index types are bad (e.g. it is useless to create a
btree index on a bit column or male/female, yes/no -- things of that
nature but a statistics counter would work nicely -- to give you whole
table measures only of course). The thing that is odd about these
statistics indexes is that they do not even bother to point back to the
data that they represent -- they are just abstract measures of the
general contents.

It seems to me that this kind of information could be used to improve
the query plans for both categorical values and scalars and also be used
to generate instant answers for some kinds of statistical queries. If
used only for query planning, the values would not need to be exact, and
could be updated only at vacuum full time or some other convenient time.
The notion behind creation of a stats index would be that we may not
need to maintain this detailed information for every column, but we can
maintain such data for columns that we often filter with in our queries
to get an idea of cardinality for a subset.

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> On Tue, 2008-11-25 at 20:33 -0500, Tom Lane wrote:
>> So we really don't have any methodically-gathered evidence about the
>> effects of different stats settings. It wouldn't take a lot to convince
>> us to switch to a different default, I think, but it would be nice to
>> have more than none.

> I don't this is not empirical but really, 150 is very reasonable. Let's
> just set it to that by default and be done with it.

What happened to the "more than zero evidence" part of the discussion?

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "Dann Corbit" <DCorbit(at)connx(dot)com> writes:
>> I also do not believe that there is any value that will be the right
>> answer. But a table of data might be useful both for people who want to
>> toy with altering the values and also for those who want to set the
>> defaults. I guess that at one time such a table was generated to
>> produce the initial estimates for default values.
>
> Sir, you credit us too much :-(. The actual story is that the current
> default of 10 was put in when we first implemented stats histograms,
> replacing code that kept track of only a *single* most common value
> (and not very well, at that). So it was already a factor of 10 more
> stats than we had experience with keeping, and accordingly conservatism
> suggested not boosting the default much past that.

I think that's actually too little credit. The sample size is chosen quite
carefully based on solid mathematics to provide a specific confidence interval
estimate for queries covering ranges the size of a whole bucket.

The actual number of buckets more of an arbitrary choice. It depends entirely
on how your data is distributed and how large a range your queries are
covering. A uniformly distributed data set should only need a single bucket to
generate good estimates. Less evenly distributed data sets need more.

I wonder actually if there are algorithms for estimating the number of buckets
needed for a histogram to achieve some measurable goal. That would close the
loop. It would be much more reassuring to base the size of the sample on solid
statistics than on hunches.

> So we really don't have any methodically-gathered evidence about the
> effects of different stats settings. It wouldn't take a lot to convince
> us to switch to a different default, I think, but it would be nice to
> have more than none.

I think the difficulty (aside from testing being laborious at the best of
times) is that it's heavily dependent on data sets which are hard to generate
good examples for. Offhand I would think the census data might make a good
starting point -- it should have columns which range from perfectly uniform to
highly skewed.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

On Tue, Nov 25, 2008 at 06:59:25PM -0800, Dann Corbit wrote:
> I do have a statistics idea/suggestion (possibly useful with some future
> PostgreSQL 9.x or something):
> It is a simple matter to calculate lots of interesting univarate summary
> statistics with a single pass over the data (perhaps during a vacuum
> full).
> For instance with numerical columns, you can calculate mean, min, max,
> standard deviation, skew, kurtosis and things like that with a single
> pass over the data.

Calculating "interesting univariate summary statistics" and having
something useful to do with them are two different things entirely. Note
also that whereas this is simple for numeric columns, it's a very
different story for non-numeric data types, that don't come from a
metric space. That said, the idea of a probability metric space is well
explored in the literature, and may have valuable application. The
current histogram implementation is effectively a description of the
probability metric space the column data live in.

> Now, if you store a few numbers calculated in this way, it can be used
> to augment your histogram data when you want to estimate the volume of a
> request. So (for instance) if someone asks for a scalar that is ">
> value" you can look to see what percentage of the tail will hang out in
> that neck of the woods using standard deviation and the mean.

Only if you know that the data follow a distribution that can be
described accurately with a standard deviation and a mean. If your data
don't follow a Gaussian distribution, this will give you bad estimates.

- Josh / eggyknap

On Nov 25, 2008, at 8:59 PM, Dann Corbit wrote:
> It is a simple matter to calculate lots of interesting univarate
> summary
> statistics with a single pass over the data (perhaps during a vacuum
> full).

I don't think that the problem we have is how to collect statistics
(well, except for cross-field stuff); the problem is what to actually
do with them. What we need people to look at is how we can improve
query plan estimates across the board. Row count estimates, page
access estimates, the cost estimates for accessing those pages, etc.
This isn't a coding problem, it's an algorithm problem. It needs
someone with an advanced (if not expert) grasp of statistics who can
come up with better ways of estimating these things.

So, if you have a statistics hammer to wield, I think you'll find a
lot of nails sticking up in the planner code. Hammer on those before
worrying about additional stats to collect. :)
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

On Nov 25, 2008, at 7:06 PM, Gregory Stark wrote:
>> The thought occurs to me that we're looking at this from the
>> wrong side of the
>> coin. I've never, ever seen query plan time pose a problem with
>> Postgres, even
>> without using prepared statements.
>
> I certainly have seen plan times be a problem. I wonder if you have
> too and
> just didn't realize it. With a default_stats_target of 1000 you'll
> have
> hundreds of kilobytes of data to slog through to plan a moderately
> complex
> query with a few text columns. Forget about prepared queries, I've
> seen plan
> times be unusable for ad-hoc interactive queries before.

Can you provide any examples?

And no, I've never seen a system where a few milliseconds of plan
time difference would pose a problem. I'm not saying they don't
exist, only that I haven't seen them (including 2 years working as a
consultant).

I'll also make the argument that anyone with a system that does have
those kind of requirements will have also needed to actually tune
their config, and tune it well. I can't see them being bothered by
having to set one more parameter. There are a lot of systems that are
being impacted by our ultra-low stats target, and a lot of those
don't necessarily need a lot of hand tuning beyond the stats target.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Decibel! <decibel(at)decibel(dot)org> writes:

> On Nov 25, 2008, at 7:06 PM, Gregory Stark wrote:
>>> The thought occurs to me that we're looking at this from the wrong side of
>>> the
>>> coin. I've never, ever seen query plan time pose a problem with Postgres,
>>> even
>>> without using prepared statements.
>>
>> I certainly have seen plan times be a problem. I wonder if you have too and
>> just didn't realize it. With a default_stats_target of 1000 you'll have
>> hundreds of kilobytes of data to slog through to plan a moderately complex
>> query with a few text columns. Forget about prepared queries, I've seen plan
>> times be unusable for ad-hoc interactive queries before.
>
> Can you provide any examples?

At the time I couldn't understand what the problem was. In retrospect I'm
certain this was the problem. I had a situation where just running EXPLAIN
took 5-10 seconds. I suspect I had some very large toasted arrays which were
having to be detoasted each time. IIRC I actually reloaded the database with
pg_dump and the problem went away.

> And no, I've never seen a system where a few milliseconds of plan time
> difference would pose a problem. I'm not saying they don't exist, only that I
> haven't seen them (including 2 years working as a consultant).

How many milliseconds does it take to read a few hundred kilobytes of toasted,
compressed data? These can easily be more data than the actual query is going
to read.

Now ideally this will all be cached but the larger the data set the less
likely it will be.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

>>> Decibel! <decibel(at)decibel(dot)org> wrote:
> On Nov 25, 2008, at 7:06 PM, Gregory Stark wrote:
>>> The thought occurs to me that we're looking at this from the
>>> wrong side of the
>>> coin. I've never, ever seen query plan time pose a problem with
>>> Postgres, even
>>> without using prepared statements.
>>
>> I certainly have seen plan times be a problem. I wonder if you have

>> too and
>> just didn't realize it. With a default_stats_target of 1000 you'll

>> have
>> hundreds of kilobytes of data to slog through to plan a moderately

>> complex
>> query with a few text columns. Forget about prepared queries, I've

>> seen plan
>> times be unusable for ad-hoc interactive queries before.
>
> Can you provide any examples?
>
> And no, I've never seen a system where a few milliseconds of plan
> time difference would pose a problem.

When we first brought the statewide circuit court data onto
PostgreSQL, on some early version of 8.1, we tried boosting the
statistics targets for a few dozen important columns, and had to back
off because of plan times up in the 20 to 30 second range. I hadn't
tried it lately, so I just gave it a go with switching from a default
statistics target of 10 with no overrides to 1000.

The plan time for a fairly complex query which is run over 300,000
times per day went from 55 ms to 315 ms; however, with the particular
search criteria I used (which I knew to be "challenging") the run time
went from something which exceeded my patience tolerance for the test
(over two minutes) to two seconds, so a better plan was definitely
found.

I'm not sure what this suggests in terms of a good default value, but
just to put some numbers out there from a real-world application....

-Kevin

>>> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> I hadn't
> tried it lately, so I just gave it a go with switching from a
default
> statistics target of 10 with no overrides to 1000.

Oh, this was on 8.2.7, Linux, pretty beefy machine. Do you want the
whole set of config info and the hardware specs, or would that just be
clutter?

-Kevin

> Even though we all agree default_statistics_target = 10 is too low,
> proposing a 40X increase in the default value requires more evidence
> than this. In particular, the prospect of a 1600-fold increase in
> the typical cost of eqjoinsel() is a mite scary.

I just did some very quick testing of a couple values for
default_statistics_target on a database for work whose contents I am
not at liberty to share. Suffice it to say it is a database with
small tables, but a lot of the queries involve many joins.

ANALYZE with default_statistics_target set to 10 takes 13 s. With
100, 92 s. With 1000, 289 s.

I tried several representative queries and there was no measurable
change in run-time. I compared a couple of the plans and the plans
didn't change either.

So then I said - gee, how much can I reduce this? I looked at two of
the queries in more detail. The lowest default_statistics_target that
produced the same plan for both queries was 5. Reducing the
default_statistics_target all the way down to 1 changed the plans, but
the new plans were just as fast as the old plans.

Given the amount of clamor for a higher value for
default_statistics_target, I'm a little surprised by these results.
It may be that the queries I'm running are not ones for which more
statistics generate better plans, but I think they really are
representative of what we run. Maybe someone could suggest some types
of query that would be likely to helped by better statistics?

A random thought: maybe the reason I'm not seeing any benefit is
because my tables are just too small - most contain at most a few
thousand rows, and some are much smaller. Maybe
default_statistics_target should vary with the table size? Something
like, 0.1% of the rows to a maximum of 100... and then 0.01% of the
rows after that to some higher maximum.... but always a minimum of at
least 10. I'm guessing that people with really big tables are less
likely to mind longer planning times and more likely to benefit from
finding better plans...

...Robert

On Thu, Nov 27, 2008 at 05:15:04PM -0500, Robert Haas wrote:
> A random thought: maybe the reason I'm not seeing any benefit is
> because my tables are just too small - most contain at most a few
> thousand rows, and some are much smaller. Maybe
> default_statistics_target should vary with the table size? Something
> like, 0.1% of the rows to a maximum of 100... and then 0.01% of the
> rows after that to some higher maximum.... but always a minimum of at
> least 10. I'm guessing that people with really big tables are less
> likely to mind longer planning times and more likely to benefit from
> finding better plans...

Something like this makes an awful lot of sense to me. A higher
statistics target (in theory) better captures the fine details of a
distribution of values; tables with fewer rows are unlikely to
demonstrate fine details (as are tables with categorical, as
opposed to continuous, values). Returning to the original topic of a
configuration creator, perhaps such a tool should ask the user how big
(s)he expects the tables to grow, or perhaps it should be able to
investigate a particular column and recommend a statistics target based
on its distribution, size, etc.

- Josh / eggyknap

"Robert Haas" <robertmhaas(at)gmail(dot)com> writes:

> ANALYZE with default_statistics_target set to 10 takes 13 s. With
> 100, 92 s. With 1000, 289 s.

That is interesting. It would also be interesting to total up the time it
takes to run EXPLAIN (without ANALYZE) for a large number of queries.

I did start looking at running the experiment Tom was asking for with DBT3
data sets but, uh, the most recent dbt-3 data generation source I could find
was four years old. Is there an active source repository for this stuff?

> Given the amount of clamor for a higher value for
> default_statistics_target, I'm a little surprised by these results.
> It may be that the queries I'm running are not ones for which more
> statistics generate better plans, but I think they really are
> representative of what we run. Maybe someone could suggest some types
> of query that would be likely to helped by better statistics?

I think there are a lot of variables here. One is the distribution of the
data. Your data sounds like it's reasonably evenly distributed.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, Nov 27, 2008 at 05:15:04PM -0500, Robert Haas wrote:
> [...] Maybe
> default_statistics_target should vary with the table size? Something
> like, 0.1% of the rows to a maximum of 100... and then 0.01% of the
> rows after that to some higher maximum.... but always a minimum of at
> least 10. I'm guessing that people with really big tables are less
> likely to mind longer planning times and more likely to benefit from
> finding better plans...

What was the running time of the planner? Quadratic in statistics_target?
Take the square root of table size. Exponential? The logarithm. Or
something like that.

My 1.75 cent
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFJL6HyBcgs9XrR2kYRAqfzAJ47w720VJLHVXeS8WKH6zUjAHkbeACfTtDh
rFq512eztVnib1ozjw9sibs=
=JXyS
-----END PGP SIGNATURE-----

On Tue, 18 Nov 2008, Josh Berkus wrote:

> Regarding the level of default_stats_target, it sounds like people agree
> that it ought to be raised for the DW use-case, but disagree how much.
> If that's the case, what if we compromize at 50 for "mixed" and 100 for
> DW?

That's what I ended up doing. The attached version of this script and its
data files (I dumped all the useful bits in the current HEAD pg_settings
for it to use) now hits all of the initial goals I had for a useful
working tool here. Here's a sample run on a system with 8GB of RAM and
the default "mixed" workload. I told the tool absolutely nothing:

./pgtune -i ~/data/postgresql.conf

And it came up with a decent mixed starter configuration for this system
appended to the input postgresql.conf:

default_statistics_target = 50 # pg_generate_conf wizard 2008-11-30
maintenance_work_mem = 480MB # pg_generate_conf wizard 2008-11-30
constraint_exclusion = on # pg_generate_conf wizard 2008-11-30
checkpoint_completion_target = 0.9 # pg_generate_conf wizard 2008-11-30
effective_cache_size = 5632MB # pg_generate_conf wizard 2008-11-30
work_mem = 48MB # pg_generate_conf wizard 2008-11-30
wal_buffers = 8MB # pg_generate_conf wizard 2008-11-30
checkpoint_segments = 16 # pg_generate_conf wizard 2008-11-30
shared_buffers = 1920MB # pg_generate_conf wizard 2008-11-30
max_connections = 80 # pg_generate_conf wizard 2008-11-30

I do plan to make that look a bit prettier.

Loose ends
----------

-Kernel shm suggestions. The main hard part there is implementing the
formula from the documentation to figure out the total expected shared
block size that will be allocated. That just needs another block of time
to finish, will fit that in this week.

-Providing hints (to stderr) for things that fall somewhere between
changes to be made automatically and just things to put into the docs. I
want to warn about the implications of the default listen_addresses for
example.

-Documentation. I've got the whole outline sketched out and some text
written, just haven't had time yet to finish implementing that as Docbook.

-Tests on more platforms (this was developed on Linux). Memory detection
works on recent (>=2.5) version of Python for Windows now. I want to do
some more tests there, on Mac OS X, and on Solaris. Some *BSD testing
would also be in order once I'm outputting shm suggestions, I don't have
any systems to test that platform myself.

Other than the docs and testing, the rest of these are really optional
anyway. What is completely done is the tuning model itself and the inputs
it needs. I made some small tweaks to what Josh suggested, documented
below, and consider that frozen from my side except for review feedback.
I'll get everything mentioned above done next weekend, and expect to
submit something of commit candidate quality at that time.

Naming and promotion
--------------------

Now that the finish line is in sight, I thought a bit about what to name
this thing. The original idea Josh threw out was based on the idea that
this would generate the postgresql.conf file from scratch, which may still
happen eventually but is not a goal for this release. This really just a
tuning tool. When searching for possible names that might fit, one of the
ones I thought of was already suggested quite some time ago, in the
original discussion that led to the TODO item I'm trying to complete:

http://archives.postgresql.org/pgsql-advocacy/2003-02/msg00085.php

Since that was the only claim I could find on the name, I've changed the
name on this new version to "pgtune" and would propose that as its final
name in contrib.

I'd like to add some text mentioning the availability of this tool to the
sample postgresql.conf, and I'd certainly be in favor of the suggestion
from that old message that a note suggesing you run it should even show up
in the output from initdb.

Tuning model
------------

The actual tuning part of the code is exactly 83 lines right now and
pretty easy to read, the rest is mainly text and GUC-related baggage.
For anyone who wants to dig into the model itself, here are the other
changes I made from what Josh has suggested.

-Since I'm focused right now just on getting a good 8.4 version of this
tool running, I dropped any attempt at tuning the FSM parameters since
they're gone. With that change, there's no need to even ask about
database size anymore. I'll put something related to that in a future
release that also targets 8.3.

-The tool doesn't do anything memory-related if you have less than 256MB
of RAM. I wasn't sure how far down this model scaled and may revisit that
cut-off.

-I was uncomfortable turning off auto-vacuum in the DW case, for fear it
would cause trouble for a newbie admin who happens to be stuck with such a
job. Instead I'm putting a section in the documentation about the
parameters that might be turned by hand beyond what the tool does. That
can mention that further increases in default_stats_target and disabling
auto-vacuum might make sense in the DW case.

-I will add a HINT generated if listen_addresses isn't set to '*", but the
tool itself never makes that change. As I said before in this thread, I
don't want to be responsible for making a change that might not be noticed
that impacts system security.

-There was no difference in the Web vs. OLTP parameters. I added one
place they diverge: the OLTP settings now have a higher
checkpoint_segments parameter. I felt that 8 is a bit low for that
application type and increased it to 16. This makes it possible to spread
the checkpoint out much more usefully.

-I changed the model for wal_buffers to be 512KB * checkpoint_segments.
That is the same value in most cases as what was originally suggested, and
the delta in the ones that changed wasn't large.

-maint_work_mem is now capped at 1GB. My own tests and others I've seen
reported on the lists here have suggested its useful upper setting is
around that range, and I'd hate to see someone run this on a DW system
with, say, 64GB of RAM and set aside 8GB of RAM just for maint_work_mem.

-checkpoint_completion_target is raised to the maximum you can set it to
while still having completion with at least one segment of slop for
rounding. That's the logic behind why it's only at 0.5 with the default
of 3 segments. The actual underlying formula I normally use is to be
slightly more conservative than
(checkpoint_segments - 1)/checkpoint_segments, capped at 0.9.

-I ignored the "round up/down" suggestions and just round everything down
using an algorithm that makes it more likely that a larger unit (MB
instead of kB, GB instead of MB) can be used when displaying the value.

And here are some platform improvements made since my last code drop here:

-Operating system is detected, as is RAM if on an OS that the detection
works on. The only parameter you really should need to specify if all
that works is the application type. I expect a future version of this
might support overriding the OS, so that you can run the tuning tool on a
system other than the server that configuration is going to be deployed
on.

-Platform bit width is detected (Python looks at how wide a pointer is to
figure that out), and that's used to figure out whether to load a 32-bit
based set of information from pg_settings or a 64-bit one.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Sun, 30 Nov 2008, Greg Smith wrote:

> Memory detection works on recent (>=2.5) version of Python for Windows
> now.

I just realized that the provided configuration is really not optimal for
Windows users because of the known limitations that prevent larger
shared_buffers settings from being effective on that platform. I know
there are some notes on that subject in the archives that I'll look
though, but I'd appreciate a suggestion for what a good upper-limit for
that setting is on Windows. I also wonder whether any of the other
parameters have similar restrictions on their useful range.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Sun, Nov 30, 2008 at 09:17:37PM -0500, Greg Smith wrote:
> That's what I ended up doing. The attached version of this script and its
> data files (I dumped all the useful bits in the current HEAD pg_settings
> for it to use) now hits all of the initial goals I had for a useful
> working tool here. Here's a sample run on a system with 8GB of RAM and
> the default "mixed" workload. I told the tool absolutely nothing:
>
> ./pgtune -i ~/data/postgresql.conf

Looks very nice.

> shared_buffers = 1920MB # pg_generate_conf wizard 2008-11-30

Do you have a check somewhere to see if this exceeds the total SYSV
memory allowed by the OS. Otherwise you've just output an unstartable
config. The output of /sbin/sysctl should tell you.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

On Mon, 1 Dec 2008, Martijn van Oosterhout wrote:

> Do you have a check somewhere to see if this exceeds the total SYSV
> memory allowed by the OS. Otherwise you've just output an unstartable
> config. The output of /sbin/sysctl should tell you.

Something to address that is listed as the first thing in the "Loose Ends"
section of the message.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Mon, Dec 1, 2008 at 3:21 AM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> On Sun, 30 Nov 2008, Greg Smith wrote:
>
>> Memory detection works on recent (>=2.5) version of Python for Windows
>> now.
>
> I just realized that the provided configuration is really not optimal for
> Windows users because of the known limitations that prevent larger
> shared_buffers settings from being effective on that platform. I know there
> are some notes on that subject in the archives that I'll look though, but
> I'd appreciate a suggestion for what a good upper-limit for that setting is
> on Windows. I also wonder whether any of the other parameters have similar
> restrictions on their useful range.

It's going to be of little use to 99% of Windows users anyway as it's
written in Python. What was wrong with C?

FWIW though, in some pgbench tests on XP Pro, on a 4GB machine, 512MB
seemed to be consistently the most effective size (out of tests on
32MB, 512MB and 1GB). There wasn't much between 32 and 512 though - my
suspicion is that 128 or 256 would be similarly effective. I didn't
have time to test that though.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

Dave Page wrote:
> On Mon, Dec 1, 2008 at 3:21 AM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
>> On Sun, 30 Nov 2008, Greg Smith wrote:
>>
>>> Memory detection works on recent (>=2.5) version of Python for Windows
>>> now.
>> I just realized that the provided configuration is really not optimal for
>> Windows users because of the known limitations that prevent larger
>> shared_buffers settings from being effective on that platform. I know there
>> are some notes on that subject in the archives that I'll look though, but
>> I'd appreciate a suggestion for what a good upper-limit for that setting is
>> on Windows. I also wonder whether any of the other parameters have similar
>> restrictions on their useful range.
>
> It's going to be of little use to 99% of Windows users anyway as it's
> written in Python. What was wrong with C?

It could be shipped with a bundled python, I guess.

Python is a lot more common in these scenarios than any of the other
"unixy languages" - much more common than perl for example - on Windows
in my experience. It could be because it's easier to bundle or something?

> FWIW though, in some pgbench tests on XP Pro, on a 4GB machine, 512MB
> seemed to be consistently the most effective size (out of tests on
> 32MB, 512MB and 1GB). There wasn't much between 32 and 512 though - my
> suspicion is that 128 or 256 would be similarly effective. I didn't
> have time to test that though.

That's about what I've heard around as well - I don't think I've ever
heard of a case where >512 has actually helped.

//Magnus

I just gave this a try and got:

$ ./pgtune
Traceback (most recent call last):
File "./pgtune", line 20, in <module>
from ctypes.wintypes import *
File "/usr/lib/python2.5/ctypes/wintypes.py", line 21, in <module>
class VARIANT_BOOL(_SimpleCData):
ValueError: _type_ 'v' not supported

This is FC7, installed RPM is python-2.5-15.fc7.

(This is also my #1 beef with Python: too many utterly impenetrable
error messages... or at least impenetrable to me.)

...Robert

On Mon, 1 Dec 2008, Dave Page wrote:

> It's going to be of little use to 99% of Windows users anyway as it's
> written in Python. What was wrong with C?

It's 471 lines of Python code that leans heavily on that language's
Dictionary type to organize everything. Had I insisted on writing
directly to C first, it would have be of no use to 100% of all users in
the 8.4 timeframe because it wouldn't be even remotely close to finished
by now.

I'd ultimately like to use the Python version as a spec to produce a C
implementation, because that's the only path to get something like this
integrated into initdb itself. There were just too many thing to get
under control for that to practical just yet.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> I'd ultimately like to use the Python version as a spec to produce a C
> implementation, because that's the only path to get something like this
> integrated into initdb itself.

It won't get integrated into initdb in any case: a standalone tool is
the correct thing. Or do you think people should have to initdb in
order to get fresh advice after changes in the environment?

regards, tom lane

On Mon, 1 Dec 2008, Robert Haas wrote:

> I just gave this a try and got:
>
> $ ./pgtune
> Traceback (most recent call last):
> File "./pgtune", line 20, in <module>
> from ctypes.wintypes import *
> File "/usr/lib/python2.5/ctypes/wintypes.py", line 21, in <module>
> class VARIANT_BOOL(_SimpleCData):
> ValueError: _type_ 'v' not supported

Looks like I need to add Python 2.5+Linux to my testing set. I did not
expect that the UNIX distributions of Python 2.5 would ship with
wintypes.py at all. I think I can fix this on the spot though. On line
40, you'll find this bit:

except ImportError:

Change that to the following:

except ImportError,ValueError:

And it should pass that point. If it doesn't, you can try the completely
general:

except:

And then it should be OK. I should probably use that formulation in that
particular section.

> (This is also my #1 beef with Python: too many utterly impenetrable
> error messages... or at least impenetrable to me.)

That one made sense to me, but that is one of the sharper parts of its
learning curve. I think part of the problem is that much of the tutorial
Python code out there never gets into error handling. One of the things
Java does that I miss is that if you create a "try" block, it won't work
until you have addressed every possible exception the code in there can
generate. It may be impractical for Python to know that much, but I
wonder if a lint-ish tool for the language might be able to do it.

The above mumbling is certainly off-topic for this list though.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Mon, 1 Dec 2008, Tom Lane wrote:

> Greg Smith <gsmith(at)gregsmith(dot)com> writes:
>> I'd ultimately like to use the Python version as a spec to produce a C
>> implementation, because that's the only path to get something like this
>> integrated into initdb itself.
>
> It won't get integrated into initdb in any case: a standalone tool is
> the correct thing. Or do you think people should have to initdb in
> order to get fresh advice after changes in the environment?

The long-term goal Josh suggested aiming for (that I abandoned for now)
was replacing the part of initdb that creates the initial postgresql.conf
via the sample with a call to a program that generates it instead. Said
program could also run standalone for later updates to the configuration.

I have no interest in arguing about whether that's a good or bad idea at
this point, I've accepted that the only useful statements to be made in
this area come in the form of a working implementation to review. Until I
have one of those, this really isn't worth getting into.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Thu, Nov 13, 2008 at 11:53 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> A lot of people have suggested raising our default_statistics target,
>> and it has been rejected because there's some O(n^2) behavior in the
>> planner, and it makes ANALYZE slower, but it's not that crazy.
>
> I think everyone agrees it ought to be raised. Where the rubber meets
> the road is deciding just *what* to raise it to. We've got no
> convincing evidence in favor of any particular value.
>
> If someone actually wanted to put some effort into this, I'd suggest
> taking some reasonably complex benchmark (maybe TPCH or one of the DBT
> series) and plotting planner runtime for each query as a function of
> statistics_target, taking care to mark the breakpoints where it shifted
> to a better (or worse?) plan due to having better stats.

Hi all,

I have some data from a mostly un-tuned system. I have a 10GB scale
factor using DBT-3 on a single 25 disk hardware RAID-0 lun. Generally
speaking, it seems like on un-tuned systems increasing the
default_statistics_target for this workload doesn't have a clear
benefit. Here is a histogram of the Power Test and Throughput Test
with default GUC values. The Power and Throughput test results are
plotted against the default_statistics_target value. For reminders,
the Power Test executes each query serially, while the Throughput Test
executes 8 streams of the 22 queries simultaneously.

Default values:
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/default.png

Raw data in http://207.173.203.223/~markwkm/community6/dbt3/,
directories 1 - 11.

I started increasing the shared_buffers and effective_cache_size to
see if there might be anything more interesting in the results, but
still don't think so.

shared_buffers=2048MB, effective_cache_size=1024MB
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/2048x1024.png

Raw data in http://207.173.203.223/~markwkm/community6/dbt3/,
directories 12 - 22.

-----

shared_buffers=4096MB, effective_cache_size=2048MB
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/4096x2048.png

Raw data in http://207.173.203.223/~markwkm/community6/dbt3/,
directories 23 - 33.

-----

shared_buffers=20480MB
effective_cache_size=10240MB
checkpoint_segments=3000

http://207.173.203.223/~markwkm/pgsql/default_statistics_target/20480x10240.png

Raw data in http://207.173.203.223/~markwkm/community6/dbt3/,
directories 34 - 44.

So then I attempted to see if there might have been difference between
the executing time of each individual query with the above parameters.
The queries that don't seem to be effected are Q1, Q4, Q12, Q13, and
Q15. Q17 suggests that anything higher than
default_statistics_target=10 is an improvement. The rest of the
queries appears not to follow any particular trend with respect to
default_statistics_target.

Here's query 17 when default_statistics_target=10

EXPLAIN ANALYZE select sum(l_extendedprice) / 7.0 as avg_yearly from
lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#52'
and p_container = 'SM PACK' and l_quantity < ( select 0.2 *
avg(l_quantity) from lineitem where l_partkey = p_partkey );

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=15725486.01..15725486.02 rows=1 width=4) (actual
time=31669.249..31669.249 rows=1 loops=1)
-> Hash Join (cost=70662.08..15725435.96 rows=20019 width=4)
(actual time=690.551..31666.031 rows=5413 loops=1)
Hash Cond: (public.lineitem.l_partkey = part.p_partkey)
Join Filter: (public.lineitem.l_quantity < (subplan))
-> Seq Scan on lineitem (cost=0.00..1668441.22
rows=60052722 width=12) (actual time=0.007..11016.720 rows=60046144
loops=1)
-> Hash (cost=70637.08..70637.08 rows=2000 width=4) (actual
time=548.192..548.192 rows=1986 loops=1)
-> Seq Scan on part (cost=0.00..70637.08 rows=2000
width=4) (actual time=0.066..547.106 rows=1986 loops=1)
Filter: ((p_brand = 'Brand#52'::bpchar) AND
(p_container = 'SM PACK'::bpchar))
SubPlan
-> Aggregate (cost=227.86..227.87 rows=1 width=4) (actual
time=0.141..0.141 rows=1 loops=59857)
-> Index Scan using i_l_partkey on lineitem
(cost=0.00..227.72 rows=54 width=4) (actual time=0.071..0.132 rows=31
loops=59857)
Index Cond: (l_partkey = $0)
Total runtime: 31669.340 ms
(13 rows)

Here's query 17 when default_statistics_target=1000

EXPLAIN ANALYZE select sum(l_extendedprice) / 7.0 as avg_yearly from
lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#21'
and p_container = 'LG BAG' and l_quantity < ( select 0.2 *
avg(l_quantity) from lineitem where l_partkey = p_partkey );

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=8660521.85..8660521.87 rows=1 width=4) (actual
time=7035.069..7035.069 rows=1 loops=1)
-> Nested Loop (cost=0.00..8660472.24 rows=19845 width=4) (actual
time=0.183..7033.231 rows=5148 loops=1)
Join Filter: (public.lineitem.l_quantity < (subplan))
-> Seq Scan on part (cost=0.00..70639.00 rows=1983 width=4)
(actual time=0.054..544.415 rows=1934 loops=1)
Filter: ((p_brand = 'Brand#21'::bpchar) AND
(p_container = 'LG BAG'::bpchar))
-> Index Scan using i_l_partkey on lineitem
(cost=0.00..131.88 rows=31 width=12) (actual time=1.378..1.400 rows=30
loops=1934)
Index Cond: (public.lineitem.l_partkey = part.p_partkey)
SubPlan
-> Aggregate (cost=135.45..135.46 rows=1 width=4) (actual
time=0.064..0.064 rows=1 loops=58151)
-> Index Scan using i_l_partkey on lineitem
(cost=0.00..135.37 rows=31 width=4) (actual time=0.005..0.056 rows=31
loops=58151)
Index Cond: (l_partkey = $0)
Total runtime: 7035.149 ms
(12 rows)

Histograms of each individual query:

Q1
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q1.png

Q2
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q2.png

Q3
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q3.png

Q4
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q4.png

Q5
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q5.png

Q6
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q6.png

Q7
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q7.png

Q8
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q8.png

Q9
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q9.png

Q10
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q10.png

Q11
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q11.png

Q12
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q12.png

Q13
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q13.png

Q14
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q14.png

Q15
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q15.png

Q16
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q16.png

Q17
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q17.png

Q18
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q18.png

Q19
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q19.png

Q20
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q20.png

Q21
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q21.png

Q22
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q22.png

I dumped a lot of raw data and gave little analysis, I hope someone
can make more sense of it... :)

Regards,
Mark

On Mon, 1 Dec 2008, Mark Wong wrote:

> So then I attempted to see if there might have been difference between
> the executing time of each individual query with the above parameters.
> The queries that don't seem to be effected are Q1, Q4, Q12, Q13, and
> Q15. Q17 suggests that anything higher than
> default_statistics_target=10 is an improvement. The rest of the queries
> appears not to follow any particular trend with respect to
> default_statistics_target.

The interesting ones are Q2, Q9, Q17, Q18, and Q20, and that data is much
more useful than the summary. As you mention, Q17 improves significantly
with a higher target. All of the rest are dramatically slower in one or
both tests going from default_statistics_target=10 to 100. Those look
like the most useful data points on the X axis--the increases from 100 up
to 1000 aren't particularly interesting in most of these, except in Q20
where the "Power Test" seems to oscillate between degrees of good and bad
behavior seemingly at random.

My picks for the most useful graphs from the long list Mark sent:

http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q2.png
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q9.png
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q17.png
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q18.png
http://207.173.203.223/~markwkm/pgsql/default_statistics_target/q20.png

I think the tests you could consider next is to graph the target going
from 10 to 100 in steps of 10 just for those 5 queries. If it gradually
degrades, that's interesting but hard to nail down. But if there's a
sharp transition, getting an explain plan for the two sides of that should
provide some insight. I'm really more interested in the ones that slowed
down than the one that improved, understanding that might finally provide
some evidence against increasing it by default.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

> Hi all,
>
> I have some data [...]

Thanks for gathering this data.

The first thing I notice is that the two versions of Q17 that you are
running are actually not the exact same query - there are hard-coded
constants that are different in each case, and that matters. The
substituted parameter doesn't affect the plan, but the constants that
are actually in the query do.

It seems that some of the other queries may be similar - for example,
Q2 has a couple of very fast runs (with default_stats_target 10 and
600) that probably got a better plan than the other runs, and Q6, Q9,
and Q20 look suspiciously like there may have been two different plans
in the mix as well, presumably because the actual queries varied
somewhat.

I think the only way to fix this is to run the test a large number of
times with each DST and average. :-(

Q17 looks like a pretty illustrative example of how a higher
statistics target can (potentially) help: it enables the planner to
realize that a qual on the part table is highly selective, and
therefore switch to a nested loop w/index-scan instead of a hash join.
(This may not be the only case, but I can't think of the others right
now.) I haven't actually looked at the selectivity estimation code,
but I'm assuming that if we have n MCVs then we can estimate that any
non-MCV occurs with frequency < 1/n (in fact, < the frequency of the
least-frequent MCV, but 1/n at most). So we want n to be large enough
that 1/n is below the cutoff for switching to an index scan
(otherwise, we'll end up using the hash join even when the qual
selects an extremely infrequent value). It might be helpful to figure
out where that cutoff is and what factors it depends on.

...Robert

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> ... where the "Power Test" seems to oscillate between degrees of good and bad
> behavior seemingly at random.

Are any of the queries complicated enough to trigger GEQO planning?

regards, tom lane

Gregory Stark wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> > "Dann Corbit" <DCorbit(at)connx(dot)com> writes:
> >> I also do not believe that there is any value that will be the right
> >> answer. But a table of data might be useful both for people who want to
> >> toy with altering the values and also for those who want to set the
> >> defaults. I guess that at one time such a table was generated to
> >> produce the initial estimates for default values.
> >
> > Sir, you credit us too much :-(. The actual story is that the current
> > default of 10 was put in when we first implemented stats histograms,
> > replacing code that kept track of only a *single* most common value
> > (and not very well, at that). So it was already a factor of 10 more
> > stats than we had experience with keeping, and accordingly conservatism
> > suggested not boosting the default much past that.
>
> I think that's actually too little credit. The sample size is chosen quite
> carefully based on solid mathematics to provide a specific confidence interval
> estimate for queries covering ranges the size of a whole bucket.
>
> The actual number of buckets more of an arbitrary choice. It depends entirely
> on how your data is distributed and how large a range your queries are
> covering. A uniformly distributed data set should only need a single bucket to
> generate good estimates. Less evenly distributed data sets need more.
>
> I wonder actually if there are algorithms for estimating the number of buckets
> needed for a histogram to achieve some measurable goal. That would close the
> loop. It would be much more reassuring to base the size of the sample on solid
> statistics than on hunches.

I have a few thoughts on this. First, people are correct that there is
no perfect default_statistics_target value. This is similar to the
problem with the pre-8.4 max_fsm_pages/max_fsm_relations, for which
there also was never a perfect value. But, if the FSM couldn't store
all the free space, a server log message was issued that recommended
increasing these values; the same is still done for
checkpoint_segments. Is there a way we could emit a server log message
to recommend increasing the statistics targets for specific columns?

Also, is there a way to increase the efficiency of the statistics
targets lookups? I assume the values are already sorted in the
pg_statistic arrays; do we already do a binary lookup on those? Does
that help?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Mark,

> Generally
> speaking, it seems like on un-tuned systems increasing the
> default_statistics_target for this workload doesn't have a clear
> benefit.

Do you have any idea how skewed the distribution of data for DBT3 is? If
values are being generated in relatively equal proportion, I'd expect
increasing DST to have little effect. The databases where higher DST is
useful is ones with skewed data distribution.

Unfortunately, all the data examples I could point to are proprietary
customer databases :-(

--
--Josh

Josh Berkus
PostgreSQL
San Francisco

* Josh Berkus <josh(at)agliodbs(dot)com> [081202 15:54]:

> Do you have any idea how skewed the distribution of data for DBT3 is? If
> values are being generated in relatively equal proportion, I'd expect
> increasing DST to have little effect. The databases where higher DST is
> useful is ones with skewed data distribution.
>
> Unfortunately, all the data examples I could point to are proprietary
> customer databases :-(

But no body's asking anybody to point out "skewed" data... I think it's
*unanimous* that on skewed data, a higher stats target is needed for the
skewed columns.

The question is how much of a penalty the (majority of?) users with "normal"
data columns will have to pay in stats/planning overhead to accomidate a
blanket increase in DST for the (few?) skewed columns.

I think Marks started to try and show that overhead/difference with real
numbers.

My (probably unsubstantiated) bias is showing, but nobody else has (yet)
showed otherwise ;-)

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

Greg,

On Mon, Dec 1, 2008 at 3:17 AM, Greg Smith <gsmith(at)gregsmith(dot)com> wrote:
> ./pgtune -i ~/data/postgresql.conf

First, thanks for your work: it will really help a lot of people to
have a decent default configuration.

A couple of comments from reading the code (I didn't run it yet):
- it would be really nice to make it work with Python 2.4 as RHEL 5 is
a Python 2.4 thing and it is a very widespread platform out there,
- considering the audience of this tool, I think you should explain in
the usage text which type of workload implies each database type (DW,
OLTP, Web, Mixed, Desktop).
- not sure of the wording of "The file this needs to operate correctly
can be generated with:"
- it would be nice to be able to define the architecture (32-64 bits)
from the command line (especially considering I won't be able to run
it on our target boxes which are all RHEL 5 :))

I'll see if I have more feedback while testing it for real.

--
Guillaume

> Looks like I need to add Python 2.5+Linux to my testing set. I did not
> expect that the UNIX distributions of Python 2.5 would ship with wintypes.py
> at all. I think I can fix this on the spot though. On line 40, you'll find
> this bit:
>
> except ImportError:
>
> Change that to the following:
>
> except ImportError,ValueError:

That didn't work, same error message.

> And it should pass that point. If it doesn't, you can try the completely
> general:
>
> except:

That worked.

The settings that this initially spit out (I guess it defaults to
"mixed" mode) didn't look too sane to me, because as discussed
elsewhere on this thread 50 is not a reasonable value for
default_statistics_target for my installation. It also wanted to set
constraint_exclusion to on, which I'm pretty confident is useless.

Then I tried "-T web" and got what seemed like a more reasonable set
of values. But I wasn't sure I needed that many connections, so I
added "-c 150" to see how much difference that made. Kaboom!

$ ./pgtune -i ~postgres/data/postgresql.conf -T web -c 150
Traceback (most recent call last):
File "./pgtune", line 463, in <module>
wizardTune(config,options,settings)
File "./pgtune", line 403, in wizardTune
'web':mem/con, 'oltp':mem/con,'dw':mem/con/2,
TypeError: unsupported operand type(s) for /: 'int' and 'str'

I'm not sure what "mixed" mode is supposed to be, but based on what
I've seen so far, I'm a skeptical of the idea that encouraging people
to raise default_statistics_target to 50 and turn on
constraint_exclusion is reasonable. I'm also a bit surprised that
there doesn't seem to be anything here that depends on the size of the
database, even order-of-magnitude. It seems like the right value for
checkpoint_segments, at least, might depend on that.

...Robert

On Wed, 2008-12-03 at 13:30 -0500, Robert Haas wrote:
> > Looks like I need to add Python 2.5+Linux to my testing set. I did not
> > expect that the UNIX distributions of Python 2.5 would ship with wintypes.py
> > at all. I think I can fix this on the spot though. On line 40, you'll find
> > this bit:
> >
> > except ImportError:
> >
> > Change that to the following:
> >
> > except ImportError,ValueError:
>
> That didn't work, same error message.
>
> > And it should pass that point. If it doesn't, you can try the completely
> > general:
> >
> > except:
>
> That worked.
>
> The settings that this initially spit out (I guess it defaults to
> "mixed" mode) didn't look too sane to me, because as discussed
> elsewhere on this thread 50 is not a reasonable value for
> default_statistics_target for my installation. It also wanted to set
> constraint_exclusion to on, which I'm pretty confident is useless.
>
> Then I tried "-T web" and got what seemed like a more reasonable set
> of values. But I wasn't sure I needed that many connections, so I
> added "-c 150" to see how much difference that made. Kaboom!
>
> $ ./pgtune -i ~postgres/data/postgresql.conf -T web -c 150
> Traceback (most recent call last):
> File "./pgtune", line 463, in <module>
> wizardTune(config,options,settings)
> File "./pgtune", line 403, in wizardTune
> 'web':mem/con, 'oltp':mem/con,'dw':mem/con/2,
> TypeError: unsupported operand type(s) for /: 'int' and 'str'
>
> I'm not sure what "mixed" mode is supposed to be, but based on what
> I've seen so far, I'm a skeptical of the idea that encouraging people
> to raise default_statistics_target to 50 and turn on
> constraint_exclusion is reasonable.

Why?

> I'm also a bit surprised that
> there doesn't seem to be anything here that depends on the size of the
> database, even order-of-magnitude. It seems like the right value for
> checkpoint_segments, at least, might depend on that.

What does checkpoint_segments have to do with the size of the database?

Joshua D. Drake

>
> ...Robert
>
--
PostgreSQL
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

>> I'm not sure what "mixed" mode is supposed to be, but based on what
>> I've seen so far, I'm a skeptical of the idea that encouraging people
>> to raise default_statistics_target to 50 and turn on
>> constraint_exclusion is reasonable.
>
> Why?

Because both of those settings are strictly worse for my database than
the defaults. I don't have any partitioned tables, and see:

http://archives.postgresql.org/pgsql-hackers/2008-11/msg01837.php

>> I'm also a bit surprised that
>> there doesn't seem to be anything here that depends on the size of the
>> database, even order-of-magnitude. It seems like the right value for
>> checkpoint_segments, at least, might depend on that.
>
> What does checkpoint_segments have to do with the size of the database?

It seems unlikely that you would want 256 MB of checkpoint segments on
a database that is only 100 MB (or even 500 MB). But you might very
well want that on a database that is 1 TB.

...Robert

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:

> On Wed, 2008-12-03 at 13:30 -0500, Robert Haas wrote:
>> I'm not sure what "mixed" mode is supposed to be, but based on what
>> I've seen so far, I'm a skeptical of the idea that encouraging people
>> to raise default_statistics_target to 50 and turn on
>> constraint_exclusion is reasonable.
>
> Why?

Well did you have any response to what I posited before? I said "mixed" should
produce the same settings that the default initdb settings produce. At least
on a moderately low-memory machine that initdb targets.

It sure seems strange to me to have initdb which presumably is targeting a
"mixed" system -- where it doesn't know for sure what workload will be run --
produce a different set of values than the tuner on the same machine.

>> I'm also a bit surprised that
>> there doesn't seem to be anything here that depends on the size of the
>> database, even order-of-magnitude. It seems like the right value for
>> checkpoint_segments, at least, might depend on that.
>
> What does checkpoint_segments have to do with the size of the database?

I had the same reaction but I think he's right.

checkpoint_segments is the maximum amount of space you want the WAL to take up
(ideally). Presumably on a small database you don't want hundreds of megabytes
of WAL for a 10M database. But on a terabyte data warehouse sitting on a big
SAN you're not going to be concerned with how much space the WAL files are
taking. In fact, really it would be nice if we allowed units of space (MB, GB,
etc) for checkpoint_segments.

I used to think of checkpoint_segments in terms of transaction rate and
maximum tolerable recovery time but really if those are your constraints
you're better off using checkpoint_timeout I think.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!

On Wed, 2008-12-03 at 15:21 -0500, Robert Haas wrote:
> >> I'm not sure what "mixed" mode is supposed to be, but based on what
> >> I've seen so far, I'm a skeptical of the idea that encouraging people
> >> to raise default_statistics_target to 50 and turn on
> >> constraint_exclusion is reasonable.
> >
> > Why?
>
> Because both of those settings are strictly worse for my database than

I can see an argument about constraint_exclusion but
default_statistics_target I don't.

> the defaults. I don't have any partitioned tables, and see:
>
> http://archives.postgresql.org/pgsql-hackers/2008-11/msg01837.php
> >
> > What does checkpoint_segments have to do with the size of the database?
>
> It seems unlikely that you would want 256 MB of checkpoint segments on
> a database that is only 100 MB (or even 500 MB). But you might very
> well want that on a database that is 1 TB.

It also seems unlikely that you would hit 256MB of checkpoint segments
on a 100MB database before checkpoint_timeout and if you did, you
certainly did need them.

Remember postgresql only creates the segments when it needs them.

Sincerely,

Joshua D. Drake

>
> ...Robert
>
--
PostgreSQL
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

> Well did you have any response to what I posited before? I said "mixed" should
> produce the same settings that the default initdb settings produce. At least
> on a moderately low-memory machine that initdb targets.

I'm actually really skeptical of this whole idea of modes. The main
thing mode does, other than set max connections, is handle desktop
differently than other modes by decreasing shared_buffers and
effective_cache_size by 4x and work_mem by 3x. And the default
settings for max_connections are a SWAG that could easily be way off
for any particular installation. I think it would be more useful to
get rid of modes, accept the user is going to have to specify
max_connections if the default of, say, 100 is not reasonable, and
handle the desktop case by telling the user to rerun the tool
overriding the system memory with a lower value.

I'm not sure if you've thought about this, but there is also a
difference between max_connections and maximum LIKELY connections.
For example my apps don't have too many users, since they are
internal-facing. But setting max_connections to 100 gives me a nice
buffer just in case everyone decides to log on at once. Still, for
performance reasons, I'd prefer to calculate based on a more likely
scenario, where the concurrent user count might be only 10 or 20.

...Robert

> I can see an argument about constraint_exclusion but
> default_statistics_target I don't.

Why not? I don't want to accept a big increase in ANALYZE times (or
planning times, though I'm really not seeing that at this point)
without some benefit.

>> It seems unlikely that you would want 256 MB of checkpoint segments on
>> a database that is only 100 MB (or even 500 MB). But you might very
>> well want that on a database that is 1 TB.
>
> It also seems unlikely that you would hit 256MB of checkpoint segments
> on a 100MB database before checkpoint_timeout and if you did, you
> certainly did need them.

So why do we have this parameter at all?

...Robert

On Wed, 2008-12-03 at 16:37 -0500, Robert Haas wrote:
> > I can see an argument about constraint_exclusion but
> > default_statistics_target I don't.
>
> Why not? I don't want to accept a big increase in ANALYZE times (or
> planning times, though I'm really not seeing that at this point)
> without some benefit.

If you are concerned about the analyze time between 10, 50 and 150, I
would suggest that you are concerned about the wrong things. Remember
that this "simple postgresql.conf wizard" is for those who don't know,
don't want to know and will likely never know how to answer, "How do I
configure this parameter?"

> >
> > It also seems unlikely that you would hit 256MB of checkpoint segments
> > on a 100MB database before checkpoint_timeout and if you did, you
> > certainly did need them.
>
> So why do we have this parameter at all?

Excellent question, for a different thread :)

Joshua D. Drake

>
> ...Robert
>
--
PostgreSQL
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:

> It also seems unlikely that you would hit 256MB of checkpoint segments
> on a 100MB database before checkpoint_timeout and if you did, you
> certainly did need them.
>
> Remember postgresql only creates the segments when it needs them.

Should we change the initdb output then?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

On Wed, Dec 3, 2008 at 4:41 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> If you are concerned about the analyze time between 10, 50 and 150, I
> would suggest that you are concerned about the wrong things. Remember

I can't rule that out. What things do you think I should be concerned
about? ISTM that default_statistics_target trades off ANALYZE time
and query planning time vs. the possibility of better plans. If the
former considerations are not an issue for dst = 50, then maybe we
should emit 50 by default. But the limited evidence that has been
published in this forum thus far doesn't support that contention.

>> > It also seems unlikely that you would hit 256MB of checkpoint segments
>> > on a 100MB database before checkpoint_timeout and if you did, you
>> > certainly did need them.
>>
>> So why do we have this parameter at all?
>
> Excellent question, for a different thread :)

I think the rhetorical answer is "so that we don't fill up the disk",
which gets us back to database size.

...Robert

On Wed, 2008-12-03 at 17:33 -0500, Robert Haas wrote:
> On Wed, Dec 3, 2008 at 4:41 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> > If you are concerned about the analyze time between 10, 50 and 150, I
> > would suggest that you are concerned about the wrong things. Remember
>
> I can't rule that out. What things do you think I should be concerned
> about?

Your databases production performance with the change of the parameter.
Quite a bit more often than not, your problem (if you have one) isn't
going to be default_statistics_target is too high.

> ISTM that default_statistics_target trades off ANALYZE time
> and query planning time vs. the possibility of better plans. If the
> former considerations are not an issue for dst = 50, then maybe we
> should emit 50 by default. But the limited evidence that has been
> published in this forum thus far doesn't support that contention.
>

Actually there are years worth of evidence in these archives. Not that
the 50 is the right number but that the current settings are definitely
wrong and that higher ones are needed. That people generally start
around 100 and go from there, except where they don't and then someone
like Tom, I or some other person says, "Oh you need to increase
default_statistics_target".

There is no empirical evidence that 50 is the right setting but there is
more than enough anecdotal evidence to suggest that 50 is a lot better
than 10 and that even higher than 50 is reasonable. In an effort to
follow the PostgereSQL conservative mantra, 50 is a good compromise.

> >> > It also seems unlikely that you would hit 256MB of checkpoint segments
> >> > on a 100MB database before checkpoint_timeout and if you did, you
> >> > certainly did need them.
> >>
> >> So why do we have this parameter at all?
> >
> > Excellent question, for a different thread :)
>
> I think the rhetorical answer is "so that we don't fill up the disk",

I don't think at any time I have said to my self, I am going to set this
parameter low so I don't fill up my disk. If I am saying that to myself
I have either greatly underestimated the hardware for the task. Consider
that we are quarreling over what amounts to a nominal amount of hard
drive space, 1000 checkpoint_segments = 1.6G of space. My phone has more
capacity than that.

*If* hard drive is a space (that much of) a concern then you are having
other problems already that pgtune won't satisfy and you should be
manually tuning the conf in the first place.

Joshua D. Drake

> which gets us back to database size.
>
> ...Robert
>
--
PostgreSQL
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

>>> "Robert Haas" <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Dec 3, 2008 at 4:41 PM, Joshua D. Drake
<jd(at)commandprompt(dot)com> wrote:
>> If you are concerned about the analyze time between 10, 50 and 150,
I
>> would suggest that you are concerned about the wrong things.
Remember
>
> I can't rule that out. What things do you think I should be
concerned
> about? ISTM that default_statistics_target trades off ANALYZE time
> and query planning time vs. the possibility of better plans. If the
> former considerations are not an issue for dst = 50, then maybe we
> should emit 50 by default. But the limited evidence that has been
> published in this forum thus far doesn't support that contention.

One more data point to try to help.

While the jump from a default_statistics_target from 10 to 1000
resulted in a plan time increase for a common query from 50 ms to 310
ms, at a target of 50 the plan time was 53 ms. Analyze time was 7.2
minutes and 18.5 minutes for targets of 10 and 50. This is an 842 GB
database on an 8 processor (3.5 GHz Xeon) machine with 64 GB RAM
running (soon to be updated) PostgreSQL 8.2.7.

Based on the minimal plan time increase of this test, we're going to
try 50 in production and see how it goes.

It's worth pondering that at the target of 1000, had we put that into
production, running this query 300,000 times per day would have used
21 hours and 40 minutes of additional CPU time per day on planning the
runs of this one query, while a target of 50 only consumes an
additional 15 minutes of 3.5 GHz CPU time per day.

-Kevin

On Wed, 3 Dec 2008, Gregory Stark wrote:

> It sure seems strange to me to have initdb which presumably is targeting a
> "mixed" system -- where it doesn't know for sure what workload will be run --
> produce a different set of values than the tuner on the same machine.

It's been a long time since the output from initdb was targeting anything
but a minimal system with an untuned kernel and limited resources. If you
invert the normal tuning recommendations, as if its initial configuration
were the output from typical practice, it would be aiming at a system with
approximately 128MB of RAM. That feels about right to me; when I had
128MB of RAM in my high-end P2-300 server running PG 7.0, 32MB of
shared_buffers was huge and 3 checkpoints segments was plenty. I don't
recall regularly dirtying things fast enough to see "checkpoints occuring
too fast" then like you can do trivially nowadays. Here in 2008, I push
checkpoint_segments up to 10 even for the most trivial apps lest the logs
fill with those buggers the first time I run an update on a table.

Right now, my program doesn't fiddle with any memory settings if you've
got less than 256MB of RAM. Were someone to champion the idea that
*nothing* should be fiddled with in those cases, that's not an
unreasonable position. I'm not the sort to be too concerned myself that
the guy who thinks he's running a DW on a system with 64MB of RAM might
get bad settings, but it's a fair criticism to point that out as a
problem.

> In fact, really it would be nice if we allowed units of space (MB, GB,
> etc) for checkpoint_segments.

That's a good way to think about this, let's run with that for a minute.
The values I'm throwing in there look like this (if your tab stops aren't
at 8 characters this will suck):

Completion Max Max
Type Segs Target Segments Usage
web 8 0.7 23 368MB
oltp 16 0.9 47 752MB
dw 64 0.9 187 3GB
mixed 16 0.9 47 752MB
desktop 3 0.5 9 144MB

Is 368MB of overhead unreasonable for a web application database today,
where you can get a mirrored pair of disks for under $1/GB? It's only the
DW case that even starts to leave trivial territory. Your example of
somebody who thinks the overhead is too high on their 10MB database is
already being blown away even at the default of 3 segments (assuming that
data has enough churn on it to go through that many segments ever--if it
doesn't then the maximum doesn't matter anyway).

The reality here is that it's the recovery playback time that's the real
bear. If I were trying to argue against me, what would be more persuasive
is some tests showing how long it takes to sort through, cleanup, and
replay the appropriate portions of as many as 47 segments worth of WAL
after an unclean shutdown when checkpoint_segments=16. Given how long
that takes, it might be possible to find a modern system takes a while to
process that much WAL volume. It's pretty rare I run into that (usually
only after I do something abusive), whereas complaints about the logs
filling with checkpoint warnings on systems set to the default seem to pop
up all the time.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

"Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:

> Actually there are years worth of evidence in these archives. Not that
> the 50 is the right number but that the current settings are definitely
> wrong and that higher ones are needed. That people generally start
> around 100 and go from there, except where they don't and then someone
> like Tom, I or some other person says, "Oh you need to increase
> default_statistics_target".

no, Tom or some other person would say "you need to raise the statistics
target *on that column*" I've never seen any case on the list where raising
the statistics target on every column of every table in every database of the
cluster was warranted.

> There is no empirical evidence that 50 is the right setting but there is
> more than enough anecdotal evidence to suggest that 50 is a lot better
> than 10 and that even higher than 50 is reasonable. In an effort to
> follow the PostgereSQL conservative mantra, 50 is a good compromise.

A factor of 5 increase is conservative? In any case the mantra is "run the
numbers and produce the evidence" not "pick a smaller arbitrary number". I
started to do this for you last week but got side-tracked. Do you have any
time for this?

>> I think the rhetorical answer is "so that we don't fill up the disk",
>
> I don't think at any time I have said to my self, I am going to set this
> parameter low so I don't fill up my disk. If I am saying that to myself
> I have either greatly underestimated the hardware for the task. Consider
> that we are quarreling over what amounts to a nominal amount of hard
> drive space, 1000 checkpoint_segments = 1.6G of space. My phone has more
> capacity than that.

Well my phone has 16G of RAM, why not 10000 ?

Any number you pick will be an arbitrary one. Whatever value you pick it's
going to be "so that we don't fill up the disk" in some form or another.

You know, you actually had me convinced but now you've just convinced me that
we do need this parameter after all.

So how big should a minimum postgres install be not including your data? Is
100M reasonable? Should we say Postgres requires 200M? 500? 1G? Whatever
number we pick (or allow the user to pick) will determine how large this value
ought to be. And incidentally also provide a bound on
autovacuum_max_freeze_age as Heikki pointed out on another thread.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:

> One more data point to try to help.
>
> While the jump from a default_statistics_target from 10 to 1000
> resulted in a plan time increase for a common query from 50 ms to 310
> ms, at a target of 50 the plan time was 53 ms.

That sounds like it would be an interesting query to analyze in more detail.
Is there any chance to could run the complete graph and get a chart of analyze
times for all statistics values from 1..1000 ? And log the explain plans to a
file so we can look for at what statistics targets the plan changed?

Or if the data is public I would be interested in looking at doing it if you
want to send it to me.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

On Thu, 2008-12-04 at 00:11 +0000, Gregory Stark wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:

> I
> started to do this for you last week but got side-tracked. Do you have any
> time for this?

I can do it if you have a script.

> So how big should a minimum postgres install be not including your data?
> Is 100M reasonable? Should we say Postgres requires 200M? 500? 1G? Whatever
> number we pick (or allow the user to pick) will determine how large this value
> ought to be. And incidentally also provide a bound on
> autovacuum_max_freeze_age as Heikki pointed out on another thread.
>

I fail to see what any of the above paragraph has to do with
checkpoint_segments.

Anyway, I have made my arguments.

Joshua D. Drake

>
--
PostgreSQL
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

On Wed, 3 Dec 2008, Robert Haas wrote:

> I'm not sure if you've thought about this, but there is also a
> difference between max_connections and maximum LIKELY connections.

It's actually an implicit assumption of the model Josh threw out if you
stare at the numbers. The settings for work_mem are twice as high per
connection in the Web+OLTP application cases, based on the assumption that
you're just not going to get everybody doing sorting at once in those
situations. I toyed with exposing that as an explicit "connection load
duty factor", then remembered I was trying to deliver something rather
than tweak the parameters forever. It may be a bit too aggressive as
written right now in those cases.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

On Wed, 3 Dec 2008, Guillaume Smet wrote:

> - it would be really nice to make it work with Python 2.4 as RHEL 5 is
> a Python 2.4 thing and it is a very widespread platform out there,

The 2.5 stuff is only required in order to detect memory on Windows. My
primary box is RHEL5 and runs 2.4, it works fine there.

> - considering the audience of this tool, I think you should explain in
> the usage text which type of workload implies each database type (DW,
> OLTP, Web, Mixed, Desktop).

Once I'm done with the docs I'll refer over to those, it's too much to put
into the usage without cluttering it.

> - it would be nice to be able to define the architecture (32-64 bits)
> from the command line (especially considering I won't be able to run
> it on our target boxes which are all RHEL 5 :))

I'm starting to lean toward making everything that gets detected as also
being possible to override, for these case. I want to make this just work
in as many cases as possible, but the situation where someone is
configuring/testing on a system other than the server is pretty common.
Now that I think about it I often setup configs on my 32-bit laptop and
them move them over onto 64-bit servers.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

Greg Smith <gsmith(at)gregsmith(dot)com> writes:

> On Wed, 3 Dec 2008, Gregory Stark wrote:
>
>> It sure seems strange to me to have initdb which presumably is targeting a
>> "mixed" system -- where it doesn't know for sure what workload will be run --
>> produce a different set of values than the tuner on the same machine.
>
> It's been a long time since the output from initdb was targeting anything but a
> minimal system with an untuned kernel and limited resources. If you invert the
> normal tuning recommendations, as if its initial configuration were the output
> from typical practice, it would be aiming at a system with approximately 128MB
> of RAM. That feels about right to me; when I had 128MB of RAM in my high-end
> P2-300 server running PG 7.0, 32MB of shared_buffers was huge and 3 checkpoints
> segments was plenty. I don't recall regularly dirtying things fast enough to
> see "checkpoints occuring too fast" then like you can do trivially nowadays.

Well I think there's also an assumption in initdb that Postgres can't assume
it's on a dedicated machine. So whether it's 32MB on a dedicated 128MB machine
or 32MB on a 256MB machine where it's only expected to be half the workload of
the machine it works out to about the same thing.

> Right now, my program doesn't fiddle with any memory settings if you've got
> less than 256MB of RAM.

What I'm suggesting is that you shouldn't have to special case this. That you
should expect whatever formulas you're using to produce the same values as
initdb if they were run on the same machine initdb is targeting.

But actually I'm more concerned with the *non* memory related parameters. It
may make sense to tweak those one way or the other for oltp or dss but "mixed"
should be exactly what initdb produces since that's exactly what it's
targeting -- a system that will have a wide mixture of queries and must
function reasonably well for both data warehouse and oltp queries.

> Completion Max Max
> Type Segs Target Segments Usage
> web 8 0.7 23 368MB
> oltp 16 0.9 47 752MB
> dw 64 0.9 187 3GB
> mixed 16 0.9 47 752MB
> desktop 3 0.5 9 144MB

(incidentally using tab stops in emails is probably a bad idea because of
quoting as above)

Uhm, I hadn't actually seen this list before. I don't understand how "web" is
different from "oltp". A web service really is just one (very typical) example
of an oltp application.

And "desktop" seems like an outlier here. I suppose it's meant to capture
whether postgres is on a dedicated box? But it's possible to have a
non-dedicated oltp application or non-dedicated data warehouse box just as
easily. It's an orthogonal issue from the oltp/data-warehouse axis.

> Is 368MB of overhead unreasonable for a web application database today

Well I think it's more than most people expect a single application install to
take up before they start putting data in it. It would probably work better if
we were asking how big their database was and then could say, well, you said
you had 10G of data so 300MB of overhead isn't going to be so bad.

> The reality here is that it's the recovery playback time that's the real bear.

I agree, but then that's what checkpoint_timeout is for, no? It might take
longer to replay but the recovery time should bear some relation to how long
it took to write out the wal. More so than to the sheer size of the wal.

> whereas complaints about the logs filling with checkpoint warnings on systems
> set to the default seem to pop up all the time.

"filling"? The cure to having too much space taken up by logs is to take up
space with, well, logs?

The logs are filling up with warnings which explain exactly what parameter to
adjust. Are there really complaints about this?

I'm really beginning to think the root of the problem is the name. If it were
"transaction_log_max_space" and measured in megabytes people would be happy to
say "ok, I'll make space for 100MB of logs" or whatever. Today they don't know
what to set it to or what the impact of setting it will be.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!

On Wed, 3 Dec 2008, Robert Haas wrote:

> Then I tried "-T web" and got what seemed like a more reasonable set
> of values. But I wasn't sure I needed that many connections, so I
> added "-c 150" to see how much difference that made. Kaboom!

That and the import errors fixed in the version attached (just replacing
the script, not its data), thanks for testing that out.

> I'm not sure what "mixed" mode is supposed to be, but based on what
> I've seen so far, I'm a skeptical of the idea that encouraging people
> to raise default_statistics_target to 50 and turn on
> constraint_exclusion is reasonable.

The statistics stuff is obviously a broader discussion, will let that rage
in existing threads. The reason for setting constraint_exclusion in the
mixed case is that people who just pick the defaults without reading
anything will get a configuration that supports partitions usefully. One
of the HINTs I intend to throw out for that specific case is that they
should turn it off if they don't ever intend to use paritions.

The idea of the mixed mode is that you want to reduce the odds someone
will get a massively wrong configuration if they're not paying attention.
Is it worse to suffer from additional query overhead if you're sloppy with
the tuning tool, or to discover addition partitions didn't work as you
expected? That's a tough call; I could invert things, so that it defaults
to off in mixed mode, as always, and just produces a HINT to turn it on.
I don't have a really strong opinion there either way.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD