[HACKERS] Proposal: vacuum and autovacuum parameters to control freezing
I wrote: > It's usually going to be the case that the oldest datvacuumxid is > template0's, meaning that it will never be possible to truncate clog > until autovacuum decides that template0 is at risk of wraparound and > goes and vacuums it. Shortening the freeze horizon will reduce the size > that pg_clog occupies just *after* that happens, but we're still going > to see pg_clog bloating up to something close to 256MB before autovacuum > kicks in. After further thought I see that there are actually two parameters involved in this process: 1. the critical age (currentXID - relfrozenxid) beyond which autovacuum will force a vacuum of a particular table to forestall XID wraparound. (Note: as the 8.2 code stands, the system will launch autovacuums even when autovac is nominally disabled in order to fix tables that have exceeded a hard-wired critical age.) 2. the freeze distance vacuum (whether auto or normal) uses to determine the new cutoff point, ie, the new relfrozenxid for the table. We can make a few observations: * For a table that otherwise goes unvacuumed, the interval between forced anti-wraparound vacuums will be critical_age - freeze_distance. Therefore, for large static tables there is value in being able to adjust this difference to be as large as possible. * The size of pg_clog is determined by the system-wide maximum of critical_age + number-of-transactions-needed-to-finish-vacuuming. Therefore, critical_age is the knob we must expose if we want to provide user control of pg_clog growth. * It might seem that there's no point in per-table adjustment of critical_age, since only the system-wide maximum means anything for resource consumption. I'm not so sure though --- for a really large table, the time needed to finish vacuuming it could be significant, meaning it would need a lower critical age than other tables. With the current one-process-at-a-time autovac infrastructure, this probably isn't very important, but we've been talking about allowing multiple parallel autovacuums specifically to deal with the problem of some tables being much larger than others. So it seems to me that an argument can be made for creating two new GUC variables and adding two columns to pg_autovacuum: vacuum_freeze_distance: number of transactions back from current that a VACUUM will use as the freeze cutoff point, ie, XIDs older than that will be replaced by FrozenXID, and the cutoff point will become the table's new relfrozenxid value. Valid range zero to perhaps 1 billion. VACUUM FREEZE is a shorthand for doing a vacuum with vacuum_freeze_distance = 0. autovacuum_freeze_limit: maximum age of relfrozenxid before autovacuum will force a vacuum for anti-wraparound purposes. Valid range perhaps 100 million to (2 billion - 100 million). pg_autovacuum.freeze_distance: per-table vacuum_freeze_distance setting for autovacuum to use. pg_autovacuum.freeze_limit: per-table autovacuum_freeze_limit for autovacuum to use. (I'm not wedded to these names, anyone have better ideas?) I'd propose default values of 200 million for autovacuum_freeze_limit and half that for vacuum_freeze_distance, resulting in a maximum pg_clog size of 50MB and forced autovacs about every 100 million transactions. One minor point is that while the values of these variables have to have sane relationships to each other, the GUC infrastructure doesn't really allow us to enforce such a constraint directly (the behavior would be too dependent on which variable got set first). I'd suggest making vacuum just silently limit the effective freeze_distance to not more than half of the system's autovacuum_freeze_limit, rather than trying to enforce any relationship within GUC. This is kind of a lot to be inventing in late beta, but if we want to have a really credible solution to the WAL-versus-freezing problem I think we need to do all of this. Comments? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Bug in WAL backup documentation
"Simon Riggs" <[EMAIL PROTECTED]> writes: >> On Fri, Nov 03, 2006 at 11:25:09AM -0500, Tom Lane wrote: >>> Since 8.1 has done this all along and no one's actually complained about >>> it, I guess no one is using scripts that do "cd". I'm inclined to go >>> with Bernd's suggestion to change the docs to match the code, but does >>> anyone have a contrary opinion? > +1 Doc bug for 8.2, feature request for 8.3, unless Windows bites. Looking back in the archives, I note that one of the arguments for making the server use relative paths everywhere was so that it'd be robust against things like DBAs moving directories that contain live postmasters. If we provide a %P option, or otherwise encourage people to write scripts that depend on the absolute path of $PGDATA, we'd lose some of this robustness. So that might be an argument for leaving the code as-is indefinitely ... not a very strong argument maybe, but it's more than just we're-too-lazy-to-add-%P. Anyway, I've corrected the documentation in HEAD and 8.1. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Design Considerations for New Authentication Methods
Tom, Josh, etc.: > But if you're looking for a "big application" that uses Kerberos, > there's that pesky thing called Windows. Every single Windows machine in > an active directory domain environment is a Kerberos client, and uses > Kerberos for authentication to all network services. Kerberos with GSSAPI is also widely used for Solaris, so supporting it helps a lot in getting a large proportion of Solaris users to adopt PostgreSQL. > So Kerberos is definitly big. And more and more apps do support GSSAPI > for authentication. Not that many apps support "raw kerberos" as pgsql > does, probably because it does have some compatibility issues and such > things. Yes ... if we were looking to cut down on both code and dependency bugs, we might consider desupporting "raw Kerberos". At this point, I think that everyone who supports Kerberos supports GSSAPI, unless we're still committed to supporting users of Red Hat 7.0 (Tom?). -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] buildfarm project changes
Life moves on, and I have found it increasingly difficult to maintain the buildfarm on a day to day basis (not that it needs much, but my availability is unpredictable). So the good people at CommandPrompt have responded to my plea for help, and are taking over the management of the project. This is good news, as they have hosted the web server from the start, and have generally been very supportive. The point person will be Darcy Buskermolen, who is a long time buildfarm user, and Aurynn Shaw will also be significantly involved. With luck she and I can get the long overdue work to tidy up the web app done. I will still be around doing some fixes and enhancements - I am not walking away from the project. But this will ensure it gets the TLC it needs and deserves, so I am very grateful to Joshua and his team for helping out. cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [PATCHES] CLUSTER micro-doc-patch
"Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > So, I send the small doc-patch saying about the space requirement of the > cluster operation using SELECT ORDER BY. Added, along with some other minor editorial fixes. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] plperl fix issues
I am working on the changes necesssary to fix the plperl issue raised a week or so ago. I have code that can detect if we can safely run two interpreters. However, things are complicated by the fact that plperl.c uses a couple of perl hashes to store data: plperl_proc_hash and plperl_query_hash. The complication arises because we would need to keep switching perl contexts in order to access them. Rather than add this complication I am tempted to replace them with standard postgres dynahashes. Can anyone see any objection? cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Design Considerations for New Authentication Methods
Josh Berkus writes: > Yes ... if we were looking to cut down on both code and dependency bugs, we > might consider desupporting "raw Kerberos". At this point, I think that > everyone who supports Kerberos supports GSSAPI, unless we're still committed > to supporting users of Red Hat 7.0 (Tom?). I have no corporate commitment to make PG 8.3+ work on ancient Red Hat versions, if that's what you mean. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] buildfarm project changes
Andrew, > I am not walking away from the > project. But this will ensure it gets the TLC it needs and deserves, so > I am very grateful to Joshua and his team for helping out. You did a fantastic job putting together the buildfarm, and did more to give us a regular release cycle than I think any other single developer besides Tom Lane. I can understand wanting to move on, now, and I'm glad that CMD is there to take over. Thanks! -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Design Considerations for New Authentication Methods
Tom, > I have no corporate commitment to make PG 8.3+ work on ancient Red Hat > versions, if that's what you mean. Well, in that case my suggestion is that we plan to transition to GSSAPI and drop support for raw Kerberos as soon as Henry is ready with a patch (plus I'm going to try to get the Solaris security folks to kick in on this). GSSAPI is the official API of Kerberos5, and in theory supporting it should reduce the number of specific-library-version dependancy bugs we get with Kerberos in the future. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] ldap: fix resource leak
Neil Conway <[EMAIL PROTECTED]> writes: > I fixed this by adding the appropriate ldap_unbind() calls in error > control paths. An alternative would be to have a single place do the > error handling, and jump to that via goto. Perhaps use a PG_TRY construct? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] PostgreSQL 8.2 (from CVS devel) first impressions
Compiled fine. Still a few warnings (using Fedora Core 6 / AMD64). The new PG_MAGIC_MODULE requirement threw me for a loop. I expect it will catch others off guard as well. One of my complicated queries that I threw at it seems to run about 10% - 20% faster now, which is pretty sweet. The multiline wrap text editor in psql works really well, except it seems to screw up if I resize the terminal. If I restore the terminal to its original size, and refresh, it fixes itself. I'm using it on one of my productions system now, and nothing has failed yet. :-) I'm pretty happy. Good job people. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PostgreSQL 8.2 (from CVS devel) first impressions
On Sun, Nov 05, 2006 at 01:15:51AM -0500, [EMAIL PROTECTED] wrote: > One of my complicated queries that I threw at it seems to run about > 10% - 20% faster now, which is pretty sweet. I take this back. I forgot to 'analyze'. After 'analyze', the times returned to the slower 8.1 times. :-( I will have to investigate. The generated plan is more complex after 'analyze'... Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
