Re: [GENERAL] Table bloat in 8.3
On Thu, 13 Nov 2008, Scott Marlowe wrote: On Thu, Nov 13, 2008 at 1:09 PM, David Wilson [EMAIL PROTECTED] wrote: On Thu, Nov 13, 2008 at 2:03 PM, [EMAIL PROTECTED] wrote: I have several tables that when I run VACUUM FULL on, they are under 200k, but after a day of records getting added they grow to 10 to 20 megabytes. They get new inserts and a small number of deletes and updates. Can you define small number of deletes and updates? The stats above would disagree with small. Remember that every update creates a new, updated version of the row, which is where the increase is coming from. And don't forget to look into failed inserts. Those too create dead tuples. I finally figured it out. I have three tables, A with child B who has a child C. I had a query that would count all the rows in C and update the total to the rows they linked to in B, and then do the same with B to A. It basicly updated every row in A and B whenever it ran. It was supposed to only run if the counts got out of sync but a copy slipped into code that got run a LOT and so that was causing those tables to grow out of control. With that query removed I am no longer seeing the db expand. Thanks for the hints! -- Ian Smith www.ian.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table bloat in 8.3
I am somewhat new to Postgresql and am trying to figure out if I have a problem here. I have several tables that when I run VACUUM FULL on, they are under 200k, but after a day of records getting added they grow to 10 to 20 megabytes. They get new inserts and a small number of deletes and updates. A normal VACUUM does not shrink the table size, but FULL does, or dumping and restoring the database to a test server. I know that some extra space is useful so disk blocks don't need to be allocated for every insert, but this seems excessive. My question is... should I be worrying about this or is this expected behaviour? I can run a daily VACUUM but if this is indicating a configuration problem I'd like to know. Here is an example table. The disk size is reported at 14,049,280 bytes. pg_stat_user_tables for the live db... table size is 14,049,280 bytes. seq_scan | 32325 seq_tup_read | 39428832 idx_scan | 6590219 idx_tup_fetch| 7299318 n_tup_ins| 2879 n_tup_upd| 6829984 n_tup_del| 39 n_tup_hot_upd| 420634 n_live_tup | 2815 n_dead_tup | 0 And after it is dumped and restored... size is now 188,416 bytes. seq_scan | 8 seq_tup_read | 22520 idx_scan | 0 idx_tup_fetch| 0 n_tup_ins| 2815 n_tup_upd| 0 n_tup_del| 0 n_tup_hot_upd| 0 n_live_tup | 2815 n_dead_tup | 0 I checked for outstanding transactions and there are none. Thanks! -- Ian Smith -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table bloat in 8.3
On Thu, Nov 13, 2008 at 02:03:22PM -0500, [EMAIL PROTECTED] wrote: I have several tables that when I run VACUUM FULL on, they are under 200k, but after a day of records getting added they grow to 10 to 20 megabytes. They get new inserts and a small number of deletes and updates. A normal VACUUM does not shrink the table size, but FULL does, or dumping and restoring the database to a test server. I'd not expect to use a FULL vacuum as part of routine maintaince. Normally, tables like this will grow until they reach some steady state and then stay there. 14MB seems a bit big for something that you'd expect to fit in 200KB though. Autovacuum is enabled by default in 8.3, but has it been disabled for some reason here? A useful thing to post would be the output of a VACUUM VERBOSE on this table when it's grown for a day. It may give some clue as to what's going on. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table bloat in 8.3
That is the expected behavior. Postgres doesn't give back disk like Java doesn't give back memory. It keeps a map of where the free space is so it can use it again. It does all this so it doesn't have to lock the table to compact it when VACUUMing. VACUUM FULL does lock the table to compact it. In practice, if you keep your free space map large enough and you have enough rows, your tables settle down to a size close to what you'd expect. I hope that helps, --Nik On Thu, Nov 13, 2008 at 2:03 PM, [EMAIL PROTECTED] wrote: I am somewhat new to Postgresql and am trying to figure out if I have a problem here. I have several tables that when I run VACUUM FULL on, they are under 200k, but after a day of records getting added they grow to 10 to 20 megabytes. They get new inserts and a small number of deletes and updates. A normal VACUUM does not shrink the table size, but FULL does, or dumping and restoring the database to a test server. I know that some extra space is useful so disk blocks don't need to be allocated for every insert, but this seems excessive. My question is... should I be worrying about this or is this expected behaviour? I can run a daily VACUUM but if this is indicating a configuration problem I'd like to know. Here is an example table. The disk size is reported at 14,049,280 bytes. pg_stat_user_tables for the live db... table size is 14,049,280 bytes. seq_scan | 32325 seq_tup_read | 39428832 idx_scan | 6590219 idx_tup_fetch| 7299318 n_tup_ins| 2879 n_tup_upd| 6829984 n_tup_del| 39 n_tup_hot_upd| 420634 n_live_tup | 2815 n_dead_tup | 0 And after it is dumped and restored... size is now 188,416 bytes. seq_scan | 8 seq_tup_read | 22520 idx_scan | 0 idx_tup_fetch| 0 n_tup_ins| 2815 n_tup_upd| 0 n_tup_del| 0 n_tup_hot_upd| 0 n_live_tup | 2815 n_dead_tup | 0 I checked for outstanding transactions and there are none. Thanks! -- Ian Smith -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table bloat in 8.3
On Thu, Nov 13, 2008 at 2:03 PM, [EMAIL PROTECTED] wrote: I have several tables that when I run VACUUM FULL on, they are under 200k, but after a day of records getting added they grow to 10 to 20 megabytes. They get new inserts and a small number of deletes and updates. seq_scan | 32325 seq_tup_read | 39428832 idx_scan | 6590219 idx_tup_fetch| 7299318 n_tup_ins| 2879 n_tup_upd| 6829984 n_tup_del| 39 n_tup_hot_upd| 420634 n_live_tup | 2815 n_dead_tup | 0 Can you define small number of deletes and updates? The stats above would disagree with small. Remember that every update creates a new, updated version of the row, which is where the increase is coming from. -- - David T. Wilson [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table bloat in 8.3
On Thu, Nov 13, 2008 at 1:09 PM, David Wilson [EMAIL PROTECTED] wrote: On Thu, Nov 13, 2008 at 2:03 PM, [EMAIL PROTECTED] wrote: I have several tables that when I run VACUUM FULL on, they are under 200k, but after a day of records getting added they grow to 10 to 20 megabytes. They get new inserts and a small number of deletes and updates. seq_scan | 32325 seq_tup_read | 39428832 idx_scan | 6590219 idx_tup_fetch| 7299318 n_tup_ins| 2879 n_tup_upd| 6829984 n_tup_del| 39 n_tup_hot_upd| 420634 n_live_tup | 2815 n_dead_tup | 0 Can you define small number of deletes and updates? The stats above would disagree with small. Remember that every update creates a new, updated version of the row, which is where the increase is coming from. And don't forget to look into failed inserts. Those too create dead tuples. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
