Clarification on some settings

From: Doug Y <dylists(at)ptd(dot)net>
To: psql-performance(at)postgresql(dot)org
Subject: Clarification on some settings
Date: 2004-05-11 21:36:31
Message-ID: 6.0.1.1.2.20040511163104.01e9cbb0@mail.traderonline.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello,
I've been having some performance issues with a DB I use. I'm trying to
come up with some performance recommendations to send to the "adminstrator".

Hardware:
CPU0: Pentium III (Coppermine) 1000MHz (256k cache)
CPU1: Pentium III (Coppermine) 1000MHz (256k cache)
Memory: 3863468 kB (4 GB)
OS: Red Hat Linux release 7.2 (Enigma)
Kernel: 2.4.9-31smp
I/O I believe is a 3-disk raid 5.

/proc/sys/kernel/shmmax and /proc/sys/kernel/shmall were set to 2G

Postgres version: 7.3.4

I know its a bit dated, and upgrades are planned, but several months out.
Load average seems to hover between 1.0 and 5.0-ish during peak hours. CPU
seems to be the limiting factor but I'm not positive (cpu utilization seems
to be 40-50%). We have 2 of those set up as the back end to 3 web-servers
each... supposedly load-balanced, but one of the 2 dbs consistently has
higher load. We have a home-grown replication system that keeps them in
sync with each other... peer to peer (master/master).

The DB schema is, well to put it nicely... not exactly normalized. No
constraints to speak of except for the requisite not-nulls on the primary
keys (many of which are compound). Keys are mostly varchar(256) fields.

Ok for what I'm uncertain of...
shared_buffers:
According to http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
Its more of a staging area and more isn't necessarily better. That psql
relies on the OS to cache data for later use.
But according to
http://www.ca.postgresql.org/docs/momjian/hw_performance/node3.html its
where psql caches previous data for queries because the OS cache is slower,
and should be as big as possible without causing swap.
Those seem to be conflicting statements. In our case, the "administrator"
kept increasing this until performance seemed to increase, which means its
now 250000 (x 8k is 2G).
Is this just a staging area for data waiting to move to the OS cache, or is
this really the area that psql caches its data?

effective_cache_size:
Again, according to the Varlena guide this tells psql how much system
memory is available for it to do its work in.
until recently, this was set at the default value of 1000. It was just
recently increased to 180000 (1.5G)
according to
http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html it
should be about 25% of memory?

Finally sort_mem:
Was until recently left at the default of 1000. Is now 16000.

Increasing the effective cache and sort mem didn't seem to make much of a
difference. I'm guessing the eff cache was probably raised a bit too much,
and shared_buffers is way to high.

What can I do to help determine what the proper settings should be and/or
look at other possible choke points. What should I look for in iostat,
mpstat, or vmstat as red flags that cpu, memory, or i/o bound?

DB maintenance wise, I don't believe they were running vacuum full until I
told them a few months ago that regular vacuum analyze no longer cleans out
dead tuples. Now normal vac is run daily, vac full weekly (supposedly). How
can I tell from the output of vacuum if the vac fulls aren't being done, or
not done often enough? Or from the system tables, what can I read?

Is there anywhere else I can look for possible clues? I have access to the
DB super-user, but not the system root/user.

Thank you for your time. Please let me know any help or suggestions you may
have. Unfortunately upgrading postgres, OS, kernel, or re-writing schema is
most likely not an option.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Rob Sell 2004-05-11 21:42:57 Re: Quad processor options
Previous Message scott.marlowe 2004-05-11 21:29:46 Re: [PERFORM] Quad processor options