Message-ID: <44F446B2.2090900@studentvillage.co.za>
Date: Tue, 29 Aug 2006 15:52:50 +0200
From: Willo van der Merwe <willo@studentvillage.co.za>
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: PostgreSQL performance issues
Content-Type: multipart/alternative;
	boundary="------------080008020200000500000601"

This is a multi-part message in MIME format.
--------------080008020200000500000601
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------080008020200000500000601
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<font face="Helvetica, Arial, sans-serif">Hi,<br>
<br>
We're running PostgreSQL 8.1.4 on CentOS 4 (Linux version
2.6.9-34.0.1.ELsmp). Hardware specs:<br>
</font>
<pre wrap=""><font face="Helvetica, Arial, sans-serif">2x AMD Dual-Core Opteron 270 Italy 1Ghz HT 2 x 1MB L2 Cache Socket 940
<span class="moz-txt-citetags"></span>4 GB Registered ECC PC3200 DDR RAM
<span class="moz-txt-citetags"></span>SuperMicro Server-Class 1U AS1020S series system
<span class="moz-txt-citetags"></span>Dual-channel Ultra320 SCSI controller
<span class="moz-txt-citetags"></span>1 x 73 GB 10,000rpm Ultra320 SCSI drive with 8MB cache</font></pre>
<font face="Helvetica, Arial, sans-serif">I use it to drive a web
application. </font><font face="Helvetica, Arial, sans-serif">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:<br>
<br>
I have a log table looking like this:<br>
<small><font face="Courier New, Courier, monospace">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Table "public.log"<br>
&nbsp;Column&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Modifiers<br>
---------+-----------------------------+---------------------------------<br>
&nbsp;site&nbsp;&nbsp;&nbsp; | bigint&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | not null<br>
&nbsp;stamp&nbsp;&nbsp; | timestamp without time zone | default now()<br>
&nbsp;type&nbsp;&nbsp;&nbsp; | character(8)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | not null default 'log'::bpchar<br>
&nbsp;user&nbsp;&nbsp;&nbsp; | text&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | not null default 'public'::text<br>
&nbsp;message | text&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |<br>
Indexes:<br>
&nbsp;&nbsp;&nbsp; "fki_log_sites" btree (site)<br>
&nbsp;&nbsp;&nbsp; "ix_log_stamp" btree (stamp)<br>
&nbsp;&nbsp;&nbsp; "ix_log_type" btree ("type")<br>
&nbsp;&nbsp;&nbsp; "ix_log_user" btree ("user")<br>
Foreign-key constraints:<br>
&nbsp;&nbsp;&nbsp; "log_sites" FOREIGN KEY (site) REFERENCES sites(id) ON UPDATE
CASCADE ON DELETE CASCADE<br>
</font></small><br>
and it has 743321 rows and a explain analyze select count(*) from
property_values;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY
PLAN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <br>
----------------------------------------------------------------------------------------------------------------------------------<br>
&nbsp;Aggregate&nbsp; (cost=55121.95..55121.96 rows=1 width=0) (actual
time=4557.797..4557.798 rows=1 loops=1)<br>
&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on property_values&nbsp; (cost=0.00..51848.56
rows=1309356 width=0) (actual time=0.026..2581.418 rows=1309498 loops=1)<br>
&nbsp;Total runtime: 4557.978 ms<br>
(3 rows)<br>
<br>
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.<br>
<br>
Any help appreciated<br>
<br>
Regards<br>
<br>
Willo van der Merwe<br>
</font>
</body>
</html>

--------------080008020200000500000601--