Обсуждение: vacuum verbose relations reporting
Looks like vacuum verbose doesn't report the number of relations necessary on version 8.2.7. It just reports the max_fsm_relations value instead: INFO: free space map contains 113311 pages in 10000 relations DETAIL: A total of 252816 page slots are in use (including overhead). 252816 page slots are required to track all free space. Current limits are: 600000 page slots, 10000 relations, using 4587 kB. NOTICE: max_fsm_relations(10000) equals the number of relations checked HINT: You have at least 10000 relations. Consider increasing the configuration parameter "max_fsm_relations". VACUUM postgres=# show server_version; server_version ---------------- 8.2.7 Does it do this in more recent versions of 8.2 or 8.3? -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
If I understand your question, you might need to turn up log_min_messages to debug1 otherwise you won't see the relations. I had the system set to autovacuum and I couldn't see that it was doing it until I made this change. Carol On Aug 26, 2008, at 3:09 PM, Jeff Frost wrote: > Looks like vacuum verbose doesn't report the number of relations > necessary on version 8.2.7. It just reports the max_fsm_relations > value instead: > > INFO: free space map contains 113311 pages in 10000 relations > DETAIL: A total of 252816 page slots are in use (including overhead). > 252816 page slots are required to track all free space. > Current limits are: 600000 page slots, 10000 relations, using 4587 > kB. > NOTICE: max_fsm_relations(10000) equals the number of relations > checked > HINT: You have at least 10000 relations. Consider increasing the > configuration parameter "max_fsm_relations". > VACUUM > > postgres=# show server_version; > server_version > ---------------- > 8.2.7 > > Does it do this in more recent versions of 8.2 or 8.3? > > -- > Jeff Frost, Owner <jeff@frostconsultingllc.com> > Frost Consulting, LLC http://www.frostconsultingllc.com/ > Phone: 916-647-6411 FAX: 916-405-4032 > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin
On Tue, 26 Aug 2008, Carol Walter wrote: > On Aug 26, 2008, at 3:09 PM, Jeff Frost wrote: > >> Looks like vacuum verbose doesn't report the number of relations necessary >> on version 8.2.7. It just reports the max_fsm_relations value instead: >> >> INFO: free space map contains 113311 pages in 10000 relations >> DETAIL: A total of 252816 page slots are in use (including overhead). >> 252816 page slots are required to track all free space. >> Current limits are: 600000 page slots, 10000 relations, using 4587 kB. >> NOTICE: max_fsm_relations(10000) equals the number of relations checked >> HINT: You have at least 10000 relations. Consider increasing the >> configuration parameter "max_fsm_relations". >> VACUUM >> >> postgres=# show server_version; >> server_version >> ---------------- >> 8.2.7 >> >> Does it do this in more recent versions of 8.2 or 8.3? > If I understand your question, you might need to turn up log_min_messages to > debug1 otherwise you won't see the relations. I had the system set to > autovacuum and I couldn't see that it was doing it until I made this change. > Hi Carol, No I actually mean that this part: >> Current limits are: 600000 page slots, 10000 relations, using 4587 kB. >> NOTICE: max_fsm_relations(10000) equals the number of relations checked >> HINT: You have at least 10000 relations. Consider increasing the Just tells me that I'm equal or greater than max_fsm_relations. But vacuum verbose does tell me how many page_slots are in use even when it's greater than max_fsm_pages, so you know what value to use in postgresql.conf for the fsm settings. -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
Jeff Frost <jeff@frostconsultingllc.com> writes:
> No I actually mean that this part:
> Current limits are: 600000 page slots, 10000 relations, using 4587 kB.
> NOTICE: max_fsm_relations(10000) equals the number of relations checked
> HINT: You have at least 10000 relations. Consider increasing the
> Just tells me that I'm equal or greater than max_fsm_relations.
Yeah, because that's all that can be told from the contents of the
shared free space map: it's full, but we have no idea how many other
tables might have been competing for space in it.
> But vacuum verbose does tell me how many page_slots are in use even
> when it's greater than max_fsm_pages, so you know what value to use in
> postgresql.conf for the fsm settings.
Well, no :-(. What that number is is the number of page slots that the
relations that are in the FSM would have liked to have --- we have the
"request" size as well as the "allocated" size for each such relation.
We have no idea how many slots the relations that aren't in FSM might
have liked to have.
This whole design is pretty awful, of course (I can say that because it
was my design :-(). There is work in progress to make the fixed-size
FSM go away entirely in 8.4, which will certainly be a boon to DBAs.
regards, tom lane
On Tue, 26 Aug 2008, Tom Lane wrote: > Jeff Frost <jeff@frostconsultingllc.com> writes: >> No I actually mean that this part: >> Current limits are: 600000 page slots, 10000 relations, using 4587 kB. >> NOTICE: max_fsm_relations(10000) equals the number of relations checked >> HINT: You have at least 10000 relations. Consider increasing the > >> Just tells me that I'm equal or greater than max_fsm_relations. > > Yeah, because that's all that can be told from the contents of the > shared free space map: it's full, but we have no idea how many other > tables might have been competing for space in it. > >> But vacuum verbose does tell me how many page_slots are in use even >> when it's greater than max_fsm_pages, so you know what value to use in >> postgresql.conf for the fsm settings. > > Well, no :-(. What that number is is the number of page slots that the > relations that are in the FSM would have liked to have --- we have the > "request" size as well as the "allocated" size for each such relation. > We have no idea how many slots the relations that aren't in FSM might > have liked to have. > > This whole design is pretty awful, of course (I can say that because it > was my design :-(). There is work in progress to make the fixed-size > FSM go away entirely in 8.4, which will certainly be a boon to DBAs. Tom, is there an easy (or hard) way to count relations from all DBs by using the system catalogs? -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
Jeff Frost wrote:
> Tom, is there an easy (or hard) way to count relations from all DBs by
> using the system catalogs?
Just do a count(*) from pg_class where relkind in ('r', 't', 'i'), and
sum across all databases (you need to connect to each one).
(Actually you only need to count indexes that are btrees, if you need
such a distinction. Other indexes do not use the FSM as far as I know).
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote:
#!/bin/sh
PSQL=/usr/bin/psql
DATABASES=$($PSQL -lt | awk {'print $1'} | grep -v template0 )
RELATIONS=0
for DB in $DATABASES; do
RELATIONS=$(($RELATIONS + $($PSQL --tuples-only --command "select count(*) from pg_class where relkind IN ('r', 't', 'i');" $DB) ))
done
echo $RELATIONS
Thanks folks!
Perfect, so here's a little script that does the trick then:Jeff Frost wrote:Tom, is there an easy (or hard) way to count relations from all DBs by using the system catalogs?Just do a count(*) from pg_class where relkind in ('r', 't', 'i'), and sum across all databases (you need to connect to each one). (Actually you only need to count indexes that are btrees, if you need such a distinction. Other indexes do not use the FSM as far as I know).
#!/bin/sh
PSQL=/usr/bin/psql
DATABASES=$($PSQL -lt | awk {'print $1'} | grep -v template0 )
RELATIONS=0
for DB in $DATABASES; do
RELATIONS=$(($RELATIONS + $($PSQL --tuples-only --command "select count(*) from pg_class where relkind IN ('r', 't', 'i');" $DB) ))
done
echo $RELATIONS
Thanks folks!
-- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
Jeff Frost wrote:
Alvaro Herrera wrote:I guess this isn't entirely accurate, as the above script returns 35883, but vacuum verbose returns:Perfect, so here's a little script that does the trick then:Jeff Frost wrote:Tom, is there an easy (or hard) way to count relations from all DBs by using the system catalogs?Just do a count(*) from pg_class where relkind in ('r', 't', 'i'), and sum across all databases (you need to connect to each one). (Actually you only need to count indexes that are btrees, if you need such a distinction. Other indexes do not use the FSM as far as I know).
#!/bin/sh
PSQL=/usr/bin/psql
DATABASES=$($PSQL -lt | awk {'print $1'} | grep -v template0 )
RELATIONS=0
for DB in $DATABASES; do
RELATIONS=$(($RELATIONS + $($PSQL --tuples-only --command "select count(*) from pg_class where relkind IN ('r', 't', 'i');" $DB) ))
done
echo $RELATIONS
INFO: free space map contains 111435 pages in 10005 relations
If I take out the toast tables and indexes, I get a result much closer to what vacuum verbose returns: 10626 which might just be because the vacuum verbose ran a few hours ago.
So, the question is, do the FSM settings take into account toast tables and indexes as Alvaro suggested and vacuum verbose isn't properly reporting on it?
-- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
Jeff Frost wrote: > I guess this isn't entirely accurate, as the above script returns 35883, > but vacuum verbose returns: > > INFO: free space map contains 111435 pages in 10005 relations Well, what this means is that not every single table has useful free space ... -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Sun, 31 Aug 2008, Alvaro Herrera wrote: > Jeff Frost wrote: > >> I guess this isn't entirely accurate, as the above script returns 35883, >> but vacuum verbose returns: >> >> INFO: free space map contains 111435 pages in 10005 relations > > Well, what this means is that not every single table has useful free > space ... Ohh...naturally. So, max_fsm_relations only cares about relations that have free space to track, and not all relations. So, is there a way to compute the number of relatinos with useful free space? -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
Is there any ways to include wildcard in pg_hba.conf file to accept all postgres clients on the network rather than specifyingspecific IP addresses on this file? Any assistance is appreciated Thanks Napolean
>>> Napolean Periathambi <Napolean.Periathambi@VSOFTCORP.COM> wrote: > Is there any ways to include wildcard in pg_hba.conf file to accept all > postgres clients on the network rather than specifying specific IP addresses > on this file? # CIDR-ADDRESS specifies the set of hosts the record matches. # It is made up of an IP address and a CIDR mask that is an integer # (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that specifies # the number of significant bits in the mask. Alternatively, you can write # an IP address and netmask in separate columns to specify the set of hosts. In other words, it says how many bits of the IP address need to match. For example: # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all postgres ident sameuser local cir all md5 # IPv4 local connections: host cir all 127.0.0.1/32 md5 # IPv6 local connections: host cir all ::1/128 md5 # Programmer/analyst network segment host cir all 165.219.88.0/22 md5 # STEP network segment host cir all 165.219.80.0/24 md5 # TECH network segment host cir all 165.219.95.0/24 md5 -Kevin