Thread: bloating vacuum
I am doing some experiment to understand the behaviour of manual vacuum.
I created small table and started doing insertion/deletion/updation on 2 rows in infinite loop. It started bloating around 844 times, but after it stopped bloating.. what could be the reason?
In between i am running manual vacuum analyze ( without full option)
On Tue, 2013-05-14 at 14:51 +0000, S H wrote: > I am doing some experiment to understand the behaviour of manual > vacuum. > > I created small table and started doing insertion/deletion/updation on > 2 rows in infinite loop. It started bloating around 844 times, but > after it stopped bloating.. what could be the reason? > > In between i am running manual vacuum analyze ( without full option) Explanation is described here http://www.postgresql.org/docs/9.1/static/sql-vacuum.html
On Tue, May 14, 2013 at 7:51 AM, S H <msq001@live.com> wrote:
Did autovacuum kick in and clean up the table?
Is autovacuum on? Type:
show autovacuum;
Is autovacuum on? Type:
show autovacuum;
You can watch the dead tuple count using *pgstattuple (an extension which can be installed via "create extension pgstattuple;".
Once installed,
select * from pgstattuple('name_of_your_table');
In between i am running manual vacuum analyze ( without full option)
After every iteration?
Got some information from following
What is the general solution to avoid bloating.
> On Tue, 2013-05-14 at 14:51 +0000, S H wrote:
> > I am doing some experiment to understand the behaviour of manual
> > vacuum.
> >
> > I created small table and started doing insertion/deletion/updation on
> > 2 rows in infinite loop. It started bloating around 844 times, but
> > after it stopped bloating.. what could be the reason?
> >
> > In between i am running manual vacuum analyze ( without full option)
>
> Explanation is described here
> http://www.postgresql.org/docs/9.1/static/sql-vacuum.html
>
>
>
> > I am doing some experiment to understand the behaviour of manual
> > vacuum.
> >
> > I created small table and started doing insertion/deletion/updation on
> > 2 rows in infinite loop. It started bloating around 844 times, but
> > after it stopped bloating.. what could be the reason?
> >
> > In between i am running manual vacuum analyze ( without full option)
>
> Explanation is described here
> http://www.postgresql.org/docs/9.1/static/sql-vacuum.html
>
>
>
On Tue, May 14, 2013 at 9:06 AM, S H <msq001@live.com> wrote: > Got some information from following > > http://www.depesz.com/2011/07/06/bloat-happens/ > > What is the general solution to avoid bloating. 1: Don't do massive deletes 2: Make sure your autovacuum is tuned aggressively enough to keep up with your workload 3: Make sure your hardware is fast enough to allow autovacuum to be tuned aggressively enough to keep up with your workload. Some minor bloating is fine. 10, 20, 50% dead space in a table is no big deal. OTOH, 99.99% dead space probably is.