Thread: relpages sudden increase
I currently do nightly database dumps on a ton of small dbs that are increasing around 2-3mb per day. Suddenly, in a recent backup file, one db in particular jumped from 55mb to 122mb overnight.
I did some investigation -
One table increased from 8mb to 31mb during a 24hr period. The table is just text so this is highly unusual given that the number of rows DIDN'T increase any more than normal.
pg_toast increased from 8mb to 27mb during the same period.
The relpages for the table in question increased from 164 to 1088 during the 24hr period. On the live db, the relpages is back to 164 but the size of the table remains massive.
Using Centos5/postgres 8.1.21 at the moment. Planning to upgrade over summer but not had issues like this before on 8.1.
What gives?!
Thanks
Rich
I did some investigation -
One table increased from 8mb to 31mb during a 24hr period. The table is just text so this is highly unusual given that the number of rows DIDN'T increase any more than normal.
pg_toast increased from 8mb to 27mb during the same period.
The relpages for the table in question increased from 164 to 1088 during the 24hr period. On the live db, the relpages is back to 164 but the size of the table remains massive.
Using Centos5/postgres 8.1.21 at the moment. Planning to upgrade over summer but not had issues like this before on 8.1.
What gives?!
Thanks
Rich
On 8.5.2012 19:27, Richard Harley wrote: > I currently do nightly database dumps on a ton of small dbs that are > increasing around 2-3mb per day. Suddenly, in a recent backup file, one > db in particular jumped from 55mb to 122mb overnight. Well, I wouldn't call that 'massive size' but in my experience such sudden changes in sizes are caused by one of these two things (a) modification patterns + slow vacuuming (b) batch updates (e.g. update of a column in the whole table) Given that this is a one-time issue, I'd guess it's (b). Were you doing any such updates or something like that? > I did some investigation - > > One table increased from 8mb to 31mb during a 24hr period. The table is > just text so this is highly unusual given that the number of rows DIDN'T > increase any more than normal. What do you mean by 'number of rows'? Is that number of live rows, i.e. the number you get from SELECT COUNT(*) FROM ... or the number you get from pg_class as reltuples? > pg_toast increased from 8mb to 27mb during the same period. > > The relpages for the table in question increased from 164 to 1088 during > the 24hr period. On the live db, the relpages is back to 164 but the > size of the table remains massive. Hmmm, I wonder how the number of pages could drop, because that does not happen unless you run VACUUM FULL / CLUSTER or such commands. And that does not happen regularly. Also, how could the table size remain massive when the number of pages dropped to 164? Did you mean a different table or the whole database? > Using Centos5/postgres 8.1.21 at the moment. Planning to upgrade over > summer but not had issues like this before on 8.1. > > What gives?! Good idea. 8.1 is not supported for a long time, plus there are so many improvements since then? Tomas
On 09/05/12 00:00, Tomas Vondra wrote:
No I wasn't doing any maintenance work compared to the days and weeks previously when size was increasing by a few mb a day.
I mean the number of live rows. So the size on disk went up unexpectedly but the rows increase was normal and the data in the rows was normal - just like previous days.
The same table. I imported the db dump that suddenly got bigger into a test db and the table in question has 1088 relpages. In the live db, same table, we're back down to 132 and no vacuuming has taken place ..
Thanks for your help
Rich
On 8.5.2012 19:27, Richard Harley wrote:I currently do nightly database dumps on a ton of small dbs that are increasing around 2-3mb per day. Suddenly, in a recent backup file, one db in particular jumped from 55mb to 122mb overnight.Well, I wouldn't call that 'massive size' but in my experience such sudden changes in sizes are caused by one of these two things (a) modification patterns + slow vacuuming (b) batch updates (e.g. update of a column in the whole table) Given that this is a one-time issue, I'd guess it's (b). Were you doing any such updates or something like that?
No I wasn't doing any maintenance work compared to the days and weeks previously when size was increasing by a few mb a day.
I did some investigation - One table increased from 8mb to 31mb during a 24hr period. The table is just text so this is highly unusual given that the number of rows DIDN'T increase any more than normal.What do you mean by 'number of rows'? Is that number of live rows, i.e. the number you get from SELECT COUNT(*) FROM ... or the number you get from pg_class as reltuples?
I mean the number of live rows. So the size on disk went up unexpectedly but the rows increase was normal and the data in the rows was normal - just like previous days.
pg_toast increased from 8mb to 27mb during the same period. The relpages for the table in question increased from 164 to 1088 during the 24hr period. On the live db, the relpages is back to 164 but the size of the table remains massive.Hmmm, I wonder how the number of pages could drop, because that does not happen unless you run VACUUM FULL / CLUSTER or such commands. And that does not happen regularly. Also, how could the table size remain massive when the number of pages dropped to 164? Did you mean a different table or the whole database?
The same table. I imported the db dump that suddenly got bigger into a test db and the table in question has 1088 relpages. In the live db, same table, we're back down to 132 and no vacuuming has taken place ..
Thanks for your help
Rich