lars <lhofhansl(at)yahoo(dot)com> wrote:
> On 07/13/2011 07:46 AM, Kevin Grittner wrote:
>>
>> I've mentioned this in a hand-wavy general sense, but I should
>> have mentioned specifics ages ago: for a database where the
>> active portion of the database is fully cached, it is best to set
>> seq_page_cost and random_page_cost to the same value, somewhere
>> in the 0.1 to 0.05 range. (In your case I would use 0.05.) In
>> highly cached databases I have sometimes also found it necessary
>> to increase cpu_tuple_cost. (In your case I might try 0.02.)
>>
> I've been doing that for other tests already (I didn't want to add
> too many variations here).
> The Bitmap Heap scans through the table are only useful for
> spinning media and not the cache (just to state the obvious).
>
> As an aside: I found that queries in a cold database take almost
> twice as long when I make that change,
> so for spinning media this is very important.
No doubt. We normally run months to years between reboots, with
most of our cache at the OS level. We don't have much reason to
ever restart PostgreSQL except to install new versions. So we don't
worry overly much about the cold cache scenario.
>> Which raises an interesting question -- what happens to the
>> timings if your SELECTs are done with synchronous_commit = off?
>
> Just tried that...
> In that case the WAL is still written (as seen via iostat), but
> not synchronously by the transaction (as seen by strace).
So transactions without an XID *are* sensitive to
synchronous_commit. That's likely a useful clue.
How much did it help the run time of the SELECT which followed the
UPDATE?
-Kevin