From: Ogden <lists@darkstatic.com>
To: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance
Date: Tue, 12 Apr 2011 16:19:32 -0500
Message-ID: <B93DFBBB-DA56-4044-A508-0B7E4A2CFD28@darkstatic.com> (raw)
In-Reply-To: <4DA4BFA2.5060601@fuzzy.cz>
References: <DF2D5436-117C-4D02-9B3C-A55723B7DDE1@darkstatic.com>
<20110412171855.GA14292@tux>
<FC3A3A2B-3ECB-41BA-8F94-356D6FED3695@darkstatic.com>
<4DA496FA.3070908@fuzzy.cz>
<8F22D592-23C1-4A3C-94A5-48363332ADD3@darkstatic.com>
<4DA4BFA2.5060601@fuzzy.cz>
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@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?
>>>
>>>
>>
>> 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
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: lists@darkstatic.com, tv@fuzzy.cz
Subject: Re: Performance
In-Reply-To: <B93DFBBB-DA56-4044-A508-0B7E4A2CFD28@darkstatic.com>
* 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