From: | Tomas Vondra <tv(at)fuzzy(dot)cz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: relpages sudden increase |
Date: | 2012-05-08 23:00:09 |
Message-ID: | 4FA9A579.8080203@fuzzy.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Angelico | 2012-05-09 03:52:37 | Seeking performance advice: Index for "recent entries" |
Previous Message | MD33 | 2012-05-08 22:05:46 | Re: COPY column order |