FAQ

[PostgreSQL] pg_toast table growth out of control

Jeffrey W. Baker
Mar 11, 2002 at 5:34 pm
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
reply

Search Discussions

10 responses

  • Jeffrey W. Baker at Mar 11, 2002 at 7:00 pm
    (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
  • John Gray at Mar 11, 2002 at 8:20 pm

    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
  • Jeffrey W. Baker at Mar 11, 2002 at 8:23 pm

    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
  • Jan Wieck at Mar 11, 2002 at 9:35 pm

    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
  • Jeffrey W. Baker at Mar 11, 2002 at 9:38 pm

    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
  • Jan Wieck at Mar 11, 2002 at 10:10 pm

    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
  • Jeffrey W. Baker at Mar 11, 2002 at 10:19 pm

    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
  • Jan Wieck at Mar 11, 2002 at 11:07 pm

    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
  • Jeffrey W. Baker at Mar 11, 2002 at 11:28 pm

    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
  • Tom Lane at Mar 12, 2002 at 2:24 am

    "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

Related Discussions