您好,登錄后才能下訂單哦!
這篇文章主要講解了“怎么使用PostgreSQL的INDEX_CLEANUP”,文中的講解內(nèi)容簡單清晰,易于學(xué)習(xí)與理解,下面請大家跟著小編的思路慢慢深入,一起來研究和學(xué)習(xí)“怎么使用PostgreSQL的INDEX_CLEANUP”吧!
VACUUM命令在PG 12有所增強(qiáng),提供新的Option可供選擇
PG 11 vacuum命令
[xdb@localhost ~]$ psql -d testdb -p 5433 psql (11.2) Type "help" for help. testdb=# \help vacuum; Command: VACUUM Description: garbage-collect and optionally analyze a database Syntax: 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_SKIPPING and table_and_columns is: table_name [ ( column_name [, ...] ) ] testdb=#
PG 12 vacuum命令
[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: VACUUM Description: garbage-collect and optionally analyze a database Syntax: 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=#
下面通過pgbench執(zhí)行簡單的測試
[pg12@localhost ~]$ createdb pgbench; [pg12@localhost ~]$ pgbench --initialize --scale=100 pgbench dropping old tables... NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping creating tables... generating data... 100000 of 10000000 tuples (1%) done (elapsed 0.14 s, remaining 14.30 s)
使用pgbench執(zhí)行60s的壓力測試
[pg12@localhost ~]$ pgbench --no-vacuum --time=60 --client=2 --jobs=2 pgbench transaction type: <builtin: TPC-B (sort of)> scaling factor: 100 query mode: simple number of clients: 2 number of threads: 2 duration: 60 s number of transactions actually processed: 42499 latency average = 2.824 ms tps = 708.298224 (including connections establishing) tps = 708.325760 (excluding connections establishing) [pg12@localhost ~]$
執(zhí)行常規(guī)的vacuum
[local]:5432 pg12@testdb=# VACUUM (VERBOSE) pgbench_accounts ; ERROR: relation "pgbench_accounts" does not exist Time: 54.069 ms [local]:5432 pg12@testdb=# \c pgbench You 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 versions DETAIL: CPU: user: 1.28 s, system: 0.68 s, elapsed: 2.54 s INFO: "pgbench_accounts": removed 37520 row versions in 37520 pages DETAIL: CPU: user: 0.21 s, system: 0.04 s, elapsed: 0.28 s INFO: index "pgbench_accounts_pkey" now contains 10000000 row versions in 27422 pages DETAIL: 37520 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "pgbench_accounts": found 37444 removable, 9976515 nonremovable row versions in 164166 out of 164551 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 48279 There were 7 unused item identifiers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 3.15 s, system: 7.45 s, elapsed: 11.31 s. VACUUM Time: 11811.362 ms (00:11.811) [local]:5432 pg12@pgbench=#
再次使用pgbench執(zhí)行壓力測試,但在執(zhí)行vacumm指定INDEX_CLEANUP選項(xiàng)為false
[pg12@localhost ~]$ pgbench --no-vacuum --time=60 --client=2 --jobs=2 pgbench transaction type: <builtin: TPC-B (sort of)> scaling factor: 100 query mode: simple number of clients: 2 number of threads: 2 duration: 60 s number of transactions actually processed: 41268 latency average = 2.908 ms tps = 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 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 168578 There were 161 unused item identifiers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.96 s, system: 4.10 s, elapsed: 5.30 s. VACUUM Time: 5314.340 ms (00:05.314) [local]:5432 pg12@pgbench=#
跳過了Index的清理,再次執(zhí)行vacumm,這次指定INDEX_CLEANUP為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 versions DETAIL: CPU: user: 2.48 s, system: 0.98 s, elapsed: 3.53 s INFO: scanned index "idx_accounts_bid" to remove 84133 row versions DETAIL: CPU: user: 1.54 s, system: 1.13 s, elapsed: 2.80 s INFO: "pgbench_accounts": removed 84133 row versions in 81168 pages DETAIL: CPU: user: 1.09 s, system: 2.47 s, elapsed: 5.04 s INFO: index "pgbench_accounts_pkey" now contains 10000000 row versions in 27422 pages DETAIL: 84133 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: index "idx_accounts_bid" now contains 10000000 row versions in 27665 pages DETAIL: 25763 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "pgbench_accounts": found 0 removable, 10000000 nonremovable row versions in 164967 out of 164967 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 168578 There were 161 unused item identifiers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 6.03 s, system: 5.34 s, elapsed: 13.06 s. VACUUM Time: 13109.490 ms (00:13.109) [local]:5432 pg12@pgbench=#
這次操作只對index執(zhí)行清理.
PG 12提供的INDEX_CLEANUP vacumm選項(xiàng)在對大表并存在多索引進(jìn)行清理時可有效的縮短執(zhí)行時間,但index上的廢棄tuple仍然保留,空間仍會膨脹,只不過時間滯后了而已.
感謝各位的閱讀,以上就是“怎么使用PostgreSQL的INDEX_CLEANUP”的內(nèi)容了,經(jīng)過本文的學(xué)習(xí)后,相信大家對怎么使用PostgreSQL的INDEX_CLEANUP這一問題有了更深刻的體會,具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云,小編將為大家推送更多相關(guān)知識點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。