X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 05B5352A40 for ; Mon, 4 Apr 2005 16:09:44 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 40962-10 for ; Mon, 4 Apr 2005 15:09:33 +0000 (GMT) Received: from hightowergroup.com (mail005.readyhosting.com [63.99.209.11]) by svr1.postgresql.org (Postfix) with ESMTP id 76E115294B for ; Mon, 4 Apr 2005 16:09:32 +0100 (BST) Received: from hightower10.mail.hightowergroup.com [66.109.231.196] by hightowergroup.com with ESMTP (SMTPD32-8.05) id A8A423800D4; Mon, 04 Apr 2005 10:09:24 -0500 Message-Id: <6.1.2.0.2.20050404111253.02f14a38@mail.hightowergroup.com> X-Sender: dleedom@hightowergroup.com@mail.hightowergroup.com X-Mailer: QUALCOMM Windows Eudora Version 6.1.2.0 Date: Mon, 04 Apr 2005 11:14:35 -0400 To: pgsql-admin@postgresql.org From: "David A. Leedom" Subject: Re: Having performance problems. In-Reply-To: <040420051427.2521.42514EEB000231AF000009D92207001641050A0A 0998020E0A@comcast.net> References: <040420051427.2521.42514EEB000231AF000009D92207001641050A0A0998020E0A@comcast.net> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/51 X-Sequence-Number: 17064 How many rows are you expecting to be returned? At 10:27 AM 4/4/2005, eanxgeek@comcast.net wrote: >First let me start by saying I am pretty new to Postgresql. To date I >have only worked with small databases, I now have a database that isn't >big but is big enough to create performance issues. The database is >roughly 450 MB. I have two tables of interest; logs and hosts. At one >point I had the following sql: > > SELECT * FROM hosts, logs where (logs.host_id = hosts.host_id) ORDER BY > date DESC, time DESC; > >This took over 1000 seconds to execute. > >I then upgraded to PGSQL 8.0.1 and created tablespace logs_t and hosts_t, >these are on separate controllers and separate disks. > List of tablespaces >Name | Owner | Location >------------+----------+----------------- > hosts_t | postgres | /pgdata/hosts_t > logs_t | postgres | /pgdata/logs_t > >I then altered the tables logs and hosts to use the new tablespace >respectively and I changed the sql to read: > >SELECT hostname,facility,priority,level,tag,date,time,program,msg,seq FROM >hosts, logs where (logs.host_id = hosts.host_id) ORDER BY date DESC, time DESC; > >Under 7.x my PGDATA was on /pgdata; however, for 8.x I have left it in in >its default location of /var/lib/pgsql. So what is happening now is: >1. I am still seeing all 4x700 MHz CPUs go to 95%+ IOWAIT, appears that >/var is the hardest hit. >2. SELECT hostname,facility,priority,level,tag,date,time,program,msg,seq >FROM hosts, logs where (logs.host_id = hosts.host_id); works but takes >awhile; however, adding the ORDER BY condition causes the SQL to execute >to the point that /var becomes full and the SQL exits with: >ERROR: could not write block 81940 of temporary file: No space left on device >HINT: Perhaps out of disk space? > >What parameters can I start "tweaking" and what can I do to addres the >issue of /var filling up? Below are some of my kernel and database settings: > >effective_cache_size | 1000 >maintenance_work_mem | 16384 >max_connections | 32 >shared_buffers | 64 >------ Shared Memory Segments -------- >key shmid owner perms bytes nattch status >0x0052e2c1 38043648 postgres 600 1540096 2 > > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster The Hightower Group, Inc. Custom Software Solutions Designed To Fit Your Business Like A Glove. 165 West Airport Road, Suite B/Lititz, PA 17543 V:717-560-4002, 877-560-4002 x: 114 F:717-560-2825 www.hightowergroup.com