Re: [PERFORM] [GENERAL] Tuning/performance question.
On Sunday 28 September 2003 09:19, David Griffiths wrote: > No difference. Note that all the keys that are used in the joins are > numeric(10)'s, so there shouldn't be any cast-issues. Can you make them bigint and see? It might make some difference perhaps. Checking the plan in the meantime.. BTW what tuning you did to postgresql? Check http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html, assuming you haven't seen earlier.. HTH Shridhar ---(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] advice on raid controller
As others have mentioned, you really ought to get battery-backed cache if you're doing any volume of writes. The ability to do safe write-back caching makes an *insane* difference to write performance. The site you link to also has that for only 15% more money: http://uk.azzurri.com/product/product.cgi?productId=80 No experience with the card(s) I'm afraid. In general though, U320 will only be faster than U160 for large sequential reads, or when you have silly numbers of disks on a channel (i.e. more than 4/channel). If you have silly numbers of disks, then RAID5 will probably be better, if you have 4 disks total then RAID1+0 will probably be better. In between it depends on all sorts of other factors. Bear in mind though that if you *do* have silly numbers of disks then more channels and more cache will count for more than anything else, so spend the money on that rather than latest-and-greatest performance for a single channel. HTH Matt > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Richard > Jones > Sent: 27 September 2003 18:25 > To: [EMAIL PROTECTED] > Subject: [PERFORM] advice on raid controller > > > Hi, i'm on the verge of buying a "MegaRAID SCSI 320-2" raid controller. > I need it to build a db server using 4x ultra320 scsi disks > i'm thinking raid 1+0 but will try with raid5 too and compare > > Does anyone have any experience with this model, good or bad i'd like to > know.. thanks :) > > as seen: > http://uk.azzurri.com/product/product.cgi?productId=188 > > Regards, > Richard. > > PS: whoever mentioned starting a site with raid controller > reviews, excellent > idea - its hard to find decent info on which card to buy. > > > ---(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 > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] avoiding seqscan?
Hi!
I have a SQL statement that I cannot get to use the index. postgresql
insists on using a seqscan and performance is very poor. set enable_seqscan
= true boost performance drastically, as you can see below. Since seqscan
is not always bad, I'd rather not turn it off completely, but rather get
the planner to do the right thing here. Is there another way to do this,
apart from setting enable_seqscan=false?
Thanks,
Palle
the tables are:
person with about 3 tuples
dyn_field_content_person, 331156 tuples
dyn_field_person, just 15 tuples
course about 700 tuples
partitcipant with ~ 7800 tuples, where ~ 60 have course_id=707...
uu=# explain analyze
uu-# select lower(substr(p.last_name,1,1)) as letter, count(*)
uu-# FROM course c join group_data gd on (c.active_group_id =
gd.this_group_id)
uu-# join person p on (gd.item_text = p.userid)
uu-# join dyn_field_person dfp on (dfp.extern_item_id = 10 and
dfp.giver=c.giver)
uu-# join dyn_field_content_person dfcp on (dfp.id =
dfcp.dyn_field_id and dfcp.userid=p.userid)
uu-# left outer join participant pt on (pt.userid = p.userid and
pt.course_id = 707)
uu-# WHERE c.id = 707
uu-# group by 1
uu-# ;
QUERY PLAN
---
---
---
---
Aggregate (cost=10496.30..10498.35 rows=27 width=106) (actual
time=4166.01..4167.23 rows=19 loops=1)
-> Group (cost=10496.30..10497.67 rows=273 width=106) (actual
time=4165.92..4166.80 rows=60 loops=1)
-> Sort (cost=10496.30..10496.98 rows=273 width=106) (actual
time=4165.91..4166.10 rows=60 loops=1)
Sort Key: lower(substr(p.last_name, 1, 1))
-> Merge Join (cost=10443.75..10485.23 rows=273 width=106)
(actual time=4094.42..4165.20 rows=60 loops=1)
Merge Cond: ("outer".userid = "inner".userid)
Join Filter: ("inner".course_id = 707)
-> Sort (cost=9803.86..9804.54 rows=273 width=88)
(actual time=3823.78..3823.97 rows=60 loops=1)
Sort Key: dfcp.userid
-> Hash Join (cost=2444.22..9792.79 rows=273
width=88) (actual time=1140.50..3822.60 rows=60 loops=1)
Hash Cond: ("outer".userid =
"inner".item_text)
Join Filter: ("inner".id =
"outer".dyn_field_id)
-> Seq Scan on dyn_field_content_person
dfcp (cost=0.00..5643.56 rows=331156 width=16) (actual time=0.01..2028.31
rows=331156 loops=1)
-> Hash (cost=2443.54..2443.54 rows=272
width=72) (actual time=340.24..340.24 rows=0 loops=1)
-> Nested Loop
(cost=1401.84..2443.54 rows=272 width=72) (actual time=338.76..339.91
rows=60 loops=1)
Join Filter: ("outer".giver =
"inner".giver)
-> Seq Scan on
dyn_field_person dfp (cost=0.00..1.19 rows=1 width=16) (actual
time=0.06..0.09 rows=1 loops=1)
Filter: (extern_item_id
= 10)
-> Materialize
(cost=2437.67..2437.67 rows=374 width=56) (actual time=338.64..338.82
rows=60 loops=1)
-> Hash Join
(cost=1401.84..2437.67 rows=374 width=56) (actual time=7.74..338.36 rows=60
loops=1)
Hash Cond:
("outer".userid = "inner".item_text)
-> Seq Scan on
person p (cost=0.00..806.09 rows=30009 width=23) (actual time=0.01..203.67
rows=30009 loops=1)
-> Hash
(cost=1400.89..1400.89 rows=378 width=33) (actual time=1.60..1.60 rows=0
loops=1)
-> Nested
Loop (cost=0.00..1400.89 rows=378 width=33) (actual time=0.12..1.28
rows=60 loops=1)
->
Index Scan using course_pkey on course c (cost=0.00..5.08 rows=1 width=16)
(actual time=0.06..0.06 rows=1 loops=1)
Index Cond: (id = 707)
->
Index Scan using group_data_this_idx on group_data gd (cost=0.00..1390.80
rows=402 width=17) (actual time=0.04..0.6
6 rows=60 loops=1)
Index Cond: ("outer".active_group_id = gd.this_group_id)
-> Sort (cost=639.90..659.42 rows=7808 width=18)
(actual time=266.55..290.81 rows=7722 loops=1)
Sort Key: pt.userid
-> Seq Scan on participant pt
(cost=0.00..135.08 rows=7808 width=18) (actual time=0.02..50.24 rows=7808
loops=1)
Total runtime: 4170.16 msec
(32 rader)
Tid: 4
Re: [PERFORM] avoiding seqscan?
Palle, > I have a SQL statement that I cannot get to use the index. postgresql > insists on using a seqscan and performance is very poor. set enable_seqscan > = true boost performance drastically, as you can see below. Since seqscan > is not always bad, I'd rather not turn it off completely, but rather get > the planner to do the right thing here. Is there another way to do this, > apart from setting enable_seqscan=false? In your postgresql.conf, try setting effective_cache_size to something like 50% of your system's RAM, and lovering random_page_cost to 2.0 or even 1.5. Then restart PostgreSQL and try your query again. What version, btw? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] avoiding seqscan?
Hi, Indeed, setting random_page_cost does the trick. Thanks! It seems to make sense to set random_page_cost to this value. Are there any drawbacks? postgresql-7.3.4 postgresql.conf: tcpip_socket = true max_connections = 100 superuser_reserved_connections = 2 # Performance # shared_buffers = 12000 sort_mem = 8192 vacuum_mem = 32768 effective_cache_size = 64000 random_page_cost = 2 ... --On söndag, september 28, 2003 14.34.25 -0700 Josh Berkus <[EMAIL PROTECTED]> wrote: Palle, I have a SQL statement that I cannot get to use the index. postgresql insists on using a seqscan and performance is very poor. set enable_seqscan = true boost performance drastically, as you can see below. Since seqscan is not always bad, I'd rather not turn it off completely, but rather get the planner to do the right thing here. Is there another way to do this, apart from setting enable_seqscan=false? In your postgresql.conf, try setting effective_cache_size to something like 50% of your system's RAM, and lovering random_page_cost to 2.0 or even 1.5. Then restart PostgreSQL and try your query again. What version, btw? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(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] avoiding seqscan?
Palle, > Indeed, setting random_page_cost does the trick. Thanks! > > It seems to make sense to set random_page_cost to this value. Are there any > drawbacks? Only if your server was heavily multi-tasking, and as a result had little RAM+CPU available. Then you'd want to raise the value again. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
