Re: My own performance/tuning q&a - Mailing list pgsql-performance

>>>>> "AL" == Allen Landsidel <all@biosys.net> writes:

AL> I recently built a rather powerful machine to be used in a heavily
AL> accessed database.. the machine is a dual AthlonMP 2800+, 2GB of
AL> PC2100 ECC, and a 4x18GB RAID-0 using 15k rpm fujitsu MAS drives on a
AL> 4ch u160 ICP-Vortex card with 256MB of cache.

The only recommendation I'd make is to switch from RAID0 to RAID10,
unless you can afford the downtime (and loss of data) when one of your
drives takes a vacation.

Also, is your RAID card cache battery backed up?  If no, then you lose
the ability to use write-back and this costs *dearly* in performance.


AL> The box runs FreeBSD, tracking RELENG_4 (-STABLE) and PostGreSQL 7.3.4
AL> from ports (7.3.4_1)

An excellent choice. :-)

[[ ... ]]

AL> I run a 'vacuum analyze verbose' on the database in question every
AL> hour, and a reindex on every table in the database every six hours,
AL> 'vacuum full' is run manually as required perhaps anywhere from once a
AL> week to once a month.  I realize the analyze may not be running often
AL> enough and the reindex more often than need be, but I don't think
AL> these are adversely affecting performance very much; degredation over
AL> time does not appear to be an issue.

Personally, I don't think you need to reindex that much.  And I don't
think you need to vacuum full *ever* if you vacuum often like you do.
Perhaps reducing the vacuum frequency may let you reach a steady state
of disk usage?

Depending on how many concurrent actions you process, perhaps you can
use a temporary table for each, so you don't have to delete many rows
when you're done.


On my busy tables, I vacuum every 6 hours.  The vacuum analyze is run
on the entire DB nightly.  I reindex every month or so my most often
updated tables that show index bloat.  Watch for bloat by monitoring
the size of your indexes:

SELECT relname,relpages FROM pg_class WHERE relname LIKE 'some_table%' ORDER BY relname;

AL> Related kernel configuration options:

AL> ...
AL> cpu I686_CPU
AL> maxusers 256

let the system autoconfigure maxusers...

AL> ...
AL> options         MAXDSIZ="(1024UL*1024*1024)"
AL> options         MAXSSIZ="(512UL*1024*1024)"
AL> options         DFLDSIZ="(512UL*1024*1024)"

above are ok at defaults.

AL> options         SHMMAXPGS=65536

perhaps bump this and increase your shared buffers.  I find that if
you do lots of writes, having a few more shared buffers helps.

AL> options         SHMMAX="(SHMMAXPGS*PAGE_SIZE+1)"

you don't need to explicitly set this... it is automatically set based
on the above setting.


AL> relevant postgresql.conf options:

AL> max_fsm_pages = 2000000

this may be overkill.  I currently run with 1000000

AL> effective_cache_size = 49152 # 384MB, this could probably be higher

the current recommendation for freebsd is to set this to:

`sysctl -n vfs.hibufspace` / 8192

where 8192 is the blocksize used by postgres.

You may also want to increase the max buffer space used by FreeBSD,
which apparently is capped at 200M (I think) by dafault.   I'll have
to look up how to bump that, as most likely you have plenty of RAM
sitting around unused.  What does "top" say about that when you're
busy?

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD       +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/