Re: Performance problem... - Mailing list pgsql-admin
From | Scott Marlowe |
---|---|
Subject | Re: Performance problem... |
Date | |
Msg-id | 1110906024.28555.146.camel@state.g2switchworks.com Whole thread Raw |
In response to | Re: Performance problem... (Marcin Giedz <marcin.giedz@eulerhermes.pl>) |
Responses |
Re: Performance problem...
(Marcin Giedz <marcin.giedz@eulerhermes.pl>)
|
List | pgsql-admin |
On Tue, 2005-03-15 at 10:17, Marcin Giedz wrote: > Dnia wtorek, 15 marca 2005 17:08, Scott Marlowe napisał: > > On Tue, 2005-03-15 at 02:59, Marcin Giedz wrote: > > > Dnia poniedziałek, 14 marca 2005 19:32, Scott Marlowe napisał: > > > > On Mon, 2005-03-14 at 12:03, Marcin Giedz wrote: > > > > > Hello... > > > > > > > > > > > > > > > Our company is going to change SQL engine from MySQL to PSQL. Of > > > > > course some performance problems occured. Our server is Dual Xeon > > > > > 3.0GHz + 8GB RAM + RAID1(software - two 146GB SCSI 15k) for sql data > > > > > + RAID1(software - two 146GB SCSI 15k) for pg_xlog. Postgres.conf > > > > > parameters are as follows: > > > > > > > > > > max_connections = 150 > > > > > shared_buffers = 50000 # min 16, at least max_connections*2, > > > > > 8KB each work_mem = 2048 # min 64, size in KB > > > > > > > > 50,000 shared buffers may or may not be too much. Try it at different > > > > sizes from 5,000 or so up to 50,000 and find the "knee". It's usually > > > > closer to 10,000 than 50,000, but ymmv... > > > > > > Playing with shared_buffers from 10000 to 50000 doesn't change anything > > > in total time for this query :( But when I change work_mem a little > > > higher to 10000 total runtime decreases a little about 10% but when I > > > change random_page_cost to 0.2 (I know that almost all papers say it > > > should be higher then 1.0) total runtime decreases almost 3 times and > > > lasts about 900ms - earlier with random_page_cost=1.2 it took 2.7s. Is it > > > possible to have random_page_cost on this value? > > > > IF random_page_cost needs to be that low, then it's likely that the > > query planner is either getting bad statistics and making a poor > > decision, or that you've got a corner case that it just can't figure > > out. What does explain analyze <yourqueryhere> say with > > random_page_cost set to 1.2 and 0.2? HAve you run analyze and vacuumed > > full lately? > It cann't be possible - I've run vacuum full analyze - it didn't change > anything ;) > You might want to try adjusting these values to see if you can get the query planner to choose the faster plan without dropping random_page_cost to 0.2. I.e. give the query planner candy and flowers, don't just bonk it on the head with a big stick and drag it back home... #cpu_tuple_cost = 0.01 # (same) #cpu_index_tuple_cost = 0.001 # (same) #cpu_operator_cost = 0.0025 # (same) Does explain analyze show a big difference in expected an actual rows returned for any of the parts of the query plan?
pgsql-admin by date: