Обсуждение: fsm allocation

Hi

   I manage a moderately large database (version 8.0.4) that experiences
about 500 inserts per hour spread over a half dozen tables.  Not many updates or deletions
(usually).

   My problem involves fsm allocation and the vacuum utility.

   Recently, I vacuumed and the output told me I had 47 relations, 151751
pages stored, and that I needed 237680 total pages.  My fsm allocation
was for 1000 relations, 150000 pages.

   I took this to mean the vacuum didn't work and that I needed to allocate
more pages.  So I edited the conf file to allocate 300000 pages.  After
altering SHMMAX on my system, I was able to restart the database.

    I tried vacuuming again.  This time, the output said I had 47
relations, 301906 pages and that I now needed 333104 total pages.

   I'm confused.  Does this mean I need to bump up my fsm allocation again?

   By the way, what exactly is the 'free map space' anyway?  And why does
it need so much space?

   As always, your help is greatly appreciated.

On Thu, Nov 17, 2005 at 08:20:12AM -0500, Colton A Smith wrote:
>   By the way, what exactly is the 'free map space' anyway?  And why does
> it need so much space?

There's a useful description in the docs, section 17.4.2 (for 8.1).
I'd look at that.

Your usage might not be stable right now because your FSM was wrong
to begin with.  My bet is that you need to do either a VACUUM FULL or
a dump and reload before twiddling these settings much more.  You
also maybe want either autovacuum, or to increase the frequency of
your vacuums.  Given the database size you discussed, I can't believe
you really need that much space.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
                --Brad Holland

Colton A Smith <smith@cs.utk.edu> writes:
>    Recently, I vacuumed and the output told me I had 47 relations, 151751
> pages stored, and that I needed 237680 total pages.  My fsm allocation
> was for 1000 relations, 150000 pages.
>    I took this to mean the vacuum didn't work and that I needed to allocate
> more pages.  So I edited the conf file to allocate 300000 pages.  After
> altering SHMMAX on my system, I was able to restart the database.
>     I tried vacuuming again.  This time, the output said I had 47
> relations, 301906 pages and that I now needed 333104 total pages.
>    I'm confused.  Does this mean I need to bump up my fsm allocation again?

I seem to recall that the FSM's threshold for how much free space is
worth remembering does vary with the number of slots it's got available,
so this increase isn't too surprising.

Without knowing anything about the total size of your database, it's
difficult to say whether these numbers are out of line or not.

            regards, tom lane

On Fri, Nov 18, 2005 at 04:11:59PM -0500, Andrew Sullivan wrote:
> On Thu, Nov 17, 2005 at 08:20:12AM -0500, Colton A Smith wrote:
> >   By the way, what exactly is the 'free map space' anyway?  And why does
> > it need so much space?
>
> There's a useful description in the docs, section 17.4.2 (for 8.1).
> I'd look at that.

There's also a description at
http://www.pervasivepostgres.com/instantkb13/article.aspx?id=10087 that
might help you.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461