1,我们有个table gl_appservice_msg配置70GB但使用1969 MB.
奇怪的是上周五它配置40几GB, 今天号它涨到70GB.
Database: Postgresql 9.0.22
OS:RHEL 6.2
我们每天都有做vacuum动作,理论上它应该会去reuse free的空间. 但看起来好像没有. 请问这是什么情况?
我试着用vacuum full 去release这个table的空间,但是也没有起作用,请问怎样去release这个表的free空间?
2,我们使用create table as select or 先rename old table,再建一样的new table,再insert into到 new table.
一样会发生allocate配置过多空间的状况.如下:
我们将gl_appservice_msg rename成gl_appservice_msg_old, 建一样的table gl_appservice_msg, 再将gl_appservice_msg_old insert
到gl_appservice_msg.
你可以看到
原来gl_appservice_msg_old 配置71 GB, used 2020 MB
新的 gl_appservice_msg配置8 GB, used 185 MB
请问使用才185mb 为什么要配置8GB?
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size,
pg_size_pretty(used_size) AS used_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_relation_size(table_name) AS used_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
-- where table_name in ('ses_mst','emas_item_m','emas_item_channel','mdzwebmst','catmdzdtl','emas_item_suspend','camhis','mdzstpmst','catdtl','camrul','emas_vendor_m','catmdz_order','emas_vendor_suspend')
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes limit 15;
table_name | table_size | indexes_size | total_size | used_size
"mobi"."gl_appservice_msg_old" | 71 GB | 323 MB | 72 GB | 2020 MB => old table |
"mobi"."gl_appservice_msg" | 8005 MB | 0 bytes | 8005 MB | 185 MB =>新 table |
另外确认到text的storage是extended(EXTENDED allows both compression and out-of-line storage. This is the default for most TOAST-able data types. Compression will be attempted first, then out-of-line storage if the row is still too big.).
但似乎它没有去做压缩, 请问allocate配置过多空间的问题和storage是extended,TOAST压缩相关联吗?
用什么方法可以解决该allocate配置过多空间的问题?
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。