Re: [HACKERS] WIP: Page space reservation (pgupgrade)
Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] writes: Attached patch allows to setup storage parameter for space reservation. What is the point of this? We don't need it for 8.3-8.4, we aren't going to back-patch such a thing into 8.2 or before (certainly not before, since reloptions didn't exist before 8.2), and I would hope we put in a more general solution than this for 8.4-to-later preparatory fixes. There is whole idea. By my opinion the space reservation is possible to split into three separate problems: 1) First mandatory is to implement space reservation functionality in the core. It is what this patch tries to do. It prevents to store new data in reserved area. The configuration is implemented with reloptions. I think This part is possible simply backported back to 8.2 and it allows in-place online upgrade form 8.2 to 8.4 (and indirectly from 8.1). There is only limitation for toasted arrays and composite datatypes, which can be solve with offline conversion. How Jim Nasby mentioned we need to add also per tuple space reservation which is not required for V3-V4 upgrade or it is negative, but it is important to have it for 8.4, because who know what will happen int 8.5 development. By my opinion, this functionality is similar to fillfactor and it is independent. And can be committed separately. 2) Second part is pre-upgrade check/preparation process. For 8.2 it has to be store procedure which will work separately (no core changes). In 8.4 it will be implemented into core - new column in pg_class and pg_database. Vacuum adjusting and so on. 3) Last part is pre-upgrade configuration which correctly setup reserved space all non-system relations in database cluster. Thats my idea. Let me know any comments. Thanks Zdenek -- 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] WIP: Page space reservation (pgupgrade)
Zdenek Kotala [EMAIL PROTECTED] writes: Tom Lane napsal(a): What is the point of this? We don't need it for 8.3-8.4, we aren't going to back-patch such a thing into 8.2 or before (certainly not before, since reloptions didn't exist before 8.2), and I would hope we put in a more general solution than this for 8.4-to-later preparatory fixes. ... I think This part is possible simply backported back to 8.2 and it allows in-place online upgrade form 8.2 to 8.4 (and indirectly from 8.1). I would never recommend to anyone that they depend on a patch like this for pre-upgrade conversions. It has no way to guarantee that all pages in the database have been fixed. And in any case, I would vote against back-patching such a thing into 8.2, at least not without a whole lot more field testing than it is likely to get. As for planning that there will someday be 8.1-8.2 or even 8.2-8.3 online upgrade, I suggest that you quit wasting your time even thinking about that. It won't happen and expending more cycles on it is mostly going to ensure that the entire upgrade project fails. What we need now is something that works for 8.3-8.4 and that we can extend and maintain for future version updates. regards, tom lane -- 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] WIP: Page space reservation (pgupgrade)
Robert Haas napsal(a): Let's suppose, for example, that in 8.5 we decide to change some type that is presently 16 bits to 32 bits, or 8 bits to 16 bits, etc. This will make some tuples bigger, and potentially much bigger, but since it presumably won't be a commonly used data-type, most tuples won't change at all. However, the worst case scenario for how much free space you might need to reserve will be very bad, and therefore a mechanism that allows reserving a fixed amount of free space per page won't be adequate. The problem with datatypes is different story. It is should be easy to manage this problem with keeping the old datatype definition for old tables and for new create new datatype with new OID. You can use ALTER TABLE for converting data from old type to the new one. Zdenek -- 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] WIP: Page space reservation (pgupgrade)
On Sun, Nov 09, 2008 at 11:28:50PM -0500, Robert Haas wrote: Well, if that's what it is, I think it's a fairly poor design decision. When I upgrade Oracle, SQL Server, or MySQL, I don't need to plan the amount of free space in my blocks a year or more before an upgrade. In fact, I don't have to plan it at all... it's completely handled by the in-place upgrade. Well, I think the proposal is that you would change the amount of free space in your blocks immediately prior to performing the upgrade, but I still think it's a poor decision to make in-place upgrade dependent on support from the OLD version of the code. ISTM the only reason why people are talking about page reservation is because people don't like the idea of an 8.4 backend being able to read 8.3 tuples without converting the whole page. If there's no hard dealine on the page conversion then you can let the 8.4 vacuum deal with it. Maybe it will take a few runs but it should get there eventually. I think you could probably sell page-reservation as: if you do this then upgrades will happen quicker but you shouldn't rely on people doing it as there are corner cases where it won't work. But if you ask people with multi-TB database whether they'll take a 1% CPU performance hit for never having to dump/restore again, which do you think they'll choose? For an I/O bound database the choice is easy. And if the performance is such a big deal provide two binaries, one to run during the upgrade and one once the upgrade is complete. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] WIP: Page space reservation (pgupgrade)
On Nov 8, 2008, at 8:35 PM, Jonah H. Harris wrote: On Sat, Nov 8, 2008 at 8:08 PM, Tom Lane [EMAIL PROTECTED] wrote: Zdenek Kotala [EMAIL PROTECTED] writes: Attached patch allows to setup storage parameter for space reservation. What is the point of this? That's my question. Why is this needed at all? I suspect this is to deal with needing to reserve space in a cluster that you're planning on upgrading to a new version that would take more space, but I think the implementation is probably too simplistic. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 -- 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] WIP: Page space reservation (pgupgrade)
Well, if that's what it is, I think it's a fairly poor design decision. When I upgrade Oracle, SQL Server, or MySQL, I don't need to plan the amount of free space in my blocks a year or more before an upgrade. In fact, I don't have to plan it at all... it's completely handled by the in-place upgrade. Well, I think the proposal is that you would change the amount of free space in your blocks immediately prior to performing the upgrade, but I still think it's a poor decision to make in-place upgrade dependent on support from the OLD version of the code. Let's suppose, for example, that in 8.5 we decide to change some type that is presently 16 bits to 32 bits, or 8 bits to 16 bits, etc. This will make some tuples bigger, and potentially much bigger, but since it presumably won't be a commonly used data-type, most tuples won't change at all. However, the worst case scenario for how much free space you might need to reserve will be very bad, and therefore a mechanism that allows reserving a fixed amount of free space per page won't be adequate. Now, maybe someone will come up with some reason why that particular example is unlikely or impossible or can be worked around. But there are LOTS of imaginable scenarios that could cause unpredictable amounts of page expansion or contraction, and I find it hard to believe that we can be confident that NONE of those things will EVER happen. ...Robert -- 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] WIP: Page space reservation (pgupgrade)
Jonah H. Harris napsal(a): On Sun, Nov 9, 2008 at 7:55 PM, Decibel! [EMAIL PROTECTED] wrote: On Nov 8, 2008, at 8:35 PM, Jonah H. Harris wrote: That's my question. Why is this needed at all? I suspect this is to deal with needing to reserve space in a cluster that you're planning on upgrading to a new version that would take more space, but I think the implementation is probably too simplistic. Well, if that's what it is, I think it's a fairly poor design decision. When I upgrade Oracle, SQL Server, or MySQL, I don't need to plan the amount of free space in my blocks a year or more before an upgrade. In fact, I don't have to plan it at all... it's completely handled by the in-place upgrade. It will be handled by PostgreSQL as well. The patch is about mechanism and configuration which will be used by in-place upgrade without any user activity. Only what user will have to do is run pg_upgrade_prepare() or something like this and when database will be ready then user can do upgrade. Zdenek -- 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] WIP: Page space reservation (pgupgrade)
On Sun, Nov 9, 2008 at 7:55 PM, Decibel! [EMAIL PROTECTED] wrote: On Nov 8, 2008, at 8:35 PM, Jonah H. Harris wrote: That's my question. Why is this needed at all? I suspect this is to deal with needing to reserve space in a cluster that you're planning on upgrading to a new version that would take more space, but I think the implementation is probably too simplistic. Well, if that's what it is, I think it's a fairly poor design decision. When I upgrade Oracle, SQL Server, or MySQL, I don't need to plan the amount of free space in my blocks a year or more before an upgrade. In fact, I don't have to plan it at all... it's completely handled by the in-place upgrade. -- Jonah H. Harris, Senior DBA myYearbook.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WIP: Page space reservation (pgupgrade)
Attached patch allows to setup storage parameter for space reservation. I use
reloptions capability for it. You can use it:
CREATE TABLE test(id int) with (reservedspace=10);
The idea is to reduce freespace value about reservedspace on places where
PageGet(Heap)FreeSpace is called.
I need perform more tests on this patch, however I need feedback if it is
reasonable way. It seems to me that the patch could be backported without any
problem.
I have still following doubts/questions:
1) GiST - gist uses gistnospace() function to check correct amount of space.
unfortunately, it does not have information about Relation. The function is
called from:
gistContinueInsert(), gistplacetopage(), and gistVacuumUpdate().
It seems to me that better that modify this function should be modified these
callers (exclude gistContinueInsert see 2)
2) WAL - I do not modify freespace during WAL replay. I think that when
reservedspace is set, then WAL record cannot break a space reservation.
3) vacuum - PageGetFreeSpaceWithFillFactor
It look likes that vacuum uses fill factor to check possible free space on page,
but it does not try to free space on page to satisfy fill factor criteria. Is it
correct or I'm wrong?
Thanks for your comments.
Zdenek
--
Zdenek Kotala Sun Microsystems
Prague, Czech Republic http://sun.com/postgresql
diff -Nrc pgsql_spacereserve.9e46c188067f/src/backend/access/common/reloptions.c pgsql_spacereserve.c901d4bb8cca/src/backend/access/common/reloptions.c
*** pgsql_spacereserve.9e46c188067f/src/backend/access/common/reloptions.c 2008-11-08 23:19:47.795930570 +0100
--- pgsql_spacereserve.c901d4bb8cca/src/backend/access/common/reloptions.c 2008-11-08 23:19:47.910535376 +0100
***
*** 286,330
default_reloptions(Datum reloptions, bool validate,
int minFillfactor, int defaultFillfactor)
{
! static const char *const default_keywords[1] = {fillfactor};
! char *values[1];
! int fillfactor;
StdRdOptions *result;
! parseRelOptions(reloptions, 1, default_keywords, values, validate);
/*
* If no options, we can just return NULL rather than doing anything.
* (defaultFillfactor is thus not used, but we require callers to pass it
* anyway since we would need it if more options were added.)
*/
! if (values[0] == NULL)
return NULL;
! if (!parse_int(values[0], fillfactor, 0, NULL))
{
! if (validate)
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
! errmsg(fillfactor must be an integer: \%s\,
! values[0])));
! return NULL;
}
! if (fillfactor minFillfactor || fillfactor 100)
{
! if (validate)
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
! errmsg(fillfactor=%d is out of range (should be between %d and 100),
! fillfactor, minFillfactor)));
! return NULL;
}
result = (StdRdOptions *) palloc(sizeof(StdRdOptions));
SET_VARSIZE(result, sizeof(StdRdOptions));
result-fillfactor = fillfactor;
return (bytea *) result;
}
--- 286,360
default_reloptions(Datum reloptions, bool validate,
int minFillfactor, int defaultFillfactor)
{
! static const char *const default_keywords[2] = {fillfactor,reservedspace};
! char *values[2];
! int fillfactor=defaultFillfactor;
! int reservedspace=0;
StdRdOptions *result;
! parseRelOptions(reloptions, 2, default_keywords, values, validate);
/*
* If no options, we can just return NULL rather than doing anything.
* (defaultFillfactor is thus not used, but we require callers to pass it
* anyway since we would need it if more options were added.)
*/
! if ((values[0] == NULL) (values[1] == NULL))
return NULL;
! /* fill factor */
! if (values[0] != NULL)
{
! if (!parse_int(values[0], fillfactor, 0, NULL))
! {
! if (validate)
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
! errmsg(fillfactor must be an integer: \%s\,
! values[0])));
! return NULL;
! }
!
! if (fillfactor minFillfactor || fillfactor 100)
! {
! if (validate)
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
! errmsg(fillfactor=%d is out of range (should be between %d and 100),
! fillfactor, minFillfactor)));
! return NULL;
! }
}
! /* reserved space */
! if (values[1] != NULL)
{
! if (!parse_int(values[1], reservedspace, 0, NULL))
! {
! if (validate)
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
! errmsg(reservedspace must be an integer: \%s\,
! values[1])));
! return NULL;
! }
!
! if (reservedspace 0 || reservedspace BLCKSZ/4)
! {
! if (validate)
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
! errmsg(reservedspace=%d is out of range (should be between 0 and %d),
! reservedspace, BLCKSZ/4)));
Re: [HACKERS] WIP: Page space reservation (pgupgrade)
Zdenek Kotala [EMAIL PROTECTED] writes: Attached patch allows to setup storage parameter for space reservation. What is the point of this? We don't need it for 8.3-8.4, we aren't going to back-patch such a thing into 8.2 or before (certainly not before, since reloptions didn't exist before 8.2), and I would hope we put in a more general solution than this for 8.4-to-later preparatory fixes. regards, tom lane -- 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] WIP: Page space reservation (pgupgrade)
On Sat, Nov 8, 2008 at 8:08 PM, Tom Lane [EMAIL PROTECTED] wrote: Zdenek Kotala [EMAIL PROTECTED] writes: Attached patch allows to setup storage parameter for space reservation. What is the point of this? That's my question. Why is this needed at all? -- Jonah H. Harris, Senior DBA myYearbook.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
