From: David A. Leedom <daleedom@hightowergroup.com>
To: pgsql-admin@postgresql.org
Subject: Re: Having performance problems.
Date: Mon, 04 Apr 2005 11:14:35 -0400
Message-ID: <6.1.2.0.2.20050404111253.02f14a38@mail.hightowergroup.com> (raw)
In-Reply-To: <040420051427.2521.42514EEB000231AF000009D92207001641050A0A 0998020E0A@comcast.net>
References: <040420051427.2521.42514EEB000231AF000009D92207001641050A0A0998020E0A@comcast.net>
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
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: daleedom@hightowergroup.com
Subject: Re: Having performance problems.
In-Reply-To: <6.1.2.0.2.20050404111253.02f14a38@mail.hightowergroup.com>
References: <040420051427.2521.42514EEB000231AF000009D92207001641050A0A0998020E0A@comcast.net> <6.1.2.0.2.20050404111253.02f14a38@mail.hightowergroup.com>
* 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