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

Re: Profiling

From: Cory 'G' Watson <gphat(at)cafes(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Profiling
Date: 2002-12-17 13:29:48
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
On Tuesday, December 17, 2002, at 12:57 AM, Shridhar Daithankar wrote:

> On Tuesday 17 December 2002 09:33 am, you wrote:
>> Folks,
>> I had a request from one of the SF-PUG members that I found
>> interesting.  She suggested that we post the specs of some of the
>> PostgreSQL servers that we administrate, their statistics, and some
>> comments on how they perform.  I'll start it off with an example:

My take:

Dual PIII-1.12Ghz, 3Gb, 5 x 36 RAID 5'ed with a spare, RedHat 7.2, Pg 

pgbench, default settings, 252tps inc. connex, 409tps excluding connex

Day to day, runs a monitoring/historical analysis tool of my design 
with gathers metrics from around 30 hosts (they report every 10 
minutes, by their clock).  Has 3,689,652 rows as of right now in the 
'metrics' table, which is indexed by timestamp.

My 'main' query is in the form of:

SELECT timestamp, data FROM metrics WHERE resgroupid=? and hostid=? AND 
timestamp BETWEEN ? AND ? ORDER BY timestamp

Index is on timestamp.

This query generally takes about half a second for 24 hours worth of 
data.  I just ran a 240 hour query on a test database with about 20,000 
rows and the result too 2998ms.

Things slowed to a crawl about 2 weeks ago, so I upgraded to 7.3 and 
saw a huge improvement.  I believe part of this might have been due to 
the recreation of the database, similar to a CLUSTER.  My performance 
is not degrading from a time perspective, but CPU usage is steadily 
degrading.  User time is steadily increasing over the last 240 hours, 
from 5% to 15%.  Attached is output of my monitoring program (well, the 
new improved Java version) showing the CPU performance over the last 
240 hours.

shared_buffers = 98304
sort_mem = 1600
fsync = false

Everything else is default, recommendations welcome. ;)

Attachment: cpu.png
Description: image/png (28.1 KB) (inlined above)

In response to


pgsql-performance by date

Next:From: Cory 'G' WatsonDate: 2002-12-17 13:30:13
Subject: Re: Profiling
Previous:From: Shridhar DaithankarDate: 2002-12-17 06:57:07
Subject: Re: Profiling

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