Обсуждение: Having performance problems.
First let me start by saying I am pretty new to Postgresql. To date I have only worked with small databases, I now
havea database that isn't big but is big enough to create performance issues. The database is roughly 450 MB. I
havetwo 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
separatedisks.
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
whatis 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
kerneland 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
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
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 responsetime 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