Re: Fill Factor

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2011/5/17 Scott Marlowe <scott.marlowe@xxxxxxxxx>:
> On Tue, May 17, 2011 at 6:59 AM, Anibal David Acosta <aa@xxxxxxxxxxxx> wrote:
>> Hello,
>>
>> How fillfactor impact performance of query?
>
> Fillfactor tells the db how much empty space to leave in the database
> when creating a table and inserting rows.  If you set it to 90% then
> 10% of the space in the table will be available for updates can be
> used for the new data.  Combined with pg 8.3+ HOT updates, this free
> space allows updates to non-indexed fields to be close to "free"
> because now the index for that row needs no updates if the new datum
> for that row first in the same 8k pg block.
>
>> I have two cases,
>> One is a operational table, for each insert it have an update, this table
>> must have aprox. 1.000 insert per second and 1.000 update per second (same
>> inserted row)
>
> If you could combine the insert and update into one action that would
> be preferable really.
>
>> Is necessary to change the fill factor?
>
> Not necessary but possibly better for performance.

depend of deletes ratio too... without delete I am unsure a reduced
fillfactor will have a good impact on the long term.

>
>> The other case is a table that have few insert (statistics) but thousands or
>> millons of update, In this case the fillfactor is not necessary to change?
>
> Actually updates are the time that a lower fill factor is most useful.
>  But it doesn't need to be really low.  anything below 95% is likely
> more than you need.  But it really depends on your access patterns. If
> you're updating 20% of a table at a time, then a fillfactor of ~80%
> might be the best fit.  Whether or not the updates fit under the HOT
> umbrella, lowering fill factor enough to allow the updates to happen
> in place without adding pages to the table files is usually a win.

And one possible way to help adjust the fillfactor is to control the
relation size.
Sometimes reducing fillfactor a lot (60-80%) is good, the table is
stuck at some XX MB and page are well reused.

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



-- 
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/ ;    PostgreSQL : Expertise, Formation et Support

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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux