Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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_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 

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 

Thanks for any help/advice,



pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group