From: Willo van der Merwe <willo@studentvillage.co.za>
To: pgsql-performance@postgresql.org
Subject: PostgreSQL performance issues
Date: Tue, 29 Aug 2006 15:52:50 +0200
Message-ID: <44F446B2.2090900@studentvillage.co.za> (raw)
Hi,
We're running PostgreSQL 8.1.4 on CentOS 4 (Linux version
2.6.9-34.0.1.ELsmp). Hardware specs:
2x AMD Dual-Core Opteron 270 Italy 1Ghz HT 2 x 1MB L2 Cache Socket 940
4 GB Registered ECC PC3200 DDR RAM
SuperMicro Server-Class 1U AS1020S series system
Dual-channel Ultra320 SCSI controller
1 x 73 GB 10,000rpm Ultra320 SCSI drive with 8MB cache
I use it to drive a web application. Everything was working fine when
all of a sudden today, things went belly up. Load on the server started
increasing and query speeds decreased rapidly. After dropping all the
clients I did some quick tests and found the following:
I have a log table looking like this:
Table "public.log"
Column | Type | Modifiers
---------+-----------------------------+---------------------------------
site | bigint | not null
stamp | timestamp without time zone | default now()
type | character(8) | not null default 'log'::bpchar
user | text | not null default 'public'::text
message | text |
Indexes:
"fki_log_sites" btree (site)
"ix_log_stamp" btree (stamp)
"ix_log_type" btree ("type")
"ix_log_user" btree ("user")
Foreign-key constraints:
"log_sites" FOREIGN KEY (site) REFERENCES sites(id) ON UPDATE
CASCADE ON DELETE CASCADE
and it has 743321 rows and a explain analyze select count(*) from
property_values;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=55121.95..55121.96 rows=1 width=0) (actual
time=4557.797..4557.798 rows=1 loops=1)
-> Seq Scan on property_values (cost=0.00..51848.56 rows=1309356
width=0) (actual time=0.026..2581.418 rows=1309498 loops=1)
Total runtime: 4557.978 ms
(3 rows)
4 1/2 seconds for a count(*) ? This seems a bit rough - is there
anything else I can try to optimize my Database? You can imagine that
slightly more complex queries goes out the roof.
Any help appreciated
Regards
Willo van der Merwe
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: willo@studentvillage.co.za
Subject: Re: PostgreSQL performance issues
In-Reply-To: <44F446B2.2090900@studentvillage.co.za>
* 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