On Wed, Apr 30, 2008 at 11:09 AM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
relid |relname | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_dead_tup
---++---+---+---+
16461 | table1 | 0 | 8352496 | 5389 |
table1 structure :
idintegernot null
codeintegernot null
cridintegernot null
statuscharacter varying(1)default 'A'::character varying
delta1bigintdefault 0
delta2bigintdefault 0
delta3bigintdefault 0
delta4
On Wed, Apr 30, 2008 at 12:16 PM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
fillfactor is set to 80 as you suggested.
delta* fields r updated and these fields are no where related to any of the
index fields.
That's weird. With that fillfactor, you should have a very high
percentage of HOT update
Gauri Kanekar wrote:
HOT doesn't seems to be working in our case.
This is table1 structure :
idintegernot null
codeintegernot null
cridintegernot null
statuscharacter varying(1)default 'A'::character varying
delta1bigintdefault 0
Heikki Linnakangas wrote:
Did you dump and reload the table after setting the fill factor? It only
affects newly inserted data.
VACUUM FULL or CLUSTER should do the job too, right? After all, they
recreate the table so they must take the fillfactor into account.
--
Craig Ringer
--
Sent
Craig Ringer wrote:
Heikki Linnakangas wrote:
Did you dump and reload the table after setting the fill factor? It
only affects newly inserted data.
VACUUM FULL or CLUSTER should do the job too, right? After all, they
recreate the table so they must take the fillfactor into account.
Please keep list in the loop.
On Wed, Apr 30, 2008 at 6:45 PM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
Hi,
We have recreated the indices with fillfactor set to 80, which has improved
HOT
a little,
Wait. Did you say, you recreated the indexes with fill factor ? That's
no help for HOT. You
Pavan Deolasee [EMAIL PROTECTED] writes:
That's weird. With that fillfactor, you should have a very high
percentage of HOT update ratio. It could be a very special case that
we might be looking at.
He's testing
update table1 set delta1 = 100 where code/100 =999;
so all the rows being
[EMAIL PROTECTED] (Frank Ch. Eigler) writes:
Tom Lane [EMAIL PROTECTED] writes:
Also, you need to make sure you have the FSM parameters set high enough
so that all the free space found by a VACUUM run can be remembered.
Would it be difficult to arrange FSM parameters to be automatically
set
On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane [EMAIL PROTECTED] wrote:
Pavan Deolasee [EMAIL PROTECTED] writes:
That's weird. With that fillfactor, you should have a very high
percentage of HOT update ratio. It could be a very special case that
we might be looking at.
He's testing
It's
We have tried fillfactor for indices and it seems to work.
Need to try fillfactor for table. May for that reason the bulk update
queries don't get the advantage of HOT
:)
On Wed, Apr 30, 2008 at 9:45 PM, Pavan Deolasee [EMAIL PROTECTED]
wrote:
On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane [EMAIL
On Tue, 29 Apr 2008, Gauri Kanekar wrote:
We do vacuum full, as vacuum verbose analyse dont regain space for us.
Ah, now we're getting to the root of your problem here. You expect that
VACUUM should reclaim space.
Whenever you UPDATE a row, it writes a new one out, then switches to use
From most of the reply found that upgrade to higher version of postgres may
be to 8.3.1 may be one of the solution to tackle this problem
Checked about HOT feature in 8.3.1.
Do we need to do any special config changes or any other setting for HOT to
work??
Any special guideline to follow to
Gauri Kanekar wrote:
Andrew,
Can you explain me in detail why u said vacuum full is making the things
worst.
We do vacuum full, as vacuum verbose analyse dont regain space for us.
vacuum full stops all access so that the data files can be re-writen
without the unused space.
normal vacuum
On Tue, Apr 29, 2008 at 4:35 PM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
Do we need to do any special config changes or any other setting for HOT to
work??
No. HOT is enabled by default, on all tables. There is no way and need
to disable it.
Any special guideline to follow to make HOT
Thanx for the help.
Need some more help.
table1 has two indices
unique indx1 = pkfld
unique indx2 = fkfld1,fkfld2
did following steps in the listed order -
1. vacuumed the whole DB
2. table1
RecCnt == 11970789
Size == 2702.41 MB
3.update table1 set fld7 = 1000 where fld1/100 =
Gauri Kanekar escribió:
Do we need to do any special config changes or any other setting for HOT to
work??
No. HOT is always working, if it can. You don't need to configure it.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command
Gauri Kanekar escribió:
Found that the size increased gradually. Is HOT working over here ??
Guide me if im doing something wrong.
Probably not. Try vacuuming between the updates.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication,
On Tue, Apr 29, 2008 at 6:29 PM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
Found that the size increased gradually. Is HOT working over here ??
Guide me if im doing something wrong.
You have chosen a bad case for HOT. Since you are repeatedly updating
the same set of rows, the dead space
Thats how our updates works.
We usually tend to touch the same row many times a day.
~ Gauri
On Tue, Apr 29, 2008 at 6:39 PM, Pavan Deolasee [EMAIL PROTECTED]
wrote:
On Tue, Apr 29, 2008 at 6:29 PM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
Found that the size increased gradually. Is HOT
On Tue, Apr 29, 2008 at 6:42 PM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
Thats how our updates works.
We usually tend to touch the same row many times a day.
Then start with a non-100 fillfactor. I would suggest something like
80 and then adjust based on the testing. Since you are anyways have
Pavan Deolasee [EMAIL PROTECTED] writes:
Any special guideline to follow to make HOT working??
You can do couple of things to benefit from HOT.
1. HOT addresses a special, but common case where UPDATE operation
does not change any of the index keys. So check if your UPDATE changes
any of
Greg Smith [EMAIL PROTECTED] writes:
The model here assumes that you'll need that space again for the next time
you UPDATE or INSERT a row. So instead VACUUM just keeps those available
for database reuse rather than returning it to the operating system.
Now, if you don't VACUUM frequently
On Apr 29, 2008, at 10:16 AM, Tom Lane wrote:
Greg Smith [EMAIL PROTECTED] writes:
The model here assumes that you'll need that space again for the
next time
you UPDATE or INSERT a row. So instead VACUUM just keeps those
available
for database reuse rather than returning it to the
[EMAIL PROTECTED] (Gauri Kanekar) writes:
Basically we have some background process which updates table1 and
we don't want the application to make any changes to table1 while
vacuum. Vacuum requires exclusive lock on table1 and if any of
the background or application is ON vacuum don't kick
Alvaro Herrera wrote:
Gauri Kanekar escribió:
Do we need to do any special config changes or any other setting for HOT to
work??
No. HOT is always working, if it can. You don't need to configure it.
Unless you have upgraded since you started this thread you are still
running 8.1.3.
HOT doesn't seems to be working in our case.
This is table1 structure :
idintegernot null
codeintegernot null
cridintegernot null
statuscharacter varying(1)default 'A'::character varying
delta1bigintdefault 0
delta2bigint
On Wed, Apr 30, 2008 at 10:59 AM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
HOT doesn't seems to be working in our case.
Can you please post output of the following query ?
SELECT relid, relname, n_tup_ins, n_tup_upd, n_tup_hot_upd, n_dead_tup
from pg_stat_user_tables WHERE relname = 'table1';
relid |relname | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_dead_tup
---++---+---+---+
16461 | table1 | 0 | 8352496 | 5389 |8351242
On Wed, Apr 30, 2008 at 11:07 AM, Pavan Deolasee [EMAIL PROTECTED]
All,
We have a table table1 which get insert and updates daily in high numbers,
bcoz of which its size is increasing and we have to vacuum it every
alternate day. Vacuuming table1 take almost 30min and during that time the
site is down.
We need to cut down on this downtime.So thought of having a
2008/4/28 Gauri Kanekar [EMAIL PROTECTED]:
All,
We have a table table1 which get insert and updates daily in high
numbers, bcoz of which its size is increasing and we have to vacuum it every
alternate day. Vacuuming table1 take almost 30min and during that time the
site is down.
We need
Peter,
We are doing vacuum full every alternate day. We also do vacuum analyze very
often.
We are currently using 8.1.3 version.
Auto vacuum is already on. But the table1 is so busy that auto vacuum don't
get sufficient chance to vacuum it :(.
Have already tried all the option listed by you,
On Mon, 2008-04-28 at 19:35 +0530, Gauri Kanekar wrote:
Peter,
We are doing vacuum full every alternate day. We also do vacuum
analyze very often.
We are currently using 8.1.3 version.
Auto vacuum is already on. But the table1 is so busy that auto vacuum
don't get sufficient chance to
Gauri Kanekar wrote:
Peter,
We are doing vacuum full every alternate day. We also do vacuum analyze very
often.
We are currently using 8.1.3 version.
Auto vacuum is already on. But the table1 is so busy that auto vacuum don't
get sufficient chance to vacuum it :(.
Have already tried all the
Thats one of the thingsto be done in near future.
But it need some changes from application point of view. :( ... so just got
escalated for that reason.
But for now, which one will be a well suited replication system ?
~ Gauri
On Mon, Apr 28, 2008 at 7:43 PM, Brad Nicholson [EMAIL PROTECTED]
Salman,
Slony don't do automatic failover. And we would appreciate a system with
automatic failover :(
~ Gauri
On Mon, Apr 28, 2008 at 7:46 PM, salman [EMAIL PROTECTED]
wrote:
Gauri Kanekar wrote:
Peter,
We are doing vacuum full every alternate day. We also do vacuum analyze
very
On Mon, Apr 28, 2008 at 07:35:37PM +0530, Gauri Kanekar wrote:
Peter,
We are doing vacuum full every alternate day. We also do vacuum analyze very
often.
VACUUM FULL is making your problem worse, not better. Don't do that.
We are currently using 8.1.3 version.
You need immediately to
On Mon, Apr 28, 2008 at 07:48:48PM +0530, Gauri Kanekar wrote:
Slony don't do automatic failover. And we would appreciate a system with
automatic failover :(
No responsible asynchronous system will give you automatic failover.
You can lose data that way.
A
--
Andrew Sullivan
[EMAIL
On Mon, 28 Apr 2008, Gauri Kanekar wrote:
We are doing vacuum full every alternate day. We also do vacuum analyze
very often. We are currently using 8.1.3 version...Have already tried
all the option listed by you, thats y we reached to the decision of
having a replication sytsem.
Andrew
On Mon, Apr 28, 2008 at 9:38 AM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
All,
We have a table table1 which get insert and updates daily in high numbers,
bcoz of which its size is increasing and we have to vacuum it every
alternate day. Vacuuming table1 take almost 30min and during that time
[EMAIL PROTECTED] (Gauri Kanekar) writes:
We have a table table1 which get insert and updates daily in high
numbers, bcoz of which its size is increasing and we have to vacuum
it every alternate day. Vacuuming table1 take almost 30min and
during that time the site is down. We need to cut down
Basically we have some background process which updates table1 and we
don't want the application to make any changes to table1 while vacuum.
Vacuum requires exclusive lock on table1 and if any of the background or
application is ON vacuum don't kick off. Thats the reason we need to get the
site
But unless we do full vacuum the space is not recovered. Thats y we prefer
full vacuum.
~ Gauri
On Tue, Apr 29, 2008 at 10:38 AM, Greg Smith [EMAIL PROTECTED] wrote:
On Tue, 29 Apr 2008, Gauri Kanekar wrote:
Basically we have some background process which updates table1 and we
don't want
On Tue, 29 Apr 2008, Gauri Kanekar wrote:
Basically we have some background process which updates table1 and we
don't want the application to make any changes to table1 while vacuum.
Vacuum requires exclusive lock on table1 and if any of the background or
application is ON vacuum don't kick
Andrew,
Can you explain me in detail why u said vacuum full is making the things
worst.
We do vacuum full, as vacuum verbose analyse dont regain space for us.
~ Gauri
On Mon, Apr 28, 2008 at 9:52 PM, Andrew Sullivan [EMAIL PROTECTED]
wrote:
On Mon, Apr 28, 2008 at 07:35:37PM +0530, Gauri
On Tue, Apr 29, 2008 at 10:41 AM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
But unless we do full vacuum the space is not recovered. Thats y we prefer
full vacuum.
There is no point in recovering the space by moving tuples and
truncating the relation (that's what VACUUM FULL does) because you are
On Tue, Apr 29, 2008 at 11:16 AM, Gauri Kanekar
[EMAIL PROTECTED] wrote:
Andrew,
Can you explain me in detail why u said vacuum full is making the things
worst.
1. VACUUM FULL takes exclusive lock on the table. That makes table
unavailable for read/writes.
2. VACUUM FULL moves live tuples
47 matches
Mail list logo
