DB become enormous with continuos insert and update
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.
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
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
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.
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
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.
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-running transactions, those could be "locking" the deleted rows' space and prevent vacuum from re-using that space for new 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!
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
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.
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.
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/