- Mastering PostgreSQL 9.6
- Hans Jurgen Schonig
- 369字
- 2021-07-09 19:57:14
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.
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.
- Introduction to DevOps with Kubernetes
- 會聲會影X5視頻剪輯高手速成
- 工業機器人技術及應用
- 機器學習與大數據技術
- Windows游戲程序設計基礎
- Learning C for Arduino
- 21天學通C語言
- 突破,Objective-C開發速學手冊
- Microsoft System Center Confi guration Manager
- 從零開始學SQL Server
- ASP.NET 2.0 Web開發入門指南
- Introduction to R for Business Intelligence
- 電腦上網入門
- Web璀璨:Silverlight應用技術完全指南
- PyTorch深度學習