Thread: PostgreSQL Configuration Tool for Dummies

Campbell, Lance wrote:
> Please think simple.  I stress the word simple.  The real challenge here
> is getting the formulas correct.  Someone mentioned to not focus on the
> values but just get something out there for everyone to help tweak.  I
> agree!
>
> What questions do you think should be asked in order to figure out what
> values should go into the formulas for the configuration suggestions?
>
> My thoughts:
>
> What version of PostgreSQL are you using?

OK, obviously not needed if embedded in the manuals.

 > How many connections will be made to PostgreSQL?
OK (but changed order)

> How much memory will be available to PostgreSQL?
Would structure it like:
- What is total memory of your machine?
- How much do you want to reserve for other apps (e.g. apache/java)?

Also:
- How many disks will PG be using?
- How much data do you think you'll store?
- Will your usage be: mostly reads|balance of read+write|mostly writes
- Are your searches: all very simple|few complex|lots of complex queries

Then, with the output provide a commentary stating reasons why for the
chosen values. e.g.
   random_page_cost = 1.0
   Because you have [effective_cache_size = 1GB] and [total db size =
0.5GB] the cost of fetching a page is the same no matter what order you
fetch them in.

--
   Richard Huxton
   Archonet Ltd

Campbell, Lance writes:

> 3) I suggested JavaScript because most people that get started with
> PostgreSQL will go to the web in order to find out about issues relating

Why not c?
It could then go into contrib.
Anyways.. language is likely the least important issue..
As someone mentioned.. once the formulas are worked out it can be done in a
few languages.. as people desire..

> How much memory will be available to PostgreSQL?
> How many connections will be made to PostgreSQL?

Will this be a dedicated Postgresql server?
Will there be mostly reads or will there also be significant amount of
writes?

Are you on a RAID system or do you have several disks over which you would
like to run postgresql on?

Francisco and Richard,
Why ask about disk or raid?  How would that impact any settings in
postgresql.conf?

I did forget the obvious question:

What OS are you using?

Thanks,

Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu


-----Original Message-----
From: Francisco Reyes [mailto:lists@stringsutils.com]
Sent: Tuesday, June 19, 2007 11:58 AM
To: Campbell, Lance
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PostgreSQL Configuration Tool for Dummies

Campbell, Lance writes:

> 3) I suggested JavaScript because most people that get started with
> PostgreSQL will go to the web in order to find out about issues
relating

Why not c?
It could then go into contrib.
Anyways.. language is likely the least important issue..
As someone mentioned.. once the formulas are worked out it can be done
in a
few languages.. as people desire..

> How much memory will be available to PostgreSQL?
> How many connections will be made to PostgreSQL?

Will this be a dedicated Postgresql server?
Will there be mostly reads or will there also be significant amount of
writes?

Are you on a RAID system or do you have several disks over which you
would
like to run postgresql on?

Campbell, Lance wrote:
> Francisco and Richard,
> Why ask about disk or raid?  How would that impact any settings in
> postgresql.conf?

Well, random_page_cost will depend on how fast your disk system can
locate a non-sequential page. If you have a 16-disk RAID-10 array that's
noticably less time than a single 5400rpm IDE in a laptop.

> I did forget the obvious question:
>
> What OS are you using?

Tricky to keep simple, isn't it :-)

--
   Richard Huxton
   Archonet Ltd

> What version of PostgreSQL are you using?

    I think newbies should be pushed a bit to use the latest versions, maybe
with some advice on how to setup the apt sources (in debian/ubuntu) to get
them.

> How much memory will be available to PostgreSQL?
>
> How many connections will be made to PostgreSQL?

    I also think Postgres newbies using PHP should be encouraged to use
