Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ PostgreSQL vacuum principle of a function and parameters     - RHEL7 system making use of OpenStack mirror (Linux)

- Java reflection Introduction (Programming)

- CentOS 6.5 Linux System Customization and Packaging Quick Implementation Script (Linux)

- Use custom backup plans for Debian backupninja (Linux)

- How to track performance issues when using the Unity Game Development Android (Programming)

- Ubuntu is expected to allow you to install the latest Nvidia Linux drivers easier (Linux)

- Python KNN algorithm of actual realization (Programming)

- Log in CentOS 6.5 Multi-user setting VNC (Server)

- MySQL5.6.17 compiler installation under CentOS (Database)

- Linux, modify the fstab file system can not start causing solve one case (Linux)

- Linux Log (Linux)

- Oracle RMAN backups of the control file backup (Database)

- curl Usage: Being the first site header and status codes (Server)

- A command to install Sublime Text 3 on Manjaro / Archlinux (Linux)

- Oracle ORA-01691 error message, a single data file size limit problem (Database)

- Linux beginners to develop the seven habits (Linux)

- The array of C language (Programming)

- SQL Server memory Misunderstanding (Database)

- Linux operating system Start Tutorial: Xmanager Remote Access Linux graphical interface (Linux)

- Ubuntu install OpenMPI (Linux)

  PostgreSQL vacuum principle of a function and parameters
  Add Date : 2017-08-31      
  From the previous article "PostgreSQL MVCC source implementation", we know that, like Oracle does not like Oracle to store the old version of the undo; Instead of the old version stored directly in the relation file. Then bring the problem is dead tuple too much, resulting in increasing the relationship between the file and space expansion problems. In order to solve this problem, PG in the introduction of the vacuum background process, specifically to clean up the dead tuple, and shrink the space. Of course, the role of vacuum not only in this, it has the following other functions:

First, vacuum function

1. Recycling space

    This is usually the easiest to think of all the features. Recovery space, the dead tuple cleared. But the space has been allocated, the general will not be released. Unless doing vacuum full, but need exclusive lock. Generally not recommended, because if the table eventually will rise to the high water level, often do vacuum full meaning is not very large. General reasonable set vacuum parameters, the conventional vacuum is enough.

2. Freeze the tuple's xid

  PG will be in each record (tuple) in the header, stored xmin, xmax information (additions and deletions to change the transaction ID). The maximum value of transactionID is 32, that is, the unsigned integer. When transactionID exceeds this maximum, it is recycled.

This poses a problem: the TransactionID of the latest transaction is less than the transactionID of the old transaction. If this happens, there is no way by PG to distinguish transaction transactionID, there is no way to achieve the MVCC. Therefore, PG vacuum with the background process, according to a certain cycle and algorithm to trigger the vacuum action, the old tuple header in the transaction ID to freeze. Freeze Transaction ID, which sets the transaction ID to "2" ( "0" for invalid transaction ID; "1" for bootstrap, or "3" for minimum transaction ID). The PG considers that the frozen transaction ID is older than any transaction. This will not appear above the situation.

3. Update the statistics

    Vacuum analysis, the statistics will be updated so that the PG planner can calculate a more accurate implementation plan. Autovacuum_analyze_threshold and autovacuum_analyze_scale_factor parameters can control the frequency of the trigger analysis.

4. Update the visibility map

In the PG, there is a visibility map used to mark those page is not dead tuple. This has two advantages, one is when vacuum scan, you can skip these pages directly. Second, the index-only scan, you can check the next visibility map. This reduces the fetch tuple visibility judgment, thereby reducing IO operations, improve performance. In addition, the relative visibility map relative to the whole, or much smaller, you can cache to memory.

Second, vacuum parameters introduced

Autovacuum has a lot of parameters, used to control their behavior, generally have the following:
 Autovacuum: The default is on, which indicates whether or not autovacuum is enabled. Open by default. In particular, when the xid needs to be frozen, the PG will be vacuumed even if this value is off.
 Autovacuum_naptime: the next vacuum time, the default 1min. This naptime will be assigned to each DB by the vacuum launcher. Autovacuum_naptime / num of db.
 Log_autovacuum_min_duration: Logs the autovacuum action to the log file, when the vacuum action exceeds this value. "-1" means not to record. "0" means that each record.
 Autovacuum_max_workers: maximum number of concurrently running workers, not including the launcher itself.
 Autovacuum_vacuum_threshold: Default 50. Used in conjunction with autovacuum_vacuum_scale_factor, autovacuum_vacuum_scale_factor defaults to 20%. When update, delete the number of tuples more than autovacuum_vacuum_scale_factor * table_size + autovacuum_vacuum_threshold, the vacuum. If you want to work hard vacuum, this value will be smaller.
 Autovacuum_analyze_threshold: Default 50. Used in conjunction with autovacuum_analyze_scale_factor, autovacuum_analyze_scale_factor defaults to 10%. When update, insert, delete the number of tuples more than autovacuum_analyze_scale_factor * table_size + autovacuum_analyze_threshold, the analysis.
 Autovacuum_freeze_max_age and autovacuum_multixact_freeze_max_age: in front of a 200 million, followed by a 400 million. The maximum number of transactions to be frozen from the next xid.
 Autovacuum_vacuum_cost_delay: If -1, the vacuum_cost_delay value is taken.
 Autovacuum_vacuum_cost_limit: If -1, to vacuum_cost_limit value, this value is the cumulative value of all workers.

Based on the cost of vacuum parameters:
 Vacuum_cost_delay: Calculates the maximum IO allowed for each millisecond level, vacuum_cost_limit / vacuum_cost_dely. The default vacuum_cost_delay is 20 milliseconds.
 Vacuum_cost_page_hit: vacuum, the page in the buffer hit, the cost. The default is 1.
 Vacuum_cost_page_miss: vacuum, page is not in the buffer, you need to read from the disk when the cost of the default 10. Vacuum_cost_page_dirty: When the vacuum, the revised clean page. This means that additional IO is required to clean the dirty block to disk. The default value is 20.
 Vacuum_cost_limit: When this value is exceeded, vacuum will sleep. The default value is 200.

Each of the above cost to adjust the small point, and then adjust the value of the larger limit, you can extend the time of each vacuum. To do so, if the system in the high load which may have an impact on the IO, because vacuum. But for the table physical storage space growth will be slowed down.
- C / C ++ language variable scope: local variables, global variables, file-level variables (Programming)
- JVM garbage collector and memory allocation strategy (Programming)
- GCC and gfortran write MEX program (Matlab2012a) under Ubuntu 14.04 (Programming)
- SQL MySQL query table duplicate data (Database)
- How to use the command line to obtain Freely RSS source on Linux (Linux)
- VMware11 virtual machine Ubuntu14.10 system partition sda1 disk expansion (Linux)
- The need to avoid a gap of InnoDB lock (Database)
- How to install Linux Kernel 4.4 on Ubuntu (Linux)
- Linux, Google Test (GTest) test environment to build step (Linux)
- RabbitMQ user roles and access control (Linux)
- Elaborate .NET Multithreading: Using Task (Programming)
- Fedora 20, Fedora 19, CentOS 6 and RHEL6 users how to install Wine 1.7.15 (Linux)
- The traffic monitoring system: cacti (Linux)
- Zabbix monitors Nginx status (Server)
- How LUKS encrypted disk / partition to perform remote incremental backup (Linux)
- linux server security (Linux)
- grep command output highlighted word (Linux)
- Some Linux networking tools you might not know (Linux)
- Arronax allows you to easily create desktop startup file (Linux)
- Oracle 11g forget approach SYS and SYSTEM password (Database)
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.