On Wed, Jan 19, 2011 at 8:56 AM, Merlin Moncure <mmonc...@gmail.com> wrote: > Ah, I tested your patch vs stock postgres vs my patch, basically your > results are unhappily correct (mine was just a hair faster than yours > which you'd expect). The differential was even wider on my laptop > class hardware, maybe 26%. I also agree that even if the penalty was > reduced or determined to be worth it anyways, your approach to move > the setting/i/o around to appropriate places is the way to go vs > wholesale removal, unless some way is found to reduce clog lookup > penalty to a fraction of what it is now (not likely, I didn't profile > but I bet a lot of the problem is the lw lock). Interesting I didn't > notice this on my original test :(.
OK. My apologies for the email yesterday in which I forgot that you actually HAD posted a patch, but thanks for testing mine and posting your results (and thanks also to Andrea for pointing out the oversight to me). Here's a new version of the patch based on some experimentation with ideas I posted yesterday. At least on my Mac laptop, this is pretty effective at blunting the response time spike for the first table scan, and it converges to steady-state after about 20 tables scans. Rather than write every 20th page, what I've done here is make every 2000'th buffer allocation grant an allowance of 100 "hint bit only" writes. All dirty pages and the next 100 pages that are dirty-only-for-hint-bits get written out. Then we stop writing the dirty-only-for-hint-bits-pages until we get our next allowance of writes. The idea is to try to avoid creating a lot of random writes on each scan through the table. At least here, that seems to work pretty well - the initial scan is only about 25% slower than the steady state (rather than 6x or more slower). I am seeing occasional latency spikes that appear to be the result of the OS write cache filling up and deciding that it has to flush everything to disk before writing anything more. I'm not too concerned about that because this is a fairly artificial test case (one doesn't usually sit around doing consecutive SELECT sum(1) FROM s commands) but it seems like pretty odd behavior. The system sits there doing no writes at all as I'm sending more and more dirty pages into the system buffer cache and then, boom, write storm. I haven't yet tested to see if the same behavior occurs on Linux. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
bm-hint-bits-v2.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers