官术网_书友最值得收藏!

Digging into transaction wraparound-related issues

There are two more settings in postgresql.conf that are quite important to understand:

autovacuum_freeze_max_age = 200000000 
autovacuum_multixact_freeze_max_age = 400000000

To understand the overall problem, it is important to understand how PostgreSQL handles concurrency. The PostgreSQL transaction machinery is based on the comparison of transaction IDs and the states transactions are in.

An example: If I am transaction ID 4711 and if you happen to be 4712, I won't see you because you are still running. If I am transaction ID 4711 but you are transaction ID 3900, I will see you provided you have committed; and I will ignore you if you failed.

The trouble is as follows: transaction IDs are finite, not unlimited. At some point, they will start to wrap around. In reality, this means that transaction number 5 might actually be after transaction number 800,000,000. How does PostgreSQL know what was first? It does so by storing a watermark. At some point, those watermarks will be adjusted, and this is exactly when VACUUM starts to be relevant. By running VACUUM (or autovacuum), you can ensure that the watermark is adjusted in a way that there are always enough future transaction IDs left to work with.

Not every transaction will increase the transaction ID counter. As long as a transaction is still reading, it will only have a virtual transaction ID. This ensures that transaction IDs are not burned too quickly.

autovacuum_freeze_max_age defines the maximum number of transactions (age) that a table's pg_class.relfrozenxid field can attain before a VACUUM operation is forced to prevent transaction ID wraparound within the table. This value is fairly low because it also has an impact on clog cleanup (the clog or commit log is a data structure that stores 2 bits per transaction, which indicate whether a transaction is running, aborted, committed, or still in a subtransaction).

The autovacuum_multixact_freeze_max_age configures the maximum age (in multixacts) that a table's pg_class.relminmxid field can attain before a VACUUM operation is forced to prevent multixact ID wraparound within the table. Freezing tuples is an important performance issue and there will be more about this process in Chapter 6, Optimizing for Good Query Performance, which is about query optimization.

主站蜘蛛池模板: 固安县| 凤庆县| 兴山县| 山东省| 曲靖市| 社会| 韶山市| 启东市| 开远市| 黑水县| 阿拉善左旗| 乌鲁木齐县| 许昌县| 汝州市| 乌苏市| 全州县| 界首市| 武陟县| 南陵县| 泰和县| 体育| 丹寨县| 南丹县| 天水市| 广南县| 阳山县| 章丘市| 元谋县| 肃南| 张家界市| 稷山县| 哈尔滨市| 丰城市| 武城县| 修文县| 久治县| 乌什县| 牙克石市| 清河县| 清水县| 乌拉特后旗|