Обсуждение: big database with very small dump !?

Hello all,

        I'm finding it very strange that my pg takes 9Giga on disk but
        pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed
        yesterday.

        Is this normal ? Should I be worried ?


        details bellow:
        ------------------------------------------
        # pg_dumpall --oids --clean > pg_dumpall.sql
        # ls -lh
        total 232M
        -rw-r--r--    1 postgres postgres     231M Aug 11 15:46
        pg_dumpall.sql
        # du -sh /var/pgsql/data/
        9.4G    /var/pgsql/data
        ------------------------------------------


        thx
        joao

On Mon, 11 Aug 2008, Joao Ferreira gmail wrote:

> I'm finding it very strange that my pg takes 9Giga on disk but
> pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed
> yesterday.

If you've been running VACUUM FULL, it's probably so-called "index bloat".
Try running the query at http://wiki.postgresql.org/wiki/Disk_Usage to
figure out where all your space has gone inside the database.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Mon, Aug 11, 2008 at 10:30 AM, Joao Ferreira gmail
<joao.miguel.c.ferreira@gmail.com> wrote:
> Hello all,
>
>        I'm finding it very strange that my pg takes 9Giga on disk but
>        pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed
>        yesterday.
>
>        Is this normal ? Should I be worried ?

It really depends.  If you've got a lot of indexes then maybe that's
normal.  But most the time you don't see more than about 4 times the
space used in the db as in the dump.

It's likely you've got index bloat.  If you reload a pg_dump of the
database in question into another server how much space does that take
up?  Look into using CLUSTER or REINDEX to fix the space usage.

I'd do
du -sh /var/pgsql/data/base
rather then /var/pgsql/data
depending on how your pgsql server logging is setup, there are other
folders and/or files that might take considerable disk space under
./data/ you may want to exclude those. I find this query useful for
something like this as well:

select datname,pg_size_pretty(pg_database_size(oid)) from pg_database ;

V.

Joao Ferreira gmail wrote:
> Hello all,
>
>         I'm finding it very strange that my pg takes 9Giga on disk but
>         pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed
>         yesterday.
>
>         Is this normal ? Should I be worried ?
>
>
>         details bellow:
>         ------------------------------------------
>         # pg_dumpall --oids --clean > pg_dumpall.sql
>         # ls -lh
>         total 232M
>         -rw-r--r--    1 postgres postgres     231M Aug 11 15:46
>         pg_dumpall.sql
>         # du -sh /var/pgsql/data/
>         9.4G    /var/pgsql/data
>         ------------------------------------------
>
>
>         thx
>         joao
>
>
>
>
>

On Mon, 2008-08-11 at 12:43 -0700, Vlad Kosilov wrote:
> ./data/ you may want to exclude those. I find this query useful for
> something like this as well:
>
> select datname,pg_size_pretty(pg_database_size(oid)) from pg_database ;
>

Hello Vlad,

I ran your query and I got the 9Gigas!

I guess it should be related to index bloating, then. Do you agree ?

thx
Joao



postgres=# select datname,pg_size_pretty(pg_database_size(oid)) from
pg_database;
    datname    | pg_size_pretty
---------------+----------------
 postgres      | 3617 kB
 egbert        | 9585 MB
 asterisk      | 3993 kB
 turba         | 3673 kB
 edgereporting | 3617 kB
 template1     | 3617 kB
 template0     | 3537 kB
(7 rows)

postgres=#


> V.
>
> Joao Ferreira gmail wrote:
> > Hello all,
> >
> >         I'm finding it very strange that my pg takes 9Giga on disk but
> >         pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed
> >         yesterday.
> >
> >         Is this normal ? Should I be worried ?
> >
> >
> >         details bellow:
> >         ------------------------------------------
> >         # pg_dumpall --oids --clean > pg_dumpall.sql
> >         # ls -lh
> >         total 232M
> >         -rw-r--r--    1 postgres postgres     231M Aug 11 15:46
> >         pg_dumpall.sql
> >         # du -sh /var/pgsql/data/
> >         9.4G    /var/pgsql/data
> >         ------------------------------------------
> >
> >
> >         thx
> >         joao
> >
> >
> >
> >
> >
>

Hello Greg, Vlad, Scott and all,

thanks for the feedback.

