Re: [PERFORM] Query plan - now what?
Here are my query and schema. The ERD is at http://dshadovi.f2o.org/pg_erd.jpg (sorry about its resolution). -David SELECT zbr.zebra_name , dog.dog_name , mnk.monkey_name , wrm.abbreviation || ptr.abbreviation as abbrev2 , whg.warthog_num , whg.color , rhn.rhino_name , der.deer_name , lin.designator , frg.frog_id , frg.sound_id , tgr.tiger_name , frg.leg_length , frg.jump_distance FROM frogs frg , deers der , warthogs whg , rhinos rhn , zebras zbr , dogs dog , monkeys mnk , worms wrm , parrots prt , giraffes grf , lions lin , tigers tgr WHERE 1 = 1 AND frg.deer_id = der.deer_id AND whg.whg_id = frg.frg_id AND frg.rhino_id = rhn.rhino_id AND zbr.zebra_id = dog.zebra_id AND dog.dog_id = mky.dog_id AND mky.dog_id = whg.dog_id AND mky.monkey_num = whg.monkey_num AND whg.worm_id = wrm.worm_id AND whg.parrot_id = prt.parrot_id AND prt.beak = 'L' AND frg.frog_id = grf.frog_id AND grf.lion_id = lin.lion_id AND frg.tiger_id = tgr.tiger_id ; CREATE TABLE zebras ( zebra_id INTEGER NOT NULL, zebra_name VARCHAR(25), PRIMARY KEY (zebra_id), UNIQUE (zebra_name)); CREATE TABLE dogs ( zebra_id INTEGER NOT NULL, dog_id INTEGER NOT NULL, dog_name VARCHAR(25), FOREIGN KEY (zebra_id) REFERENCES zebras (zebra_id), PRIMARY KEY (dog_id), UNIQUE (dog_name, dog_num)); CREATE TABLE monkeys ( dog_id INTEGER NOT NULL, monkey_num INTEGER, monkey_name VARCHAR(25), PRIMARY KEY (dog_id, monkey_num), FOREIGN_KEY (dog_id) REFERENCES dogs (dog_id)); CREATE INDEX mnk_dog_id_idx ON monkeys (dog_id); CREAIE INDEX mnk_mnk_num_idx ON monkeys (monkey_num); CREATE TABLE warthogs ( warthog_id INTEGER NOT NULL, warthog_num INTEGER, color VARCHAR(25) NOT NULL, dog_id INTEGER NOT NULL, monkey_num INTEGER NOT NULL, parrot_id INTEGER, beakCHAR(l), worm_id INTEGER, PRIMARY KEY (warthog_id), FOREIGN KEY (parrot_id, beak)REFERENCES parrots (parrot_id, beak) FOREIGN KEY (dog_id, monkey_num) REFERENCES monkeys (dog_id, monkey_nun) FOREIGN KEY (worm_id)REFERENCES worms (worm_id)); CREATE UNIQUE INDEX whg_whg_id_idx ON warthogs (warthog_id) CREATE INDEX whg_dog_id_idx ON warthogs (dog_id); CREATE INDEX whg_mnk_num_idx ON warthogs (monkey_num) CREATE INDEX whg_wrm_id_idx ON warthogs (worm_id); CREATE INDEX IDX_warthogs_1 ON warthogs (monkey_num, dog_id) CREATE INDEX lOX warthogs_2 ON warthogs (beak, parrot_id); CREATE TABLE worms ( worm_id INTEGER NOT NULL, abbreviation CHAR(l), PRIMARY KEY worm_id)); CREATE TABLE parrots ( parrot_idINTEGER NOT NULL, beak CHAR(1) NOT NULL, abbreviation CHAR(1), PRIMARY KEY (parrot_id, beak)); CREATE INDEX prt_prt_id_idx ON parrots (parrot_id) CREATE INDEX prt_beak_idx ON parrots (beak): CREATE TABLE deers ( deer_id INTEGER NOT NULL, deer_name VARCHAR(40), PRIMARY KEY (deer_id)); CREATE UNIQUE INDEX der_der_id_unq_idx ON deers (deer_id); CREATE TABLE rhinos ( rhino_id INTEGER NOT NULL, rhino_name VARCHAR(255), CONSTRAINT rhn_rhn_name_unique UNIQUE, CONSTRAINT PK_rhn PRIMARY KEY (rhino_id)); CREATE UNIQUE INDEX rhn_rhn_id_unq_idx ON rhinos (rhino_id); CREATE TABLE tigers ( tiger_id INTEGER NOT NULL, tiger_name VARCHAR(255), PRIMARY KEY (tiger_id)); CREATE UNIQUE INDEX tgr_tgr_id_unq_idx ON tigers (tiger_id); CREATE TABLE frogs ( frog_id INTEGER NOT NULL, warthog_idINTEGER NOT NULL, rhino_id INTEGER NOT NULL, deer_id INTEGER NOT NULL, sound_id INTEGER, tiger_id INTEGER, leg_lengthVARCHAR(255), jump_distance VARCHAR(lOO), PRIMARY KEY (frog_id)); ALTER TABLE frogs ADD FOREIGN KEY (warthog_id) REFERENCES warthogs (warthog_id), ALTER TABLE frogs ADD FOREIGN KEY (rhino_id) REFERENCES rhinos (rhino_id); ALTER TABLE frogs ADD FOREIGN KEY (deer id)REFERENCES deers (deer_id) ALTER TABLE frogs ADD FOREIGN KEY (sound_id) REFERENCES sounds (sound id); ALTER TABLE frogs ADD FOREIGN KEY (tiger_id) REFERENCES tigers (tiger_id); CREATE UNIQUE INDEX frg_frg_id_unq_idx ON frogs (frog_id); CREATE UNIQUE INDEX frg_w_r_d_t_unq_idx ON frogs (warthog_id, rhino_id, deer_id, tiger_id); CREATE INDEX frg_whg_id_idx ON frogs (warthog_id); CREATE INDEX frg rhn_id_idx ON frogs (rhino_id); CREATE INDEX frg_der_id_idx ON frogs (deer_id); CREATE INDEX frg_snd_id_idx ON frogs (sound_id); CREATE INDEX frg_tgr_id_idx ON frogs (tiger_id); CREATE TABLE lions ( lion_id INTEGER NOT NULL, deer_id INTEGER, PRIMARY KEY (lion_id)); CREATE UNIQUE INDEX lin_lin_id_unq_idx ON lions (lion_id); CREATE TABLE frogs_lions ( frog_id INTEGER NOT NULL, lion_id INTEGER NOT NULL, PRIMARY KEY (frog_id, lion_id)); ALTER TABLE frogs_lions ADD FOREIGN KEY (lion_id) REFERENCES lions (lion_id); ALTER TABLE frogs_lions ADD FOREIGN KEY (frog id) REFERENCES frogs (frog_id); CREATE UNIQUE INDEX frg_lin_frg_id_lin_id_unq_idx ON frogs_lions (frog_id, lion_id); CREATE INDEX frg_lin_lin_id_idx ON frogs_lions (lion_id); CREATE INDEX frg_lin_frg_id_idx ON fro
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
Re: [PERFORM] Hardware suggestions for Linux/PGSQL server
I don't know what your budget is, but there are now 10k RPM SATA 150 drives on the market. Their price/performance is impressive. You may want to consider going with a bunch of these instead of SCSI disks (more spindles vs. faster spindles). 3ware makes a hardware raid card that can drive up to 12 SATA disks. I have been told by a few people who have used it that the linux driver is very solid. Drew Jeff Bohmer wrote: Just one more piece of advice, you might want to look into a good battery backed cache hardware RAID controller. They work quite well for heavily updated databases. The more drives you throw at the RAID array the faster it will be. I've seen this list often recommended such a setup. We'll probably get battery-backed write cache and start out with a 4 disk RAID 10 array. Then add more disks and change RAID 5 if more read performance is needed. Thanks, - Jeff ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Hardware suggestions for Linux/PGSQL server
In the last exciting episode, [EMAIL PROTECTED] ("Andrew G. Hammond") wrote:
> I don't know what your budget is, but there are now 10k RPM SATA 150
> drives on the market. Their price/performance is impressive. You may
> want to consider going with a bunch of these instead of SCSI disks
> (more spindles vs. faster spindles). 3ware makes a hardware raid
> card that can drive up to 12 SATA disks. I have been told by a few
> people who have used it that the linux driver is very solid.
We got a couple of those in for testing purposes; when opportunity
presents itself, I'll have to check to see if they are any more honest
about commits than traditional IDE drives.
If they still "lie" the same way IDE drives do, it is entirely
possible that they are NOT nearly as impressive as you presently
imagine. It's not much good if they're "way fast" if you can't trust
them to actually store data when they claim it is stored...
--
(reverse (concatenate 'string "gro.gultn" "@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/lisp.html
"Much of this software was user-friendly, meaning that it was intended
for users who did not know anything about computers, and furthermore
had absolutely no intention whatsoever of learning."
-- A. S. Tanenbaum, "Modern Operating Systems, ch 1.2.4"
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
