Re: Practical limit on number of tables ina single database

On Fri, Mar 24, 2006 at 06:56:19PM -0500, Tom Lane wrote:
> "Just Someone" <just(dot)some(at)gmail(dot)com> writes:
> > Actually, most table are VERY small. On each schema I would expect 4 -
> > 5 tables to go over 1000 records. The rest will be much smaller, with
> > most at under 100. And aside from 2-3 tables, the activity will be
> > pretty low (few records a day at the most extreme).
>
> > Can I use this to optimize the fsm part in a different way?
>
> Seems like a one-page table hardly needs an FSM entry at all: there's
> only one place to look for free space anyway. Maybe we should have a
> threshold for the minimum size a table must be before it gets an FSM
> entry.

Well, it makes absolutely no sense to trouble the FSM with any relation
that's only a single page. I'm not sure we'd want the limit any higher,
though. IIRC when vacuum attempts to register free space in the map, if
we've bumped into max_fsm_relations it will pick the smallest relation
currently in FSM and drop it, and there is some value in keeping info
for 2 page relations. Whats unfortunate is that a 2 page relation would
end up with 16 pages in the FSM. Perhaps it would be a lot better if
smaller relations didn't get CHUNKPAGES allocated to them, but only what
they needed (though that could dramatically complicate FSM
management...).

Actually, reading the comments, I'm wondering if there's other bad
assumptions going on. For example, if we're not running into
max_fsm_pages, ISTM that we shouldn't be as stringent about pruning
pages based on averege request size. And the comment about using a
moving average on RRFS counts might make sense, though I'm not really
sure how RRFS causes thrashing of the storage allocations (the reason
given for using CHUNKPAGES in the first place).

Perhaps an interum fix might be to ignore CHUNKPAGES for any relation
that would only be getting one chunk, and just give it the exact number
of pages it needs.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461