Thread: Good configuration?

I've saved my company more than $20,000 by convincing them to switch
from MS SQL Server to PostgreSQL and building a DB server that meets
our needs.  Here are the basic DB server specs:
Dual 1.4 Ghz Athlon
1GB DDR RAM
RedHat 7.2
IDE software RAID 1 (two 40GB IBM 60GXP)
(btw, I had a budget of $1200 to build this system, which I made
easily)

I have the system up and running, and everything is going great.  The
company is happy and we have extra money for R&D.  I am fairly new to
PostgreSQL, however, and have done my best to optimize PostgreSQL for
this box, but I would like to get some counsel from those of you more
experienced with PostgreSQL on what you would think the best settings
would be for this kind of setup.  Here is my postgresql.conf file
(edited):

The more important settings:
sort_mem = 16384
shared_buffers = 96000
effective_cache_size = 18200  # default in 8k pages
wal_buffers = 32 # min 4
wal_files = 64 # range 0-64
deadlock_timeout = 5000

The rest of the settings are all commented out or have their default
values.
We are running PostgreSQL 7.1.3.  When considering these settings,
remember that this machine is pretty much dedicated to PostgreSQL.
As far as the OS itself goes, I have optimized the hard drives using
hdparm (UDMA 5, 32-bit, blah, blah) and have increased the shared mem
size to handle the settings I have above.  The drive (raid) is using
the ext3 filesystem.
Thanks for your consideration.

sir_anodos@yahoo.com (anodos) writes:
> shared_buffers = 96000

800MB of shared buffers in a 1GB machine is almost certainly
a *bad* idea.  I don't believe there is any good reason for going
over about a quarter of physical RAM, and one can make a pretty
strong argument that a few thousand shared buffers are enough
no matter how much RAM you have.  Leave it to the kernel to use
the rest of RAM.  Cf. my comments to mlw today in pgsql-hackers.

> deadlock_timeout = 5000

Do you have any reason for messing with deadlock_timeout?

            regards, tom lane

> > deadlock_timeout = 5000
>
> Do you have any reason for messing with deadlock_timeout?

I think increasing deadlock_timeout is essential if he wants to use
lots of concurrent connections.

Also, he might want to tweak wal_sync_method...
--
Tatsuo Ishii