Please read the whole email before replying:



I love the feedback I have received but I feel that somehow I did not
communicate the intent of this mini project very well. So let me
outline a few basics and who the audience was intended for.





Mini project title:

Initial Configuration Tool for PostgreSQL for Dummies



1) This is intended for newbie's. Not for experienced users or advanced
DBAs.



2) This tool is NOT intended to monitor your PostgreSQL efficiency.



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
to configuration. I wanted a very simple way for people to access the
tool that would not be tied to any particular environment or OS. If
there is someone that is using a text browser to view the web then they
are probably geeky enough not to want to bother with using this tool.



4) The intent is just to give people that have no clue a better starting
point than some very generic defaults.



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?

How much memory will be available to PostgreSQL?

How many connections will be made to PostgreSQL?





Thanks,







Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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?

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

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?

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

> 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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

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.

--
Yudhvir Singh Sidhu
408 375 3134 cell

Yudhvir,

I completely agree. I was just putting together a similar email.



Thanks,



Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu



________________________________

From: pgsql-performance-owner@postgresql.org
[mailtogsql-performance-owner@postgresql.org] On Behalf Of Y Sidhu
Sent: Tuesday, June 19, 2007 12:49 PM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PostgreSQL Configuration Tool for Dummies





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.

--
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.

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