Re: [PERFORM] a lot of problems with pg 7.4
On Sat, 13 Dec 2003, Kari Lavikka wrote: I evaluated pg 7.4 on our development server and it looked just fine but performance with production loads seems to be quite poor. Most of performance problems are caused by nonsensical query plans but there's also some strange slowness that I can't locate. I had the same problem. I use Fedora Core 1 and after I updated from 7.4RC1/7.4RC2 (I build my own RPMs) to 7.4 using the binary RPMs from a mirror site and sometimes I had to restart postmaster to make something work. I rebuilt the src.rpm from current rawhide (7.4-5) and now everything is ok. The guys from redhat/fedora also add some patches (rpm-pgsql-7.4.patch seems to be the most important, the rest seem to be for a proper compile) but I didn't have the time to test if the loss of performance is because in the original binary RPMs from postgresql.org the patch(es) is(are) not present, because of the compiler and optflags used to build the RPMs are not chosed well or something else. I used gcc 3.3.2 (from FC1 distro) and the following optflags: - On a P4 machine: optflags: i686 -O2 -g -march=pentium4 -msse2 -mfpmath=sse -fomit-frame-pointer -fforce-addr -fforce-mem -maccumulate-outgoing-args -finline-limit=2048 - On a Celeron Tualatin: optflags: i686 -O2 -g -march=pentium3 -msse -mfpmath=sse -fomit-frame-pointer -fforce-addr -fforce-mem -maccumulate-outgoing-args -finline-limit=2048 So, if you use the original binaries from postgresql.org try to recompile from sources setting CFLAGS and CXXFLAGS to proper values (maybe -msse2 -mfpmath=sse are not a good choice, you can try removing them). If not then review your postgresql configuration (buffers, memory, page cost, etc), because 7.4 seems to be faster than 7.3 and there is no reason for it to run slower on your system. -- Any views or opinions presented within this e-mail are solely those of the author and do not necessarily represent those of any company, unless otherwise expressly stated. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] a lot of problems with pg 7.4
On Sat, 13 Dec 2003, Kari Lavikka wrote: I evaluated pg 7.4 on our development server and it looked just fine but performance with production loads seems to be quite poor. Most of performance problems are caused by nonsensical query plans Some of the estimates that pg made in the plans you showed was way off. I assume you have run VACUUM ANALYZE recently? If that does not help maybe you need to increaste the statistics gathering on some columns so that pg makes better estimates. With the wrong statistics it's not strange that pg chooses bad plans. -- /Dennis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Tuning for mid-size server
On Sun, Dec 14, 2003 at 12:42:21AM -0500, Bruce Momjian wrote: I know this is an old email, but have you tested larger shared buffers in CVS HEAD with Jan's new cache replacement policy? Not yet. It's on our TODO list, for sure, because the consequences of relying too much on the filesystem buffers under certain perverse loads is lousy database performance _precisely_ when we need it. I expect some testing of this type some time in January. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Tables Without OIDS and its effect
Neil Conway [EMAIL PROTECTED] writes: BTW, we intend to phase out the use of OIDs for user tables in the long term. I don't believe anyone has proposed removing the facility altogether. There's a big difference between making the default behavior be not to have OIDs and removing the ability to have OIDs. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Tables Without OIDS and its effect
Tom Lane [EMAIL PROTECTED] writes: I don't believe anyone has proposed removing the facility altogether. There's a big difference between making the default behavior be not to have OIDs and removing the ability to have OIDs. Right, that's what I had meant to say. Sorry for the inaccuracy. -Neil ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
