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

This article introduces the relevant knowledge of "what are the parameters related to AUTOVACUUM IO in PostgreSQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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.

IO related parameters

Autovacuum processes the pages table by table, reading the pages from disk or memory. If these pages need to clean up the dead rows, you need to perform update / write pages operation, so it involves IO. In order to make IO smoother without affecting normal database operations, PG provides relevant parameters to control the frequency of autovacuum execution, so as to avoid IO problems when the database is under great pressure.

Autovacuum_vacuum_cost_limit: total cost limit autovacuum could reach (combined by all autovacuum jobs). The upper limit of the cost of performing the autovacuum.

Autovacuum_vacuum_cost_delay: autovacuum will sleep for these many milliseconds when a cleanup reaching autovacuum_vacuum_cost_limit cost is done. How long, in milliseconds, will autovacuum need to rest after reaching the cost limit?

Vacuum_cost_page_hit: Cost of reading a page that is already in shared buffers and doesn't need a disk read. The cost of hitting pages in the cache.

Vacuum_cost_page_miss: Cost of fetching a page that is not in shared buffers. There is no cost when hit in the cache.

Vvacuum_cost_page_dirty: Cost of writing to each page when dead tuples are found in it. For example, the processing cost when there is dead rows in pages.

The default values for the above parameters are:

Autovacuum_vacuum_cost_limit =-1 # # if it is-1, the default is vacuum_cost_limit, that is, the IO metric of 200autovacuum_vacuum_cost_delay = 20msvacuum_cost_page_hit = 1vacuum_cost_page_miss = 10vacuum_cost_page_dirty = 20autovacuum

Based on the above parameters, let's calculate the IO generated by autovacuum per second.

If autovacuum_vacuum_cost_delay is set to 20ms, there can be 50 rounds of autovacuum:50=1s/20ms per second.

Memory read: assuming that all hits are in the cache, which means that 200 pages,200=autovacuum_vacuum_cost_limit/vacuum_cost_page_hit=200/1 are read, the Read IO Rate=50*200*8K/s ≈ 80MB/s can be calculated.

OS read: assuming all misses mean reading 20 pages,20=autovacuum_vacuum_cost_limit/vacuum_cost_page_miss=200/10,Read IO Rate=50*20*8K/s ≈ 8MB/s from OS (page cache or disk)

Write: assuming that all the read page have dead rows to deal with, then you need to write 10 pages,10=autovacuum_vacuum_cost_limit/vacuum_cost_page_dirty=200/20,Write IO Rate=50*10*8K/s ≈ 5MB/s

Compared with current hardware devices, this kind of throughput is too "low-key". You can increase the overall throughput by increasing autovacuum_vacuum_cost_limit. For example, setting autovacuum_vacuum_cost_limit to 2000 can increase the above throughput by 10x.

This is the end of the content of "what are the parameters related to AUTOVACUUM IO in PostgreSQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.