On Wed, Apr 30, 2008 at 11:09 AM, Gauri Kanekar
<meetgaurikanekar@gmail.com> wrote:
> relid | relname | n_tup_ins | n_tup_upd | n_tup_hot_upd | n_dead_tup
> -------+----------------+-----------+-----------+---------------+------------
> 16461 | table1 | 0 | 8352496 | 5389 | 8351242
>

Hmm.. So indeed there are very few HOT updates. What is the fillfactor
you are using for these tests ? If its much less than 100, the very
low percentage of HOT updates would make me guess that you are
updating one of the index columns. Otherwise at least the initial
updates until you fill up the free space should be HOT.

Thanks,
Pavan


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

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

"table1" structure :
id integer not null
code integer not null
crid integer not null
status character varying(1) default 'A'::character varying
delta1 bigint default 0
delta2 bigint default 0
delta3 bigint default 0
delta4 bigint default 0
tz_id integer default 0
Indexes:
"idx1" PRIMARY KEY, btree (id)
"idx2" UNIQUE, btree (code, crid)
"idx3" btree (tz_id)
"idx4" btree (status)

code as crid are foreign key.

update table1 set delta1 = 100 where code/1000000 =999;


On Wed, Apr 30, 2008 at 12:16 PM, Gauri Kanekar <meetgaurikanekar@gmail.com>
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.
>
>
>
> On Wed, Apr 30, 2008 at 12:13 PM, Pavan Deolasee <pavan.deolasee@gmail.com>
> wrote:
>
> > On Wed, Apr 30, 2008 at 11:09 AM, Gauri Kanekar
> > <meetgaurikanekar@gmail.com> wrote:
> > > relid | relname | n_tup_ins | n_tup_upd | n_tup_hot_upd |
> > n_dead_tup
> > >
> > -------+----------------+-----------+-----------+---------------+------------
> > > 16461 | table1 | 0 | 8352496 | 5389 | 8351242
> > >
> >
> > Hmm.. So indeed there are very few HOT updates. What is the fillfactor
> > you are using for these tests ? If its much less than 100, the very
> > low percentage of HOT updates would make me guess that you are
> > updating one of the index columns. Otherwise at least the initial
> > updates until you fill up the free space should be HOT.
> >
> > Thanks,
> > Pavan
> >
> >
> > --
> > Pavan Deolasee
> > EnterpriseDB http://www.enterprisedb.com
> >
>
>
>
> --
> Regards
> Gauri




--
Regards
Gauri

On Wed, Apr 30, 2008 at 12:16 PM, Gauri Kanekar
<meetgaurikanekar@gmail.com> 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 ratio. It could be a very special case that
we might be looking at. I think a self contained test case or a very
detail explanation of the exact usage is what we need to explain this
behavior. You may also try dropping non-critical indexes and test
again.

Btw, I haven't been able to reproduce this at my end. With the given
indexes and kind of updates, I get very high percentage of HOT
updates.

Thanks,
Pavan

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

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Gauri Kanekar wrote:
> HOT doesn't seems to be working in our case.
>
> This is "table1" structure :
> id integer not null
> code integer not null
> crid integer not null
> status character varying(1) default 'A'::character varying
> delta1 bigint default 0
> delta2 bigint default 0
> delta3 bigint default 0
> delta4 bigint default 0
> tz_id integer default 0
> Indexes:
> "idx1" PRIMARY KEY, btree (id)
> "idx2" UNIQUE, btree (code, crid)
> "idx3" btree (tz_id)
> "idx4" btree (status)
>
> code as crid are foreign key.
>
> Here delta* fields get updated through out the day. and most of the time it
> may update the same row again n again.
>
> table1 contains around 12843694 records.
>
> Now not understanding y HOT don't work in our case.
>
> Changed fillfactor to 80, 75,70.... but nothing seems to work.

Did you dump and reload the table after setting the fill factor? It only
affects newly inserted data.

Another possibility is that there's a long running transaction in the
background, preventing HOT/vacuum from reclaiming the dead tuples.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

"Pavan Deolasee" <pavan.deolasee@gmail.com> 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/1000000 =999;

so all the rows being updated fall into a contiguous range of "code"
values. If the table was loaded in such a way that those rows were
also physically contiguous, then the updates would be localized and
would very soon run out of freespace on those pages.

regards, tom lane

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

fche@redhat.com (Frank Ch. Eigler) writes:
> Tom Lane <tgl@sss.pgh.pa.us> 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 from the VACUUM reclaim results?

Yeah, because the problem is that FSM is kept in shared memory which
cannot be resized on-the-fly.

In retrospect, trying to keep FSM in shared memory was a spectacularly
bad idea (one for which I take full blame). There is work afoot to
push it out to disk so that the whole problem goes away; so I don't see
much point in worrying about band-aid solutions.

regards, tom lane

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Pavan Deolasee" <pavan.deolasee@gmail.com> 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 "She" :-)

Oh yes. Apologies if I sounded harsh; did not mean that. I was just
completely confused why she is not seeing the HOT updates.

> >> update table1 set delta1 = 100 where code/1000000 =999;
>
> so all the rows being updated fall into a contiguous range of "code"
> values. If the table was loaded in such a way that those rows were
> also physically contiguous, then the updates would be localized and
> would very soon run out of freespace on those pages.
>

Yeah, that seems like the pattern. I tested with the similar layout
and a fill factor 80. The initial few bulk updates had comparatively
less HOT updates (somewhere 20-25%), But within 4-5 iterations of
updating the same set of rows, HOT updates were 90-95%. That's because
after few iterations (and because of non-HOT updates) the tuples get
scattered in various blocks, thus improving chances of HOT updates.

I guess the reason probably is that she is using fill factor for
indexes and not heap, but she hasn't yet confirmed.

Thanks,
Pavan

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

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

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 <pavan.deolasee@gmail.com>
wrote:

> On Wed, Apr 30, 2008 at 8:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "Pavan Deolasee" <pavan.deolasee@gmail.com> 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 "She" :-)
>
> Oh yes. Apologies if I sounded harsh; did not mean that. I was just
> completely confused why she is not seeing the HOT updates.
>
> > >> update table1 set delta1 = 100 where code/1000000 =999;
> >
> > so all the rows being updated fall into a contiguous range of "code"
> > values. If the table was loaded in such a way that those rows were
> > also physically contiguous, then the updates would be localized and
> > would very soon run out of freespace on those pages.
> >
>
> Yeah, that seems like the pattern. I tested with the similar layout
> and a fill factor 80. The initial few bulk updates had comparatively
> less HOT updates (somewhere 20-25%), But within 4-5 iterations of
> updating the same set of rows, HOT updates were 90-95%. That's because
> after few iterations (and because of non-HOT updates) the tuples get
> scattered in various blocks, thus improving chances of HOT updates.
>
> I guess the reason probably is that she is using fill factor for
> indexes and not heap, but she hasn't yet confirmed.
>
> Thanks,
> Pavan
>
> --
> Pavan Deolasee
> EnterpriseDB http://www.enterprisedb.com
>



--
Regards
Gauri