Thread: DB become enormous with continuos insert and update

DB become enormous with continuos insert and update

From
Hfe80
Date:
Some one know how to solve the problem of db that became huge from 700 Mbyte
to 16 Gbyte in 10 days and grow...
Vacuum doesn't solve the problem.
Only vacuum full is too invasive, the only way to reduce the DB  is dump and
restore, but we cannot stop service...

I know that a in a similar db where there is a lot of update the problem was
solved but I don't know how...
Some one Know this problem?

Thank's :)
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/DB-become-enormous-with-continuos-insert-and-update-tp3237814p3237814.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: DB become enormous with continuos insert and update

From
"Joshua D. Drake"
Date:
On Tue, 2010-10-26 at 14:01 -0700, Hfe80 wrote:
> Some one know how to solve the problem of db that became huge from 700 Mbyte
> to 16 Gbyte in 10 days and grow...
> Vacuum doesn't solve the problem.
> Only vacuum full is too invasive, the only way to reduce the DB  is dump and
> restore, but we cannot stop service...
>
> I know that a in a similar db where there is a lot of update the problem was
> solved but I don't know how...
> Some one Know this problem?

You are not vacuuming enough. If you vacuuum more often you should be
able to keep bloat down.

Joshua D. Drake

>
> Thank's :)
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/DB-become-enormous-with-continuos-insert-and-update-tp3237814p3237814.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


Re: DB become enormous with continuos insert and update

From
Gabriele Bartolini
Date:
Hi,

Il 26/10/10 23:01, Hfe80 ha scritto:
> Some one know how to solve the problem of db that became huge from 700 Mbyte
> to 16 Gbyte in 10 days and grow...
Which version of PostgreSQL are you using?
> Some one Know this problem?
Yes. Welcome to the "Bloating" club. :)

Ciao,
Gabriele

--
  Gabriele Bartolini - 2ndQuadrant Italia
  PostgreSQL Training, Services and Support
  gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it


Re: DB become enormous with continuos insert and update

From
John R Pierce
Date:
On 10/26/10 2:01 PM, Hfe80 wrote:
> Some one know how to solve the problem of db that became huge from 700 Mbyte
> to 16 Gbyte in 10 days and grow...
> Vacuum doesn't solve the problem.
> Only vacuum full is too invasive, the only way to reduce the DB  is dump and
> restore, but we cannot stop service...
>
> I know that a in a similar db where there is a lot of update the problem was
> solved but I don't know how...
> Some one Know this problem?

if you have a lot of updates of small non-indexed fields, use a
fillfactor of something like 60 or 70% so that the HOT thing can do its
job (assuming 8.3+).    do be sure to have autovacuum running and
cranked up sufficiently that its keeping up with the workload.

never do VACUUM FULL.   Rather, use CLUSTER to rebuild heavily used
tables in order of the most frequently used key (typically the PK),
however this requires a global table lock for the duration, so should
only be used when your application is relatively inactive.



Re: DB become enormous with continuos insert and update

From
Peter Geoghegan
Date:
On 26 October 2010 22:01, Hfe80 <federico.mo@gmail.com> wrote:
> Some one know how to solve the problem of db that became huge from 700 Mbyte
> to 16 Gbyte in 10 days and grow...
> Vacuum doesn't solve the problem.
> Only vacuum full is too invasive, the only way to reduce the DB  is dump and
> restore, but we cannot stop service...
>
> I know that a in a similar db where there is a lot of update the problem was
> solved but I don't know how...
> Some one Know this problem?
>

This article describes why you should almost never use vacuum full:

http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959

You need to vacuum more aggressively.


--
Regards,
Peter Geoghegan

Re: DB become enormous with continuos insert and update

From
Hfe80
Date:
We tried everything...vacumm works.
The problem is that updates need more space becouse datas is not overwritten
in the same space...
it seems that when there is huge update db grow to create space necessary
...700 Mb every time and than vacuum works but only since next update! and
db grow 700Mb more and 700 Mb more....

It seems a bug

Sorry for my english that isn't so good :(
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/DB-become-enormous-with-continuos-insert-and-update-tp3237814p3237903.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: DB become enormous with continuos insert and update

