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

PostgreSQL performance issues

From: Willo van der Merwe <willo(at)studentvillage(dot)co(dot)za>
To: pgsql-performance(at)postgresql(dot)org
Subject: PostgreSQL performance issues
Date: 2006-08-29 13:52:50
Message-ID: 44F446B2.2090900@studentvillage.co.za (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: A. KretschmerDate: 2006-08-29 14:46:36
Subject: Re: PostgreSQL performance issues
Previous:From: A. KretschmerDate: 2006-08-29 07:38:11
Subject: Re: Internal Operations on LIMIT & OFFSET clause

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