开发者社区> 问答> 正文

Table空间使用率异常和allocate配置过多空间的问题

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配置过多空间的问题?

展开
收起
simon007 2016-06-07 20:00:31 2541 0
0 条回答
写回答
取消 提交回答
问答排行榜
最热
最新

相关电子书

更多
低代码开发师(初级)实战教程 立即下载
冬季实战营第三期:MySQL数据库进阶实战 立即下载
阿里巴巴DevOps 最佳实践手册 立即下载