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 <[EMAI
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 t
[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 automaticall
"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
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.
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.
CLUSTER
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 via
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
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 u
"table1" structure :
idintegernot null
codeintegernot null
cridintegernot null
statuscharacter varying(1)default 'A'::character varying
delta1bigintdefault 0
delta2bigintdefault 0
delta3bigintdefault 0
delta
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 |83
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]>
wrot
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';
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
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.
HO
[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 d
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 opera
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 freque
"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 chan
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 h
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 graduall
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
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, Consult
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
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
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
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 w
>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 ma
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
th
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 tupl
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 a
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 Ka
"Gauri Kanekar" <[EMAIL PROTECTED]> writes:
> 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 down.
As has been pointed out to you repeatedly, "vacuum" hasn't required
exclusive lock si
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 kic
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
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
s
[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
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 th
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 Sull
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 PROTECT
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
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
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]>
w
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 opt
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
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, that
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.
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 havin
48 matches
Mail list logo
