Re: [PERFORM] failures on machines using jfs
Andrew, None of this is to say that jfs is in fact to blame, nor even that, if it is, it does not have something to do with the age of our installations, c. (these are all RH 8). In fact, I suspect hardware in both cases. But I thought I'd mention it just in case other people are seeing strange behaviour, on the principle of better safe than sorry. Always useful.Actually, I just fielded on IRC a report of poor I/O utilization with XFS during checkpointing.Not sure if the problem is XFS or PostgreSQL, but the fact that XFS (alone among filesystems) does its own cache management instead of using the kernel cache makes me suspicious. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] optimizing Postgres queries
... wow: executing a batch file with about 4250 selects, including lots of joins other things PostgreSQL 7.4 is about 2 times faster than FrontBase 3.6.27. OK, we will start to make larger tests but this is quite interesting already: we did not optimize a lot, just invoked VACUUM ANALYZE and then the selects ;-) Thanks to all who answered to this thread. cheers David ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PERFORM] Slow query problem
Hi, We've set up a little test box (1GHz Athlon, 40G IDE drive, 256M RAM, Redhat 9) to do some basic comparisons between postgresql and firebird 1.0.3 and 1.5rc8. Mostly the results are comparable, with one significant exception. QUERY select invheadref, invprodref, sum(units) from invtran group by invheadref, invprodref RESULTS pg 7.3.4 - 5.5 min pg 7.4.0 - 10 min fb 1.0.3 - 64 sec fb 1.5 - 44 sec * The invtran table has about 2.5 million records, invheadref and invprodref are both char(10) and indexed. * shared_buffers = 12000 and sort_mem = 8192 are the only changes I've made to postgresql.conf, with relevant changes to shmall and shmmax. This is an explain analyse plan from postgresql 7.4: QUERY PLAN GroupAggregate (cost=572484.23..601701.15 rows=1614140 width=39) (actual time=500091.171..554203.189 rows=147621 loops=1) - Sort (cost=572484.23..578779.62 rows=2518157 width=39) (actual time=500090.939..527500.940 rows=2521530 loops=1) Sort Key: invheadref, invprodref - Seq Scan on invtran (cost=0.00..112014.57 rows=2518157 width=39) (actual time=16.002..25516.917 rows=2521530 loops=1) Total runtime: 554826.827 ms (5 rows) Am I correct in interpreting that most time was spent doing the sorting? Explain confuses the heck out of me and any help on how I could make this run faster would be gratefully received. Cheers, Bradley. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] optimizing Postgres queries
David Teran [EMAIL PROTECTED] writes: Much better. So i think i will first read more about this optimization stuff and regular maintenance things. See http://www.postgresql.org/docs/7.4/static/maintenance.html Is there any hint where to start to understand more about this optimization problem? http://www.postgresql.org/docs/7.4/static/performance-tips.html regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Slow query problem
On Thu, 08 Jan 2004 16:52:05 +1100 Bradley Tate [EMAIL PROTECTED] wrote: Am I correct in interpreting that most time was spent doing the sorting? looks so. your table is about 70MB total size, and its getting loaded completely into memory (you have 12000 * 8k = 96M available). 26s to load 70MB from disk seems reasonable. The rest of the time is used for sorting. Explain confuses the heck out of me and any help on how I could make this run faster would be gratefully received. You should bump sort_mem as high as you can stand. with only 8MB sort memory available, you're swapping intermediate sort pages to disk -- a lot. Try the query with sort_mem set to 75MB (to do the entire sort in memory). -mike Cheers, Bradley. ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Mike Glover Key ID BFD19F2C [EMAIL PROTECTED] pgp0.pgp Description: PGP signature
[PERFORM] PostgreSQL vs. Oracle disk space usage
Hi, I searched through the archive and could not find any conclusive discussion of results on this. Has anyone compared the disk space usage between PostgreSQL and Oracle ? I am interested in knowing for the same tuple (i.e same dictionary), the disk usage between the two. Thanks. Gan -- ++ | Seum-Lim GAN email : [EMAIL PROTECTED] | | Lucent Technologies| | 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 | | Naperville, IL 60566, USA.fax : (630)-713-7272 | | web : http://inuweb.ih.lucent.com/~slgan | ++ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Slow query problem
On Thu, Jan 08, 2004 at 19:27:16 -0800, Mike Glover [EMAIL PROTECTED] wrote: You should bump sort_mem as high as you can stand. with only 8MB sort memory available, you're swapping intermediate sort pages to disk -- a lot. Try the query with sort_mem set to 75MB (to do the entire sort in memory). Postgres also might be able to switch to a hash aggregate instead of using a sort if sortmem is made large enough to hold the results for all of the (estimated) groups. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Slow query problem
Mike Glover [EMAIL PROTECTED] writes: You should bump sort_mem as high as you can stand. with only 8MB sort memory available, you're swapping intermediate sort pages to disk -- a lot. Try the query with sort_mem set to 75MB (to do the entire sort in memory). 7.4 will probably flip over to a hash-based aggregation method, and not sort at all, once you make sort_mem large enough that it thinks the hash table will fit in sort_mem. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
