Обсуждение: Maximum reasonable free space map
Hi Everyone, Just wondering what the maximum reasonable free space map setting should be? I'm receiving the following advice from vacuum: INFO: free space map contains 170803 pages in 117 relations DETAIL: A total of 185000 page slots are in use (including overhead). 733008 page slots are required to track all free space. Current limits are: 185000 page slots, 5000 relations, using 1623 KB. NOTICE: number of page slots needed (733008) exceeds max_fsm_pages (185000) HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 733008. I thought 185K was pretty high, is going to 700K+ reasonable? I've got 16GB of ram and am running very high volume 100GB+ DBs. Cheers Phil
On Wed, Dec 17, 2008 at 12:55 AM, Phillip Berry <pberry@stellaconcepts.com> wrote: > > I thought 185K was pretty high, is going to 700K+ reasonable? I've got 16GB of ram and am running > very high volume 100GB+ DBs. all depends on how often does the data change. I would go with whatever vacuum is suggesting on production :) in 8.4 you won't have to worry about it anymore anyway ;) -- GJ
The data in nearly every table is constantly changing due to a high volume of new data constantly coming in, processing on the existing data and heavy reporting being done all at once all day and night. So I guess my question is, is there a point where you start to see diminishing returns or even negative returns by setting the fsm too high? Cheers Phil On Wednesday 17 December 2008 13:02:21 Grzegorz Jaśkiewicz wrote: > On Wed, Dec 17, 2008 at 12:55 AM, Phillip Berry > > <pberry@stellaconcepts.com> wrote: > > I thought 185K was pretty high, is going to 700K+ reasonable? I've got > > 16GB of ram and am running very high volume 100GB+ DBs. > > all depends on how often does the data change. I would go with > whatever vacuum is suggesting on production :) > in 8.4 you won't have to worry about it anymore anyway ;)
On Wed, Dec 17, 2008 at 2:29 AM, Phillip Berry <pberry@stellaconcepts.com> wrote: > > The data in nearly every table is constantly changing due to a high volume of new data constantly > coming in, processing on the existing data and heavy reporting being done all at once all day and > night. > > So I guess my question is, is there a point where you start to see diminishing returns or even > negative returns by setting the fsm too high? I personally didn't experience anything like that. If the data is updated, but not deleted mostly - you should do something to utilize HOT (if using 8.3). Otherwise, you want FSM to be big enough - so that more and more deleted records could get reused without need for so often vacuum. -- GJ
Phillip Berry <pberry@stellaconcepts.com> writes: > So I guess my question is, is there a point where you start to see > diminishing returns or even negative returns by setting the fsm too high? There is no benefit to having FSM larger than necessary, so I suppose that qualifies as "diminishing returns". The only negative effect is the reduced memory available for caches and shared buffers. You might also want to check that you don't have just a few tables which have a lot of dead space in them. If so filling the FSM is the least of your worries. The tables with lots of dead space will perform poorly because of the time spent sifting through all that dead space. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!
On Tue, Dec 16, 2008 at 5:55 PM, Phillip Berry <pberry@stellaconcepts.com> wrote: > Hi Everyone, > > Just wondering what the maximum reasonable free space map setting should be? I'm receiving the > following advice from vacuum: > > INFO: free space map contains 170803 pages in 117 relations > DETAIL: A total of 185000 page slots are in use (including overhead). > 733008 page slots are required to track all free space. > Current limits are: 185000 page slots, 5000 relations, using 1623 KB. > NOTICE: number of page slots needed (733008) exceeds max_fsm_pages (185000) > HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 733008. > > I thought 185K was pretty high, is going to 700K+ reasonable? I've got 16GB of ram and am running > very high volume 100GB+ DBs. It's all about the size of your tables. If you've got 1 table with 100k rows that's updated a lot then an fsm of 100k is likely reasonable, assuming you've got autovac keeping things in check. Got 4G rows but none are ever updated, then you don't need much if any fsm. If you've got 40M rows and 10% are updated each day, then it's likely you'll want 4M fsm entries avaialble for those dead rows. I think that as long as you're not using a huge amount of shared memory it's nothing to worry about much, as long as it's not too small. We had to go to 1Million fsm entries because we routinely have 400k to 600k dead rows in our db at work.
On Wed, Dec 17, 2008 at 5:45 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > It's all about the size of your tables. If you've got 1 table with > 100k rows that's updated a lot then an fsm of 100k is likely > reasonable, assuming you've got autovac keeping things in check. Got > 4G rows but none are ever updated, then you don't need much if any > fsm. > > If you've got 40M rows and 10% are updated each day, then it's likely > you'll want 4M fsm entries avaialble for those dead rows. > > I think that as long as you're not using a huge amount of shared > memory it's nothing to worry about much, as long as it's not too > small. We had to go to 1Million fsm entries because we routinely have > 400k to 600k dead rows in our db at work. That's why I said - go for whatever vacuum suggests you on production, with assumption that db is vacuum regularly. -- GJ
On Wed, Dec 17, 2008 at 5:45 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > > If you've got 40M rows and 10% are updated each day, then it's likely > you'll want 4M fsm entries avaialble for those dead rows. FWIW you only need an entry for each *page* of the table, not every row. Of course if you have 40M rows and 10% are updated each day they might all be on different pages. Also, it's not really the end of the world if the FSM doesn't track every single page with free space -- as long as it tracks enough to cover your usage until the next vacuum. Thankfully all this is gone in 8.4. -- greg