Help with performance problems

From: "Chris Hoover" <revoohc(at)sermonaudio(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Cc: <revoohc(at)sermonaudio(dot)com>
Subject: Help with performance problems
Date: 2004-04-23 13:31:17
Message-ID: 200404230931.17850.revoohc@sermonaudio.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I need some help. I have 5 db servers running our database servers, and they
all are having various degrees of performance problems. The problems we are
experiencing are:

1. General slowness
2. High loads

All of our db's are running on Dell Poweredge 2650 with 2 P4 Xeons (2.8 ->
3.06 GHz) with 8 to 12 GB of memory. The databases are running on attached
Dell Powervault 220s running raid5.

The databases were created and taken into production before I started working
here and are very flat. Most of the major tables have a combined primary key
using an int field and a single char field. There are some additional
indexes on some tables. Most queries I see in the logs are running at less
than .01 seconds with many significantly slower.

We are trying to narrow down the performance problem to either the db or the
hardware. As the dba, I need to try and get these db's tuned to the best
possible way considering the current db state. We are in the beginning of a
complete db redesign and application re-write, but the completion and
deployment of the new db and app are quite a ways off.

Anyway, we are running the following:
PE 2650 w/ 2 cpus (2.8-3.06) - HT on
8-12 GB memory
OS on raid 0
DB's on Powervaults 220S using raid 5 (over 6 disks)
Each Postgresql cluster has 2 db up to almost 170db's (project to level out
the num of db's/cluster is being started)
DB's are no bigger than a few GB in size (largest is about 11GB according to a
du -h)
Running RH ES 2.1

Here is the postgresql.conf from the server with the 11GB db:

max_connections = 64
shared_buffers = 32768 # 256MB=32768(buffs)*8192(bytes/buff)
max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes
sort_mem = 4096 # 256MB=4096(bytes/proc)*64(procs or conns)
checkpoint_segments = 16 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 30 # range 30-3600, in seconds
effective_cache_size = 131072 # typically 8KB each
log_connections = true
log_pid = true
log_statement = true
log_duration = true
log_timestamp = true
stats_start_collector = true
stats_reset_on_server_start = true
stats_command_string = true
stats_row_level = true
stats_block_level = true
LC_MESSAGES = 'en_US'
LC_MONETARY = 'en_US'
LC_NUMERIC = 'en_US'
LC_TIME = 'en_US'

Here is top (server running pretty good right now)
9:28am up 25 days, 16:02, 2 users, load average: 0.54, 0.33, 0.22
94 processes: 91 sleeping, 3 running, 0 zombie, 0 stopped
CPU0 states: 64.0% user, 0.1% system, 0.0% nice, 34.0% idle
CPU1 states: 29.0% user, 9.0% system, 0.0% nice, 60.0% idle
CPU2 states: 2.0% user, 0.1% system, 0.0% nice, 96.0% idle
CPU3 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle
Mem: 7720072K av, 7711648K used, 8424K free, 265980K shrd, 749888K buff
Swap: 2096440K av, 22288K used, 2074152K free 6379304K
cached

Here is top from another server (with the most db's):
9:31am up 25 days, 16:05, 5 users, load average: 2.34, 3.39, 4.28
147 processes: 145 sleeping, 2 running, 0 zombie, 0 stopped
CPU0 states: 6.0% user, 1.0% system, 0.0% nice, 91.0% idle
CPU1 states: 9.0% user, 4.0% system, 0.0% nice, 85.0% idle
CPU2 states: 9.0% user, 3.0% system, 0.0% nice, 86.0% idle
CPU3 states: 9.0% user, 4.0% system, 0.0% nice, 85.0% idle
Mem: 7721096K av, 7708040K used, 13056K free, 266132K shrd, 3151336K buff
Swap: 2096440K av, 24208K used, 2072232K free 3746596K
cached

Thanks for any help/advice,

Chris

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Aaron Werman 2004-04-23 15:16:13 Re: Help with performance problems
Previous Message Aaron Werman 2004-04-23 12:19:36 Re: Looking for ideas on how to speed up warehouse loading