From
Alban Hertroys
Date:
On 27 Oct 2010, at 24:22, Hfe80 wrote:

> We tried everything...vacumm works.
> The problem is that updates need more space becouse datas is not overwritten
> in the same space...
> it seems that when there is huge update db grow to create space necessary
> ...700 Mb every time and than vacuum works but only since next update! and
> db grow 700Mb more and 700 Mb more....
>
> It seems a bug
>
> Sorry for my english that isn't so good :(

Do you have any long-running transactions?
Rows can't be reclaimed while there are transactions active in which those rows are visible. So if you have
long-runningtransactions, those could be "locking" the deleted rows' space and prevent vacuum from re-using that space
fornew rows. That's one reason that could explain your database growth. 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4cc75a3310291878029243!



Re: DB become enormous with continuos insert and update

From
Gabriele Bartolini
Date:
Hi,

Il 27/10/10 00:22, Hfe80 ha scritto:
> The problem is that updates need more space becouse datas is not overwritten
> in the same space...

As I said earlier, we need to know which PostgreSQL version you are
using. PostgreSQL 8.3 introduced Heap Only Tuples (HOT) updates. Is it
at least an 8.3 version (so 8.4 or 9.0)?

> it seems that when there is huge update db grow to create space necessary
> ...700 Mb every time and than vacuum works but only since next update! and
> db grow 700Mb more and 700 Mb more....

As John said earlier, you might want to look at the fillfactor attribute
for a table and lower it (see
http://www.postgresql.org/docs/current/static/sql-createtable.html - for
9.0) and make sure there is not a long transaction that keeps those rows
alive.

Another good place where to look and find more information is the
PostgreSQL catalog (pg_stat_user_tables table).

> It seems a bug
It is definitely not a bug, but a desired behaviour deriving from the
MVCC architecture.

Ciao,
Gabriele

--
  Gabriele Bartolini - 2ndQuadrant Italia
  PostgreSQL Training, Services and Support
  gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it


Re: DB become enormous with continuos insert and update

From
Vick Khera
Date:
On Tue, Oct 26, 2010 at 5:55 PM, John R Pierce <pierce@hogranch.com> wrote:
> never do VACUUM FULL.   Rather, use CLUSTER to rebuild heavily used tables
> in order of the most frequently used key (typically the PK), however this
> requires a global table lock for the duration, so should only be used when
> your application is relatively inactive.

Another trick I like to use is to alter a table field from type
integer to type integer.  This causes pg to rewrite the table without
trying to do any sorting that cluster would imply, and results in
basically the same end result, and is MVCC safe to boot.

Re: DB become enormous with continuos insert and update

From
Hfe80
Date:
Thank to all
I couldn't respond yesterday but we had tried all you suggestion in the past
weeks.

But nothing change really
Now finally have resolve the problem!!!

It was a bug of version 8.1.
We solve all our problem simply update to 8.4 !!!

It's incredible, DB don't change dimension even after 1 million insert and
update.
CPUs average load  drastically is fallen down!

before 16 core server has an incredible average load from 2 to 16!
Now 16 core server now have nothing to do! :)  average load  is 0,1   :)

NB Thank you to friends of  http://www.nikos.it nikos  that has give us the
right suggestion.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/DB-become-enormous-with-continuos-insert-and-update-tp3237814p3240118.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: DB become enormous with continuos insert and update

From
Gabriele Bartolini
Date:
Hello,

> It was a bug of version 8.1.
> We solve all our problem simply update to 8.4 !!!

Well ... it was not a bug, actually a feature that was not yet
implemented.

My first question about which version of PostgreSQL you were using in this
thread was exactly for that purpose, but ... you never replied to that.

Next time, please post the version you are using and possibly the
operating system.

> It's incredible, DB don't change dimension even after 1 million insert
and
> update.
> CPUs average load  drastically is fallen down!

Glad you fixed it.

Ciao,
Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 Gabriele.Bartolini@2ndQuadrant.it - www.2ndQuadrant.it
 http://www.2ndquadrant.com/books/