> -----Original Message-----
> From: Dann Corbit 
> Sent: Wednesday, June 11, 2003 12:35 PM
> To: Tom Lane; Kaarel
> Cc: Justin Clift; Jay O'Connor; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Postgres performance comments from a 
> MySQL user 
> 
> 
> > -----Original Message-----
> > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> > Sent: Wednesday, June 11, 2003 12:28 PM
> > To: Kaarel
> > Cc: Justin Clift; Jay O'Connor; pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Postgres performance comments from a 
> > MySQL user 
> > 
> > 
> > Kaarel <kaarel@future.ee> writes:
> > > This makes me wonder why is the default configuration so
> > conservative?
> > 
> > There are still a lot of platforms where desirable settings
> > will cause the database to fail to start, because the default 
> > kernel limits on shared memory are still set for 
> > 1980s-vintage hardware.
> > 
> > We have had a policy for a long time that we'd rather the
> > out-of-the-box settings be guaranteed to start than that they 
> > be optimal for performance.  No one is going to get as far as 
> > testing performance if the system won't start for them.
> > 
> > Having said that, though, I do wonder whether we couldn't
> > bump them up a little.  Are there still any live platforms 
> > where the default SHMMAX is less than 4 MB?  (There was 
> > discussion of this a month or two back on pghackers, but no 
> > conclusion.)
> 
> I think this would be very, very nice:
> Config_tool.exe runs, and examines:
> Operating system, available memory, disk, cpu speed, etc. 
> (whatever it can figure out). Then it makes good guesses for 
> what PostgreSQL parameters to use and reconfigures PostgreSQL.
> 
> If it was part of the installation, then even better.

/*
Sample for Win32 machines.  It should be a snap for some UNIX guru to
write one for UNIX type systems.
*/


#include <windows.h>
#include <stdio.h>
// Use to change the divisor from Kb to Mb.

#define DIV 1024
// #define DIV 1

char           *divisor = "K";
// char *divisor = "";

// Handle the width of the field in which to print numbers this way to
// make changes easier. The asterisk in the print format specifier
// "%*ld" takes an int from the argument list, and uses it to pad and
// right-justify the number being formatted.
#define WIDTH 7

int             main(int argc, char *argv[])
{
    MEMORYSTATUS    stat;

    GlobalMemoryStatus(&stat);

    printf("%ld percent of memory is in use.\n",
           stat.dwMemoryLoad);
    printf("There are %*ld total %sbytes of physical memory.\n",
           WIDTH, stat.dwTotalPhys / DIV, divisor);
    printf("There are %*ld free %sbytes of physical memory.\n",
           WIDTH, stat.dwAvailPhys / DIV, divisor);
    printf("There are %*ld total %sbytes of paging file.\n",
           WIDTH, stat.dwTotalPageFile / DIV, divisor);
    printf("There are %*ld free %sbytes of paging file.\n",
           WIDTH, stat.dwAvailPageFile / DIV, divisor);
    printf("There are %*lx total %sbytes of virtual memory.\n",
           WIDTH, stat.dwTotalVirtual / DIV, divisor);
    printf("There are %*lx free %sbytes of virtual memory.\n",
           WIDTH, stat.dwAvailVirtual / DIV, divisor);
    return 0;
}
/*
C:\tmp>ramsize
74 percent of memory is in use.
There are  523744 total Kbytes of physical memory.
There are  131796 free Kbytes of physical memory.
There are 1539616 total Kbytes of paging file.
There are  646924 free Kbytes of paging file.
There are  1fff80 total Kbytes of virtual memory.
There are  1feb54 free Kbytes of virtual memory.
*/

Wed, 11 Jun 2003 13:05:26 -0700
"Dann Corbit" <DCorbit@connx.com>
Re: Postgres performance comments from a MySQL user 
The problem with this is that in troubleshooting there's no frame of
reference.  Having a stock config file, or stock config file options allows
a person to write to the list and say, "hey, I'm using medium.conf and I
have x ram..."

The alternative is, "hey, see my attached .conf file..." which takes a lot
more effort.

--
Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org
 

