Re: [HACKERS] pg_prewarm really needs some CHECK_FOR_INTERRUPTS
On 2014-11-11 12:17:11 +0100, Andres Freund wrote: pg_prewarm() currently can't be cannot be interrupted - which seems odd given that it's intended to read large amounts of data from disk. A rather slow process. Unless somebody protests I'm going to add a check to the top of each of the three loops. Pushed to master and 9.4. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_prewarm really needs some CHECK_FOR_INTERRUPTS
Hi, pg_prewarm() currently can't be cannot be interrupted - which seems odd given that it's intended to read large amounts of data from disk. A rather slow process. Unless somebody protests I'm going to add a check to the top of each of the three loops. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm really needs some CHECK_FOR_INTERRUPTS
On Tue, Nov 11, 2014 at 8:17 PM, Andres Freund and...@2ndquadrant.com wrote: pg_prewarm() currently can't be cannot be interrupted - which seems odd given that it's intended to read large amounts of data from disk. A rather slow process. Unless somebody protests I'm going to add a check to the top of each of the three loops. Good idea, +1. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm really needs some CHECK_FOR_INTERRUPTS
On Tue, Nov 11, 2014 at 6:17 AM, Andres Freund and...@2ndquadrant.com wrote: pg_prewarm() currently can't be cannot be interrupted - which seems odd given that it's intended to read large amounts of data from disk. A rather slow process. Oops. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
On Sun, Jun 24, 2012 at 1:36 PM, Cédric Villemain ced...@2ndquadrant.comwrote: Le samedi 23 juin 2012 02:47:15, Josh Berkus a écrit : The biggest problem with pgfincore from my point of view is that it only works under Linux, whereas I use a MacOS X machine for my development, and there is also Windows to think about. Even if that were fixed, though, I feel we ought to have something in the core distribution. This patch got more +1s than 95% of what gets proposed on hackers. Fincore is only a blocker to this patch if we think pgfincore is ready to be proposed for the core distribution. Do we? I'll make it ready for. (not a huge task). Hi Cedric, Can you please post the progress on this, if any. I am planning on polishing up pg_prewarm based on the reviews. As others have said, I don't see a reason why both can't coexist, maybe in pgxn. I am all ears if you think otherwise. Best regards, -- Gurjeet Singh http://gurjeet.singh.im/
Re: [HACKERS] pg_prewarm
I hope it's not too late for another reviewer to pitch in :) I have let some time pass between previous reviews so that I can give this patch a fresh look, and not be tainted by what the other reviews said, so I may be repeating a few things already covered by other reviewers. I haven't performed any tests on this (yet) because I have seen a few other posts which show that other people have already used this utility. When I get time next, I will try to develop some useful scripts around this function to help in hibernation-like feature, and also the speeding-up of recovery when combined with xlogdump as previously suggested in this thread. This is my first review of a patch, and I just realized after finishing the review that this does not qualify as proper review as documented in Reviewing a patch wiki page. But this is an ungodly hour for me, so cannot spend more time on it right now. These are just the notes I took while doing the code review. Hope it helps in improving the patch. Applying the patch on master HEAD needs some hunk adjustments, but I didn't see anything out of place during the review. snip patching file doc/src/sgml/contrib.sgml Hunk #1 succeeded at 128 with fuzz 2 (offset 12 lines). patching file doc/src/sgml/filelist.sgml Hunk #1 succeeded at 125 (offset 1 line). /snip I think it'll be useful to provide some overloaded functions, or provide some defaults. Here's what I think are sensible defaults. Note that I have moved prefetch_type parameter from position 3 to 2; I think prefetch_type will see more variations in the field than fork (which will be 'main' most of the times). pg_prewarm(relation) defaults: type (prefetch/read), fork (main), first_block(null), last_block(null) pg_prewarm(relation, type) defaults: fork (main), first_block(null), last_block(null) pg_prewarm(relation, type, fork) defaults: first_block(null), last_block(null) pg_prewarm(relation, type, fork, first_block) defaults: last_block(null) pg_prewarm(relation, type, fork, first_block, last_block) -- already provided in the patch. Should we provide a capability to prewarm all forks of a relation by allowing a pseudo fork by the name 'all'. I don't see much use for it, but others might. We should consider making this error 'fork \%s\ does not exist for this relation' into a warning, unless we can guarantee that forks always exist for a relation; for eg. if Postgres delays creating the 'vm' fork until first vacuum on a relation, then a script that simply tries to prewarm all forks of a relation will encounter errors, which may stop the script processing altogether and lead to not prewarming the rest of the relations the user might have wanted to. Does the regclass conversion of first parameter respects the USAGE privileges on the schema? Does it need to if the user has SELECT privilege on it? Do not raise error when the provided last_block number is larger than total blocks in the relation. This may have been caused by a truncate operation since the user initiated the function. Just raise a warning and use total blocks as last_block. Make this error prefetch is not supported by this build into a warning. This will let the scripts developed on one build at least complete successfully on a different build. Check for last_block 0. Better yet, raise an error if last_block first_block. In PREWARM_BUFFER case, raise a warning and load only (end_buffer - begin_buffer) number of buffers if ((end_buffer - begin_buffer) shared_buffers). This will help prewarming complete quicker if the relation is too big for the shared_buffers to accommodate, and also let the user know that she needs to tweak the prewarming method. It may help to perform PREWARM_READ on the rest of the buffers. In the docs, where it says so it is possible + for other system activity may evict the newly prewarmed the word 'for' seems out of place. Replace it with 'that'. Best regards, On Sat, Jul 14, 2012 at 5:33 AM, Cédric Villemain ced...@2ndquadrant.comwrote: c) isn't necessarily safe in production (I've crashed Linux with Fincore in the recent past). fincore is another soft, please provide a bugreport if you hit issue with pgfincore, I then be able to fix it and all can benefit. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- Gurjeet Singh
Re: [HACKERS] pg_prewarm
c) isn't necessarily safe in production (I've crashed Linux with Fincore in the recent past). fincore is another soft, please provide a bugreport if you hit issue with pgfincore, I then be able to fix it and all can benefit. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] pg_prewarm
Jeff Janes jeff.ja...@gmail.com writes: I think we want this. There is some discussion about how much overlap it has with pgfincore, but I don't think there is an active proposal to put that into contrib, so don't see that as blocking this. It is my understanding that Cédric wants to propose a patch for pgfincore as a contrib module in next Commit Fest, and has already been working on some necessary cleaning to see that happen. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
On 7/10/12 5:22 AM, Dimitri Fontaine wrote: Jeff Janes jeff.ja...@gmail.com writes: I think we want this. There is some discussion about how much overlap it has with pgfincore, but I don't think there is an active proposal to put that into contrib, so don't see that as blocking this. It is my understanding that Cédric wants to propose a patch for pgfincore as a contrib module in next Commit Fest, and has already been working on some necessary cleaning to see that happen. Still means not a blocker in my book. pgFincore, great as it is: a) might not be ready for contrib in 9.2 b) isn't supported on all platforms c) isn't necessarily safe in production (I've crashed Linux with Fincore in the recent past). As such, I see no reason why pgprewarm and pgfincore in contrib should block each other, either way. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
This is a review for pg_prewarm V2.
It applies (with some fuzz, but it is handled correctly) and builds cleanly.
It includes docs, but does not include regression tests, which it
probably should (just to verify that it continues to compile and
execute without throwing errors, I wouldn't expect an automated test
to verify actual performance improvement).
I think we want this. There is some discussion about how much overlap
it has with pgfincore, but I don't think there is an active proposal
to put that into contrib, so don't see that as blocking this.
It works as advertised. using pgbench -i -s100 (about 1.5Gig), with
shared_buffers of current default (128 MB), it takes 10 minutes for
pgbench -S to revive the cache from a cold start and reach its full
TPS. If I use pg_prewarm on both pgbench_accounts and
pgbench_accounts_pkey from a cold start, it takes 22 seconds, and then
pgbench -S runs at full speed right from the start.
It does not matter if I use 'read' or 'buffer'. While all the data
doesn't fit in shared_buffers, trying to read it into the buffers acts
to populate the file cache anyway, and doesn't take significantly more
time.
On my test system (openSuse 12.1) 'prefetch' took just as long 'read'
or 'buffer', and sometimes it seemed to fail to load everything (it
would take pgbench -S up to 60 seconds to reach full speed). I expect
this to be too system depend to care much about figuring what is going
on, though.
For the implementation:
1)
I think that for most users, making them know or care about forks and
block numbers is too much. It would be nice if there were a
single-argument form: pg_prewarm(relation) which loaded all of either
main, or all of all forks, using 'buffer'. This seems like a good
default. Also, the last two arguments are NULL in all the given
examples. Do we expect those to be used only for experimental
purposes by hackers, or are those of general interest?
2)
The error message:
ERROR: prewarm type cannot be null
Should offer the same hint as:
ERROR: invalid prewarm type
HINT: Valid prewarm types are prefetch, read, and buffer.
3)
In the docs, the precedence seems to be that fork names ('main', here)
when in LITERAL classes are shown with single quotes around them,
rather than bare.
4) Not sure that the copyright should start in 2010 in pg_prewarm.c:
Copyright (c) 2010-2012
I have not tested on a system which does not support posix_fadvise.
Cheers,
Jeff
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm(some more observations in patch)
From: pgsql-hackers-ow...@postgresql.org [pgsql-hackers-ow...@postgresql.org]
on behalf of Jeff Janes [jeff.ja...@gmail.com]
For the implementation:
1)
I think that for most users, making them know or care about forks and
block numbers is too much. It would be nice if there were a
single-argument form: pg_prewarm(relation) which loaded all of either
main, or all of all forks, using 'buffer'. This seems like a good
default. Also, the last two arguments are NULL in all the given
examples. Do we expect those to be used only for experimental
purposes by hackers, or are those of general interest?
I agree with you.
2 forms of the function can exist one with only one argument and other
with the arguments as specified in you interface. This can solve the purpose
of all kind of users.
In the first form there should be defaults for all other values.
1. For the prewarm type(prefetch,read,buffer), it would have been better if
either it is enum or
an case insensitive string. It could have been more convienient from user
perspective.
2.
+ if (PG_ARGISNULL(4))
+ last_block = nblocks - 1;
+ else
+ {
+ last_block = PG_GETARG_INT64(4);
+ if (last_block nblocks)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg(ending block number INT64_FORMAT exceeds number of blocks in
relation INT64_FORMAT, last_block, nblocks)));
+ }
It can add additional check if last block number is less than first block
number, then report meaningful error.
As for this kind of case, it will not load any buffers and returns
successfully.
3. + else if (ptype == PREWARM_READ)
+ {
+ /*
+ * In read mode, we actually read the blocks, but not into shared
+ * buffers. This is more portable than prefetch mode (it works
+ * everywhere) and is synchronous.
+ */
+ RelationOpenSmgr(rel);
Is it required to call RelationOpenSmgr(rel) as in the begining already it
is done?
With Regards,
Amit Kapila.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
Le samedi 23 juin 2012 02:47:15, Josh Berkus a écrit : The biggest problem with pgfincore from my point of view is that it only works under Linux, whereas I use a MacOS X machine for my development, and there is also Windows to think about. Even if that were fixed, though, I feel we ought to have something in the core distribution. This patch got more +1s than 95% of what gets proposed on hackers. Fincore is only a blocker to this patch if we think pgfincore is ready to be proposed for the core distribution. Do we? I'll make it ready for. (not a huge task). -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] pg_prewarm
Robert Haas robertmh...@gmail.com writes: 73%? I think it's got about 15% overlap. 83.7% of stats are wrong. This one included. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
The biggest problem with pgfincore from my point of view is that it only works under Linux, whereas I use a MacOS X machine for my development, and there is also Windows to think about. Even if that were fixed, though, I feel we ought to have something in the core distribution. This patch got more +1s than 95% of what gets proposed on hackers. Fincore is only a blocker to this patch if we think pgfincore is ready to be proposed for the core distribution. Do we? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
On tis, 2012-04-10 at 13:29 +0200, Cédric Villemain wrote: I have no problem deprecating overlapping features from pgfincore as soon as I can do a «depend:pg_prewarm[os_warm]» :) ...It would have been better to split pgfincore analyze and warming parts times ago, anyway. So pg_prewarm is now pending in the commitfest, so let's see what the situation is. I'm worried about the overlap with pgfincore. It's pretty well established in this space, and has about 73% feature overlap with pg_prewarm, while having more features all together. So it would seem to me that it would be better to add whatever is missing to pgfincore instead. Or split pgfincore, as suggested above, but that would upset existing users. But adding severely overlapping stuff for no technical reasons (or there any?) doesn't sound like a good idea. Also, Robert has accurately described this as mechanism, not policy. That's fine, that's what all of our stuff is. Replication and most of postgresql.conf suffers from that. Eventually someone will want to create a way to save and restore various caches across server restarts, as discussed before. Would that mean there will be a third way to do all this, or could we at least align things a bit so that such a facility could use most of the proposed prewarming stuff? (Patches for the cache restoring exist, so it should be possible to predict this a little bit.) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
On Wed, Jun 20, 2012 at 3:53 PM, Peter Eisentraut pete...@gmx.net wrote: I'm worried about the overlap with pgfincore. It's pretty well established in this space, and has about 73% feature overlap with pg_prewarm, while having more features all together. So it would seem to me that it would be better to add whatever is missing to pgfincore instead. Or split pgfincore, as suggested above, but that would upset existing users. But adding severely overlapping stuff for no technical reasons (or there any?) doesn't sound like a good idea. 73%? I think it's got about 15% overlap. The biggest problem with pgfincore from my point of view is that it only works under Linux, whereas I use a MacOS X machine for my development, and there is also Windows to think about. Even if that were fixed, though, I feel we ought to have something in the core distribution. This patch got more +1s than 95% of what gets proposed on hackers. Also, Robert has accurately described this as mechanism, not policy. That's fine, that's what all of our stuff is. Replication and most of postgresql.conf suffers from that. Eventually someone will want to create a way to save and restore various caches across server restarts, as discussed before. Would that mean there will be a third way to do all this, or could we at least align things a bit so that such a facility could use most of the proposed prewarming stuff? (Patches for the cache restoring exist, so it should be possible to predict this a little bit.) Well, pg_buffercache + pg_prewarm is enough to save and restore shared buffers. Not the OS cache, but we don't have portable code to query the OS cache yet anyway. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
The biggest problem with pgfincore from my point of view is that it only works under Linux, whereas I use a MacOS X machine for my development, and there is also Windows to think about. Even if that were fixed, though, I feel we ought to have something in the core distribution. This patch got more +1s than 95% of what gets proposed on hackers. pgfincore works also on BSD kernels. Can you try on your MacOSX ? (I don't have one here). As of freeBSD 8.3 there is suport for posix_fadvise call so both PostgreSQL core and pgfincore now support the preloading on this distribution (I've not tested it recently but it should). All pgfincore features should now works in most places, except windows. Also, Robert has accurately described this as mechanism, not policy. That's fine, that's what all of our stuff is. Replication and most of postgresql.conf suffers from that. Eventually someone will want to create a way to save and restore various caches across server restarts, as discussed before. Would that mean there will be a third way to do all this, or could we at least align things a bit so that such a facility could use most of the proposed prewarming stuff? (Patches for the cache restoring exist, so it should be possible to predict this a little bit.) Well, pg_buffercache + pg_prewarm is enough to save and restore shared buffers. Not the OS cache, but we don't have portable code to query the OS cache yet anyway. +pgfincore and the OS cache part is done. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
Re: [HACKERS] pg_prewarm
Le mercredi 20 juin 2012 21:53:43, Peter Eisentraut a écrit : On tis, 2012-04-10 at 13:29 +0200, Cédric Villemain wrote: I have no problem deprecating overlapping features from pgfincore as soon as I can do a «depend:pg_prewarm[os_warm]» :) ...It would have been better to split pgfincore analyze and warming parts times ago, anyway. So pg_prewarm is now pending in the commitfest, so let's see what the situation is. I have refused to propose pgfincore so far because BSD didn't supported POSIX_FADVISE (but already supported mincore(2)). Now, things change and pgfincore should work on linux, bsd, hp, ... (but not on windows) I'll be happy to propose it if people want. I'm worried about the overlap with pgfincore. It's pretty well established in this space, and has about 73% feature overlap with pg_prewarm, while having more features all together. So it would seem to me that it would be better to add whatever is missing to pgfincore instead. Or split pgfincore, as suggested above, but that would upset existing users. But adding severely overlapping stuff for no technical reasons (or there any?) doesn't sound like a good idea. And I am also worried with the overlap. Also, Robert has accurately described this as mechanism, not policy. That's fine, that's what all of our stuff is. Replication and most of postgresql.conf suffers from that. Eventually someone will want to create a way to save and restore various caches across server restarts, as discussed before. Would that mean there will be a third way to do all this, or could we at least align things a bit so that such a facility could use most of the proposed prewarming stuff? (Patches for the cache restoring exist, so it should be possible to predict this a little bit.) It makes some time I have a look at the postgresql source code about readBuffer and friends. AFAIR pgfincore needed some access to file decsriptor which were not possible with PostgreSQL core functions. I can have a look as this is near the stuff I'll work on next (posix_fadvice random/sequential/normal applyed directly by PostgreSQL, instead of relying on hacks around read-the-first-block to start readahead). -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
Re: [HACKERS] pg_prewarm
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. Agreed, pgfincore first use was to analyze cache usage and performance impacts. (this works with systems having mincore(), not only linux, only windows is really different and while I can add the support for it, I've never been requested for that, I can do if it helps going to contrib/ if someone care). Warming with pg_prewarm looks really cool and it does the job. Pgfincore only advantage here are that if you call POSIX_FADVISE on whole file, the kernel will *try* to load as much of possible while not destructing the cache its have. My experience is that if you call block-per-block all the blocks you touch are in cache (and eviction can occur more often). 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. Load-per-block is indeed very useful as the Slave can really catch-up more quickly with the workload in case of switchover for example (this is why I've moved pgfincore results in a varbit that can be shared with the slaves more easily). I have no problem deprecating overlapping features from pgfincore as soon as I can do a «depend:pg_prewarm[os_warm]» :) ...It would have been better to split pgfincore analyze and warming parts times ago, anyway. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
Re: [HACKERS] pg_prewarm
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
Re: [HACKERS] pg_prewarm
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. 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. 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). I would be happy to test both. But when reading the current documentation I'm missing installation requirements (PG version, replication? memory/hardware requirements), specifics of Linux (and Windows if supported), and some config. hints (e.g. relationships/dependencies of OS cache and PG cache an postgresql.conf). pgfincore works with all postgresql stable releases. Probably idem for pg_prewarm. in both case, make make install, then some SQL file to load for =9.0. With 9.1, once you've build and install, just CREATE EXTENSION pg_fincore; (probably the same with pg_prewarm) -Stefan 2012/3/11 Cédric Villemain ced...@2ndquadrant.com: Le vendredi 9 mars 2012 16:50:05, Robert Haas a écrit : On Fri, Mar 9, 2012 at 10:33 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: So that's complementary with pgfincore, ok. I still wish we could maintain the RAM content HOT on the standby in the same way we are able to maintain its data set on disk, though. That's an interesting idea. It seems tricky, though. it is the purpose of the latest pgfincore version. I use a varbit as output of introspection on master, then you are able to store in a table, stream to slaves, then replay localy. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation
Re: [HACKERS] pg_prewarm
On Fri, Mar 9, 2012 at 5:24 AM, Fujii Masao masao.fu...@gmail.com wrote: For such system, so far I've been suggesting using pgstatindex, but it's good if pg_prewarm can do that. Relevant to this, see commit 2e46bf67114586835f4a9908f1a1f08ee8ba83a8. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
Hi Robert 2012/3/11 Robert Haas robertmh...@gmail.com: On Sat, Mar 10, 2012 at 4:35 PM, Stefan Keller sfkel...@gmail.com wrote: The main conclusion was: * Do a tar cf /dev/zero $PG_DATA/base either shortly before or shortly after the database is created * Do a seq scan SELECT * FROM osm_point. Is your tool a replacement of those above? It can be used that way, although it is more general. (The patch does include documentation...) Thanks for the hint. That function is cool and it seems to be the solution of the concluding question in my talk about read-only databases at pgconf.de 2011! I'm new to the contrib best practices of Postgres so I did not expect that a file 'pg_prewarm_v1.patch' contains a brand new stand-alone extension. Does pg_prewarm have already a website entry somewhere? I did not find anything (like here http://www.postgresql.org/search/?q=pg_prewarma=1submit=Search ) except at Commitfest open patches (https://commitfest.postgresql.org/ ). -Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
Le vendredi 9 mars 2012 16:50:05, Robert Haas a écrit : On Fri, Mar 9, 2012 at 10:33 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: So that's complementary with pgfincore, ok. I still wish we could maintain the RAM content HOT on the standby in the same way we are able to maintain its data set on disk, though. That's an interesting idea. It seems tricky, though. it is the purpose of the latest pgfincore version. I use a varbit as output of introspection on master, then you are able to store in a table, stream to slaves, then replay localy. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
Cédric and Robert Thanks, Cédric, for the reminder. 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? I would be happy to test both. But when reading the current documentation I'm missing installation requirements (PG version, replication? memory/hardware requirements), specifics of Linux (and Windows if supported), and some config. hints (e.g. relationships/dependencies of OS cache and PG cache an postgresql.conf). -Stefan 2012/3/11 Cédric Villemain ced...@2ndquadrant.com: Le vendredi 9 mars 2012 16:50:05, Robert Haas a écrit : On Fri, Mar 9, 2012 at 10:33 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: So that's complementary with pgfincore, ok. I still wish we could maintain the RAM content HOT on the standby in the same way we are able to maintain its data set on disk, though. That's an interesting idea. It seems tricky, though. it is the purpose of the latest pgfincore version. I use a varbit as output of introspection on master, then you are able to store in a table, stream to slaves, then replay localy. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
On Mar 9, 2012, at 2:34 PM, Robert Haas wrote: On Fri, Mar 9, 2012 at 5:42 AM, Hans-Jürgen Schönig postg...@cybertec.at wrote: we had some different idea here in the past: what if we had a procedure / method to allow people to save the list of current buffers / cached blocks to be written to disk (sorted). we could then reload this cache profile on startup in the background or people could load a certain cache content at runtime (maybe to test or whatever). writing those block ids in sorted order would help us to avoid some random I/O on reload. I don't think that's a bad idea at all, and someone actually did write a patch for it at one point, though it didn't get committed, partly I believe because of technical issues and partly because Greg Smith was uncertain how much good it did to restore shared_buffers without thinking about the OS cache. Personally, I don't buy into the latter objection: a lot of people are running with data sets that fit inside shared_buffers, and those people would benefit tremendously. However, this just provides mechanism, not policy, and is therefore more general. You could use pg_buffercache to save the cache contents at shutdown and pg_prewarm to load those blocks back in at startup, if you were so inclined. Or if you just want to load up your main relation, and its indexes, you can do that, too. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company i also think that it can be beneficial. once in a while people ask how to bring a database up to speed after a restart. i have seen more than one case when a DB was close to death after a restart because random I/O was simply killing it during cache warmup. it seems the problem is getting worse as we see machines with more and more RAM in the field. technically i would see a rather brute force approach: if we just spill out of the list of blocks we got in shared buffer atm (not content of course, just physical location sorted by file / position in file) it would be good enough. if a block physically does not exist on reload any more it would not even be an issue and allow people basically to snapshot their cache status. we could allow named cache profiles or so and make a GUC to indicate of one of them should be preloaded on startup (background or beforehand - i see usecases for both approaches). yes, somehow linking to pg_buffercache makes a lot of sense. maybe just extending it with some extra functions is already enough for most cases. hans -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
Hi Robert, Just recently I asked on postgres-performance PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory? Somehow open was, what's the best practice of configuration and relationship between disk/OS cache vs. Portgres cache The main conclusion was: * Do a tar cf /dev/zero $PG_DATA/base either shortly before or shortly after the database is created * Do a seq scan SELECT * FROM osm_point. Is your tool a replacement of those above? -Stefan 2012/3/9 Robert Haas robertmh...@gmail.com: On Fri, Mar 9, 2012 at 10:53 AM, Jeff Janes jeff.ja...@gmail.com wrote: On Fri, Mar 9, 2012 at 5:21 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 9, 2012 at 5:24 AM, Fujii Masao masao.fu...@gmail.com wrote: When a relation is loaded into cache, are corresponding indexes also loaded at the same time? No, although if you wanted to do that you could easily do so, using a query like this: select pg_prewarm(indexrelid, 'main', 'read', NULL, NULL) from pg_index where indrelid = 'your_table_name'::regclass; Could that be included in an example? Maybe admins are expected to know how to construct such queries of the cuff, but I always need to look it up each time which is rather tedious. Not a bad idea. I thought of including an Examples section, but it didn't seem quite worth it for the simple case of prewarming a heap. Might be worth it to also include this. In the patch: s/no special projection/no special protection/ OK, will fix. Thanks for putting this together. I will confess that it was 0% altruistic. Not having it was ruining my day. :-) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
On Sat, Mar 10, 2012 at 4:35 PM, Stefan Keller sfkel...@gmail.com wrote: The main conclusion was: * Do a tar cf /dev/zero $PG_DATA/base either shortly before or shortly after the database is created * Do a seq scan SELECT * FROM osm_point. Is your tool a replacement of those above? It can be used that way, although it is more general. (The patch does include documentation...) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
So I wrote a prewarming utility. Patch is attached. You can prewarm either the OS cache or PostgreSQL's cache, and there are two options for prewarming the OS cache to meet different needs. By passing the correct arguments to the function, you can prewarm an entire relation or just the blocks you choose; prewarming of blocks from alternate relation forks is also supported, for completeness. Hope you like it. +1
Re: [HACKERS] pg_prewarm
Hi, On Thu, 2012-03-08 at 23:13 -0500, Robert Haas wrote: It's been bugging me for a while now that we don't have a prewarming utility, for a couple of reasons, including: 1. Our customers look at me funny when I suggest that they use pg_relation_filepath() and /bin/dd for this purpose. 2. Sometimes when I'm benchmarking stuff, I want to get all the data cached in shared_buffers. This is surprisingly hard to do if the size of any relation involved is =1/4 of shared buffers, because the BAS_BULKREAD stuff kicks in. You can do it by repeatedly seq-scanning the relation - eventually all the blocks trickle in - but it takes a long time, and that's annoying. So I wrote a prewarming utility. I was talking to an Oracle DBA about this just yesterday. We also have pgfincore, but pg_prewarm is pretty much we need actually, I think. Did not test the patch, but the feature should be in core/contrib/whatever. This will also increase performance for the static tables that needs to be in the buffers all the time. I'm also seeing some use cases for BI databases. Thanks! Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [HACKERS] pg_prewarm
On Fri, Mar 9, 2012 at 1:13 PM, Robert Haas robertmh...@gmail.com wrote: It's been bugging me for a while now that we don't have a prewarming utility, for a couple of reasons, including: 1. Our customers look at me funny when I suggest that they use pg_relation_filepath() and /bin/dd for this purpose. 2. Sometimes when I'm benchmarking stuff, I want to get all the data cached in shared_buffers. This is surprisingly hard to do if the size of any relation involved is =1/4 of shared buffers, because the BAS_BULKREAD stuff kicks in. You can do it by repeatedly seq-scanning the relation - eventually all the blocks trickle in - but it takes a long time, and that's annoying. So I wrote a prewarming utility. Patch is attached. You can prewarm either the OS cache or PostgreSQL's cache, and there are two options for prewarming the OS cache to meet different needs. By passing the correct arguments to the function, you can prewarm an entire relation or just the blocks you choose; prewarming of blocks from alternate relation forks is also supported, for completeness. Hope you like it. +1 When a relation is loaded into cache, are corresponding indexes also loaded at the same time? Can this load only the specified index into cache? When the relation is too huge to fit into the cache and most access pattern in the system is index scan, DBA might want to load only index rather than table. For such system, so far I've been suggesting using pgstatindex, but it's good if pg_prewarm can do that. This utility might be helpful to accelerate a recovery of WAL record not containing FPW. IOW, before starting a recovery, list the relations to recover from WAL files by using xlogdump tool, load them into cache by using this utility, and then start a recovery. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
we had some different idea here in the past: what if we had a procedure / method to allow people to save the list of current buffers / cached blocks to be written to disk (sorted). we could then reload this cache profile on startup in the background or people could load a certain cache content at runtime (maybe to test or whatever). writing those block ids in sorted order would help us to avoid some random I/O on reload. regards, hans On Mar 9, 2012, at 5:13 AM, Robert Haas wrote: It's been bugging me for a while now that we don't have a prewarming utility, for a couple of reasons, including: 1. Our customers look at me funny when I suggest that they use pg_relation_filepath() and /bin/dd for this purpose. 2. Sometimes when I'm benchmarking stuff, I want to get all the data cached in shared_buffers. This is surprisingly hard to do if the size of any relation involved is =1/4 of shared buffers, because the BAS_BULKREAD stuff kicks in. You can do it by repeatedly seq-scanning the relation - eventually all the blocks trickle in - but it takes a long time, and that's annoying. So I wrote a prewarming utility. Patch is attached. You can prewarm either the OS cache or PostgreSQL's cache, and there are two options for prewarming the OS cache to meet different needs. By passing the correct arguments to the function, you can prewarm an entire relation or just the blocks you choose; prewarming of blocks from alternate relation forks is also supported, for completeness. Hope you like it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company pg_prewarm_v1.patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
On Fri, Mar 9, 2012 at 5:24 AM, Fujii Masao masao.fu...@gmail.com wrote: When a relation is loaded into cache, are corresponding indexes also loaded at the same time? No, although if you wanted to do that you could easily do so, using a query like this: select pg_prewarm(indexrelid, 'main', 'read', NULL, NULL) from pg_index where indrelid = 'your_table_name'::regclass; Can this load only the specified index into cache? Yes. The relation can be anything that has storage, so you can prewarm either a table or an index (or even a sequence or TOAST table, if you're so inclined). When the relation is too huge to fit into the cache and most access pattern in the system is index scan, DBA might want to load only index rather than table. For such system, so far I've been suggesting using pgstatindex, but it's good if pg_prewarm can do that pgstatindex is an interesting idea; hadn't thought of that. Actually, though, pgstaindex probably ought to be using a BufferAccessStrategy to avoid trashing the cache. I've had reports of pgstatindex torpedoing performance on production systems. This utility might be helpful to accelerate a recovery of WAL record not containing FPW. IOW, before starting a recovery, list the relations to recover from WAL files by using xlogdump tool, load them into cache by using this utility, and then start a recovery. Interesting idea. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
Robert Haas robertmh...@gmail.com writes:
It's been bugging me for a while now that we don't have a prewarming
utility, for a couple of reasons, including:
1. Our customers look at me funny when I suggest that they use
pg_relation_filepath() and /bin/dd for this purpose.
Try telling them about pgfincore maybe.
https://github.com/klando/pgfincore
2. Sometimes when I'm benchmarking stuff, I want to get all the data
cached in shared_buffers. This is surprisingly hard to do if the size
of any relation involved is =1/4 of shared buffers, because the
BAS_BULKREAD stuff kicks in. You can do it by repeatedly seq-scanning
the relation - eventually all the blocks trickle in - but it takes a
long time, and that's annoying.
That reminds me of something…
cedric=# select * from pgfadvise_willneed('pgbench_accounts');
relpath | os_page_size | rel_os_pages | os_pages_free
+--+--+---
base/11874/16447 | 4096 | 262144 |169138
base/11874/16447.1 | 4096 |65726 |103352
(2 rows)
Time: 4462,936 ms
With pgfincore you can also get at how many pages are in memory already,
os cache or shared buffers, per file segment of a relation. So you can
both force warming up a whole relation, parts of it, and check the
current state of things.
So I wrote a prewarming utility. Patch is attached. You can prewarm
either the OS cache or PostgreSQL's cache, and there are two options
for prewarming the OS cache to meet different needs. By passing the
correct arguments to the function, you can prewarm an entire relation
or just the blocks you choose; prewarming of blocks from alternate
relation forks is also supported, for completeness.
Is it possible with your tool to snapshot the OS and PostgreSQL cache in
order to warm an Hot Standby server?
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
On Fri, Mar 9, 2012 at 5:42 AM, Hans-Jürgen Schönig postg...@cybertec.at wrote: we had some different idea here in the past: what if we had a procedure / method to allow people to save the list of current buffers / cached blocks to be written to disk (sorted). we could then reload this cache profile on startup in the background or people could load a certain cache content at runtime (maybe to test or whatever). writing those block ids in sorted order would help us to avoid some random I/O on reload. I don't think that's a bad idea at all, and someone actually did write a patch for it at one point, though it didn't get committed, partly I believe because of technical issues and partly because Greg Smith was uncertain how much good it did to restore shared_buffers without thinking about the OS cache. Personally, I don't buy into the latter objection: a lot of people are running with data sets that fit inside shared_buffers, and those people would benefit tremendously. However, this just provides mechanism, not policy, and is therefore more general. You could use pg_buffercache to save the cache contents at shutdown and pg_prewarm to load those blocks back in at startup, if you were so inclined. Or if you just want to load up your main relation, and its indexes, you can do that, too. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
On Fri, Mar 9, 2012 at 8:25 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote:
Robert Haas robertmh...@gmail.com writes:
It's been bugging me for a while now that we don't have a prewarming
utility, for a couple of reasons, including:
1. Our customers look at me funny when I suggest that they use
pg_relation_filepath() and /bin/dd for this purpose.
Try telling them about pgfincore maybe.
https://github.com/klando/pgfincore
Oh, huh. I had no idea that pgfincore could do that. I thought that
was just for introspection; I didn't realize it could actually move
data around for you.
2. Sometimes when I'm benchmarking stuff, I want to get all the data
cached in shared_buffers. This is surprisingly hard to do if the size
of any relation involved is =1/4 of shared buffers, because the
BAS_BULKREAD stuff kicks in. You can do it by repeatedly seq-scanning
the relation - eventually all the blocks trickle in - but it takes a
long time, and that's annoying.
That reminds me of something…
cedric=# select * from pgfadvise_willneed('pgbench_accounts');
relpath | os_page_size | rel_os_pages | os_pages_free
+--+--+---
base/11874/16447 | 4096 | 262144 | 169138
base/11874/16447.1 | 4096 | 65726 | 103352
(2 rows)
Time: 4462,936 ms
That's not the same thing. That's pulling them into the OS cache, not
shared_buffers.
Is it possible with your tool to snapshot the OS and PostgreSQL cache in
order to warm an Hot Standby server?
Nope. It doesn't have any capabilities to probe for information,
because I knew those things already existed in pg_buffercache and
pgfincore, and also because they weren't what I needed to solve my
immediate problem, which was a way to get the entirety of a relation
into shared_buffers.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
Robert Haas robertmh...@gmail.com writes: https://github.com/klando/pgfincore Oh, huh. I had no idea that pgfincore could do that. I thought that was just for introspection; I didn't realize it could actually move data around for you. Well, I though Cédric already had included shared buffers related facilities, so that make us square it seems… Is it possible with your tool to snapshot the OS and PostgreSQL cache in order to warm an Hot Standby server? Nope. It doesn't have any capabilities to probe for information, because I knew those things already existed in pg_buffercache and pgfincore, and also because they weren't what I needed to solve my immediate problem, which was a way to get the entirety of a relation into shared_buffers. So that's complementary with pgfincore, ok. I still wish we could maintain the RAM content HOT on the standby in the same way we are able to maintain its data set on disk, though. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
On Fri, Mar 9, 2012 at 10:33 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: So that's complementary with pgfincore, ok. I still wish we could maintain the RAM content HOT on the standby in the same way we are able to maintain its data set on disk, though. That's an interesting idea. It seems tricky, though. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
On Fri, Mar 9, 2012 at 5:21 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 9, 2012 at 5:24 AM, Fujii Masao masao.fu...@gmail.com wrote: When a relation is loaded into cache, are corresponding indexes also loaded at the same time? No, although if you wanted to do that you could easily do so, using a query like this: select pg_prewarm(indexrelid, 'main', 'read', NULL, NULL) from pg_index where indrelid = 'your_table_name'::regclass; Could that be included in an example? Maybe admins are expected to know how to construct such queries of the cuff, but I always need to look it up each time which is rather tedious. In the patch: s/no special projection/no special protection/ Thanks for putting this together. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_prewarm
On Fri, Mar 9, 2012 at 10:53 AM, Jeff Janes jeff.ja...@gmail.com wrote: On Fri, Mar 9, 2012 at 5:21 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 9, 2012 at 5:24 AM, Fujii Masao masao.fu...@gmail.com wrote: When a relation is loaded into cache, are corresponding indexes also loaded at the same time? No, although if you wanted to do that you could easily do so, using a query like this: select pg_prewarm(indexrelid, 'main', 'read', NULL, NULL) from pg_index where indrelid = 'your_table_name'::regclass; Could that be included in an example? Maybe admins are expected to know how to construct such queries of the cuff, but I always need to look it up each time which is rather tedious. Not a bad idea. I thought of including an Examples section, but it didn't seem quite worth it for the simple case of prewarming a heap. Might be worth it to also include this. In the patch: s/no special projection/no special protection/ OK, will fix. Thanks for putting this together. I will confess that it was 0% altruistic. Not having it was ruining my day. :-) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_prewarm
It's been bugging me for a while now that we don't have a prewarming utility, for a couple of reasons, including: 1. Our customers look at me funny when I suggest that they use pg_relation_filepath() and /bin/dd for this purpose. 2. Sometimes when I'm benchmarking stuff, I want to get all the data cached in shared_buffers. This is surprisingly hard to do if the size of any relation involved is =1/4 of shared buffers, because the BAS_BULKREAD stuff kicks in. You can do it by repeatedly seq-scanning the relation - eventually all the blocks trickle in - but it takes a long time, and that's annoying. So I wrote a prewarming utility. Patch is attached. You can prewarm either the OS cache or PostgreSQL's cache, and there are two options for prewarming the OS cache to meet different needs. By passing the correct arguments to the function, you can prewarm an entire relation or just the blocks you choose; prewarming of blocks from alternate relation forks is also supported, for completeness. Hope you like it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company pg_prewarm_v1.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
Re: [HACKERS] pg_prewarm
On Thu, Mar 8, 2012 at 11:13 PM, Robert Haas robertmh...@gmail.com wrote:
It's been bugging me for a while now that we don't have a prewarming
utility, for a couple of reasons, including:
1. Our customers look at me funny when I suggest that they use
pg_relation_filepath() and /bin/dd for this purpose.
well, you can't deny that is funny see people doing faces ;)
So I wrote a prewarming utility. Patch is attached.
cool!
just a suggestion, can we relax this check? just send a WARNING or a
NOTICE and set last_block = nblocks - 1
just an opinion
+ if (PG_ARGISNULL(4))
+ last_block = nblocks - 1;
+ else
+ {
+ last_block = PG_GETARG_INT64(4);
+ if (last_block nblocks)
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+errmsg(ending block number
INT64_FORMAT exceeds number of
blocks in relation INT64_FORMAT, last_block, nblocks)));
+ }
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
