Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id AE0969552D4 for ; Tue, 28 Feb 2012 12:05:37 -0400 (AST) Received: from elizabeth.gransy.com ([89.187.132.199]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S2PYT-000723-0a for pgsql-performance@postgresql.org; Tue, 28 Feb 2012 16:05:37 +0000 Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id BA6B515E4585; Tue, 28 Feb 2012 17:05:08 +0100 (CET) Received: from 193.179.187.70 (proxying for unknown) (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Tue, 28 Feb 2012 17:05:08 +0100 Message-ID: <7993f32e79b113a0ca3de4d9e500d106.squirrel@sq.gransy.com> In-Reply-To: References: <1330346798.90731.YahooMailNeo@web30808.mail.mud.yahoo.com> <8da9b15646d973a5ee23ba49faeee8fd.squirrel@sq.gransy.com> <347477cf700da0b19ad62b50cae5ae1c.squirrel@sq.gransy.com> Date: Tue, 28 Feb 2012 17:05:08 +0100 Subject: =?utf-8?B?UmU6IFtQRVJGT1JNXSBSZTogW1BFUkZPUk1dIFJlOiBbUEVSRk9STV0gUmU6?= =?utf-8?B?IFtQRVJGT1JNXSBSZTog5Zue5aSN77yaIFtQRVJGT1JNXSBQRyBhcyBpbi1t?= =?utf-8?B?ZW1vcnkgZGI/IEhvdyB0byB3YXJtIHVwIGFuZCByZS1wb3B1bGF0ZSBidWZm?= =?utf-8?B?ZXJzPyBIb3cgdG8gcmVhZCBpbiBhbGwgdHVwbGVzIGludG8gbWVtb3J5Pw==?= From: "Tomas Vondra" To: "Claudio Freire" Cc: "Tomas Vondra" , "pgsql-performance@postgresql.org" User-Agent: SquirrelMail/1.4.21 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 X-Priority: 3 (Normal) Importance: Normal Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201202/249 X-Sequence-Number: 46421 On 28 =C3=9Anor 2012, 15:24, Claudio Freire wrote: > On Tue, Feb 28, 2012 at 11:15 AM, Tomas Vondra wrote: >> I haven't investigated why exactly the data are not cached initially, >> but >> none of the options that I can think of could be "fixed" by setting >> "fsync=3Doff". That's something that influences writes (not read-only >> database) and I don't think it influences how buffers are evicted from >> shared buffers / page cache. >> >> It might speed up the initial load of data, but that's not what the OP >> was >> asking. > > It speeds a lot more than the initial load of data. > > Assuming the database is read-only, but not the filesystem (ie: it's > not a slave, in which case all this is moot, as you said, there are no > writes on a slave). That is, assuming this is a read-only master, then > read-only queries don't mean read-only filesystem. Bookkeeping tasks > like updating catalog dbs, statistics tables, page cleanup, stuff like > that can actually result in writes. > > Writes that go through the WAL and then the filesystem. I'm not sure what maintenance tasks you mean. Sure, there are tasks that need to be performed after the load (stats, hint bits, updating system catalogs etc.) but this may happen once right after the load and then there's effectively zero write activity. Unless the database needs to write temp files, but that contradicts the 'fits into RAM' assumption ... > With fsync=3Doff, those writes happen on the background, and are carrie= d > out by the OS. Effectively releasing postgres from having to wait on > them, and, assuming there's enough RAM, merging repeated writes to the > same sectors in one operation in the end. For stats, bookkeeping, and > who knows what else, the merging would be quite effective. With enough > RAM to hold the entire DB, the merging would effectively keep > everything in RAM (in system buffers) until there's enough I/O > bandwidth to transparently push that to persistent storage. The writes are always carried out by the OS - except when dirty_ratio is exceeded (but that's a different story) and WAL with direct I/O enabled. The best way to allow merging the writes in shared buffers or page cache is to set the checkpoint_segments / checkpoint_timeout high enough. That way the transactions won't need to wait for writes to data files (which is the part related to evictions of buffers from cache). And read-only transactions won't need to wait at all because they don't need to wait for fsync on WAL. > In essence, what was required, to keep everything in RAM for as much > as possible. > > It *does* in the same way affect buffer eviction - it makes eviction > *very* quick, and re-population equally as quick, if everything fits > into memory. No it doesn't. Only a write caused by a background process (due to full shared buffers) means immediate eviction. A simple write (caused by a checkpoint) does not evict the page from shared buffers. Not even a background writer evicts a page from shared buffers, it merely marks them as 'clean' and leaves them there. And all those writes happen on the background, so the clients don't need to wait for them to complete (excep= t for xlog checkpoints). kind regards Tomas