X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 4AED09FB2F1 for ; Tue, 29 Aug 2006 20:12:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 87967-04-3 for ; Tue, 29 Aug 2006 23:11:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.infogears.com (mail.infogears.com [69.51.90.143]) by postgresql.org (Postfix) with ESMTP id 648619FB3E6 for ; Tue, 29 Aug 2006 18:47:21 -0300 (ADT) Received: from mail.infogears.com (localhost.localdomain [127.0.0.1]) by mail.infogears.com (Postfix) with ESMTP id EAC4330980A3; Tue, 29 Aug 2006 15:47:19 -0600 (MDT) Received: from (HELO [69.51.88.19]) () (smtp-auth username rconover, mechanism plain) by mail.infogears.com (qpsmtpd/0.31-dev) with ESMTP; Tue, 29 Aug 2006 15:47:19 -0600 In-Reply-To: <44F446B2.2090900@studentvillage.co.za> References: <44F446B2.2090900@studentvillage.co.za> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: multipart/alternative; boundary=Apple-Mail-103-339695047 Message-Id: <9A079B52-0D1D-4682-A841-AA856439698E@infogears.com> Cc: pgsql-performance@postgresql.org From: Rusty Conover Subject: Re: PostgreSQL performance issues Date: Tue, 29 Aug 2006 15:47:17 -0600 To: Willo van der Merwe X-Mailer: Apple Mail (2.752.2) X-Virus-Checked: Checked by ClamAV on mail.infogears.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200608/462 X-Sequence-Number: 20647 --Apple-Mail-103-339695047 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed On Aug 29, 2006, at 7:52 AM, Willo van der Merwe wrote: > 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 Hi, What about doing a little bit of normalization? With 700k rows you could probably gain some improvements by: * normalizing the type and user columns to integer keys (dropping the 8 byte overhead for storing the field lengths) * maybe change the type column so that its a smallint if there is just a small range of possible values (emulating a enum type in other databases) rather the joining to another table. * maybe move message (if the majority of the rows are big and not null but not big enough to be TOASTed, ergo causing only a small number of rows to fit onto a 8k page) out of this table into a separate table that is joined only when you need the column's content. Doing these things would fit more rows onto each page, making the scan less intensive by not causing the drive to seek as much. Of course all of these suggestions depend on your workload. Cheers, Rusty -- Rusty Conover InfoGears Inc. --Apple-Mail-103-339695047 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1
On Aug 29, 2006, = at 7:52 AM, Willo van der Merwe wrote:

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:
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 Table "public.log"
=A0Column=A0 = |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Type=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Modifiers
= ---------+-----------------------------+---------------------------------<= BR> =A0site=A0=A0=A0 | bigint=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0 | not null
=A0stamp=A0=A0 | timestamp without = time zone | default now()
=A0type=A0=A0=A0 | = character(8)=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | not null = default 'log'::bpchar
=A0user=A0=A0=A0 | text=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 | not null default = 'public'::text
=A0message | text=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |
Indexes:
=A0=A0=A0 = "fki_log_sites" btree (site)
=A0=A0=A0 "ix_log_stamp" btree = (stamp)
=A0=A0=A0 "ix_log_type" btree ("type")
=A0=A0=A0 = "ix_log_user" btree ("user")
Foreign-key constraints:
=A0=A0=A0 = "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;
= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY = PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0
= --------------------------------------------------------------------------= --------------------------------------------------------
=A0Aggregate=A0= (cost=3D55121.95..55121.96 rows=3D1 width=3D0) (actual = time=3D4557.797..4557.798 rows=3D1 loops=3D1)
=A0=A0 ->=A0 Seq = Scan on property_values=A0 (cost=3D0.00..51848.56 rows=3D1309356 = width=3D0) (actual time=3D0.026..2581.418 rows=3D1309498 loops=3D1)
= =A0Total 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


Hi,

What about doing a little = bit of normalization?=A0

With 700k rows you could = probably gain some improvements by:

*=A0normalizing the type = and user columns to integer keys (dropping the 8 byte overhead for = storing the field lengths)
* maybe change the type column so = that its a smallint if there is just a small range of possible values = (emulating a enum type in other databases) rather the joining to another = table.
* maybe move message (if the majority of the rows are = big and not null but not big enough to be TOASTed, ergo causing only a = small number of rows to fit onto a 8k page) out of this table into = a=A0separate table that is joined only when you need the column's = content.

Doing = these things would fit more rows onto each page, making the scan less = intensive by not causing the drive to seek as much.=A0 Of course all of = these suggestions depend on your workload.

Cheers,

Rusty
--
Rusty = Conover