No image

Let's Study Vacuum 2

Part 2. How to use Autovacuum

Vacuum Index

Part1: Definition and Necessity of Vacuum / Vacuum Execution Structure / Standard Vacuum vs. Vacuum full

Part2: XID Wraparound, Freeze / VM, FSM structure analysis

Part3: Autovacuum

 

4. XID Wraparound, Freeze

     

  • MVCC technique for transactions: Process and compare transaction IDs (XIDs) as numbers
  • Can be expressed up to 232 (approximately 4 billion)
  • Old XID(2 billion) + New XID(2 billion)
  • Old XID 2 billion visible, new XID 2 billion invisible
  • Use as it continues to circulate

  • XID wrapperound causes loss of old data
  • Occurs only in Insert–only tables

 

  • Tuples are marked as "FREEZE", and this FrozenXID is always shown because it is always excluded from normal XID comparison targets.
  • If you change the FrozenXID, the persistent archive, before 2 billion transactions occur, you can avoid XID overlap errors.
  • These changes(data freezing) are processed with the VACUUM FREEZE command

 

 Vacuum Count=0   Vacuum Freeze only when it’s over 50 million

 Vacuum Count>0   Vacuum Freeze only when it’s over 150 million

Vacuum Count=0 Scenario

create table e2 ( c1 char(15),c2 char(15),c3 char(689) );
insert into e2 SELECT 'Tuple'||generate_series(1001,2000),'A',' ';
  • psql -x < ./fsm_vm/tab.sql

 

  • Jump Current Xid -> 52,428,800 (pg_resetxlog)
  • psql -x < ./fsm_vm/tab.sql

 

  • update t2 set c2='B' where c1 >= 'Tuple1700' ;
  • psql -x < ./fsm_vm/tab.sql

 

  • ./fsm_vm/pageinspect.sql

 

Vacuum Count > 0 Scenario

create table e2 ( c1 char(15),c2 char(15),c3 char(689) );
INSERT INTO e2 SELECT 'Tuple'||generate_series(1001,2000),'A',' ';
  • psql -x < ./fsm_vm/tab.sql

 

  • psql -c "vacuum freeze t2;"
  • psql -x < ./fsm_vm/tab.sql

 

  • Jump Current Xid -> 52,428,800 (pg_resetxlog)
  • psql -x < ./fsm_vm/tab.sql

 

  • psql -c "update t2 set c2='B' where c1 > 'Tuple1700' ;"
  • psql -x < ./fsm_vm/tab.sql
  • Jump Current Xid è 157,286,400 (pg_resetxlog)
  • psql -x < ./fsm_vm/tab.sql
  • psql -c "update t2 set c2='C' where c1 > 'Tuple1700' ;"
  • psql -x < ./fsm_vm/tab.sql

5. Autovacuum

 What is AUTOVACUUM?    automatically execute the VACUUM and ANALYZE commands periodically

  • Run Standard Vacuum periodically to free up space to avoid Vacuum Full operations as much as possible.
  • Use optimal disk space, not minimal disk space

  • Autovacuum launcher: Manage the running time of the worker process so that you can work on one database at a time at the interval of seconds specified by the Autovacuum_naptime value
  • Autovacuum workers: Maximum number of Autovacuum_max_workers worker processes

Vacuum work should be done regularly at night when I have less work to do!

A task with many changes occur

In the worst case, execute Vacuum Full

  • Autovacuum daemon can automatically proceed with Vacuum work on unexpected situations to avoid the problems above.
  • Do not turn off autovacuum without knowing the exact database usage.
  • If you do not use the AutoVacuum daemon, you must perform Vacuum operations on all the DBs in use by the DB server.
  • If the Autovacuum daemon is added, changed, or deleted from the table data, it shall be considered as an unconditional work. (However, the temporary tables are not eligible)

 

  • autovacuum_analyze_scale_factorWhen starting analyze as the autovacuum_analyze_threshold value excess in a table, the additional amount of change in table size (the default is 0.1=10% of table size)
  • autovacuum_vacuum_threshold / autovacuum_analyze_thresholdThe minimum number of change to start the work which is needed to trigger VACUUM/ANALYZE if the updated or deleted tuples are amount of set value in any one table.
  • autovacuum_freeze_max_ageVacuum operation is performed if the pg_class.relfrozenxid value is greater than the setting after the last vacuum operation within a table is specified.
  • autovacuum_naptime: ‘activity rounds interval’ for autovacuum daemon, which is the resting time after autovacuum done its work (default: 1 minute-set as seconds)
  • autovacuum_vacuum_cost_delay: The maximum amount of time to stop when the cost of autovacuum daemon is overused (default: 20 millisecond)
  • autovacuum_vacuum_cost_limitMaximum cost available for autovacuum daemon (default -1)
  • autovacuum_vacuum_scale_factor: the additional amount of change in table size when the vacuum operation is started because the autovacuum_vacuum_threshold value is exceeded within a table.

Working standard of Autovacuum

1. If the age of table is greater than the number of transaction specified by autovacuum_freeze_max_age, the table must be VACUUMed.

  • age of table: the relfrozenxid column value investigated by age() function

2. VACUUM if the data in the table has changed and exceeded the Vacuum threshold.

  • vacuum threshold = vacuum initial threshold + vacuum scale value * number of tuples
  • vacuum initial threshold: autovacuum_vacuum_threshold
  • vacuum scale value: autovacuum_vacuum_scale_factor
  • number of tuples: pg_class.reltuples

3. ANALYZE if the data exceeded the Analyze threshold as the table data has changed

  • Analyze threshold = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * pg_class.retuples

 

  • autovacuum_vacuum_scale_factor=0.2
  • autovacuum_vacuum_threshold=50
  1. Insert 1000 cases
  2. Check after wait if autovacuum_naptime=60s or higher: autovacuum NOT OK, autoanalyze OK.
  3. Update 200 cases that autovacuum_vacuum_scale_factor=0.2 -> Check after wait : autovacuum NOT OK.
  4. Update 50 cases that autovacuum_vacuum_thresold=50 -> Check after wait: autovacuum NOT OK.
  5. Only one case updated -> check after wait: autovacuum OK.

** autovacuum vacuum target condition: changed case number (except insert) > ( reltuples * 0.2 (autovacuum_vacuum_scale_factor) ) + 50 (autovacuum_vacuum_scale_factor)

  • autovacuum_vacuum_scale_factor=0.1
  • autovacuum_vacuum_threshold=50
  1. Insert 1000 cases
  2. Check after wait if autovacuum_naptime=60s or higher: autoanalyze OK.
  3. Insert 100 cases that autovacuum_analyze_scale_factor=0.1 -> Check after wait : autoanalyze NOT OK.
  4. Insert 50 cases that autovacuum_analyze_threshold=50 -> Check after wait : autoanalyze NOT OK.
  5. Only one case inserted -> check after wait: autoanalyze OK.

** autovacuum analyze target condition: changed case number (including insert) > ( reltuples * 0.1 (autovacuum_analyze_scale_factor) ) + 50 (autovacuum_analyze_scale_factor)

Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]