Re: [HACKERS] GUC for default heap fillfactor
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 [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] GUC for default heap fillfactor
On 8/8/07, Simon Riggs [EMAIL PROTECTED] wrote: 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
Re: [HACKERS] GUC for default heap fillfactor
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] GUC for default heap fillfactor
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[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpkDxwB7dNwA.pgp Description: PGP signature
[HACKERS] GUC for default heap fillfactor
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 ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] GUC for default heap fillfactor
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[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpxPBiJIwcPJ.pgp Description: PGP signature
Re: [HACKERS] GUC for default heap fillfactor
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] GUC for default heap fillfactor
Pavan Deolasee [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
