Re: The black art of postgresql.conf tweaking

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

Thanks to everyone for there help.

I've changed my postgres settings to the following

max_connections = 500
shared_buffers = 10000
sort_mem = 2000
effective_cache_size = 5000

The 'effective_cache_size' is just a guess, but some references suggest
it so I added it.

Dropping the Apache Keep-alive down to 3 seconds seems to have was a
great tip I now have far less idle connections hanging about.

I've not maxed out the connections since making the changes, but I'm
still not convinced everything is running as well as it could be. I've
got some big result sets that need sorting and I'm sure I could spare a
bit more sort memory.

Where does everyone get there information about the settings? I still
can't find anything that helps explain each of the settings and how you
determine there optimal settings.

If anyone wants interested here is a table schema form one of the most
used tables.

CREATE TABLE "tblForumMessages" (
~    "pk_iForumMessagesID" serial,
~    "fk_iParentMessageID" integer DEFAULT 0 NOT NULL,
~    "fk_iAuthorID" integer NOT NULL,
~    "sSubject" character varying(255) NOT NULL,
~    "sBody" text,
~    "fk_iImageID" oid,
~    "dtCreatedOn" timestamp with time zone DEFAULT now(),
~    "iType" integer DEFAULT 0,
~    "bAnonymous" boolean DEFAULT false,
~    "bLocked" boolean DEFAULT false,
~    "dtHidden" timestamp with time zone,
~    "fk_iReplyToID" integer,
~    "iCreateLevel" integer DEFAULT 7
);

This is the query that is most called on the server explained

EXPLAIN ANALYZE SELECT "tblForumMessages".* FROM "tblForumMessages"
WHERE "fk_iParentMessageID" = 90 ORDER BY "dtCreatedOn" DESC

Which gives the following:

Sort  (cost=8156.34..8161.71 rows=2150 width=223) (actual
time=0.264..0.264 rows=0 loops=1)
~   Sort Key: "dtCreatedOn"
~   ->  Index Scan using "fk_iParentMessageID_key" on "tblForumMessages"
~ (cost=0.00..8037.33 rows=2150 width=223) (actual time=0.153..0.153
rows=0 loops=1)
~         Index Cond: ("fk_iParentMessageID" = 90)
~ Total runtime: 0.323 ms

SELECT COUNT(*) FROM "tblForumMessages" WHERE "fk_iParentMessageID" = 90
Returns: 22920

SELECT COUNT(*) FROM "tblForumMessages"
Returns: 429913

Paul Serby wrote:
| Can anyone give a good reference site/book for getting the most out of
| your postgres server.
|
| All I can find is contradicting theories on how to work out your settings.
|
| This is what I followed to setup our db server that serves our web
| applications.
|
| http://www.phpbuilder.com/columns/smith20010821.php3?page=2
|
| We have a Dell Poweredge with the following spec.
|
| CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache)
| CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache)
| CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache)
| CPU: Intel(R) Xeon(TM) CPU 3.06GHz (512 KB Cache)
| Physical Memory: 2077264 kB
| Swap Memory: 2048244 kB
|
| Apache on the Web server can take up to 300 connections and PHP is using
|  pg_pconnect
|
| Postgres is set with the following.
|
| max_connections = 300
| shared_buffers = 38400
| sort_mem = 12000
|
| But Apache is still maxing out the non-super user connection limit.
|
| The machine is under no load and I would like to up the max_connections
| but I would like to know more about what you need to consider before
| doing so.
|
| The only other source I've found is this:
|
| http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
|
| But following its method my postgres server locks up straight away as it
| recommends setting max_connections to 16 for Web sites?
|
| Is there a scientific method for optimizing postgres or is it all
| 'finger in the air' and trial and error.
|
| ---------------------------(end of broadcast)---------------------------
| TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFBF4nxp51pUZR6gxsRAi8cAJ9HBfpNMGQR7vurk0wYW+p6KfqZzACfc9NX
k72iabZxK+gku06Pf7NmHfQ=
=Ftv6
-----END PGP SIGNATURE-----