Maximum reasonable free space map

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 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