From: Tomas Vondra <tv@fuzzy.cz>
To: pgsql-performance@postgresql.org
Subject: Re: Performance
Date: Tue, 12 Apr 2011 20:16:26 +0200
Message-ID: <4DA496FA.3070908@fuzzy.cz> (raw)
In-Reply-To: <FC3A3A2B-3ECB-41BA-8F94-356D6FED3695@darkstatic.com>
References: <DF2D5436-117C-4D02-9B3C-A55723B7DDE1@darkstatic.com>
<20110412171855.GA14292@tux>
<FC3A3A2B-3ECB-41BA-8F94-356D6FED3695@darkstatic.com>
Dne 12.4.2011 19:23, Ogden napsal(a):
>
> On Apr 12, 2011, at 12:18 PM, Andreas Kretschmer wrote:
>
>> Ogden <lists@darkstatic.com> 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?
regards
Tomas
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tv@fuzzy.cz
Subject: Re: Performance
In-Reply-To: <4DA496FA.3070908@fuzzy.cz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox