Re: [PERFORM] TPCH 100GB - need some help
Mr. Tom Lane --- Tom Lane [EMAIL PROTECTED] wrote: Eduardo Almeida [EMAIL PROTECTED] writes: I need some help in a TPCH 100GB benchmark. Here I put the query #19, the explain and the top for it. IIRC, this is one of the cases that inspired the work that's been done on the query optimizer for 7.5. I don't think you will be able to get 7.4 to generate a good plan for it (at least not without changing the query, which is against the TPC rules). How do you feel about running CVS tip? We are testing the postgre 7.4.2 to show results to some projects here in Brazil. We are near the deadline for these projects and we need to show results with a stable version. ASAP I want and I will help the PG community testing the CVS with VLDB. BTW, are you aware that OSDL has already done a good deal of work with running TPC benchmarks for Postgres (and some other OS databases)? No! Now I'm considering the use of OSDL because of query rewrite. Yesterday the query #19 that I describe runs in the OSDL way. We found some interesting patterns in queries that take to long to finish in the 100 GB test. Sub-queries inside other sub-queries (Q20 and Q22); Exists and Not exists selection (Q4, Q21 and Q22); Aggregations with in-line views, that is queries inside FROM clause (Q7, Q8, Q9 and Q22); In fact these queries were aborted by timeout statement_timeout = 2500 I took off the timeout to Q20 and it finished in 23:53:49 hs. tks a lot, Eduardo ps. sorry about my english regards, tom lane __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Interpreting vmstat
Hello, (note best viewed in fixed-width font) I'm still trying to find where my performance bottle neck is... I have 4G ram, PG 7.3.4 shared_buffers = 75000 effective_cache_size = 75000 Run a query I've been having trouble with and watch the output of vmstat (linux): $ vmstat 1 procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 0 0 0148 8732 193652 2786668 0 0 0 0 292 151 0 2 98 2 0 2148 7040 193652 2786668 0 0 0 208 459 697 45 10 45 0 0 0148 9028 193652 2786684 0 016 644 318 613 25 4 71 1 0 0148 5092 193676 2780196 0 012 184 441 491 37 5 58 0 1 0148 5212 193684 2772512 0 0 112 9740 682 1063 45 12 43 1 0 0148 5444 193684 2771584 0 0 120 4216 464 1303 44 3 52 1 0 0148 12232 193660 2771620 0 0 244 628 340 681 43 20 38 1 0 0148 12168 193664 2771832 0 0 196 552 332 956 42 2 56 1 0 0148 12080 193664 2772248 0 0 272 204 371 201 40 1 59 1 1 0148 12024 193664 2772624 0 0 368 0 259 127 42 3 55 Thats the first 10 lines or so... the query takes 60 seconds to run. I'm confused on the bo bi parts of the io: IO bi: Blocks sent to a block device (blocks/s). bo: Blocks received from a block device (blocks/s). yet it seems to be opposite of that... bi only increases when doing a largish query, while bo also goes up, I typically see periodic bo numbers in the low 100's, which I'd guess are log writes. I would think that my entire DB should end up cached since a raw pg_dump file is about 1G in size, yet my performance doesn't indicate that that is the case... running the same query a few minutes later, I'm not seeing a significant performance improvement. Here's a sample from iostat while the query is running: $ iostat -x -d 1 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda0.00 0.00 0.00 0.000.000.00 0.00 42949552.960.00 0.00 100.00 sda1 0.00 0.00 0.00 0.000.000.00 0.00 42949662.960.00 0.00 100.00 sda2 0.00 0.00 0.00 0.000.000.00 0.00 42949642.960.00 0.00 100.00 sdb0.00 428.00 0.00 116.000.00 4368.0037.66 2844.40 296.55 86.21 100.00 sdb1 0.00 428.00 0.00 116.000.00 4368.0037.66 6874.40 296.55 86.21 100.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda0.00 0.00 0.00 0.000.000.00 0.00 42949552.960.00 0.00 100.00 sda1 0.00 0.00 0.00 0.000.000.00 0.00 42949662.960.00 0.00 100.00 sda2 0.00 0.00 0.00 0.000.000.00 0.00 42949642.960.00 0.00 100.00 sdb4.00 182.00 6.00 77.00 80.00 2072.0025.93 2814.50 54.22 120.48 100.00 sdb1 4.00 182.00 6.00 77.00 80.00 2072.0025.93 6844.50 54.22 120.48 100.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda0.00 0.00 0.00 0.000.000.00 0.00 42949552.960.00 0.00 100.00 sda1 0.00 0.00 0.00 0.000.000.00 0.00 42949662.960.00 0.00 100.00 sda2 0.00 0.00 0.00 0.000.000.00 0.00 42949642.960.00 0.00 100.00 sdb0.00 43.00 0.00 11.000.00 432.0039.27 2810.40 36.36 909.09 100.00 sdb1 0.00 43.00 0.00 11.000.00 432.0039.27 6840.40 36.36 909.09 100.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda0.00 15.84 0.00 17.820.00 269.3115.11 42524309.47 44.44 561.11 100.00 sda1 0.00 15.84 0.00 17.820.00 269.3115.11 42524419.47 44.44 561.11 100.00 sda2 0.00 0.00 0.00 0.000.000.00 0.00 42524398.670.00 0.00 100.00 sdb0.99 222.77 0.99 114.85 15.84 2700.9923.45 2814.16 35.90 86.32 100.00 sdb1 0.99 222.77 0.99 114.85 15.84 2700.9923.45 6844.16 35.90 86.32 100.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda0.00 0.00 0.00 0.000.000.00 0.00 42949551.760.00 0.00 101.00 sda1 0.00 0.00 0.00 0.000.000.00 0.00 42949662.860.00 0.00 101.00 sda2 0.00 0.00 0.00 0.000.000.00 0.00 42949642.660.00 0.00 101.00 sdb1.00 91.00 1.00 28.00 16.00 960.0033.66 2838.40 10.34 348.28 101.00 sdb1 1.00 91.00 1.00 28.00 16.00 960.0033.66 6908.70 10.34 348.28 101.00 The DB files and logs are on sdb1. Can someone point me in the direction of some documentation on how to interpret these numbers? Also, I've tried to figure out
[PERFORM] where to find out when a table was last analyzed?
All, Does PG store when a table was last analyzed? Thanks, __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Using LIKE expression problem..
Use the text_pattern_ops operator when creating the index, see: http://www.postgresql.org/docs/7.4/static/indexes-opclass.html Michael Ryan S. Puncia wrote: Sorry .. I am a newbie and I don't know :( How can I know that I am in C locale ? How can I change my database to use C locale? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christopher Kings-Lynne Sent: Wednesday, May 12, 2004 3:59 PM To: Michael Ryan S. Puncia Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Using LIKE expression problem.. Are you in a non-C locale? Chris Michael Ryan S. Puncia wrote: Yes , I already do that but the same result .. LIKE uses seq scan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Christopher Kings-Lynne Sent: Wednesday, May 12, 2004 2:48 PM To: Michael Ryan S. Puncia Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Using LIKE expression problem.. In the query plan ..it uses seq scan rather than index scan .. why ? I have index on lastname, firtname. Have you run VACUUM ANALYZE; on the table recently? Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Quad processor options
After reading the replies to this, it is clear that this is a Lintel-centric question, but I will throw in my experience. I am curious if there are any real life production quad processor setups running postgresql out there. Yes. We are running a 24/7 operation on a quad CPU Sun V880. Since postgresql lacks a proper replication/cluster solution, we have to buy a bigger machine. This was a compelling reason for us to stick with SPARC and avoid Intel/AMD when picking a DB server. We moved off of an IBM mainframe in 1993 to Sun gear and never looked back. We can upgrade to our heart's content with minimal disruption and are only on our third box in 11 years with plenty of life left in our current one. Right now we are running on a dual 2.4 Xeon, 3 GB Ram and U160 SCSI hardware-raid 10. A couple people mentioned hardware RAID, which I completely agree with. I prefer an external box with a SCSI or FC connector. There are no driver issues that way. We boot from our arrays. The Nexsan ATABoy2 is a nice blend of performance, reliability and cost. Some of these with 1TB and 2TB of space were recently spotted on ebay for under $5k. We run a VERY random i/o mix on ours and it will consistently sustain 15 MB/s in blended read and write i/o, sustaining well over 1200 io/s. These are IDE drives, so they fail more often than SCSI, so run RAID1 or RAID5. The cache on these pretty much eliminates the RAID5 penalties. The 30k+ setups from Dell etc. don't fit our budget. For that kind of money you could get a lower end Sun box (or IBM RS/6000 I would imagine) and give yourself an astounding amount of headroom for future growth. Sincerely, Marty ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
