Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to understand the parameter autovacuum_max_workers in PostgreSQL". In daily operation, I believe many people have doubts about how to understand the parameter autovacuum_max_workers in PostgreSQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubt of "how to understand the parameter autovacuum_max_workers in PostgreSQL". Next, please follow the editor to study!

Overview

PG's MVCC requires that "out-of-date" data can not be physically purged immediately, but instead marked as dead rows, and these dead rows will be cleaned up later through the vacuuming process.

Vacuuming keeps the database "healthy" in the following ways:

1. Marking dead rows can be used to store new data, which avoids unnecessary disk waste and can skip dead rows to improve sequential scan performance

two。 Update vm (used to track expired or obsolete data, reflected on pages). This can improve the performance of index-only scans.

3. Avoid database downtime caused by transaction ID rollback.

PG provides an autovacuum mechanism to collect recently frequently updated data table statistics by running ANALYZE periodically, and schedule and clean up dead rows based on these statistics autovacuum.

Number of Worker

PG provides a configuration parameter autovacuum_max_workers to configure the number of autovacuum worker. From an intuitive point of view, increasing this parameter means that there are more worker processes, which can effectively improve the throughput of autovacuum. But in fact, due to the limitation of the autovacuum_vacuum_cost_limit parameter, simply improving autovacuum_max_workers does not have any effect.

Because the cost limit imposed by autovacuum_vacuum_cost_limit is global, that is, this parameter controls the upper limit of the cost that can be reached by the global worker, even if the autovacuum_vacuum_cost_limit is increased, the throughput will not be improved. For example, enable multithreading to write files to the file system, but the total throughput limit is 10MB/s, each thread can only handle a maximum of (10MB/ threads) / s, which means that no matter how many threads you enable, the highest throughput can only be 10MB/s, in addition to increasing the number of threads (autovacuum_max_workers), you also need to increase the total throughput limit (autovacuum_vacuum_cost_limit).

Personalized solution

PG provides a personalized autovacuum solution for a single table, which can set parameters such as autovacuum_vacuum_cost_limit, autovacuum_vacuum_cost_delay, and so on, at the table level. The autovacuum_vacuum_cost_limit parameters on the table are not included in the global statistics.

[local:/data/run/pg12]: 5120 pg12@testdb=# create table t (id int); CREATE TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# ALTER TABLE t SET (autovacuum_vacuum_cost_limit = 1000); ALTER TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# ALTER TABLE t SET (autovacuum_vacuum_cost_delay = 10) ALTER TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# at this point, the study on "how to understand the parameter autovacuum_max_workers in PostgreSQL" is over. I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.