pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Having performance problems.
3+ messages / 2 participants
[nested] [flat]

* Having performance problems.
@ 2005-04-04 14:27  eanxgeek@comcast.net
  0 siblings, 1 reply; 3+ messages in thread

From: eanxgeek@comcast.net @ 2005-04-04 14:27 UTC (permalink / raw)
  To: pgsql-admin

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Having performance problems.
@ 2005-04-04 15:14  David A. Leedom <daleedom@hightowergroup.com>
  parent: eanxgeek@comcast.net
  0 siblings, 0 replies; 3+ messages in thread

From: David A. Leedom @ 2005-04-04 15:14 UTC (permalink / raw)
  To: pgsql-admin

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  




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Having performance problems.
@ 2005-04-04 16:44  eanxgeek@comcast.net
  0 siblings, 0 replies; 3+ messages in thread

From: eanxgeek@comcast.net @ 2005-04-04 16:44 UTC (permalink / raw)
  To: David A. Leedom <daleedom@hightowergroup.com>; +Cc: pgsql-admin

At this moment, in my 8.x configuration using tablespaces for logs_t and hosts_t I get 1,221,000 rows back.  My new response time is 563 seconds, which is about half of what it was under 7.x with no tablepaces.

Anything else I can be looking at or tuning?

-Thanks!


> 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  
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2005-04-04 16:44 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-04 14:27 Having performance problems. eanxgeek@comcast.net
2005-04-04 15:14 ` David A. Leedom <daleedom@hightowergroup.com>
2005-04-04 16:44 Re: Having performance problems. eanxgeek@comcast.net

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