On Apr 12, 2011, at 4:09 PM, Tomas Vondra wrote: > Dne 12.4.2011 20:28, Ogden napsal(a): >> >> On Apr 12, 2011, at 1:16 PM, Tomas Vondra wrote: >> >>> Dne 12.4.2011 19:23, Ogden napsal(a): >>>> >>>> On Apr 12, 2011, at 12:18 PM, Andreas Kretschmer wrote: >>>> >>>>> Ogden <lists@xxxxxxxxxxxxxx> wrote: >>>>> >>>>>> I have been wrestling with the configuration of the dedicated Postges 9.0.3 >>>>>> server at work and granted, there's more activity on the production server, but >>>>>> the same queries take twice as long on the beefier server than my mac at home. >>>>>> I have pasted what I have changed in postgresql.conf - I am wondering if >>>>>> there's any way one can help me change things around to be more efficient. >>>>>> >>>>>> Dedicated PostgreSQL 9.0.3 Server with 16GB Ram >>>>>> >>>>>> Heavy write and read (for reporting and calculations) server. >>>>>> >>>>>> max_connections = 350 >>>>>> shared_buffers = 4096MB >>>>>> work_mem = 32MB >>>>>> maintenance_work_mem = 512MB >>>>> >>>>> That's okay. >>>>> >>>>> >>>>>> >>>>>> >>>>>> seq_page_cost = 0.02 # measured on an arbitrary scale >>>>>> random_page_cost = 0.03 >>>>> >>>>> Do you have super, Super, SUPER fast disks? I think, this (seq_page_cost >>>>> and random_page_cost) are completly wrong. >>>>> >>>> >>>> No, I don't have super fast disks. Just the 15K SCSI over RAID. I >>>> find by raising them to: >>>> >>>> seq_page_cost = 1.0 >>>> random_page_cost = 3.0 >>>> cpu_tuple_cost = 0.3 >>>> #cpu_index_tuple_cost = 0.005 # same scale as above - 0.005 >>>> #cpu_operator_cost = 0.0025 # same scale as above >>>> effective_cache_size = 8192MB >>>> >>>> That this is better, some queries run much faster. Is this better? >>> >>> I guess it is. What really matters with those cost variables is the >>> relative scale - the original values >>> >>> seq_page_cost = 0.02 >>> random_page_cost = 0.03 >>> cpu_tuple_cost = 0.02 >>> >>> suggest that the random reads are almost as expensive as sequential >>> reads (which usually is not true - the random reads are significantly >>> more expensive), and that processing each row is about as expensive as >>> reading the page from disk (again, reading data from disk is much more >>> expensive than processing them). >>> >>> So yes, the current values are much more likely to give good results. >>> >>> You've mentioned those values were recommended on this list - can you >>> point out the actual discussion? >>> >>> >> >> Thank you for your reply. >> >> http://archives.postgresql.org/pgsql-performance/2010-09/msg00169.php is how I first played with those values... >> > > OK, what JD said there generally makes sense, although those values are > a bit extreme - in most cases it's recommended to leave seq_page_cost=1 > and decrease the random_page_cost (to 2, the dafault value is 4). That > usually pushes the planner towards index scans. > > I'm not saying those small values (0.02 etc.) are bad, but I guess the > effect is about the same and it changes the impact of the other cost > variables (cpu_tuple_cost, etc.) > > I see there is 16GB of RAM but shared_buffers are just 4GB. So there's > nothing else running and the rest of the RAM is used for pagecache? I've > noticed the previous discussion mentions there are 8GB of RAM and the DB > size is 7GB (so it might fit into memory). Is this still the case? > > regards > Tomas Thomas, By decreasing random_page_cost to 2 (instead of 4), there is a slight performance decrease as opposed to leaving it just at 4. For example, if I set it 3 (or 4), a query may take 0.057 seconds. The same query takes 0.144s when I set random_page_cost to 2. Should I keep it at 3 (or 4) as I have done now? Yes there is 16GB of RAM but the database is much bigger than that. Should I increase shared_buffers? Thank you so very much Ogden -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
