Re: UPDATEDs slowing SELECTs in a fully cached database

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

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).

-- Lars


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux