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

This article mainly explains "how to use PostgreSQL's INDEX_CLEANUP". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to use PostgreSQL's INDEX_CLEANUP".

The VACUUM command has been enhanced in PG 12 to provide a new Option to choose from

PG 11 vacuum command

[xdb@localhost] $psql-d testdb-p 5433psql Type "help" for help.testdb=#\ help vacuum Command: VACUUMDescription: garbage-collect and optionally analyze a databaseSyntax:VACUUM [(option [,...])] [table_and_columns [,...]] VACUUM [FULL] [FREEZE] [VERBOSE] [ANALYZE] [table_and_columns [,...]] where option can be one of: FULL FREEZE VERBOSE ANALYZE DISABLE_PAGE_SKIPPINGand table_and_columns is: table_name [(column_name [,...])] testdb=#

PG 12 vacuum command

[local]: 5432 pg12@pgbench=# select version () Version -PostgreSQL 12beta3 on x86_64-pc-linux-gnu Compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit (1 row) Time: 11.981 ms [local]: 5432 pg12@pgbench=#\ help vacuum Command: VACUUMDescription: garbage-collect and optionally analyze a databaseSyntax:VACUUM [(option [,...])] [table_and_columns [,...]] VACUUM [FULL] [FREEZE] [VERBOSE] [ANALYZE] [table_and_columns [ ] where option can be one of: FULL [boolean] FREEZE [boolean] VERBOSE [boolean] ANALYZE [boolean] DISABLE_PAGE_SKIPPING [boolean] SKIP_LOCKED [boolean] INDEX_CLEANUP [boolean] TRUNCATE [boolean] and table_and_columns is: table_name [(column_name [,...])] URL: https://www.postgresql.org/docs/12/sql-vacuum.html[local]:5432 pg12@pgbench=#

The following simple tests are performed through pgbench

[pg12@localhost ~] $createdb pgbench; [pg12@localhost ~] $pgbench-- initialize-- scale=100 pgbenchdropping old tables...NOTICE: table "pgbench_accounts" does not exist, skippingNOTICE: table "pgbench_branches" does not exist, skippingNOTICE: table "pgbench_history" does not exist, skippingNOTICE: table "pgbench_tellers" does not exist, skippingcreating tables...generating data...100000 of 10000000 tuples (1%) done (elapsed 0.14s, remaining 14.30s)

Perform a 60s stress test using pgbench

[pg12@localhost ~] $pgbench-- no-vacuum-- time=60-- client=2-- jobs=2 pgbenchtransaction type: scaling factor: 100query mode: simplenumber of clients: 2duration: 60 snumber of transactions actually processed: 42499latency average = 2.824 mstps = 708.298224 (including connections establishing) tps = 708.325760 (excluding connections establishing) [pg12@localhost] $

Perform regular vacuum

[local]: 5432 pg12@testdb=# VACUUM (VERBOSE) pgbench_accounts; ERROR: relation "pgbench_accounts" does not existTime: 54.069 ms [local]: 5432 pg12@testdb=#\ c pgbenchYou are now connected to database "pgbench" as user "pg12". [local]: 5432 pg12@pgbench=# VACUUM (VERBOSE) pgbench_accounts INFO: vacuuming "public.pgbench_accounts" INFO: scanned index "pgbench_accounts_pkey" to remove 37520 row versionsDETAIL: CPU: user: 1.28s, system: 0.68s, elapsed: 2.54 sINFO: "pgbench_accounts": removed 37520 row versions in 37520 pagesDETAIL: CPU: user: 0.21s, system: 0.04s, elapsed: 0.28sINFO: index "pgbench_accounts_pkey" now contains 10000000 row versions in 27422 pagesDETAIL: 37520 index row versions were removed.0 index pages have been deleted 0 are currently reusable.CPU: user: 0.00s, system: 0.00s, elapsed: 0.00s: "pgbench_accounts": found 37444 removable, 9976515 nonremovable row versions in 164166 out of 164551 pagesDETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 48279There were 7 unused item identifiers.Skipped 0 pages due to buffer pins, 0 frozen pages.0 pages are entirely empty.CPU: user: 3.15s, system: 7.45s Elapsed: 11.31 s.VACUUMTime: 11811.362 ms [local]: 5432 pg12@pgbench=#

Use pgbench to perform the stress test again, but specify the INDEX_CLEANUP option as false when executing vacumm

[pg12@localhost ~] $pgbench-- no-vacuum-- time=60-- client=2-- jobs=2 pgbenchtransaction type: scaling factor: 100query mode: simplenumber of clients: 2duration: 60 snumber of transactions actually processed: 41268latency average = 2.908 mstps = 687.790258 (including connections establishing) tps = 687.817603 (excluding connections establishing) [local]: 5432 pg12@pgbench=# VACUUM (INDEX_CLEANUP False, VERBOSE) pgbench_accounts INFO: vacuuming "public.pgbench_accounts" INFO: "pgbench_accounts": found 36498 removable, 10000000 nonremovable row versions in 164967 out of 164967 pagesDETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 168578There were unused item identifiers.Skipped 0 pages due to buffer pins, 0 frozen pages.0 pages are entirely empty.CPU: user: 0.96s, system: 4.10s, elapsed: 5.30s.VACUUMTime: 5314.340 ms [local]: 5432 pg12@pgbench=#

Skipping Index cleanup, execute vacumm again, this time specifying INDEX_CLEANUP as true

[local]: 5432 pg12@pgbench=# VACUUM (INDEX_CLEANUP true, VERBOSE) pgbench_accounts INFO: vacuuming "public.pgbench_accounts" INFO: scanned index "pgbench_accounts_pkey" to remove 84133 row versionsDETAIL: CPU: user: 2.48s, system: 0.98s, elapsed: 3.53sINFO: scanned index "idx_accounts_bid" to remove 84133 row versionsDETAIL: CPU: user: 1.54s, system: 1.13s, elapsed: 2.80sINFO: "pgbench_accounts": removed 84133 row versions in 81168 pagesDETAIL: CPU: user: 1.09s, system: 2.47s Elapsed: 5.04 sINFO: index "pgbench_accounts_pkey" now contains 10000000 row versions in 27422 pagesDETAIL: 84133 index row versions were removed.0 index pages have been deleted, 0 are currently reusable.CPU: user: 0.00s, system: 0.00s, elapsed: 0.00s.INFO: index "idx_accounts_bid" now contains 10000000 row versions in 27665 pagesDETAIL: 25763 index row versions were removed.0 index pages have been deleted, 0 are currently reusable.CPU: user: 0.00s, system: 0.00s Elapsed: 0.00 s.INFO: "pgbench_accounts": found 0 removable, 10000000 nonremovable row versions in 164967 out of 164967 pagesDETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 168578There were 161unused item identifiers.Skipped 0 pages due to buffer pins, 0 frozen pages.0 pages are entirely empty.CPU: user: 6.03s, system: 5.34s, elapsed: 13.06s.VACUUMTime: 13109.490 ms (00l13.109) [local]: 5432 pg12@pgbench=#

This operation only performs cleanup on the index.

The INDEX_CLEANUP vacumm option provided by PG 12 can effectively shorten the execution time when cleaning up large tables and multiple indexes, but the discarded tuple on index is still retained and the space is still inflated, but the time is delayed.

Thank you for your reading, the above is the content of "how to use PostgreSQL's INDEX_CLEANUP", after the study of this article, I believe you have a deeper understanding of how to use PostgreSQL's INDEX_CLEANUP, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.