zjh@postgres=# create table big_table(id int,v text); CREATE TABLE zjh@postgres=# ALTER TABLE big_table SET (autovacuum_enabled = off); # 关闭自动vacuum ALTER TABLE zjh@postgres=# insert into big_table select id, rpad('x',64,'x') from generate_series(1,1000000) id; INSERT 0 1000000 zjh@postgres=# zjh@postgres=# \timing on Timing is on. zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 76.603 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 60.682 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 60.963 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 60.632 ms ### 稳定的执行时间 zjh@postgres=# update big_table set v = v, id = id; #生成50%死元祖 UPDATE 1000000 Time: 1006.034 ms (00:01.006) zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 307.994 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 75.222 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 115.800 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 109.309 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 76.994 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 76.219 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 75.804 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 75.834 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 76.684 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 76.299 ms ## 稳定后带死元祖的执行时间，25%以上的额外时延 zjh@postgres=# update big_table set v = v, id = id; UPDATE 1000000 Time: 1923.425 ms (00:01.923) zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 352.238 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 103.585 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 211.861 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 144.573 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 99.129 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 100.284 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 99.148 ms ## 两次update后，额外负载65% zjh@postgres=# vacuum big_table ; VACUUM Time: 214.800 ms zjh@postgres=# zjh@postgres=# vacuum big_table ; VACUUM Time: 11.348 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 88.478 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 85.893 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 87.403 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 85.340 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 85.990 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 86.514 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 85.684 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 84.336 ms # vacuum之后，仍然还有40%的额外负载 zjh@postgres=# vacuum full big_table ; # full vacuum VACUUM Time: 416.220 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 66.535 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 63.514 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 63.039 ms ## 重组织后，负载基本消除，虽然也有一点点，但基本客户忽略不计了。 zjh@postgres=# update big_table set v = v, id = id; UPDATE 1000000 Time: 1116.814 ms (00:01.117) zjh@postgres=# update big_table set v = v, id = id; UPDATE 1000000 Time: 2250.193 ms (00:02.250) zjh@postgres=# update big_table set v = v, id = id; UPDATE 1000000 Time: 1264.835 ms (00:01.265) zjh@postgres=# update big_table set v = v, id = id; UPDATE 1000000 Time: 1266.069 ms (00:01.266) zjh@postgres=# update big_table set v = v, id = id; UPDATE 1000000 Time: 2000.205 ms (00:02.000) zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 384.475 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 174.367 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 364.749 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 178.216 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 176.623 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 170.568 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 170.408 ms zjh@postgres=# zjh@postgres=# zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 172.460 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 171.467 ms ## 五次update之后 zjh@postgres=# update big_table set v = v, id = id; UPDATE 1000000 Time: 1114.980 ms (00:01.115) zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 455.640 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 197.581 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 335.761 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 212.413 ms zjh@postgres=# select count(1) from big_table ; count --------- 1000000 (1 row) Time: 195.236 ms ## 六次update之后
zjh@postgres=# select pg_size_pretty(pg_relation_size(‘big_table’));
pg_size_pretty
—————-
675 MB — 可见膨胀是非常厉害的。
(1 row)
zjh@postgres=# vacuum big_table ; VACUUM Time: 662.148 ms zjh@postgres=# select pg_size_pretty(pg_relation_size('big_table')); pg_size_pretty ---------------- 675 MB # 不回收，但是新的DML可以复用 (1 row) Time: 0.406 ms zjh@postgres=# vacuum full big_table ; VACUUM Time: 568.773 ms zjh@postgres=# select pg_size_pretty(pg_relation_size('big_table')); pg_size_pretty ---------------- 96 MB # 回收，其实就是重组织 (1 row)
zjh@postgres=# vacuum big_table ; VACUUM Time: 40.442 ms zjh@postgres=# zjh@postgres=# select pg_size_pretty(pg_relation_size('big_table')); pg_size_pretty ---------------- 204 MB (1 row) Time: 0.331 ms zjh@postgres=# update big_table set v = v, id = id limit 100000; UPDATE 100000 Time: 98.444 ms zjh@postgres=# update big_table set v = v, id = id limit 100000; UPDATE 100000 Time: 105.852 ms zjh@postgres=# update big_table set v = v, id = id limit 100000; UPDATE 100000 Time: 104.016 ms zjh@postgres=# select pg_size_pretty(pg_relation_size('big_table')); pg_size_pretty ---------------- 204 MB (1 row) Time: 0.273 ms zjh@postgres=# update big_table set v = v, id = id limit 100000; UPDATE 100000 Time: 106.985 ms zjh@postgres=# update big_table set v = v, id = id limit 100000; UPDATE 100000 Time: 106.612 ms zjh@postgres=# update big_table set v = v, id = id limit 100000; UPDATE 100000 Time: 106.837 ms zjh@postgres=# update big_table set v = v, id = id limit 100000; UPDATE 100000 Time: 107.296 ms zjh@postgres=# update big_table set v = v, id = id limit 100000; UPDATE 100000 Time: 105.588 ms zjh@postgres=# select pg_size_pretty(pg_relation_size('big_table')); pg_size_pretty ---------------- 204 MB (1 row) Time: 0.347 ms zjh@postgres=# update big_table set v = v, id = id limit 100000; UPDATE 100000 Time: 103.695 ms zjh@postgres=# update big_table set v = v, id = id limit 100000; UPDATE 100000 Time: 104.665 ms zjh@postgres=# update big_table set v = v, id = id limit 100000; UPDATE 100000 Time: 106.016 ms zjh@postgres=# select pg_size_pretty(pg_relation_size('big_table')); pg_size_pretty ---------------- 204 MB (1 row) Time: 0.310 ms zjh@postgres=# update big_table set v = v, id = id limit 100000; UPDATE 100000 Time: 104.338 ms # 此时所有死元祖回收的空间已经用完。 zjh@postgres=# select pg_size_pretty(pg_relation_size('big_table')); pg_size_pretty ---------------- 212 MB # 即使大量的死元祖，也会导致空间膨胀 (1 row) Time: 0.446 ms zjh@postgres=# update big_table set v = v, id = id limit 100000; UPDATE 100000 Time: 109.384 ms zjh@postgres=# select pg_size_pretty(pg_relation_size('big_table')); pg_size_pretty ---------------- 222 MB # 即使大量的死元祖，也会导致空间膨胀
(1 row) Time: 0.266 ms
从上可知，因为未清理的死元祖会占用额外空间、也会导致vm未被置为可见，所以不仅I/O更高、占用的buffer更多、也需要额外判断mvcc是否满足，进而对性能的影响还是比较大。只读特性确保避免意外操作导致表被更新，进而产生额外不必要的死元祖、vaccum活动以及vm被误干扰。
原文地址：http://www.cnblogs.com/zhjh256/p/16818340.html
1. 本站所有资源来源于用户上传和网络，如有侵权请邮件联系站长！
2. 分享目的仅供大家学习和交流，请务用于商业用途!
3. 如果你也有好源码或者教程，可以到用户中心发布，分享有积分奖励和额外收入！
4. 本站提供的源码、模板、插件等等其他资源，都不包含技术服务请大家谅解！
5. 如有链接无法下载、失效或广告，请联系管理员处理！
6. 本站资源售价只是赞助，收取费用仅维持本站的日常运营所需！
7. 如遇到加密压缩包，默认解压密码为"gltf",如遇到无法解压的请联系管理员！
8. 因为资源和程序源码均为可复制品，所以不支持任何理由的退款兑现，请斟酌后支付下载
声明：如果标题没有注明"已测试"或者"测试可用"等字样的资源源码均未经过站长测试.特别注意没有标注的源码不保证任何可用性