> -----Original Message-----
> From: Dann Corbit [mailto:DCorbit@connx.com]
> Sent: Wednesday, June 11, 2003 4:05 PM
> To: Dann Corbit; Tom Lane; Kaarel
> Cc: Justin Clift; Jay O'Connor; pgsql-general@postgresql.org
> Subject: Re: Postgres performance comments from a MySQL user
> 
> > -----Original Message-----
> > From: Dann Corbit
> > Sent: Wednesday, June 11, 2003 12:35 PM
> > To: Tom Lane; Kaarel
> > Cc: Justin Clift; Jay O'Connor; pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Postgres performance comments from a
> > MySQL user
> >
> >
> > > -----Original Message-----
> > > From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> > > Sent: Wednesday, June 11, 2003 12:28 PM
> > > To: Kaarel
> > > Cc: Justin Clift; Jay O'Connor; pgsql-general@postgresql.org
> > > Subject: Re: [GENERAL] Postgres performance comments from a
> > > MySQL user
> > >
> > >
> > > Kaarel <kaarel@future.ee> writes:
> > > > This makes me wonder why is the default configuration so
> > > conservative?
> > >
> > > There are still a lot of platforms where desirable settings
> > > will cause the database to fail to start, because the default
> > > kernel limits on shared memory are still set for
> > > 1980s-vintage hardware.
> > >
> > > We have had a policy for a long time that we'd rather the
> > > out-of-the-box settings be guaranteed to start than that they
> > > be optimal for performance.  No one is going to get as far as
> > > testing performance if the system won't start for them.
> > >
> > > Having said that, though, I do wonder whether we couldn't
> > > bump them up a little.  Are there still any live platforms
> > > where the default SHMMAX is less than 4 MB?  (There was
> > > discussion of this a month or two back on pghackers, but no
> > > conclusion.)
> >
> > I think this would be very, very nice:
> > Config_tool.exe runs, and examines:
> > Operating system, available memory, disk, cpu speed, etc.
> > (whatever it can figure out). Then it makes good guesses for
> > what PostgreSQL parameters to use and reconfigures PostgreSQL.
> >
> > If it was part of the installation, then even better.
> 
> /*
> Sample for Win32 machines.  It should be a snap for some UNIX guru to
> write one for UNIX type systems.
> */
> 
> 
> #include <windows.h>
> #include <stdio.h>
> // Use to change the divisor from Kb to Mb.
> 
> #define DIV 1024
> // #define DIV 1
> 
> char           *divisor = "K";
> // char *divisor = "";
> 
> // Handle the width of the field in which to print numbers this way to
> // make changes easier. The asterisk in the print format specifier
> // "%*ld" takes an int from the argument list, and uses it to pad and
> // right-justify the number being formatted.
> #define WIDTH 7
> 
> int             main(int argc, char *argv[])
> {
>     MEMORYSTATUS    stat;
> 
>     GlobalMemoryStatus(&stat);
> 
>     printf("%ld percent of memory is in use.\n",
>            stat.dwMemoryLoad);
>     printf("There are %*ld total %sbytes of physical memory.\n",
>            WIDTH, stat.dwTotalPhys / DIV, divisor);
>     printf("There are %*ld free %sbytes of physical memory.\n",
>            WIDTH, stat.dwAvailPhys / DIV, divisor);
>     printf("There are %*ld total %sbytes of paging file.\n",
>            WIDTH, stat.dwTotalPageFile / DIV, divisor);
>     printf("There are %*ld free %sbytes of paging file.\n",
>            WIDTH, stat.dwAvailPageFile / DIV, divisor);
>     printf("There are %*lx total %sbytes of virtual memory.\n",
>            WIDTH, stat.dwTotalVirtual / DIV, divisor);
>     printf("There are %*lx free %sbytes of virtual memory.\n",
>            WIDTH, stat.dwAvailVirtual / DIV, divisor);
>     return 0;
> }
> /*
> C:\tmp>ramsize
> 74 percent of memory is in use.
> There are  523744 total Kbytes of physical memory.
> There are  131796 free Kbytes of physical memory.
> There are 1539616 total Kbytes of paging file.
> There are  646924 free Kbytes of paging file.
> There are  1fff80 total Kbytes of virtual memory.
> There are  1feb54 free Kbytes of virtual memory.
> */


Wed, 11 Jun 2003 17:37:09 -0400
"Matthew Nuzum" <cobalt@bearfruit.org>
Re: Postgres performance comments from a MySQL user 
On Wednesday 11 June 2003 2:37 pm, Matthew Nuzum wrote:
> The problem with this is that in troubleshooting there's no frame of
> reference.  Having a stock config file, or stock config file options allows
> a person to write to the list and say, "hey, I'm using medium.conf and I
> have x ram..."
>
> The alternative is, "hey, see my attached .conf file..." which takes a lot
> more effort.

The postfix mail transport agent has a command "postconf" which allows you to 
read or change entries in the config file. Like postgresql, postfix uses 
certain defaults when there is no corresponding configuration entry.

Running "postconf" displays all settings while "postconf -n" command displays 
only non-default configuration settings. The output of "postconf -n" is 
generally one of the first things requested when someone asks a question. 
(Being a mail server, most questions are ultimately configuration related - 
postgresql questions are more varied.)

You can also change a setting with "postconf parameter=value".

Perhaps a similar command would be useful for postgresql. Just please don't 
call it "postconf". :)

Note: this would also provide a nice core interface for all sorts of purposes 
like updating configuration entries based on automatic analysis of database 
size/memory/cpu or based on asking the admin questions about the database use 
profile and then using the command (say pg_conf) to update specified options, 
save current settings and restore everything to default for 
testing/troubleshooting then restore the original settings, etc.

Looking through the useful options for postconf, I think a useful base set of 
features for pg_conf would be

pg_conf -D datadir [-n] [-h] [-d] [parameter...]

Output (unless -h is specified) would look like:
foo = 8192
bar = 0
...

where:
-n = only output values that are different than the default

-h = show the value only (useful for extracting values into scripts, eg, 
pg_conf -h foo would return "8192" instead of "foo = 8192". Note: I just 
borrowed -h from postconf - choose any logical letter.)

-d = show the default value for the parameter

The optional parameter list limits the display to that/those parameter(s).

I haven't checked the source code, yet, but I suspect that most of the 
necessary code for such a command is already in whatever module reads 
postgresql.conf.

Thoughts?

Cheers,
Steve


Thu, 12 Jun 2003 10:06:44 -0700
Steve Crawford <scrawford@pinpointresearch.com>
pg_conf idea (was Re: Postgres performance comments from a MySQL
 user)