Thread: 7.1 performance
Hi there, i just upgraded my database system from 7.0.3 to 7.1 ... i did a dumpall and psql -f dump.sql to the new db. the data is in place correctly and its running fine. but as soon as i put the connections back on to the db the load raised to 6. prior to the upgrade the system load was an 0.3 ... now it is at 6. is this normal for 7.1 to need so much more hardware? i use the same settings for sort_mem, buffers, etc. just no fsync. I thought 7.1 wouldn't need that anymore. Any suggestions that i should do? the performance is really bad now :( btw: the db is a dedicated p3-800 + 1gig ram + uw scsi drives running redhat6.2 thanks cu, thomas
"Thomas Heller" <maniax@comtron.net> writes:
> is this normal for 7.1 to need so much more hardware?
No...
> i use the same
> settings for sort_mem, buffers, etc. just no fsync. I thought 7.1 wouldn't
> need that anymore.
Does the behavior change if you turn off fsync again?
regards, tom lane
> i just upgraded my database system from 7.0.3 to 7.1 ... i > did a dumpall and psql -f dump.sql to the new db. the data > is in place correctly and its running fine. but as soon as > i put the connections back on to the db the load raised to > 6. prior to the upgrade the system load was an 0.3 ... > now it is at 6. > > is this normal for 7.1 to need so much more hardware? i use > the same settings for sort_mem, buffers, etc. just no fsync. > I thought 7.1 wouldn't need that anymore. 7.1 requires fsync! fsync-s are just much more optimized there. > Any suggestions that i should do? the performance is really bad now :( What kind of queries have bad performance now? For insert/update/delete queries you could consider increasing wal_buffers (~256) and wal_files (~16). Vadim
Hi, > > i use the same > > settings for sort_mem, buffers, etc. just no fsync. I thought 7.1 wouldn't > > need that anymore. > > Does the behavior change if you turn off fsync again? No. The Database is loaded with up to 25req/s ... most of them are just simple SELECTS (no ORDER BY, no JOINS) ... i use indexes on all important fields. Some queries just return 1 row ... some even 1.000 ... when i run these queries in psql they seem fast and not problematic. (i did no query optimizing yet) Note: its a backend database to a webpage (php). There are arround 8 postgres processes running on the machine. Through the mass of Queries i can't really say WHO is causing the load to the machine. As the load raised to about 17 after 1 hour online, i switched back to 7.0.3 ... now the machine is at load 0,3 again and everything is fine. Here are some setting from my postgresql.conf log_connections = off fsync = on (also off tested) tcpip_socket = true max_connections = 32 sort_mem = 4096 shared_buffers = 128 wal_buffers = 8 wal_files = 8 wal_sync_method = fsync wal_debug = 0 commit_delay = 0 commit_siblings = 5 checkpoint_segments = 3 checkpoint_timeout = 300 i would really be interested in using 7.1 cause i set it up for a new project (running a dual-p3-1ghz + 2 gig ram) and this machine really needs good performance and i use (binary &|) which wheren't available in <7.1 afaik. thanks, thomas
I had to run a script which did few thousand queries on 7.1 few times and after that even the queries that are done entirely based on indexes were also _VERY_ slow. But I did a "vacuumdb dbname" as postgres user and after that thigs were back to normal - very fast. But I could _NOT_ reproduce this. Does the number of WAL files have to do anything with this? Anuradha On Wed, 25 Apr 2001, Thomas Heller wrote: > Hi there, > > i just upgraded my database system from 7.0.3 to 7.1 ... i did a > dumpall and psql -f dump.sql to the new db. the data is in place > correctly and its running fine. but as soon as i put the connections > back on to the db the load raised to 6. prior to the upgrade the > system load was an 0.3 ... now it is at 6. > > is this normal for 7.1 to need so much more hardware? i use the same > settings for sort_mem, buffers, etc. just no fsync. I thought 7.1 > wouldn't need that anymore. > > Any suggestions that i should do? the performance is really bad now :( > > btw: the db is a dedicated p3-800 + 1gig ram + uw scsi drives running > redhat6.2 > > thanks cu, > thomas > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Two things you should check. 1) Are you using persistent connections in your PHP scripts? 2) Are you certain your queries are utilizing the indices you have set up for them. For the answer to 1) check your php configuration. For the answer to 2) do an explain on each query that is slow, making sure it's hitting the index you think it should be. Stephen Blake s t e p h e n <dot/> b l a k e <at/> v e r i t e c t <dot/> c o m -----Original Message----- From: Thomas Heller [mailto:maniax@comtron.net] Sent: Thursday, April 26, 2001 12:56 PM To: pgsql-admin@postgresql.org Subject: [ADMIN] Re: 7.1 performance Hi, > > i use the same > > settings for sort_mem, buffers, etc. just no fsync. I thought 7.1 wouldn't > > need that anymore. > > Does the behavior change if you turn off fsync again? No. The Database is loaded with up to 25req/s ... most of them are just simple SELECTS (no ORDER BY, no JOINS) ... i use indexes on all important fields. Some queries just return 1 row ... some even 1.000 ... when i run these queries in psql they seem fast and not problematic. (i did no query optimizing yet) Note: its a backend database to a webpage (php). There are arround 8 postgres processes running on the machine. Through the mass of Queries i can't really say WHO is causing the load to the machine. As the load raised to about 17 after 1 hour online, i switched back to 7.0.3 ... now the machine is at load 0,3 again and everything is fine. Here are some setting from my postgresql.conf log_connections = off fsync = on (also off tested) tcpip_socket = true max_connections = 32 sort_mem = 4096 shared_buffers = 128 wal_buffers = 8 wal_files = 8 wal_sync_method = fsync wal_debug = 0 commit_delay = 0 commit_siblings = 5 checkpoint_segments = 3 checkpoint_timeout = 300 i would really be interested in using 7.1 cause i set it up for a new project (running a dual-p3-1ghz + 2 gig ram) and this machine really needs good performance and i use (binary &|) which wheren't available in <7.1 afaik. thanks, thomas ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Hi, > Two things you should check. > > 1) Are you using persistent connections in your PHP scripts? not that good for 250+ apache httpd sessions running (only serving php ... images/static content is balanced to another server). ;) definitly no. > 2) Are you certain your queries are utilizing the indices you have set up > for them. they use indices .. i'm pretty sure with it cause i didn't change any indices as i pg_dumpall'd the db and restored it to 7.1. As said .. the 7.0.3 is laughing about the traffic (load 0.3) and the 7.1 is about to break (load 16). No change in php conf ... the only change is the upgrade to 7.1. hmpf (i hope the next dual p3-1ghz won't break with 7.1, i need it there ...) cu thomas