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

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.

主站蜘蛛池模板: 信宜市| 南京市| 长治市| 沙田区| 卫辉市| 邮箱| 翼城县| 弋阳县| 化德县| 察隅县| 湾仔区| 紫阳县| 凤凰县| 昌都县| 韶关市| 西藏| 昌吉市| 东乌珠穆沁旗| 洛扎县| 高阳县| 洮南市| 西充县| 九寨沟县| 建水县| 夹江县| 武威市| 化隆| 罗江县| 左云县| 塘沽区| 湾仔区| 大安市| 白城市| 清徐县| 武宁县| 新密市| 湖北省| 津南区| 壤塘县| 乌兰浩特市| 肃北|