Re: [PERFORM] 8.1beta3 performance
I'm seeing some other little oddities in the beta as well. I'm watching an ALTER TABLE ADD COLUMN right now that has been running almost two hours. I stopped it the first time at 1 hour; I suppose I'll let it go this time and see if it ever completes. The table is about 150K rows. Top, vmstat, and iostat show almost no cpu or disk activity (1 to 3%) - it's as if it just went to sleep. Tom Lane [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Jon Brisbin [EMAIL PROTECTED] writes: I have a stock FreeBSD 5.4 box that I put 8.1 on last night. I ran pgbench against it and my tps dropped from ~300tps in 8.0.3 to 20tps in 8.1. That's right. 20. No changes in any system configuration. You sure about that last? These numbers are kind of consistent with the idea that fsync is off in the 8.0 database and on in the 8.1 database. Using the same test case you mention (pgbench -s 10, -c 25 -t 1000), I find that 8.1 is a bit faster than 8.0, eg 8.1 fsync off: tps = 89.831186 (including connections establishing) tps = 89.865065 (excluding connections establishing) 8.1 fsync on: tps = 74.865078 (including connections establishing) tps = 74.889066 (excluding connections establishing) 8.0 fsync off: tps = 80.271338 (including connections establishing) tps = 80.302054 (excluding connections establishing) 8.0 fsync on: tps = 67.405708 (including connections establishing) tps = 67.426546 (excluding connections establishing) (All database parameters are defaults except fsync.) These numbers are with assert-enabled builds, on a cheap PC whose drive lies about write-complete, so they're not very representative of the real world I suppose. But I'm sure not seeing any 10x degradation. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] 8.1beta3 performance
PostgreSQL [EMAIL PROTECTED] writes: I'm seeing some other little oddities in the beta as well. I'm watching an ALTER TABLE ADD COLUMN right now that has been running almost two hours. I stopped it the first time at 1 hour; I suppose I'll let it go this time and see if it ever completes. The table is about 150K rows. Top, vmstat, and iostat show almost no cpu or disk activity (1 to 3%) - it's as if it just went to sleep. You sure it's not blocked on a lock? Check pg_locks ... if that sheds no light, try attaching to the backend process with gdb and getting a stack trace. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] 8.1beta3 performance
On Mon, 31 Oct 2005 17:16:46 -0600 PostgreSQL [EMAIL PROTECTED] wrote: We're running 8.1beta3 on one server and are having ridiculous performance issues. This is a 2 cpu Opteron box and both processors are staying at 98 or 99% utilization processing not-that-complex queries. Prior to the upgrade, our I/O wait time was about 60% and cpu utilization rarely got very high, now I/O wait time is at or near zero. I'm planning to go back to 8.0 tonight or tomorrow night but thought I'd check the pqsql-performance prophets before I gave it up. I have a stock FreeBSD 5.4 box that I put 8.1 on last night. I ran pgbench against it and my tps dropped from ~300tps in 8.0.3 to 20tps in 8.1. That's right. 20. No changes in any system configuration. No data in the new 8.1 database, only the pgbench init'ed stuff. 25 clients, 100 and 1000 transactions with a scaling factor of 10, which gives me 1,000,000 tuples to shoot through. I wiped out the 8.1 installation, put 8.0.4 in it's place, and pgbenched it again. ~300tps again. It's not a problem with system configuration if 8.0 works fine, but 8.1 has problems, unless there is something that 8.1 needs tweaked that 8.0 doesn't. In that case, I just need to know what that is and I can tweak it. Dual Xeon 2.6GB HTT PowerEdge, 4GB RAM, RAID 5 FreeBSD 5.4 RELEASE, custom-compiled kernel CFLAGS=-O3 -funroll-loops -pipe (also tried -O2, same difference) Jon Brisbin Webmeister NPC International, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] 8.1beta3 performance
On 1/11/05 2:50 pm, Jon Brisbin [EMAIL PROTECTED] wrote: On Mon, 31 Oct 2005 17:16:46 -0600 PostgreSQL [EMAIL PROTECTED] wrote: We're running 8.1beta3 on one server and are having ridiculous performance issues. This is a 2 cpu Opteron box and both processors are staying at 98 or 99% utilization processing not-that-complex queries. Prior to the upgrade, our I/O wait time was about 60% and cpu utilization rarely got very high, now I/O wait time is at or near zero. I'm planning to go back to 8.0 tonight or tomorrow night but thought I'd check the pqsql-performance prophets before I gave it up. I have a stock FreeBSD 5.4 box that I put 8.1 on last night. I ran pgbench against it and my tps dropped from ~300tps in 8.0.3 to 20tps in 8.1. That's right. 20. No changes in any system configuration. No data in the new 8.1 database, only the pgbench init'ed stuff. 25 clients, 100 and 1000 transactions with a scaling factor of 10, which gives me 1,000,000 tuples to shoot through. I wiped out the 8.1 installation, put 8.0.4 in it's place, and pgbenched it again. ~300tps again. It's not a problem with system configuration if 8.0 works fine, but 8.1 has problems, unless there is something that 8.1 needs tweaked that 8.0 doesn't. In that case, I just need to know what that is and I can tweak it. Hi Jon, Did you run the bundled version of pgbench against it's own installation? There we some changes to pgbench for 8.1, and I have to wonder (bearing in mind I haven't really looked at them) whether they could be affecting things in any way. Do you get comparable results running the 8.0 pgbench against both server versions? Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] 8.1beta3 performance
Jon Brisbin [EMAIL PROTECTED] writes: I have a stock FreeBSD 5.4 box that I put 8.1 on last night. I ran pgbench against it and my tps dropped from ~300tps in 8.0.3 to 20tps in 8.1. That's right. 20. No changes in any system configuration. You sure about that last? These numbers are kind of consistent with the idea that fsync is off in the 8.0 database and on in the 8.1 database. Using the same test case you mention (pgbench -s 10, -c 25 -t 1000), I find that 8.1 is a bit faster than 8.0, eg 8.1 fsync off: tps = 89.831186 (including connections establishing) tps = 89.865065 (excluding connections establishing) 8.1 fsync on: tps = 74.865078 (including connections establishing) tps = 74.889066 (excluding connections establishing) 8.0 fsync off: tps = 80.271338 (including connections establishing) tps = 80.302054 (excluding connections establishing) 8.0 fsync on: tps = 67.405708 (including connections establishing) tps = 67.426546 (excluding connections establishing) (All database parameters are defaults except fsync.) These numbers are with assert-enabled builds, on a cheap PC whose drive lies about write-complete, so they're not very representative of the real world I suppose. But I'm sure not seeing any 10x degradation. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] 8.1beta3 performance
On Mon, Oct 31, 2005 at 05:16:46PM -0600, PostgreSQL wrote: We're running 8.1beta3 on one server and are having ridiculous performance issues. This is a 2 cpu Opteron box and both processors are staying at 98 or 99% utilization processing not-that-complex queries. Prior to the upgrade, our I/O wait time was about 60% and cpu utilization rarely got very high, now I/O wait time is at or near zero. It sounds like some query got planned a different way that happened to be really suboptimal -- I've seen really bad queries be quick on earlier versions by accident and then not have the same luck on later versions. Could you find out what queries are taking so long (use log_min_duration_statement), and post table definitions and EXPLAIN ANALYZE output here? /* Steinar */ -- Homepage: http://www.sesse.net/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
