Thread: GUC for default heap fillfactor
<br />If HOT gets into 8.3, we might need a GUC to set database wide heap<br />fillfactor to an appropriate value. The onlyway to so today is compile<br />time value of DEFAULT_HEAP_FILLFACTOR. HOT works much<br />better if there is atleastone tuple worth of free space in each block. <br />Otherwise atleast the first UPDATE in each block would be a COLD<br/>update.<br /><br clear="all" />Should we consider adding such a GUC now ? I searched through<br />archives, butdid not find any strong objection to doing so in the past. <br />But I might have missed something. Comments ?<br /><br/>Thanks,<br />Pavan<br /><br />-- <br />Pavan Deolasee<br />EnterpriseDB <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a>
On Wed, Aug 08, 2007 at 06:05:06PM +0530, Pavan Deolasee wrote: > If HOT gets into 8.3, we might need a GUC to set database wide heap > fillfactor to an appropriate value. The only way to so today is compile > time value of DEFAULT_HEAP_FILLFACTOR. HOT works much > better if there is atleast one tuple worth of free space in each block. > Otherwise atleast the first UPDATE in each block would be a COLD > update. > > Should we consider adding such a GUC now ? I searched through > archives, but did not find any strong objection to doing so in the past. > But I might have missed something. Comments ? ISTM if we add one for tables we should add one for indexes as well... Or do we want different fill factors for different index methods? -- Decibel!, aka Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
On Wed, 2007-08-08 at 18:05 +0530, Pavan Deolasee wrote: > > If HOT gets into 8.3, we might need a GUC to set database wide heap > fillfactor to an appropriate value. The only way to so today is > compile > time value of DEFAULT_HEAP_FILLFACTOR. HOT works much > better if there is atleast one tuple worth of free space in each > block. > Otherwise atleast the first UPDATE in each block would be a COLD > update. > > Should we consider adding such a GUC now ? I searched through > archives, but did not find any strong objection to doing so in the > past. > But I might have missed something. Comments ? I see why you think that, but it is really just the first update that is cold. All further updates on that block will be able to re-use the dead row left by the first update. If they can't they will spread out to other blocks where they will eventually have sufficiently reduced contention to allow hot updates. So this effect only happens immediately after the load of a table. Sure that effects the first few seconds or minutes, but then after that we will get into a steady state. If you can demonstrate that the performance after say 8 hours is significantly different because of such a fill factor then it is worth having. I doubt that is the case, but test results may prove me wrong. My take is that it would do nothing for longer term performance on heavily updated tables and have a negative effect on tables that are seldom updated. Overall, thats a loss, for me. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
"Pavan Deolasee" <pavan.deolasee@gmail.com> wrote: > If HOT gets into 8.3, we might need a GUC to set database wide heap > fillfactor to an appropriate value. I have no objection to do that, but we will need other default values soon, something like default_[heap|btree|hash|gist|gin]_fillfactor. Some of us might feel it is mess to add random guc variables. > HOT works much > better if there is atleast one tuple worth of free space in each block. I find it is more important. You say *number-of-tuples* is more suitable unit for fillfactor than *percentage*. It might be good to reconsider the unit when we add a database wide GUC parameter. It could be that there are a tables with 80 bytes length of tuples (1%) and one with 800 bytes length (10%) in the same database. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
On 8/8/07, Simon Riggs <simon@2ndquadrant.com> wrote:
That is mostly true, but may not work if the tuple length changes
with each update. The space freed up the previous update may
not be enough to accommodate the new tuple. Also we should
take the unusable space left by redirected line pointer.
I see why you think that, but it is really just the first update that is
cold. All further updates on that block will be able to re-use the dead
row left by the first update. If they can't they will spread out to
other blocks where they will eventually have sufficiently reduced
contention to allow hot updates.
That is mostly true, but may not work if the tuple length changes
with each update. The space freed up the previous update may
not be enough to accommodate the new tuple. Also we should
take the unusable space left by redirected line pointer.
My take is that it would do nothing for longer term performance on
heavily updated tables and have a negative effect on tables that are
seldom updated. Overall, thats a loss, for me.
OK. I wish to have a config parameter so that DBA can set
the system level default and then override that (if required) for
each table. It will also be useful for experimenting with different
fillfactor. The only way today is either to recompile your sources
or change the parameter in every CREATE TABLE statement.
Thanks,
Pavan
--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com
On Thu, 2007-08-09 at 12:17 +0530, Pavan Deolasee wrote: > OK. I wish to have a config parameter so that DBA can set > the system level default and then override that (if required) for > each table. It will also be useful for experimenting with different > fillfactor. The only way today is either to recompile your sources > or change the parameter in every CREATE TABLE statement. Why not just put an extra file into your test script that issues ALTER TABLE statements on appropriate tables? If you run this between CREATE and COPY you won't have to edit a thing. You'll be able to more easily control the tables you want to set, so you'll be in less danger of having a gain on one table wiped out by a loss on another table. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
On Thu, Aug 09, 2007 at 09:57:48AM +0900, ITAGAKI Takahiro wrote: > > If HOT gets into 8.3, we might need a GUC to set database wide heap > > fillfactor to an appropriate value. > > I have no objection to do that, but we will need other default values soon, > something like default_[heap|btree|hash|gist|gin]_fillfactor. Some of us > might feel it is mess to add random guc variables. I think we'd want to handle indexes with a different mechanism, probably one that makes changes to pg_am. In any case, how important is it to do this before 8.3? We were supposed to release this month, after all. -- Decibel!, aka Jim Nasby decibel@decibel.org EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Decibel! wrote: -- Start of PGP signed section. > On Thu, Aug 09, 2007 at 09:57:48AM +0900, ITAGAKI Takahiro wrote: > > > If HOT gets into 8.3, we might need a GUC to set database wide heap > > > fillfactor to an appropriate value. > > > > I have no objection to do that, but we will need other default values soon, > > something like default_[heap|btree|hash|gist|gin]_fillfactor. Some of us > > might feel it is mess to add random guc variables. > > I think we'd want to handle indexes with a different mechanism, probably > one that makes changes to pg_am. > > In any case, how important is it to do this before 8.3? We were supposed > to release this month, after all. Agreed. This seems like 8.4 material once we have HOT usage in the field. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +