|
1 | | -<!-- $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.92 2009/04/06 17:56:31 momjian Exp $ --> |
| 1 | +<!-- $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.93 2009/04/23 10:09:11 heikki Exp $ --> |
2 | 2 |
|
3 | 3 | <chapter id="maintenance"> |
4 | 4 | <title>Routine Database Maintenance Tasks</title> |
|
375 | 375 | </para> |
376 | 376 |
|
377 | 377 | <para> |
378 | | - <command>VACUUM</>'s behavior is controlled by the two configuration |
379 | | - parameters: <xref linkend="guc-vacuum-freeze-min-age"> and |
380 | | - <xref linkend="guc-vacuum-freeze-table-age">. |
381 | | - <varname>vacuum_freeze_table_age</> controls when <command>VACUUM</> |
382 | | - performs a full sweep of the table, in order to replace old XID values |
383 | | - with <literal>FrozenXID</>. <varname>vacuum_freeze_min_age</> |
| 378 | + <command>VACUUM</> normally skips pages that don't have any dead row |
| 379 | + versions, but those pages might still have tuples with old XID values. |
| 380 | + To replace them too, a scan of the whole table is needed. |
| 381 | + <xref linkend="guc-vacuum-freeze-table-age"> controls when |
| 382 | + <command>VACUUM</> does that: a whole table sweep is forced if |
| 383 | + the table hasn't been fully scanned for <varname>vacuum_freeze_table_age</> |
| 384 | + - <varname>vacuum_freeze_min_age</> transactions. Setting it to 0 |
| 385 | + makes <command>VACUUM</> to ignore the visibility map and always scan all |
| 386 | + pages. |
| 387 | + </para> |
| 388 | + |
| 389 | + <para> |
| 390 | + <xref linkend="guc-vacuum-freeze-min-age"> |
384 | 391 | controls how old an XID value has to be before it's replaced with |
385 | | - <literal>FrozenXID</>. Larger values ofthese settings |
| 392 | + <literal>FrozenXID</>. Larger values ofthis setting |
386 | 393 | preserve transactional information longer, while smaller values increase |
387 | 394 | the number of transactions that can elapse before the table must be |
388 | 395 | vacuumed again. |
|
416 | 423 | </para> |
417 | 424 |
|
418 | 425 | <para> |
419 | | - The sole disadvantage of increasing <varname>vacuum_freeze_table_age</> |
420 | | - and <varname>autovacuum_freeze_max_age</> |
| 426 | + The effective maximum for <varname>vacuum_table_age</> is 0.95 * |
| 427 | + <varname>autovacuum_freeze_max_age</>; a setting higher than that will be |
| 428 | + capped to that maximum. A value higher than |
| 429 | + <varname>autovacuum_freeze_max_age</> wouldn't make sense because an |
| 430 | + anti-wraparound autovacuum would be triggered at that point anyway, and |
| 431 | + the 0.95 multiplier leaves some breathing room to run a manual |
| 432 | + <command>VACUUM</> before that happens. As a rule of thumb, |
| 433 | + <command>vacuum_freeze_table_age</> should be set to a value somewhat |
| 434 | + below <varname>autovacuum_freeze_max_age</>, leaving enough gap so that |
| 435 | + a regularly scheduled <command>VACUUM</> or an autovacuum triggered by |
| 436 | + normal delete and update activity is run in that window. Setting it too |
| 437 | + close could lead to anti-wraparound autovacuums, even though the table |
| 438 | + was recently vacuumed to reclaim space, whereas lower values lead to more |
| 439 | + frequent whole-table scans. |
| 440 | + </para> |
| 441 | + |
| 442 | + <para> |
| 443 | + The sole disadvantage of increasing <varname>autovacuum_freeze_max_age</> |
| 444 | + (and <varname>vacuum_freeze_table_age</> along with it) |
421 | 445 | is that the <filename>pg_clog</> subdirectory of the database cluster |
422 | 446 | will take more space, because it must store the commit status for all |
423 | 447 | transactions back to the <varname>autovacuum_freeze_max_age</> horizon. |
424 | 448 | The commit status uses two bits per transaction, so if |
425 | 449 | <varname>autovacuum_freeze_max_age</> has its maximum allowed value of |
426 | 450 | a little less than two billion, <filename>pg_clog</> can be expected to |
427 | 451 | grow to about half a gigabyte. If this is trivial compared to your |
428 | | - total database size, setting <varname>autovacuum_freeze_max_age</> and |
429 | | - <varname>vacuum_freeze_table_age</varname> to their maximum allowed values |
430 | | - is recommended. Otherwise, set them depending |
| 452 | + total database size, setting <varname>autovacuum_freeze_max_age</> to |
| 453 | + its maximum allowed value is recommended. Otherwise, set it depending |
431 | 454 | on what you are willing to allow for <filename>pg_clog</> storage. |
432 | 455 | (The default, 200 million transactions, translates to about 50MB of |
433 | 456 | <filename>pg_clog</> storage.) |
@@ -470,24 +493,19 @@ SELECT datname, age(datfrozenxid) FROM pg_database; |
470 | 493 | </programlisting> |
471 | 494 |
|
472 | 495 | The <literal>age</> column measures the number of transactions from the |
473 | | - cutoff XID to the current transaction's XID. When <command>VACUUM</> |
| 496 | + cutoff XID to the current transaction's XID. <command>VACUUM</> normally |
| 497 | + only scans pages that have been modified since last vacuum, but |
| 498 | + <structfield>relfrozenxid</> can only be advanced when the whole table is |
| 499 | + scanned. The whole table is scanned when <structfield>relfrozenxid</> is |
| 500 | + more than <varname>vacuum_freeze_table_age</> transactions old, if |
| 501 | + <command>VACUUM FREEZE</> command is used, or if all pages happen to |
| 502 | + require vacuuming to remove dead row versions. When <command>VACUUM</> |
474 | 503 | scans the whole table, after it's finished <literal>age(relfrozenxid)</> |
475 | 504 | should be a little more than the <varname>vacuum_freeze_min_age</> setting |
476 | 505 | that was used (more by the number of transactions started since the |
477 | | - <command>VACUUM</> started). |
478 | | - </para> |
479 | | - |
480 | | - <para> |
481 | | - <command>VACUUM</> normally only scans pages that have been modified |
482 | | - since last vacuum, but <structfield>relfrozenxid</> can only be advanced |
483 | | - when the whole table is scanned. The whole table is scanned when |
484 | | - <structfield>relfrozenxid</> is more than |
485 | | - <varname>vacuum_freeze_table_age</> transactions old, if |
486 | | - <command>VACUUM FREEZE</> command is used, or if all pages happen to |
487 | | - require vacuuming to remove dead row versions. If no whole-table-scanning |
488 | | - <command>VACUUM</> is issued on the table until |
489 | | - <varname>autovacuum_freeze_max_age</> is reached, an autovacuum will soon |
490 | | - be forced for the table. |
| 506 | + <command>VACUUM</> started). If no whole-table-scanning <command>VACUUM</> |
| 507 | + is issued on the table until <varname>autovacuum_freeze_max_age</> is |
| 508 | + reached, an autovacuum will soon be forced for the table. |
491 | 509 | </para> |
492 | 510 |
|
493 | 511 | <para> |
@@ -599,7 +617,11 @@ vacuum threshold = vacuum base threshold + vacuum scale factor * number of tuple |
599 | 617 | collector; it is a semi-accurate count updated by each |
600 | 618 | <command>UPDATE</command> and <command>DELETE</command> operation. (It |
601 | 619 | is only semi-accurate because some information might be lost under heavy |
602 | | - load.) |
| 620 | + load.) If the <structfield>relfrozenxid</> value of the table is more |
| 621 | + than <varname>vacuum_freeze_table_age</> transactions old, the whole |
| 622 | + table is scanned to freeze old tuples and advance |
| 623 | + <structfield>relfrozenxid</>, otherwise only pages that have been modified |
| 624 | + since last vacuum are vacuumed. |
603 | 625 | </para> |
604 | 626 |
|
605 | 627 | <para> |
|