something like ligttpd/fastcgi instead of Apache. The fastcgi model
permits use of very few database connections and working PHP processes
since lighttpd handles all the slow transfers to the client
asynchronously. You can do the same with two Apache instances, one serving
static pages and acting as a proxy for the second Apache serving dynamic
pages.
    With this setup, even low-end server setups (For our personal sites, a
friend and I share a dedicated server with 256MB of RAM, which we rent for
20€ a month). This thing will never run 200 Apache processes, but we have
no problem with lighttpd/php-fcgi and postgres.

Campbell, Lance writes:

> Francisco and Richard,
> Why ask about disk or raid?  How would that impact any settings in
> postgresql.conf?

If the user has 2 disks and says that he will do a lot of updates he could
put pg_xlog in the second disk.

Let's not ask about disk or raid at this level of sanity tuning. It is important for a newbie to take the right first step. When it comes to disks, we start talking I/O, SATA, SCSI and the varying degrees of SATA and SCSI, and controller cards. Then we throw in RAID and the different levels therein. Add to that, we can talk about drivers controlling these drives and which OS is faster, more stable, etc. As you can see, a newbie would get drowned. So, please keep it simple. I know many people on this list are Gurus. We know you are the best in this field, but we are not and are just trying to improve what we have.

--
Yudhvir Singh Sidhu
408 375 3134 cell

On Tue, 19 Jun 2007 12:58:26 -0400
Francisco Reyes <lists@stringsutils.com> wrote:
> Campbell, Lance writes:
> > 3) I suggested JavaScript because most people that get started with
> > PostgreSQL will go to the web in order to find out about issues relating
>
> Why not c?

Why not whatever and install it on www.PostgreSQL.org?  Is there any
reason that this tool would need to be run on every installation.  Run
it on the site and it can always be up to date and can be written in
whatever language is easiest to maintain on the mother system.

I would also like to make a pitch for a JavaScript-free tool.  Just
collect all the pertinent information, work it out and display the
results in a second page.  Some people just don't like JavaScript and
turn it off even if we can run it in our browser.

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

D'Arcy,
I wanted to put it on the www.postgresql.org site.  That is what I said
in my original email.  I don't believe anyone from the actual project
has contacted me.

I am setting up a JavaScript version first.  If someone wants to do a
different one feel free.  I will have all of the calculations in the
JavaScript so it should be easy to do it in any language.

Thanks,

Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
http://webservices.uiuc.edu


-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of D'Arcy J.M.
Cain
Sent: Tuesday, June 19, 2007 12:32 PM
To: Francisco Reyes
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PostgreSQL Configuration Tool for Dummies

On Tue, 19 Jun 2007 12:58:26 -0400
Francisco Reyes <lists@stringsutils.com> wrote:
> Campbell, Lance writes:
> > 3) I suggested JavaScript because most people that get started with
> > PostgreSQL will go to the web in order to find out about issues
relating
>
> Why not c?

Why not whatever and install it on www.PostgreSQL.org?  Is there any
reason that this tool would need to be run on every installation.  Run
it on the site and it can always be up to date and can be written in
whatever language is easiest to maintain on the mother system.

I would also like to make a pitch for a JavaScript-free tool.  Just
collect all the pertinent information, work it out and display the
results in a second page.  Some people just don't like JavaScript and
turn it off even if we can run it in our browser.

--
D'Arcy J.M. Cain <darcy@druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Tue, 19 Jun 2007, Y Sidhu wrote:

> On 6/19/07, Francisco Reyes <lists@stringsutils.com> wrote:
>>
>>  Campbell, Lance writes:
>>
>> >  Francisco and Richard,
>> >  Why ask about disk or raid?  How would that impact any settings in
>> >  postgresql.conf?
>>
>>  If the user has 2 disks and says that he will do a lot of updates he could
>>  put pg_xlog in the second disk.
>>
>>
>>  ---------------------------(end of broadcast)---------------------------
>>  TIP 2: Don't 'kill -9' the postmaster
>>
>
> Let's not ask about disk or raid at this level of sanity tuning. It is
> important for a newbie to take the right first step. When it comes to disks,
> we start talking I/O, SATA, SCSI and the varying degrees of SATA and SCSI,
> and controller cards. Then we throw in RAID and the different levels
> therein. Add to that, we can talk about drivers controlling these drives and
> which OS is faster, more stable, etc. As you can see, a newbie would get
> drowned. So, please keep it simple. I know many people on this list are
> Gurus. We know you are the best in this field, but we are not and are just
> trying to improve what we have.

I strongly agree.

besides, the number and types of drives, raid configurations, etc is so
variable that I strongly believe that the right answer is going to be
something along the lines of 'run this tool and then enter the number(s)
that the tool reports' and then let the tool measure the end result of all
the variables rather then trying to calculate the results.

David Lang

On Tue, Jun 19, 2007 at 10:49:01AM -0700, Y Sidhu wrote:
> On 6/19/07, Francisco Reyes <lists@stringsutils.com> wrote:
> >
> >Campbell, Lance writes:
> >
> >> Francisco and Richard,
> >> Why ask about disk or raid?  How would that impact any settings in
> >> postgresql.conf?
> >
> >If the user has 2 disks and says that he will do a lot of updates he could
> >put pg_xlog in the second disk.
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 2: Don't 'kill -9' the postmaster
> >
>
> Let's not ask about disk or raid at this level of sanity tuning. It is
> important for a newbie to take the right first step. When it comes to disks,
> we start talking I/O, SATA, SCSI and the varying degrees of SATA and SCSI,
> and controller cards. Then we throw in RAID and the different levels
> therein. Add to that, we can talk about drivers controlling these drives and
> which OS is faster, more stable, etc. As you can see, a newbie would get
> drowned. So, please keep it simple. I know many people on this list are
> Gurus. We know you are the best in this field, but we are not and are just
> trying to improve what we have.



Ignoring the i/o subsystem in db configuration, there's an idea.

You could request some bonnie++ output (easy to aquire) as a baseline,
do your magic analysis based on this, and skip it if it is not provided
with a warning.  Course the magic may be harder to come by.

PFC <lists@peufeu.com> writes:
>> What version of PostgreSQL are you using?

>     I think newbies should be pushed a bit to use the latest versions,

How about pushed *hard* ?  I'm constantly amazed at the number of people
who show up in the lists saying they installed 7.3.2 or whatever random
version they found in a dusty archive somewhere.  "Please upgrade" is at
least one order of magnitude more valuable configuration advice than
anything else we could tell them.

If the configurator is a live tool on the website, then it could be
aware of the latest release numbers and prod people with an appropriate
amount of urgency depending on how old they say their version is.  This
may be the one good reason not to provide it as a standalone program.

(No, we shouldn't make it try to "phone home" for latest release numbers
--- in the first place, that won't work if the machine is really
isolated from the net, and in the second place people will be suspicious
of the motives.)

            regards, tom lane

Tom Lane wrote:
> PFC <lists@peufeu.com> writes:
>>> What version of PostgreSQL are you using?
>
>>     I think newbies should be pushed a bit to use the latest versions,
>
> How about pushed *hard* ?  I'm constantly amazed at the number of people
> who show up in the lists saying they installed 7.3.2 or whatever random
> version they found in a dusty archive somewhere.  "Please upgrade" is at
> least one order of magnitude more valuable configuration advice than
> anything else we could tell them.

(picking up an old thread while at a boring wait at the airport.. anyway)

I keep trying to think of more nad better ways to do this :-) Perhaps we
should put some text on the bug reporting form (and in the documentation
about bug reporting) that's basically "don't bother reporting a bug
unless you're on the latest in a branch, and at least make sure you're
on one of the maojr releases listed on www.postgresql.org"?

Seems reasonable?

//Magnus

Magnus,


absolutely. Should be standard practice.

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2007 will take place in Vilnius, Lithuania from Monday 9th July to Wednesday 11th July. See you there!