pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Fill Factor 3+ messages / 3 participants [nested] [flat]
* Fill Factor @ 2011-05-17 12:59 Anibal David Acosta <aa@devshock.com> 2011-05-17 13:23 ` Re: Fill Factor Scott Marlowe <scott.marlowe@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Anibal David Acosta @ 2011-05-17 12:59 UTC (permalink / raw) To: pgsql-performance Hello, How fillfactor impact performance of query? 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) Is necessary to change the fill factor? 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? Thanks! ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Fill Factor 2011-05-17 12:59 Fill Factor Anibal David Acosta <aa@devshock.com> @ 2011-05-17 13:23 ` Scott Marlowe <scott.marlowe@gmail.com> 2011-05-17 13:52 ` Re: Fill Factor Cédric Villemain <cedric.villemain.debian@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Scott Marlowe @ 2011-05-17 13:23 UTC (permalink / raw) To: Anibal David Acosta <aa@devshock.com>; +Cc: pgsql-performance On Tue, May 17, 2011 at 6:59 AM, Anibal David Acosta <aa@devshock.com> 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. > 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. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Fill Factor 2011-05-17 12:59 Fill Factor Anibal David Acosta <aa@devshock.com> 2011-05-17 13:23 ` Re: Fill Factor Scott Marlowe <scott.marlowe@gmail.com> @ 2011-05-17 13:52 ` Cédric Villemain <cedric.villemain.debian@gmail.com> 0 siblings, 0 replies; 3+ messages in thread From: Cédric Villemain @ 2011-05-17 13:52 UTC (permalink / raw) To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Anibal David Acosta <aa@devshock.com>; pgsql-performance 2011/5/17 Scott Marlowe <scott.marlowe@gmail.com>: > On Tue, May 17, 2011 at 6:59 AM, Anibal David Acosta <aa@devshock.com> 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@postgresql.org) > 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-05-17 13:52 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2011-05-17 12:59 Fill Factor Anibal David Acosta <aa@devshock.com> 2011-05-17 13:23 ` Scott Marlowe <scott.marlowe@gmail.com> 2011-05-17 13:52 ` Cédric Villemain <cedric.villemain.debian@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox