On Sun, Mar 18, 2012 at 7:25 AM, Cédric Villemain <ced...@2ndquadrant.com> wrote: >> Would be nice to sort out the features of the two Postgres extentions >> pgfincore (https://github.com/klando/pgfincore ) and pg_prewarm: what >> do they have in common, what is complementary? > > pg_prewarm use postgresql functions (buffer manager) to warm data (different > kind of 'warm', see pg_prewarm code). Relations are warmed block by block, > for a range of block.
pg_prewarm actually supports three modes of prewarming: (1) pulling things into the OS cache using PostgreSQL's asynchronous prefetching code, which internally uses posix_fadvise on platforms where it's available, (2) reading the data into a fixed-size buffer a block at a time to force the OS to read it in synchronously, and (3) actually pulling the data all the way into shared buffers. So in terms of prewarming, it can do the stuff that pgfincore does, plus some extra stuff. Of course, pgfincore has a bunch of extra capabilities in related areas, like being able to check what's in core and being able to evict things from core, but those things aren't prewarming and I didn't feel any urge to include them in pg_prewarm, not because they are bad ideas but just because they weren't what I was trying to do. > pgfincore does not use the postgresql buffer manager, it uses the posix > calls. It can proceed per block or full relation. > > Both need POSIX_FADVISE compatible system to be efficient. > > The main difference between pgfincore and pg_prewarm about full relation > warm is that pgfincore will make very few system calls when pg_prewarm will > do much more. That's a fair complaint, but I'm not sure it matters in practice, because I think that in real life the time spent prewarming is going to be dominated by I/O, not system call time. Now, that's not an excuse for being less efficient, but I actually did have a reason for doing it this way, which is that it makes it work on systems that don't support POSIX_FADVISE, like Windows and MacOS X. Unless I'm mistaken or it's changed recently, pgfincore makes no effort to be cross-platform, whereas pg_prewarm should be usable anywhere that PostgreSQL is, and you'll be able to do prewarming in any of those places, though of course it may be a bit less efficient without POSIX_FADVISE, since you'll have to use the "read" or "buffer" mode rather than "prefetch". Still, being able to do it less efficiently is better than not being able to do it at all. Again, I'm not saying this to knock pgfincore: I see the advantages of its approach in exposing a whole suite of tools to people running on, well, the operating systems on which the largest number of people run PostgreSQL. But I do think that being cross-platform is an advantage, and I think it's essential for anything we'd consider shipping as a contrib module. I think you could rightly view all of this as pointing to a deficiency in the APIs exposed by core: there's no way for anything above the smgr layer to do anything with a range of blocks, which is exactly what we want to do here. But I wasn't as interested in fixing that as I was in getting something which did what I needed, which happened to be getting the entirety of a relation into shared_buffers without much ado. > The current implementation of pgfincore allows to make a snapshot and > restore via pgfincore or via pg_prewarm (just need some SQL-fu for the > later). Indeed. Just to make completely clear my position on pgfincore vs. pg_prewarm, I think they are complementary utilities with a small overlap. I think that the prewarming is important enough to a broad enough group of people that we should find some way of exposing that functionality in core or contrib, and I wrote pg_prewarm as a minimalist implementation of that concept. I am not necessarily opposed to someone taking the bull by the horns and coming up with a grander vision for what kind of tool we pull into the core distribution - either by extending pg_prewarm, recasting pgfincore as a contrib module with appropriate cross-platform sauce, or coming up with some third approach that is truly the one ring to rule them all and in the darkness bind them. At the same time, I want to get something done for 9.3 and I don't want to make it harder than it needs to be. I honestly believe that just having an easy way to pull stuff into memory/shared_buffers will give us eighty to ninety percent of what people need in this area; we can do more, either in core or elsewhere, as the motivation may strike us. Attached is an updated patch, with fixes for documentation typo noted by Jeff Janes and some addition documentation examples also inspired by comments from Jeff. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
pg_prewarm_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
