Thread: pg_toast table growth out of control
(resent -- mailing lists seem horked up today)
I have a pg_toast table that is using up my entire storage device. When
I vacuum the database, it does not get smaller. This is driving me
crazy, since I have to dump and reload the data to shrink the database.
The table is simple:
rupert=# \d resp_body
Table "resp_body"
Column | Type | Modifiers
--------+---------+-----------
resp | integer | not null
body | text |
Unique keys: resp_body_resp_idx
The vacuum shows the problem:
rupert=# vacuum verbose resp_body;
NOTICE: --Relation resp_body--
NOTICE: Pages 1008: Changed 42, Empty 0; Tup 35101: Vac 0, Keep 0,
UnUsed 10292.
Total CPU 0.08s/0.03u sec elapsed 0.79 sec.
NOTICE: --Relation pg_toast_2947588--
NOTICE: Pages 1759978: Changed 555, Empty 0; Tup 197417: Vac 0, Keep 0,
UnUsed 6979775.
Total CPU 164.66s/9.69u sec elapsed 589.04 sec.
VACUUM
Hey, only 7 million unused records taking 15GB of disk, no problem!?!?
Help!
-jwb
On Mon, 2002-03-11 at 19:00, Jeffrey W. Baker wrote: > (resent -- mailing lists seem horked up today) > > I have a pg_toast table that is using up my entire storage device. When > I vacuum the database, it does not get smaller. This is driving me > crazy, since I have to dump and reload the data to shrink the database. > Are you using VACUUM FULL at all? AIUI, the current VACUUM will generally not truncate tables -in this respect, the TOAST table is like any other. I would suggest running VACUUM FULL VERBOSE resp_body; and see whether you get any different message. (this gets an exclusive lock on the table, so it will block operations on resp_body while it operates). Regards John Gray
On Mon, 2002-03-11 at 12:18, John Gray wrote: > On Mon, 2002-03-11 at 19:00, Jeffrey W. Baker wrote: > > (resent -- mailing lists seem horked up today) > > > > I have a pg_toast table that is using up my entire storage device. When > > I vacuum the database, it does not get smaller. This is driving me > > crazy, since I have to dump and reload the data to shrink the database. > > > > Are you using VACUUM FULL at all? AIUI, the current VACUUM will > generally not truncate tables -in this respect, the TOAST table is like > any other. > > I would suggest running VACUUM FULL VERBOSE resp_body; and see whether > you get any different message. (this gets an exclusive lock on the > table, so it will block operations on resp_body while it operates). That isn't the problem ... the rest of my tables get truncated normally but only this one which contains long objects grows. In any case VACUUM FULL takes ages. pg_dump + pg_restore is actually faster. -jwb
Jeffrey W. Baker wrote:
> On Mon, 2002-03-11 at 12:18, John Gray wrote:
> > On Mon, 2002-03-11 at 19:00, Jeffrey W. Baker wrote:
> > > (resent -- mailing lists seem horked up today)
> > >
> > > I have a pg_toast table that is using up my entire storage device. When
> > > I vacuum the database, it does not get smaller. This is driving me
> > > crazy, since I have to dump and reload the data to shrink the database.
> > >
> >
> > Are you using VACUUM FULL at all? AIUI, the current VACUUM will
> > generally not truncate tables -in this respect, the TOAST table is like
> > any other.
> >
> > I would suggest running VACUUM FULL VERBOSE resp_body; and see whether
> > you get any different message. (this gets an exclusive lock on the
> > table, so it will block operations on resp_body while it operates).
>
> That isn't the problem ... the rest of my tables get truncated normally
> but only this one which contains long objects grows.
>
> In any case VACUUM FULL takes ages. pg_dump + pg_restore is actually
> faster.
The best cure for a problem is avoiding it. I would suggest
running the light weight VACUUM more often, so that it
doesn't grow that big in the first place.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
On Mon, 2002-03-11 at 13:30, Jan Wieck wrote: > The best cure for a problem is avoiding it. I would suggest > running the light weight VACUUM more often, so that it > doesn't grow that big in the first place. I think everybody is missing my point. This entire database is vacuumed every HOUR. All the tables are reasonably sized, and they stay that way. Except, the magic pg_toast table where long objects from resp_body are store is growing and growing and growing and growing and does not seem to respond to VACUUM whatsoever. -jwb
Jeffrey W. Baker wrote:
> On Mon, 2002-03-11 at 13:30, Jan Wieck wrote:
>
> > The best cure for a problem is avoiding it. I would suggest
> > running the light weight VACUUM more often, so that it
> > doesn't grow that big in the first place.
>
> I think everybody is missing my point. This entire database is vacuumed
> every HOUR. All the tables are reasonably sized, and they stay that
> way. Except, the magic pg_toast table where long objects from resp_body
> are store is growing and growing and growing and growing and does not
> seem to respond to VACUUM whatsoever.
You actually did a VACUUM FULL and it didn't shrink? In 7.2
no table does shrink on a normal VACUUM. So if you don't run
VACUUM FULL, it cannot!
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
On Mon, 2002-03-11 at 14:07, Jan Wieck wrote: > Jeffrey W. Baker wrote: > > On Mon, 2002-03-11 at 13:30, Jan Wieck wrote: > > > > > The best cure for a problem is avoiding it. I would suggest > > > running the light weight VACUUM more often, so that it > > > doesn't grow that big in the first place. > > > > I think everybody is missing my point. This entire database is vacuumed > > every HOUR. All the tables are reasonably sized, and they stay that > > way. Except, the magic pg_toast table where long objects from resp_body > > are store is growing and growing and growing and growing and does not > > seem to respond to VACUUM whatsoever. > > You actually did a VACUUM FULL and it didn't shrink? In 7.2 > no table does shrink on a normal VACUUM. So if you don't run > VACUUM FULL, it cannot! You still don't understand my problem. I insert into this database at the rate of 1000 rows per hour. Every hour, I delete the rows that are more than 1 day old and vacuum. Thus, the maximum size of the table should be 24 * 1000 = 24000 rows and the file size should be stable. HOWEVER The actual observed behavior is that the file simply grows constantly. Forever. No stability. Period. Despite the fact that select count(*) from table == a constant. -jwb
Jeffrey W. Baker wrote:
> On Mon, 2002-03-11 at 14:07, Jan Wieck wrote:
> > You actually did a VACUUM FULL and it didn't shrink? In 7.2
> > no table does shrink on a normal VACUUM. So if you don't run
> > VACUUM FULL, it cannot!
>
> You still don't understand my problem. I insert into this database at
> the rate of 1000 rows per hour. Every hour, I delete the rows that are
> more than 1 day old and vacuum. Thus, the maximum size of the table
> should be 24 * 1000 = 24000 rows and the file size should be stable.
>
> HOWEVER
>
> The actual observed behavior is that the file simply grows constantly.
> Forever. No stability. Period. Despite the fact that select count(*)
> from table == a constant.
Get the name of the toast table with
SELECT T.relname FROM pg_class T, pg_class R
WHERE R.relname = '<your main tables name>'
AND R.reltoastrelid = T.oid;
Now query that toast table with:
SELECT sum(length(chunk_data)) FROM pg_toast_????????;
What is the length sum and how big is the real file?
Another question, do you frequently restart the postmaster? I
don't know for sure, but maybe a restart of the postmaster
will cause you to loose the freespace map for the relation
and therefore all new tuples go allways at the end, not into
some free'd space.
How big is the freespace map anyway, could this be an example
for that this table needs to be vacuumed even more often than
once per hour, Tom?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
On Mon, 2002-03-11 at 15:04, Jan Wieck wrote: > Get the name of the toast table with > > SELECT T.relname FROM pg_class T, pg_class R > WHERE R.relname = '<your main tables name>' > AND R.reltoastrelid = T.oid; > > Now query that toast table with: > > SELECT sum(length(chunk_data)) FROM pg_toast_????????; > > What is the length sum and how big is the real file? Unfortunately during the course of this conversation I was forced to dump and restore the database because of critical shortage of disk space. The query now returns 362619750. pg_class says 48080008 is stored in .../data/base/.../48080010, which has size 420536320. > > Another question, do you frequently restart the postmaster? I > don't know for sure, but maybe a restart of the postmaster > will cause you to loose the freespace map for the relation > and therefore all new tuples go allways at the end, not into > some free'd space. I don't ever restart it unless except to upgrade the software. The last time was 23 days ago. > How big is the freespace map anyway, could this be an example > for that this table needs to be vacuumed even more often than > once per hour, Tom? I don't know how to tell. -jwb
"Jeffrey W. Baker" <jwb@saturn5.com> writes:
>> How big is the freespace map anyway, could this be an example
>> for that this table needs to be vacuumed even more often than
>> once per hour, Tom?
> I don't know how to tell.
I think what Jan was suggesting was increasing the default freespace
map size parameters (see postgresql.conf) to see if that makes any
difference. I'm not sure if it will, but it'd be worth trying.
Note you will need a postmaster restart after editing postgresql.conf.
These are the relevant items:
#max_fsm_relations = 100 # min 10, fsm is free space map
#max_fsm_pages = 10000 # min 1000, fsm is free space map
regards, tom lane