异常数据增长 [英] abnormal data grow
问题描述
我一直遇到这个问题，数据库大小突然增长
，从正常大小约300Mb到12Gb一晚。
当我查看表格大小时，最大的一个只有41Mb，所有表格大小的
总共只有223Mb。
但是在文件系统数据目录中，总大小为12Gb。我注意到
有10个文件，每个1Gb大小：
1.1G 25677563
1.1G 25677563.1
1.1 G 25677563.2
1.1G 25677563.3
1.1G 25677563.4
1.1G 25677563.5
1.1G 25677563.6
1.1G 25677563.7
1.1G 25677563.8
1.1G 25677563.9
所以我试图找出哪个表格是，但这个查询什么都不返回：
SELECT relname，relpages
FROM pg_class
WHERE relname =''pg_toast_25677563''或者relname =
''pg_toast_25677563_index''
ORDER BY relname;
如果我运行此查询：
SELECT relname，relpages
FROM pg_class ORDER BY relpages desc
顶部的是这些表（不是1.1 Gb文件的id）
pg_toast_25677561 1382845
pg_toast_25677561_index 22116
我只是想知道有没有办法知道25677563文件是什么？
为什么postgres会创建该文件的副本。是，.2，.3等等。
该文件仍在使用中（因为我在pg_class表中找不到它）？
将postgres清理干净数据库还是我应该手动清理？
- reynard
----------------- ----------（广播结束）---------------------------
提示4：不要杀死-9''邮政局长
Reynard Hilman< re **** ***@lightsky.com>写道：但在文件系统数据目录中，总大小为12Gb。我注意到有10个文件，每个1Gb大小：
1.1G 25677563
1.1G 25677563.1
...
我只是想知道有没有办法要知道25677563文件是什么？
从pg_class中选择relname，其中relfilenode = 25677563;
问候，tom lane
---------------------------（广播结束）------- --------------------
提示2：您可以使用取消注册命令一次性取消所有列表
（发送取消注册YourEmailAddressHere到 ma*******@postgresql.org ）
Reynard Hilman< re ******* @ lightsky.com>写道：
我一直有这个问题，数据库大小突然增长，从正常大小约300Mb到12Gb一晚。
当我查看表格大小时，最大的一个只有41Mb，所有表格大小只有223Mb。
但是在文件系统数据目录中总大小是12GB。我注意到有10个文件，每个文件大小为1Gb：
1.1G 25677563
1.1G 25677563.1
1.1G 25677563.2
1.1G 25677563.3
1.1G 25677563.4
1.1G 25677563.5
1.1G 25677563.6
1.1G 25677563.7
1.1G 25677563.8
1.1G 25677563.9
25677563是此对象的OID。不同的文件是分段
的关系，而不是重复（PG将个别数据文件限制为
大小为1GB）。那么看看哪个关系有那个OID - 它可能是一个膨胀的指数。
-Doug
-
让我们过河，在树荫下休息。
--T。 J. Jackson，1863
---------------------------（播出结束） - --------------------------
提示8：解释分析是你的朋友
2004年9月21日星期二上午09:51:15 -0500，Reynard Hilman写道：
我''我只是想知道有没有办法知道25677563文件是什么？
为什么postgres用.1，.2，.3等创建该文件的副本。
那些不是副本。 Postgres将每个关系（表/索引）拆分为1GB
个文件。所以关系实际上使用10 GB;你需要一些清理。
该文件是否仍在使用中（因为我在pg_class
表中找不到它）？
是的。不要手动删除它。
将postgres清理数据库还是我应该手动清理？
你可能需要一个真空;或者，如果它是一个索引，REINDEX。
-
Alvaro Herrera（< alvherre [a] dcc.uchile.cl> ）
授予软件进化自由只保证不同的结果，
不是更好的结果。 （Zygo Blaxell）
---------------------------（播出结束）------ ---------------------
提示7：别忘了增加免费空间地图设置
Hi,
I have been having this problem where the database size suddenly grows
from the normal size of about 300Mb to 12Gb in one night.
When I look up the table size, the biggest one is only 41Mb and the
total of all table size is only 223Mb.
But in the filesystem data directory the total size is 12Gb. I noticed
there are 10 files with 1Gb size each:
1.1G 25677563
1.1G 25677563.1
1.1G 25677563.2
1.1G 25677563.3
1.1G 25677563.4
1.1G 25677563.5
1.1G 25677563.6
1.1G 25677563.7
1.1G 25677563.8
1.1G 25677563.9
So I tried to find out what table that is, but this query return nothing:
SELECT relname, relpages
FROM pg_class
WHERE relname = ''pg_toast_25677563'' OR relname =
''pg_toast_25677563_index''
ORDER BY relname;
and if I run this query:
SELECT relname, relpages
FROM pg_class ORDER BY relpages desc
the top ones are these tables (which is not the id of the 1.1 Gb files)
pg_toast_25677561 1382845
pg_toast_25677561_index 22116
I''m just wondering is there a way to know what that 25677563 file is?
Why does postgres create a copy of that file with .1, .2, .3, etc. Is
that file still in used (because I can''t find it in the pg_class table)?
Will postgres clean the database up or should I do a manual clean?
- reynard
---------------------------(end of broadcast)---------------------------
TIP 4: Don''t ''kill -9'' the postmaster
Reynard Hilman <re*******@lightsky.com> writes:But in the filesystem data directory the total size is 12Gb. I noticed
there are 10 files with 1Gb size each:
1.1G 25677563
1.1G 25677563.1
... I''m just wondering is there a way to know what that 25677563 file is?
select relname from pg_class where relfilenode = 25677563;
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)
Reynard Hilman <re*******@lightsky.com> writes:
Hi,
I have been having this problem where the database size suddenly grows
from the normal size of about 300Mb to 12Gb in one night.
When I look up the table size, the biggest one is only 41Mb and the
total of all table size is only 223Mb.
But in the filesystem data directory the total size is 12Gb. I noticed
there are 10 files with 1Gb size each:
1.1G 25677563
1.1G 25677563.1
1.1G 25677563.2
1.1G 25677563.3
1.1G 25677563.4
1.1G 25677563.5
1.1G 25677563.6
1.1G 25677563.7
1.1G 25677563.8
1.1G 25677563.9
25677563 is the OID of this object. The different files are segments
of the relation, not duplicates (PG restricts individual data file
size to 1GB). So look for which relation has that OID--it''s quite
possibly an index that is bloating up.
-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
On Tue, Sep 21, 2004 at 09:51:15AM -0500, Reynard Hilman wrote:
I''m just wondering is there a way to know what that 25677563 file is?
Why does postgres create a copy of that file with .1, .2, .3, etc.
Those are not copies. Postgres splits each relation (table/index) in 1GB
files. So the relation actually uses 10 GB; you need some cleanup.
Is that file still in used (because I can''t find it in the pg_class
table)?
Yes. Don''t delete it manually.
Will postgres clean the database up or should I do a manual clean?
You probably need a VACUUM; or, if it''s an index, a REINDEX.
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Granting software the freedom to evolve guarantees only different results,
not better ones." (Zygo Blaxell)
---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings
这篇关于异常数据增长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
