Skip site navigation (1) Skip section navigation (2)

Re: Vacuuming on heavily changed databases

From: Dragan Zubac <zubac(at)vlayko(dot)tv>
To: Bohdan Linda <bohdan(dot)linda(at)seznam(dot)cz>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Vacuuming on heavily changed databases
Date: 2008-05-19 23:44:26
Message-ID: 483210DA.2010004@vlayko.tv (view raw or flat)
Thread:
Lists: pgsql-general
Hello

I have some similar situation like Yours,we're using at the moment PG 
8.2.0. At the moment we do manually vacuum (one or more times to 
minimize 'dead' data/tuples),and if necessary we do 'full' vacuum. On 
heavy-updated PG,one surely must think of this procedures because they 
are considered to be 'daily maintenance routine'. Still haven't migrated 
to PG 8.3,which is planned,but one way to look at this problem is to 
have 'timeout aware applications',meaning when You fire up vacuum or 
some other command that will lock some (or all data), You application 
does not stop operating but put itself into a little 'sleep' until data 
became available again. Therefore Your frontend 
(apps,clients,whatsoever) will observe only a small glitch and not a 
corruption in database connectivity,operations,etc.

Also bear in mind that more TPS,more 'dead' data/tuples You will 
have,meaning the following:

1. Ordinary inserts

insert into foo (column1,column2) values (val1,val2);
insert into foo (column1,column2) values (val3,val4);
insert into foo (column1,column2) values (val5,val6);

3 separated transaction,guess it means 3 'dead' tuples ?

2. Multi-insert command

insert into foo (column1,column2) values 
(val1,val2),(val3,val4),(val5,val6);

1 transaction,guess it means 1 'dead' tuples ?

I'm not sure about this,but guess somebody will correct me if I'm wrong :)

The questions are:

Is number of transactions related to the number of 'dead' rows in PG ? 
Meaning less transactions,means less frequently vacuum needed for same 
amount of data ?

Sincerely

Dragan

Bohdan Linda wrote:
> Hello,
>
> I would like to ask an opinion on vacuuming general. Imagine situation
> that you have single table with 5 fields (one varchar). This table has
> during the day
>
> - cca 620 000 inserts
> - 0 updates
> - cca 620 000 deletes
>   


In response to

Responses

pgsql-general by date

Next:From: Sam MasonDate: 2008-05-20 00:04:30
Subject: Re: Join three fields into one on same table
Previous:From: Decibel!Date: 2008-05-19 23:39:33
Subject: Re: DB page cache/query performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group