Jack, > Right, because re-architecture of a cross-platform query makes sense if > performance is bad on all systems, but is questionable activity when > performance is fine on some systems and lousy on others. Hence my > statement that while SQL optimization is certainly something we want to > do for across-the-board performance increase, I wanted to focus on other > issues for troubleshooting this problem. I will be back to ask about > data access models later :-)
Yes, but an EXPLAIN ANALYZE will also help show issues like sorts running out of memory, etc. Really, we don't currently have enough information to do more than speculate; it's like trying to repair a car engine wearing a blindfold. Particularly since it's possible that there are only 1 or 2 "bad queries" which are messing everything else up. For that matter, it would really help to know: -- How many simulatneous connections are running update queries during this process? -- How about some sample VACUUM VERBOSE results for the intra-process vacuums? > I ended up going back to a default postgresql.conf and reapplying the > various tunings one-by-one. Turns out that while setting fsync = false > had little effect on the slow IDE box, it had a drastic effect on this > faster SCSI box and performance is quite acceptable now (aside from the > expected falloff of about 30% after the first twenty minutes, which I > believe comes from growing and shrinking tables without vacuumdb > --analyzing). Well, that brings 2 things immediately to mind: 1) That may improve performance, but it does mean that if your machine loses power you *will* be restoring from backup. It's risky to do. 2) Your IDE system has write-caching enabled. Once again, this is a nice performmance boost, if you don't mind database corruption in a power-out. -- Josh Berkus Aglio Database Solutions San Francisco ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
