pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Doug Y <dylists@ptd.net>
To: psql-performance@postgresql.org
Subject: Clarification on some settings
Date: Tue, 11 May 2004 17:36:31 -0400
Message-ID: <6.0.1.1.2.20040511163104.01e9cbb0@mail.traderonline.com> (raw)

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.




view thread (6+ messages)  latest in thread

Message-ID: <6.0.1.1.2.20040511163104.01e9cbb0@mail.traderonline.com>
Permalink:  ../6.0.1.1.2.20040511163104.01e9cbb0@mail.traderonline.com/
Also on:    postgresql.org/message-id/6.0.1.1.2.20040511163104.01e9cbb0@mail.traderonline.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: dylists@ptd.net, psql-performance@postgresql.org
  Subject: Re: Clarification on some settings
  In-Reply-To: <6.0.1.1.2.20040511163104.01e9cbb0@mail.traderonline.com>
  References: <6.0.1.1.2.20040511163104.01e9cbb0@mail.traderonline.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox