Re: Practical limit on number of tables ina single database

Hi Jim,

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?

Bye,

Guy.

On 3/24/06, Jim C. Nasby <jnasby(at)pervasive(dot)com> wrote:
> On Fri, Mar 24, 2006 at 11:15:56AM -0800, Just Someone wrote:
> > Hi Jim,
> >
> > On 3/24/06, Jim Nasby <jnasby(at)pervasive(dot)com> wrote:
> > > You want max_fsm_relations to be greater than select count(*) from pg_class where
> > > relkind in ('i','t') *across all databases*. And you want max_fsm_pages to be bigger than
> > > that. That's the only way you can be assured that you'll be tracking free space info for
> > > every table.
> >
> > So I need something like 2,000,000 in max_fsm_relations. So
> > max_fsm_pages now need to be 16 times that? That's how I read the
> > postgresql.conf comment.
>
> Hrm... I forgot that FSM allocation is done in terms of CHUNKPAGES,
> which is #defined at 16. So yes, you'd need 32M pages to track freespace
> for all tables. Given that that's 250GB, I guess it won't work terribly
> well... :)
>
> Will any of these tables be very small and not see any real update
> activity? If so, you could possibly do without being able to store FSM
> info for them. Keeping a close eye on the last few lines of vacuumdb -av
> would be key here.
>
> Another possibility is to change CHUNKPAGES in
> include/storage/freespace.h to 1. That means you could get by with 2M
> pages, which is 'only' 16GB.
>
> Perhaps it would be worth considering some alternatives to how the FSM
> works. In particular, it might be worth it to be able to store free
> space info for multiple relations on a single page. Or perhaps allow the
> backend to tablescan very small tables to look for free space.
> --
> 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
>

--
Family management on rails: http://www.famundo.com - coming soon!
My development related blog: http://devblog.famundo.com