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... Ogden -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
