Re: Profiling

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
7.3

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. ;)



Cory 'G' Watson