Re: Vacuum ALL FULL

Found a notice after completing the
vacuumdb -p 5433 -- all --analyze --full -v
max_fsm_relation = 1400 in postgresql.conf
Thou all our 50 db individually have less then 1400 relation , when it
completes , there was NOTICE that increase the max_fsm_relation.
INFO: free space map contains 10344 pages in 1400 relations
DETAIL: A total of 25000 page slots are in use (including overhead).
54304 page slots are required to track all free space.
Current limits are: 25000 page slots, 1400 relations, using 299 KB.
NOTICE: max_fsm_relations(1400) equals the number of relations checked
HINT: You have at least 1400 relations. Consider increasing the
configuration parameter "max_fsm_relations".
VACUUM

But there nearly only 300 tables in that db. Is the free space map is per
DB or for all DB. Can i know the reason of this problem?

-Arvind S

*

"Many of lifes failure are people who did not realize how close they were to
success when they gave up."
-Thomas Edison*

should i have to increase max_fsm_relations more. If yes why i have to ?
Since number of relation is less only.

--Arvind S

On Sun, Jun 7, 2009 at 4:06 AM, S Arvind <arvindwill(at)gmail(dot)com> wrote:

> Found a notice after completing the
> vacuumdb -p 5433 -- all --analyze --full -v
> max_fsm_relation = 1400 in postgresql.conf
> Thou all our 50 db individually have less then 1400 relation , when it
> completes , there was NOTICE that increase the max_fsm_relation.
> INFO: free space map contains 10344 pages in 1400 relations
> DETAIL: A total of 25000 page slots are in use (including overhead).
> 54304 page slots are required to track all free space.
> Current limits are: 25000 page slots, 1400 relations, using 299 KB.
> NOTICE: max_fsm_relations(1400) equals the number of relations checked
> HINT: You have at least 1400 relations. Consider increasing the
> configuration parameter "max_fsm_relations".
> VACUUM
>
> But there nearly only 300 tables in that db. Is the free space map is per
> DB or for all DB. Can i know the reason of this problem?
>
>
> -Arvind S
>
> *
>
> "Many of lifes failure are people who did not realize how close they were
> to success when they gave up."
> -Thomas Edison*
>

S Arvind <arvindwill(at)gmail(dot)com> writes:
> But there nearly only 300 tables in that db. Is the free space map is per
> DB or for all DB. Can i know the reason of this problem?

It's across all DBs in the installation.

regards, tom lane

Thanks Tom,
So do i have to increase the max_fsm_relation based on (Average_no_relation
per db * number of db)? if so it will be very high since in our one db
server we have 200 db with average 800 tables in each db. What is the value
we have to give for this kind of server?

-Arvind S

On Sun, Jun 7, 2009 at 4:15 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> S Arvind <arvindwill(at)gmail(dot)com> writes:
> > But there nearly only 300 tables in that db. Is the free space map is
> per
> > DB or for all DB. Can i know the reason of this problem?
>
> It's across all DBs in the installation.
>
> regards, tom lane
>

So is it no use running
vacuumdb --all --analyze --full
as fsm map is full?

-Arvind S

On Sun, Jun 7, 2009 at 4:24 AM, S Arvind <arvindwill(at)gmail(dot)com> wrote:

> Thanks Tom,
> So do i have to increase the max_fsm_relation based on (Average_no_relation
> per db * number of db)? if so it will be very high since in our one db
> server we have 200 db with average 800 tables in each db. What is the value
> we have to give for this kind of server?
>
> -Arvind S
>
>
>
>
> On Sun, Jun 7, 2009 at 4:15 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> S Arvind <arvindwill(at)gmail(dot)com> writes:
>> > But there nearly only 300 tables in that db. Is the free space map is
>> per
>> > DB or for all DB. Can i know the reason of this problem?
>>
>> It's across all DBs in the installation.
>>
>> regards, tom lane
>>
>
>

S Arvind <arvindwill(at)gmail(dot)com> writes:
> So is it no use running
> vacuumdb --all --analyze --full
> as fsm map is full?

Well, it's not of *no* use. But you'd be well advised to crank up the
FSM size.

regards, tom lane

Sorry Tom, i cant able to understand. Should i have to increse the
max_fsm_rel based on formula and re-run the vacuum command? The main reason
for vacuum for us is to increase performance of our db. Please tell value
for our kind of server(as provided in previous mail) ?

-- Arvind S

On Sun, Jun 7, 2009 at 4:32 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> S Arvind <arvindwill(at)gmail(dot)com> writes:
> > So is it no use running
> > vacuumdb --all --analyze --full
> > as fsm map is full?
>
> Well, it's not of *no* use. But you'd be well advised to crank up the
> FSM size.
>
> regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

S Arvind <arvindwill(at)gmail(dot)com> writes:
> So do i have to increase the max_fsm_relation based on (Average_no_relation
> per db * number of db)? if so it will be very high since in our one db
> server we have 200 db with average 800 tables in each db. What is the value
> we have to give for this kind of server?

About 160000.

One wonders whether you shouldn't rethink your schema design. Large
numbers of small tables usually are not a good use of SQL. (I assume
they're small, else you'd have had serious bloat problems already from
your undersized max_fsm_pages setting ...)

regards, tom lane

Thanks Tom Lane,
I think we must have to consider about your last mail words. But now
reducing the table is mearly impossible, but very thanks for advice , we
will try it in future.

-Arvind S

On Sun, Jun 7, 2009 at 4:42 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> S Arvind <arvindwill(at)gmail(dot)com> writes:
> > So do i have to increase the max_fsm_relation based on
> (Average_no_relation
> > per db * number of db)? if so it will be very high since in our one db
> > server we have 200 db with average 800 tables in each db. What is the
> value
> > we have to give for this kind of server?
>
> About 160000.
>
> One wonders whether you shouldn't rethink your schema design. Large
> numbers of small tables usually are not a good use of SQL. (I assume
> they're small, else you'd have had serious bloat problems already from
> your undersized max_fsm_pages setting ...)
>
> regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>