Thread: 8.1beta3 performance
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.
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/
On Mon, 2005-31-10 at 17:16 -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. Have you done anything to verify that this is actually a problem with 8.1, and not some other change that was made as part of the upgrade process? For example, if ANALYZE hasn't been re-run, that could cause the plans chosen by the optimizer to be completely different. -Neil
On Mon, 31 Oct 2005 17:16:46 -0600 "PostgreSQL" <martin@portant.com> 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.
On 1/11/05 2:50 pm, "Jon Brisbin" <jon.brisbin@npcinternational.com> wrote: > On Mon, 31 Oct 2005 17:16:46 -0600 > "PostgreSQL" <martin@portant.com> 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
Jon Brisbin <jon.brisbin@npcinternational.com> 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
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" <tgl@sss.pgh.pa.us> wrote in message news:26529.1130865883@sss.pgh.pa.us... > Jon Brisbin <jon.brisbin@npcinternational.com> 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 >
"PostgreSQL" <martin@portant.com> 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