Re: Replication Syatem

["Gauri Kanekar" <meetgaurikanekar(at)gmail(dot)com>]

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/1000000 = 999 ;
    this UPDATED 1230307 records
4. checked "table1" size again
     Reccnt =>   11970789
     Size ==> 2996.57MB
5. Again did the update, update "table1" set fld7 = 1000 where fld1/1000000 = 999 ;
    this UPDATED 1230307 records
6. Got "table1" size as
    RecCnt ==> 11970789
    Size ==> 3290.64
7. Updated again, update "table1" set fld7 = 1000 where fld1/1000000 = 999 ;
    this UPDATED 1230307 records
6. "table1" size as
    RecCnt ==> 11970789
    Size ==> 3584.66

Found that the size increased gradually. Is HOT working over here ??
Guide me if im doing something wrong.

~ Gauri

On Tue, Apr 29, 2008 at 4:55 PM, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> wrote:

On Tue, Apr 29, 2008 at 4:35 PM, Gauri Kanekar
<meetgaurikanekar(at)gmail(dot)com> 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 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 the index keys. If so, see if you can avoid having index
involving that column. Of course, I won't advocate dropping an index
if it would drastically impact your frequently run queries.

2. You may leave some free space in the heap (fillfactor less than
100). My recommendation would be to leave space worth of one row or
slightly more than that to let first UPDATE be an HOT update.
Subsequent UPDATEs in the page may reuse the dead row created by
earlier UPDATEs.

3. Avoid any long running transactions.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

-- 
Regards
Gauri