企业官网建设流程全解析

网站开发可能性分析,wordpress如何添加首页图片,做网站头视频,传媒网站建设网文章目录环境症状问题原因解决方案环境 系统平台#xff1a;N/A 版本#xff1a;4.1.1 症状 在业务并不繁忙的情况下发现服务器负载较高#xff0c;执行SQL的速度明显缓慢。检查数据库TOP SQL#xff0c;发现与表test有关#xff0c;查看该表的统计值#xff0c;live_…文章目录环境症状问题原因解决方案环境系统平台N/A版本4.1.1症状在业务并不繁忙的情况下发现服务器负载较高执行SQL的速度明显缓慢。检查数据库TOP SQL发现与表test有关查看该表的统计值live_tuple只有91966条dead tuple为702346条。highgo# select n_live_tup,n_dead_tup from pg_stat_all_tables where relname test;n_live_tup|n_dead_tup------------------------91966|702346(1row)手动对该表执行VACUUM同样无法回收dead tuple报错如下highgo# vacuum(verbose,analyze) test; INFO: 00000: vacuuming public.test INFO: 00000: index index_test_content_id now contains 146076 row versions in 698 pages DETAIL: 0 index row versions were removed. 175 index pages have been deleted, 126 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: 00000: index test_pkey now contains 146076 row versions in 568 pages DETAIL: 0 index row versions were removed. 77 index pages have been deleted, 68 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: 00000: test: found 0 removable, 789485 nonremovable row versions in 10178 out of 10276 pages DETAIL: 702346 dead row versions cannot be removed yet. There were 59707 unused item pointers. Skipped 0 pages due to buffer pins. 0 pages are entirely empty. CPU 0.04s/0.15u sec elapsed 0.20 sec. INFO: 00000: vacuuming pg_toast.pg_toast_16737 INFO: 00000: index pg_toast_16737_index now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.13 sec. INFO: 00000: pg_toast_16737: found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. Skipped 0 pages due to buffer pins. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.59 sec. INFO: 00000: analyzing public.test INFO: 00000: test: scanned 10276 of 10276 pages, containing 90959 live rows and 702346 dead rows; 30000 rows in sample, 90959 estimated total row问题原因当我们开启一个事务后为了实现多版本并发控制数据库会阻止vacuum清理未结束的长事务之后产生的所有dead tuple,即使与该事务无关的表也是如此。对于未结束的长事务而言之后产生的所有dead tuple对于该事务都是可见的所以即使手动执行vacuum也无法进行清理。Live transactions performing a write operation in any table will prevent vacuuming dead rowsgenerated by commited transactions that started after first live transaction in any other table.解决方案检查数据库中存在的长事务highgo#selectusename,datname,state,waiting,xact_start,now(),query,pid,now()-xact_startastimefrompg_stat_activitywherenow()-xact_startinterval5 secandquery!~^COPYandstateidleorderbyxact_start;-[RECORD1]-----------------------------------------------------------------------------------------------------------------------------------------------------------------usename|highgo datname|highgo state|idleintransactionwaiting|f xact_start|2018-07-0211:09:22.60788408now|2018-07-0416:59:43.21271708query|selectxxxxxxxx pid|17924time|2day05:50:26.909971检查该SQL执行未结束的原因如需尽快恢复前台应用可以在确认安全的情况下使用如下命令将该长事务终止。SELECTpg_terminate_backend(17924);再次对该表手动VACUUMhighgo# vacuum(verbose,analyze) test;INFO: 00000: vacuuming “public.test”INFO: 00000: scanned index “index_test_content_id” to remove 62482 row versionsDETAIL: CPU 0.01s/0.09u sec elapsed 0.26 secINFO: 00000: scanned index “test_pkey” to remove 62482 row versionsDETAIL: CPU 0.01s/0.08u sec elapsed 0.19 secINFO: 00000: “test”: removed 62482 row versions in 11041 pagesDETAIL: CPU 0.11s/1.30u sec elapsed 1.52 secINFO: 00000: index “index_test_content_id” now contains 91106 row versions in 698 pagesDETAIL: 62482 index row versions were removed.346 index pages have been deleted, 152 are currently reusable.CPU 0.00s/0.00u sec elapsed 0.01 sec.INFO: 00000: index “test_pkey” now contains 91106 row versions in 568 pagesDETAIL: 62482 index row versions were removed.244 index pages have been deleted, 50 are currently reusable.CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: 00000: “test”: found 781909 removable, 87305 nonremovable row versions in 11179 out of 11215 pagesDETAIL: 0 dead row versions cannot be removed yet.There were 777839 unused item pointers.Skipped 0 pages due to buffer pins.0 pages are entirely empty.CPU 0.32s/2.97u sec elapsed 3.96 sec.INFO: 00000: vacuuming “pg_toast.pg_toast_16737”INFO: 00000: index “pg_toast_16737_index” now contains 0 row versions in 1 pagesDETAIL: 0 index row versions were removed.0 index pages have been deleted, 0 are currently reusable.CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: 00000: “pg_toast_16737”: found 0 removable, 0 nonremovable row versions in 0 out of 0 pagesDETAIL: 0 dead row versions cannot be removed yet.There were 0 unused item pointers.Skipped 0 pages due to buffer pins.0 pages are entirely empty.CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: 00000: analyzing “public.test”INFO: 00000: “test”: scanned 11215 of 11215 pages, containing 91106 live rows and 0 dead rows; 30000 rows in sample, 91106 estimated total rowsVACUUM测试问题SQL确保该问题不会再次发生。