怎么使用PostgreSQL的INDEX_CLEANUP

2025-03-13 技术教程

这篇文章主要讲解了“怎么使用PostgreSQL的INDEX_CLEANUP”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么使用PostgreSQL的INDEX_CLEANUP”吧！

VACUUM命令在PG 12有所增强,提供新的Option可供选择

PG 11 vacuum命令

[xdb@localhost~]$psql-dtestdb-p5433psql(11.2)Type"help"forhelp.testdb=#\helpvacuum;Command:VACUUMDescription:garbage-collectandoptionallyanalyzeadatabaseSyntax:VACUUM[(option[,...])][table_and_columns[,...]]VACUUM[FULL][FREEZE][VERBOSE][ANALYZE][table_and_columns[,...]]whereoptioncanbeoneof:FULLFREEZEVERBOSEANALYZEDISABLE_PAGE_SKIPPINGandtable_and_columnsis:table_name[(column_name[,...])]testdb=#

PG 12 vacuum命令

[local]:5432pg12@pgbench=#selectversion();version--------------------------------------------------------------------------------------------PostgreSQL12beta3onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-16),64-bit(1row)Time:11.981ms[local]:5432pg12@pgbench=#\helpvacuumCommand:VACUUMDescription:garbage-collectandoptionallyanalyzeadatabaseSyntax:VACUUM[(option[,...])][table_and_columns[,...]]VACUUM[FULL][FREEZE][VERBOSE][ANALYZE][table_and_columns[,...]]whereoptioncanbeoneof:FULL[boolean]FREEZE[boolean]VERBOSE[boolean]ANALYZE[boolean]DISABLE_PAGE_SKIPPING[boolean]SKIP_LOCKED[boolean]INDEX_CLEANUP[boolean]TRUNCATE[boolean]andtable_and_columnsis:table_name[(column_name[,...])]URL:https://www.postgresql.org/docs/12/sql-vacuum.html[local]:5432pg12@pgbench=#

下面通过pgbench执行简单的测试

[pg12@localhost~]$createdbpgbench;[pg12@localhost~]$pgbench--initialize--scale=100pgbenchdroppingoldtables...NOTICE:table"pgbench_accounts"doesnotexist,skippingNOTICE:table"pgbench_branches"doesnotexist,skippingNOTICE:table"pgbench_history"doesnotexist,skippingNOTICE:table"pgbench_tellers"doesnotexist,skippingcreatingtables...generatingdata...100000of10000000tuples(1%)done(elapsed0.14s,remaining14.30s)

使用pgbench执行60s的压力测试

执行常规的vacuum

[local]:5432pg12@testdb=#VACUUM(VERBOSE)pgbench_accounts;ERROR:relation"pgbench_accounts"doesnotexistTime:54.069ms[local]:5432pg12@testdb=#\cpgbenchYouarenowconnectedtodatabase"pgbench"asuser"pg12".[local]:5432pg12@pgbench=#VACUUM(VERBOSE)pgbench_accounts;INFO:vacuuming"public.pgbench_accounts"INFO:scannedindex"pgbench_accounts_pkey"toremove37520rowversionsDETAIL:CPU:user:1.28s,system:0.68s,elapsed:2.54sINFO:"pgbench_accounts":removed37520rowversionsin37520pagesDETAIL:CPU:user:0.21s,system:0.04s,elapsed:0.28sINFO:index"pgbench_accounts_pkey"nowcontains10000000rowversionsin27422pagesDETAIL:37520indexrowversionswereremoved.0indexpageshavebeendeleted,0arecurrentlyreusable.CPU:user:0.00s,system:0.00s,elapsed:0.00s.INFO:"pgbench_accounts":found37444removable,9976515nonremovablerowversionsin164166outof164551pagesDETAIL:0deadrowversionscannotberemovedyet,oldestxmin:48279Therewere7unuseditemidentifiers.Skipped0pagesduetobufferpins,0frozenpages.0pagesareentirelyempty.CPU:user:3.15s,system:7.45s,elapsed:11.31s.VACUUMTime:11811.362ms(00:11.811)[local]:5432pg12@pgbench=#

再次使用pgbench执行压力测试,但在执行vacumm指定INDEX_CLEANUP选项为false

[pg12@localhost~]$pgbench--no-vacuum--time=60--client=2--jobs=2pgbenchtransactiontype:<builtin:TPC-B(sortof)>scalingfactor:100querymode:simplenumberofclients:2numberofthreads:2duration:60snumberoftransactionsactuallyprocessed:41268latencyaverage=2.908mstps=687.790258(includingconnectionsestablishing)tps=687.817603(excludingconnectionsestablishing)[local]:5432pg12@pgbench=#VACUUM(INDEX_CLEANUPFalse,VERBOSE)pgbench_accounts;INFO:vacuuming"public.pgbench_accounts"INFO:"pgbench_accounts":found36498removable,10000000nonremovablerowversionsin164967outof164967pagesDETAIL:0deadrowversionscannotberemovedyet,oldestxmin:168578Therewere161unuseditemidentifiers.Skipped0pagesduetobufferpins,0frozenpages.0pagesareentirelyempty.CPU:user:0.96s,system:4.10s,elapsed:5.30s.VACUUMTime:5314.340ms(00:05.314)[local]:5432pg12@pgbench=#

跳过了Index的清理,再次执行vacumm,这次指定INDEX_CLEANUP为true

[local]:5432pg12@pgbench=#VACUUM(INDEX_CLEANUPtrue,VERBOSE)pgbench_accounts;INFO:vacuuming"public.pgbench_accounts"INFO:scannedindex"pgbench_accounts_pkey"toremove84133rowversionsDETAIL:CPU:user:2.48s,system:0.98s,elapsed:3.53sINFO:scannedindex"idx_accounts_bid"toremove84133rowversionsDETAIL:CPU:user:1.54s,system:1.13s,elapsed:2.80sINFO:"pgbench_accounts":removed84133rowversionsin81168pagesDETAIL:CPU:user:1.09s,system:2.47s,elapsed:5.04sINFO:index"pgbench_accounts_pkey"nowcontains10000000rowversionsin27422pagesDETAIL:84133indexrowversionswereremoved.0indexpageshavebeendeleted,0arecurrentlyreusable.CPU:user:0.00s,system:0.00s,elapsed:0.00s.INFO:index"idx_accounts_bid"nowcontains10000000rowversionsin27665pagesDETAIL:25763indexrowversionswereremoved.0indexpageshavebeendeleted,0arecurrentlyreusable.CPU:user:0.00s,system:0.00s,elapsed:0.00s.INFO:"pgbench_accounts":found0removable,10000000nonremovablerowversionsin164967outof164967pagesDETAIL:0deadrowversionscannotberemovedyet,oldestxmin:168578Therewere161unuseditemidentifiers.Skipped0pagesduetobufferpins,0frozenpages.0pagesareentirelyempty.CPU:user:6.03s,system:5.34s,elapsed:13.06s.VACUUMTime:13109.490ms(00:13.109)[local]:5432pg12@pgbench=#

这次操作只对index执行清理.

PG 12提供的INDEX_CLEANUP vacumm选项在对大表并存在多索引进行清理时可有效的缩短执行时间,但index上的废弃tuple仍然保留,空间仍会膨胀,只不过时间滞后了而已.

感谢各位的阅读，以上就是“怎么使用PostgreSQL的INDEX_CLEANUP”的内容了，经过本文的学习后，相信大家对怎么使用PostgreSQL的INDEX_CLEANUP这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！