Re: [PATCHES] [HACKERS] Autovacuum loose ends
On Fri, Jul 29, 2005 at 03:33:09PM -0400, Tom Lane wrote: It occurs to me that vacuuming to prevent XID wraparound is not the only reason to do DB-wide vacuums: we also need to keep pg_database.datvacuumxid from getting too old, else we will have problems with clog bloat. We may need to rethink the test used. I was unable to come up with a reasonable test for this. How would we determine what is too old? Of course, I could pick any number from thin air, if that was what you were thinking. Going forward (8.2) I think this should also be handled on a table per table basis, just like the freeze Xid limit. OTOH I just saw this comment in createdb(): /* * Normally we mark the new database with the same datvacuumxid and * datfrozenxid as the source. However, if the source is not allowing * connections then we assume it is fully frozen, and we can set the * current transaction ID as the xid limits. This avoids immediately * starting to generate warnings after cloning template0. */ This means that if the user manages to unfreeze a database, disallow connections, and later use it as a template, we could suffer Xid- wraparound data loss in the new database. Should we rethink this? Sadly, the only interface for disallowing connections is to manually update pg_database, so it's impossible to raise a warning about it, or something; and it's quite likely that people will disallow connections without reading the proper documentation. (They do such things all the time). -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) La experiencia nos dice que el hombre peló millones de veces las patatas, pero era forzoso admitir la posibilidad de que en un caso entre millones, las patatas pelarían al hombre (Ijon Tichy) ---(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: [PATCHES] [HACKERS] Autovacuum loose ends
Alvaro Herrera [EMAIL PROTECTED] writes: On Fri, Jul 29, 2005 at 03:33:09PM -0400, Tom Lane wrote: It occurs to me that vacuuming to prevent XID wraparound is not the only reason to do DB-wide vacuums: we also need to keep pg_database.datvacuumxid from getting too old, else we will have problems with clog bloat. We may need to rethink the test used. I was unable to come up with a reasonable test for this. How would we determine what is too old? Well, it depends what you think is too much space for pg_clog. If we just follow the standard anti-wrap policy, we'll vacuum at least once every half billion transactions, so pg_clog could be expected to grow to about 125Mb, which maybe isn't a problem these days. OTOH I just saw this comment in createdb(): /* * Normally we mark the new database with the same datvacuumxid and * datfrozenxid as the source. However, if the source is not allowing * connections then we assume it is fully frozen, and we can set the * current transaction ID as the xid limits. This avoids immediately * starting to generate warnings after cloning template0. */ This means that if the user manages to unfreeze a database, disallow connections, and later use it as a template, we could suffer Xid- wraparound data loss in the new database. Should we rethink this? I don't think so. Fooling with a template database is risky in any case, and the fact that autovacuum might save your bacon (if you are running autovacuum) doesn't make it less so. BTW, it strikes me that there is one serious error in the current autovac logic: it does VACUUM ANALYZE rather than merely VACUUM when doing XID-wrap protection. This means that it actively introduces unfrozen tuples into template databases, which is A Bad Move. We should just VACUUM, instead. Sadly, the only interface for disallowing connections is to manually update pg_database, As of now, we have a documented way of disallowing connections that doesn't involve messing with datallowconn, so this argument seems a lot weaker than it might have awhile back. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Autovacuum loose ends
Here is another patch for autovacuum:
- vacuum_cost_delay and vacuum_cost_limit can be set per table, as well
as globally with autovacuum_vacuum_cost_{limit,delay}
- pgstat is reset if recovery is required
- pgstat reset at postmaster start is disabled by default
- Xid-wraparound VACUUM is now FULL without ANALYZE
Note that because of the cost parameters, I changed the vacuum call in a
per-table call instead of passing a list of Oids. This could be changed
by having two separate lists, one which uses the default values and
other for the rest, but it hardly seems worth the trouble.
(This patch requires catversion bump.)
On Sun, Jul 31, 2005 at 03:15:35PM -0400, Tom Lane wrote:
BTW, it strikes me that there is one serious error in the current
autovac logic: it does VACUUM ANALYZE rather than merely VACUUM
when doing XID-wrap protection. This means that it actively introduces
unfrozen tuples into template databases, which is A Bad Move. We
should just VACUUM, instead.
True. Changed in the attached patch.
I think this completes our expectations for 8.1, doesn't it? Now we
only need the documentation.
--
Alvaro Herrera (alvherre[a]alvh.no-ip.org)
I call it GNU/Linux. Except the GNU/ is silent. (Ben Reiter)
Index: doc/src/sgml/catalogs.sgml
===
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.109
diff -c -r2.109 catalogs.sgml
*** doc/src/sgml/catalogs.sgml 26 Jul 2005 16:38:25 - 2.109
--- doc/src/sgml/catalogs.sgml 31 Jul 2005 20:19:20 -
***
*** 1187,1192
--- 1187,1206
entryMultiplier for reltuples to add to
structfieldanl_base_thresh//entry
/row
+
+row
+ entrystructfieldvac_cost_delay/structfield/entry
+ entrytypeinteger/type/entry
+ entry/entry
+ entryCustom variablevacuum_cost_delay/ parameter/entry
+/row
+
+row
+ entrystructfieldvac_cost_limit/structfield/entry
+ entrytypeinteger/type/entry
+ entry/entry
+ entryCustom variablevacuum_cost_limit/ parameter/entry
+/row
/tbody
/tgroup
/table
***
*** 1207,1213
para
Any of the numerical fields can contain literal-1/ (or indeed
any negative value) to indicate that the system-wide default should
!be used for this particular value.
/para
/sect1
--- 1221,1231
para
Any of the numerical fields can contain literal-1/ (or indeed
any negative value) to indicate that the system-wide default should
!be used for this particular value. Observe that the
!structfieldvac_cost_delay/ variable inherits its default value from the
!varnameautovacuum_vacuum_cost_delay/ configuration parameter,
!or from varnamevacuum_cost_delay/ if the former is set to a negative
!value. The same applies to structfieldvac_cost_limit/.
/para
/sect1
Index: doc/src/sgml/runtime.sgml
===
RCS file: /home/alvherre/cvs/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.341
diff -c -r1.341 runtime.sgml
*** doc/src/sgml/runtime.sgml 30 Jul 2005 17:15:35 - 1.341
--- doc/src/sgml/runtime.sgml 31 Jul 2005 23:22:11 -
***
*** 3399,3404
--- 3399,3436
/listitem
/varlistentry
+ varlistentry id=guc-autovacuum-vacuum-cost-delay
xreflabel=autovacuum_vacuum_cost_delay
+ termvarnameautovacuum_vacuum_cost_delay/varname
(typeinteger/type)/term
+ indexterm
+primaryvarnameautovacuum_vacuum_cost_delay/ configuration
parameter/primary
+ /indexterm
+ listitem
+para
+ Specifies the default value that will be applied to each
+ commandVACUUM/ operation, for tables that do not have
+ a default value set in structnamepg_autovacuum/. If a
+ negative value is specified (like the default value of -1),
+ the varnamevacuum_cost_delay/ value will be applied instead.
+/para
+ /listitem
+ /varlistentry
+
+ varlistentry id=guc-autovacuum-cost-limit
xreflabel=autovacuum_vacuum_cost_limit
+ termvarnameautovacuum_vacuum_cost_limit/varname
(typeinteger/type)/term
+ indexterm
+primaryvarnameautovacuum_vacuum_cost_limit/ configuration
parameter/primary
+ /indexterm
+ listitem
+para
+ Specifies the default value that will be applied to each
+ commandVACUUM/ operation, for tables that do not have
+ a default value set in structnamepg_autovacuum/. If a
+ negative value is specified (like the default value of -1),
+ the varnamevacuum_cost_limit/ value will be applied instead.
+/para
+ /listitem
+ /varlistentry
+
/variablelist
/sect2
Index: src/backend/access/transam/xlog.c
Re: [PATCHES] [HACKERS] For review: Server instrumentation patch
Dave Page wrote:
[Resent as the list seems to have rejected yesterdays attempt]
As per Bruce's request, here's a copy of Andreas' server
instrumentation patch for review. I've separated out the
dbsize stuff and pg_terminate_backend is also not included.
This version was generated against CVS today.
As far as I can tell from review of comments made back to
pre-8.0, all security and other concerns raised have been addressed.
Here is a modified version of your patch that adds functions to do
configuration file reload, and log file rotation.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/func.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.275
diff -c -c -r1.275 func.sgml
*** doc/src/sgml/func.sgml 30 Jul 2005 22:53:15 - 1.275
--- doc/src/sgml/func.sgml 1 Aug 2005 02:22:48 -
***
*** 9069,9074
--- 9069,9077
indexterm zone=functions-admin
primarypg_cancel_backend/primary
/indexterm
+indexterm zone=functions-admin
+ primarypg_reload_conf/primary
+/indexterm
indexterm zone=functions-admin
primarysignal/primary
***
*** 9076,9082
/indexterm
para
! The function shown in xref
linkend=functions-admin-signal-table sends control signals to
other server processes. Use of this function is restricted
to superusers.
--- 9079,9085
/indexterm
para
! The functions shown in xref
linkend=functions-admin-signal-table sends control signals to
other server processes. Use of this function is restricted
to superusers.
***
*** 9098,9118
entrytypeint/type/entry
entryCancel a backend's current query/entry
/row
/tbody
/tgroup
/table
para
! This function returns 1 if successful, 0 if not successful.
The process ID (literalpid/literal) of an active backend can be found
from the structfieldprocpid/structfield column in the
structnamepg_stat_activity/structname view, or by listing the
commandpostgres/command
processes on the server with applicationps/.
/para
!
!indexterm zone=functions-admin
! primarypg_start_backup/primary
!/indexterm
indexterm zone=functions-admin
primarypg_stop_backup/primary
--- 9101,9129
entrytypeint/type/entry
entryCancel a backend's current query/entry
/row
+ row
+entry
+ literalfunctionpg_reload_conf/function()/literal
+ /entry
+entrytypeint/type/entry
+entryTriggers the server processes to reload configuration
files/entry
+ /row
/tbody
/tgroup
/table
para
! These functions return 1 if successful, 0 if not successful.
The process ID (literalpid/literal) of an active backend can be found
from the structfieldprocpid/structfield column in the
structnamepg_stat_activity/structname view, or by listing the
commandpostgres/command
processes on the server with applicationps/.
/para
!para
! functionpg_reload_conf/ sends a SIGHUP event to the
! postmaster, and thus triggers a reload of the configuration files
! in all backend processes.
!/para
indexterm zone=functions-admin
primarypg_stop_backup/primary
Index: src/backend/postmaster/postmaster.c
===
RCS file: /cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.461
diff -c -c -r1.461 postmaster.c
*** src/backend/postmaster/postmaster.c 29 Jul 2005 19:30:04 - 1.461
--- src/backend/postmaster/postmaster.c 1 Aug 2005 02:22:50 -
***
*** 3393,3398
--- 3393,3403
}
}
+ if (CheckPostmasterSignal(PMSIGNAL_ROTATE_LOGFILE) SysLoggerPID != 0)
+ {
+ kill(SysLoggerPID, SIGUSR1);
+ }
+
PG_SETMASK(UnBlockSig);
errno = save_errno;
Index: src/backend/postmaster/syslogger.c
===
RCS file: /cvsroot/pgsql/src/backend/postmaster/syslogger.c,v
retrieving revision 1.18
diff -c -c -r1.18 syslogger.c
*** src/backend/postmaster/syslogger.c 21 Jul 2005 18:06:12 - 1.18
--- src/backend/postmaster/syslogger.c 1 Aug 2005 02:22:50 -
***
*** 101,106
--- 101,107
* Flags set by interrupt handlers for later service in the main loop.
*/
static volatile sig_atomic_t got_SIGHUP = false;
+ static volatile sig_atomic_t rotation_requested = false;
/* Local subroutines */
***