O forgot to mention that I execute REINDEX on all tables and INDEXes
every week (right after executing VACUUM FULL).

Is this enough to eliminate the possibility of "index bloat" ?



and, yes, my database has some crazy indexes. I use these indexes, and I
keep them REINDEXed to keep query execution time down. see bellow.

could these indexes be the real reason for taking up all that space ?

thanks
joao



egbert=# \d timeslots;
    Table "public.timeslots"
  Column   |  Type   | Modifiers
-----------+---------+-----------
 str1      | text    |
 str2      | text    |
 ...
 ...
 str20     | text    |
 val1      | real    |
 ...
...
 val6      | real    |
 var       | text    |
 count     | integer |
 total     | real    |
 timeslot  | integer | not null
 timestamp | integer | not null
 tsws      | integer | not null
 tses      | integer | not null
Indexes:
    "timeslots_strs_var_ts_key" UNIQUE, btree (str1, str2, str3, str4,
str5, str6, str7, str8, str9, str10, str11, str12, str13, str14, str15,
str16, str17, str18, str19, str20, var, timeslot) CLUSTER
    "timeslots_timeslot_index" btree (timeslot)
    "timeslots_timestamp_index" btree ("timestamp")
    "timeslots_var_index" btree (var)

egbert=#
------------------------------------------------------------


On Mon, 2008-08-11 at 12:45 -0400, Greg Smith wrote:
> On Mon, 11 Aug 2008, Joao Ferreira gmail wrote:
>
> > I'm finding it very strange that my pg takes 9Giga on disk but
> > pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed
> > yesterday.
>
> If you've been running VACUUM FULL, it's probably so-called "index bloat".
> Try running the query at http://wiki.postgresql.org/wiki/Disk_Usage to
> figure out where all your space has gone inside the database.
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, M

On Mon, 2008-08-11 at 12:45 -0400, Greg Smith wrote:
> On Mon, 11 Aug 2008, Joao Ferreira gmail wrote:
>
> > I'm finding it very strange that my pg takes 9Giga on disk but
> > pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed
> > yesterday.
>
> If you've been running VACUUM FULL, it's probably so-called "index bloat".
> Try running the query at http://wiki.postgresql.org/wiki/Disk_Usage to
> figure out where all your space has gone inside the database.
>


egbert=# SELECT nspname || '.' || relname AS "relation",
egbert-#     pg_size_pretty(pg_relation_size(nspname || '.' || relname))
AS "size"
egbert-#   FROM pg_class C
egbert-#   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
egbert-#   WHERE nspname NOT IN ('pg_catalog', 'information_schema')
egbert-#     AND nspname !~ '^pg_toast'
egbert-#     AND pg_relation_size(nspname || '.' || relname)>0
egbert-#   ORDER BY pg_relation_size(nspname || '.' || relname) DESC
egbert-#   LIMIT 20;

             relation             |  size
----------------------------------+---------
 public.timeslots_strs_var_ts_key | 5643 MB     #this is a UNIQUE clause
 public.timeslots                 | 2660 MB     #this is the only table
 public.timeslots_timestamp_index | 583 MB      #this is an index
 public.timeslots_var_index       | 314 MB      #this is an index
 public.timeslots_timeslot_index  | 275 MB      "this is an index
(5 rows)


so it seems that the UNIQUE clause is taking up more space than the data
itself...

stil I have 2660 MB of data but the dump is about 10x smaller !!!

any hints ?




> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>

In response to Joao Ferreira gmail <joao.miguel.c.ferreira@gmail.com>:
>
> On Mon, 2008-08-11 at 12:43 -0700, Vlad Kosilov wrote:
> > ./data/ you may want to exclude those. I find this query useful for
> > something like this as well:
> >
> > select datname,pg_size_pretty(pg_database_size(oid)) from pg_database ;
> >
>
> Hello Vlad,
>
> I ran your query and I got the 9Gigas!
>
> I guess it should be related to index bloating, then. Do you agree ?

No, the index size is included in pg_database_size().

Perhaps do a du -hd1 /var/pgsql/data to see which directories have all
the space.  (or du -h --max-depth=1 /var/pgsql/data on Linux)

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************

On Mon, 2008-08-11 at 10:58 -0600, Scott Marlowe wrote:

> It's likely you've got index bloat.  If you reload a pg_dump of the
> database in question into another server how much space does that take
> up?

right. just loaded the dump into a clean database and everything came
down about 10 times...
----------------------------------------------
NOW: (injected dump into fresh Pg):
             relation             |  size
----------------------------------+--------
 public.timeslots                 | 549 MB
 public.timeslots_strs_var_ts_key | 482 MB
 public.timeslots_var_index       | 59 MB
 public.timeslots_timeslot_index  | 37 MB
 public.timeslots_timestamp_index | 37 MB
(5 rows)


------------------------------------------------
BEFORE:
             relation             |  size
----------------------------------+---------
 public.timeslots_strs_var_ts_key | 5643 MB
 public.timeslots                 | 2660 MB
 public.timeslots_timestamp_index | 583 MB
 public.timeslots_var_index       | 314 MB
 public.timeslots_timeslot_index  | 275 MB


I'm confused here....

on the fresh database the whole set only takes 1.3G

on the original db, even after VACUUM FULL and REINDEX it takes 9G.

can I really do anything about it ?

If I try cluster, I'm guessing I'll choose the big index and forget
about the smaller ones... is this right ?

thanks
joao


thx


>  Look into using CLUSTER or REINDEX to fix the space usage.
>

Joao Ferreira gmail wrote:
> On Mon, 2008-08-11 at 12:45 -0400, Greg Smith wrote:
>> On Mon, 11 Aug 2008, Joao Ferreira gmail wrote:
>>
>>> I'm finding it very strange that my pg takes 9Giga on disk but
>>> pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed
>>> yesterday.
>> If you've been running VACUUM FULL, it's probably so-called "index bloat".
>> Try running the query at http://wiki.postgresql.org/wiki/Disk_Usage to
>> figure out where all your space has gone inside the database.
>>
>
>
> egbert=# SELECT nspname || '.' || relname AS "relation",
> egbert-#     pg_size_pretty(pg_relation_size(nspname || '.' || relname))
> AS "size"
> egbert-#   FROM pg_class C
> egbert-#   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
> egbert-#   WHERE nspname NOT IN ('pg_catalog', 'information_schema')
> egbert-#     AND nspname !~ '^pg_toast'
> egbert-#     AND pg_relation_size(nspname || '.' || relname)>0
> egbert-#   ORDER BY pg_relation_size(nspname || '.' || relname) DESC
> egbert-#   LIMIT 20;
>
>              relation             |  size
> ----------------------------------+---------
>  public.timeslots_strs_var_ts_key | 5643 MB     #this is a UNIQUE clause
>  public.timeslots                 | 2660 MB     #this is the only table
>  public.timeslots_timestamp_index | 583 MB      #this is an index
>  public.timeslots_var_index       | 314 MB      #this is an index
>  public.timeslots_timeslot_index  | 275 MB      "this is an index
> (5 rows)
>
>
> so it seems that the UNIQUE clause is taking up more space than the data
> itself...
>
> stil I have 2660 MB of data but the dump is about 10x smaller !!!
>
> any hints ?


I would try running a cluster on the table. This will usually clean up
things and free diskspace both in the table and the indexes.
It does require quite extensive locking though, so might not be an
option if you can't afford having the database unavailable for a few
(10-15) minutes.


--
Tommy Gildseth

Joao Ferreira gmail wrote:
> On Mon, 2008-08-11 at 10:58 -0600, Scott Marlowe wrote:
>
> If I try cluster, I'm guessing I'll choose the big index and forget
> about the smaller ones... is this right ?
>

CLUSTER will sort out all the indexes, even though you're just
clustering on on.


--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39

Hi guys,

If found the reason for all this problem.


explanation: vacuum reindex cron scripts were not being executed.

I executed the operations by hand and the values became normal.

thank you all for the fine discussion.

joao




On Tue, 2008-08-12 at 13:49 +0200, Tommy Gildseth wrote:
> Joao Ferreira gmail wrote:
> > On Mon, 2008-08-11 at 10:58 -0600, Scott Marlowe wrote:
> >
> > If I try cluster, I'm guessing I'll choose the big index and forget
> > about the smaller ones... is this right ?
> >
>
> CLUSTER will sort out all the indexes, even though you're just
> clustering on on.
>
>