Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite2a186b

Browse files
committed
Add a multi-worker capability to autovacuum. This allows multiple worker
processes to be running simultaneously. Also, now autovacuum processes do notcount towards the max_connections limit; they are counted separately fromregular processes, and are limited by the new GUC variableautovacuum_max_workers.The launcher now has intelligence to launch workers on each database everyautovacuum_naptime seconds, limited only on the max amount of worker slotsavailable.Also, the global worker I/O utilization is limited by the vacuum cost-baseddelay feature. Workers are "balanced" so that the total I/O consumption doesnot exceed the established limit. This part of the patch was contributed byITAGAKI Takahiro.Per discussion.
1 parent42dc4b6 commite2a186b

File tree

12 files changed

+1171
-159
lines changed

12 files changed

+1171
-159
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 24 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.119 2007/04/02 15:27:02 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.120 2007/04/16 18:29:50 alvherre Exp $ -->
22

33
<chapter Id="runtime-config">
44
<title>Server Configuration</title>
@@ -3166,7 +3166,7 @@ SELECT * FROM parent WHERE key = 2400;
31663166
<listitem>
31673167
<para>
31683168
Controls whether the server should run the
3169-
autovacuum daemon. This isoff by default.
3169+
autovacuumlauncherdaemon. This ison by default.
31703170
<varname>stats_start_collector</> and <varname>stats_row_level</>
31713171
must also be turned on for autovacuum to work.
31723172
This parameter can only be set in the <filename>postgresql.conf</>
@@ -3175,16 +3175,31 @@ SELECT * FROM parent WHERE key = 2400;
31753175
</listitem>
31763176
</varlistentry>
31773177

3178+
<varlistentry id="guc-autovacuum-max-workers" xreflabel="autovacuum_max_workers">
3179+
<term><varname>autovacuum_max_workers</varname> (<type>integer</type>)</term>
3180+
<indexterm>
3181+
<primary><varname>autovacuum_max_workers</> configuration parameter</primary>
3182+
</indexterm>
3183+
<listitem>
3184+
<para>
3185+
Specifies the maximum number of autovacuum processes (other than the
3186+
autovacuum launcher) which may be running at any one time. The default
3187+
is three (<literal>3</literal>). This parameter can only be set in
3188+
the <filename>postgresql.conf</> file or on the server command line.
3189+
</para>
3190+
</listitem>
3191+
</varlistentry>
3192+
31783193
<varlistentry id="guc-autovacuum-naptime" xreflabel="autovacuum_naptime">
31793194
<term><varname>autovacuum_naptime</varname> (<type>integer</type>)</term>
31803195
<indexterm>
31813196
<primary><varname>autovacuum_naptime</> configuration parameter</primary>
31823197
</indexterm>
31833198
<listitem>
31843199
<para>
3185-
Specifies the delay betweenactivity rounds for the autovacuum
3186-
daemon. In each round the daemon examinesone database
3187-
and issues <command>VACUUM</> and <command>ANALYZE</> commands
3200+
Specifies theminimumdelay betweenautovacuum runs on any given
3201+
database. In each round the daemon examinesthe
3202+
databaseand issues <command>VACUUM</> and <command>ANALYZE</> commands
31883203
as needed for tables in that database. The delay is measured
31893204
in seconds, and the default is one minute (<literal>1m</>).
31903205
This parameter can only be set in the <filename>postgresql.conf</>
@@ -3318,7 +3333,10 @@ SELECT * FROM parent WHERE key = 2400;
33183333
Specifies the cost limit value that will be used in automatic
33193334
<command>VACUUM</> operations. If <literal>-1</> is specified (which is the
33203335
default), the regular
3321-
<xref linkend="guc-vacuum-cost-limit"> value will be used.
3336+
<xref linkend="guc-vacuum-cost-limit"> value will be used. Note that
3337+
the value is distributed proportionally among the running autovacuum
3338+
workers, if there is more than one, so that the sum of the limits of
3339+
each worker never exceeds the limit on this variable.
33223340
This parameter can only be set in the <filename>postgresql.conf</>
33233341
file or on the server command line.
33243342
This setting can be overridden for individual tables by entries in

‎doc/src/sgml/maintenance.sgml

Lines changed: 36 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.70 2007/02/01 19:10:24 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.71 2007/04/16 18:29:50 alvherre Exp $ -->
22

33
<chapter id="maintenance">
44
<title>Routine Database Maintenance Tasks</title>
@@ -466,26 +466,43 @@ HINT: Stop the postmaster and use a standalone backend to VACUUM in "mydb".
466466
<secondary>general information</secondary>
467467
</indexterm>
468468
<para>
469-
Beginning in <productname>PostgreSQL</productname> 8.1, there isa
470-
separateoptionalserver processcalledthe<firstterm>autovacuum
471-
daemon</firstterm>,whose purpose is to automate the execution of
469+
Beginning in <productname>PostgreSQL</productname> 8.1, there isan
470+
optionalfeaturecalled <firstterm>autovacuum</firstterm>,
471+
whose purpose is to automate the execution of
472472
<command>VACUUM</command> and <command>ANALYZE </command> commands.
473-
When enabled,theautovacuum daemon runs periodically and checks for
473+
When enabled, autovacuum checks for
474474
tables that have had a large number of inserted, updated or deleted
475475
tuples. These checks use the row-level statistics collection facility;
476-
therefore,theautovacuum daemon cannot be used unless <xref
476+
therefore, autovacuum cannot be used unless <xref
477477
linkend="guc-stats-start-collector"> and <xref
478-
linkend="guc-stats-row-level"> are set to <literal>true</literal>. Also,
479-
it's important to allow a slot for the autovacuum process when choosing
480-
the value of <xref linkend="guc-superuser-reserved-connections">. In
481-
the default configuration, autovacuuming is enabled and the related
478+
linkend="guc-stats-row-level"> are set to <literal>true</literal>.
479+
In the default configuration, autovacuuming is enabled and the related
482480
configuration parameters are appropriately set.
483481
</para>
484482

485483
<para>
486-
The autovacuum daemon, when enabled, runs every <xref
487-
linkend="guc-autovacuum-naptime"> seconds. On each run, it selects
488-
one database to process and checks each table within that database.
484+
Beginning in <productname>PostgreSQL</productname> 8.3, autovacuum has a
485+
multi-process architecture: there is a daemon process, called the
486+
<firstterm>autovacuum launcher</firstterm>, which is in charge of starting
487+
an <firstterm>autovacuum worker</firstterm> process on each database every
488+
<xref linkend="guc-autovacuum-naptime"> seconds.
489+
</para>
490+
491+
<para>
492+
There is a limit of <xref linkend="guc-autovacuum-max-workers"> worker
493+
processes that may be running at at any time, so if the <command>VACUUM</>
494+
and <command>ANALYZE</> work to do takes too long to run, the deadline may
495+
be failed to meet for other databases. Also, if a particular database
496+
takes long to process, more than one worker may be processing it
497+
simultaneously. The workers are smart enough to avoid repeating work that
498+
other workers have done, so this is normally not a problem. Note that the
499+
number of running workers does not count towards the <xref
500+
linkend="guc-max-connections"> nor the <xref
501+
linkend="guc-superuser-reserved-connections"> limits.
502+
</para>
503+
504+
<para>
505+
On each run, the worker process checks each table within that database, and
489506
<command>VACUUM</command> or <command>ANALYZE</command> commands are
490507
issued as needed.
491508
</para>
@@ -581,6 +598,12 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
581598
</para>
582599
</caution>
583600

601+
<para>
602+
When multiple workers are running, the cost limit is "balanced" among all
603+
the running workers, so that the total impact on the system is the same,
604+
regardless of the number of workers actually running.
605+
</para>
606+
584607
</sect2>
585608
</sect1>
586609

‎src/backend/commands/vacuum.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
*
1414
*
1515
* IDENTIFICATION
16-
* $PostgreSQL: pgsql/src/backend/commands/vacuum.c,v 1.349 2007/03/14 18:48:55 tgl Exp $
16+
* $PostgreSQL: pgsql/src/backend/commands/vacuum.c,v 1.350 2007/04/16 18:29:50 alvherre Exp $
1717
*
1818
*-------------------------------------------------------------------------
1919
*/
@@ -3504,6 +3504,9 @@ vacuum_delay_point(void)
35043504

35053505
VacuumCostBalance=0;
35063506

3507+
/* update balance values for workers */
3508+
AutoVacuumUpdateDelay();
3509+
35073510
/* Might have gotten an interrupt while sleeping */
35083511
CHECK_FOR_INTERRUPTS();
35093512
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp