Res: Res: Archive files growth!!!

From: paulo matadr <saddoness(at)yahoo(dot)com(dot)br>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: GENERAL <pgsql-general(at)postgresql(dot)org>
Subject: Res: Res: Archive files growth!!!
Date: 2008-11-17 14:53:34
Message-ID: 701925.43778.qm@web52507.mail.re2.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

result for your query

"cliente_fone" 341130 3345 26760k

________________________________
De: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Para: paulo matadr <saddoness(at)yahoo(dot)com(dot)br>
Cc: GENERAL <pgsql-general(at)postgresql(dot)org>
Enviadas: Sábado, 15 de Novembro de 2008 5:53:12
Assunto: Re: Res: [GENERAL] Archive files growth!!!

paulo matadr wrote:
>
> Let me see, for example one insert type (inser into table2 (select *
> from table1), where table have size 26megas,make 226megas for archives
> files.
> (i made a test with parameter wal_bufffer before this is defaul value
> 64k and the same commando make 640megas of archives after modify to
> 1024k a take 226m)
> however continuous confused for me.
> how 26mega of insert generate 226mega of archive.

OK, that's interesting, since those should be full WAL archives, so your
problem isn't to do with unused WAL tails.

When you say that the table is 26 MB, how did you measure that? Is that
the size of a dump of the table using pg_dump ? If so, that is not the
real size of the table as it is actually stored by PostgreSQL.

The best way to find the size of the table is by asking PostgreSQL:

SELECT relname, reltuples, relpages, relpages*8 AS size_kb
FROM pg_class WHERE relname = 'tablename' ORDER BY relpages DESC ;

(I'm assuming that your database uses the default 8kb page size).

This will be quite a bit bigger than the size of the dump.

I am also fairly sure that the WAL contains a record of what is done to
the indexes as well as to the tables. If you have lots of indexes,
especially multi-column indexes, on the table you are interested in then
the WAL files generated by an INSERT will be a lot bigger than the
amount of data inserted.

It might help if you could upload some example data and SQL somewhere.
Right now it is not possible to see what you are doing, so there is a
lot of guesswork involved.

--
Craig Ringer

Veja quais são os assuntos do momento no Yahoo! +Buscados
http://br.maisbuscados.yahoo.com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gustavo Rosso 2008-11-17 15:08:02 ERROR
Previous Message Lennin Caro 2008-11-17 14:51:29 Re: ERROR