Thread: Another small bug (pg_autovacuum)
Now that I have pg_autovacuum working I've bumped into another small bug. When pg_autovacuum goes to vacuum or analyze one of my tables it runs... analyze public.ConfigBackup Because ConfigBackup is mixed case it cannot find the relation. I fixed this by going to the function init_table_info and increasing the malloc for new_tbl->table_name by 2 and adding "'s to either side of the table name. Is there anything wrong with this approach? Is there a config I can set to make this non-case sensitive? Thanks again for your time. --- Adam Kavan --- akavan@cox.net
Ouch... sorry, my fault. I'll fix this tomorrow (Friday) and submit a patch, or if you want to submit a patch that would be fine. All you have to do is change the the sql statements to put quotes around the relation name. Thanks for catching this. Matthew T. O'Connor On Thu, 2003-09-04 at 18:39, Adam Kavan wrote: > Now that I have pg_autovacuum working I've bumped into another small > bug. When pg_autovacuum goes to vacuum or analyze one of my tables it runs... > > analyze public.ConfigBackup > > Because ConfigBackup is mixed case it cannot find the relation. I fixed > this by going to the function init_table_info and increasing the malloc for > new_tbl->table_name by 2 and adding "'s to either side of the table > name. Is there anything wrong with this approach? Is there a config I can > set to make this non-case sensitive? > > Thanks again for your time. > > --- Adam Kavan > --- akavan@cox.net > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
On Thu, 2003-09-04 at 18:39, Adam Kavan wrote: > Now that I have pg_autovacuum working I've bumped into another small > bug. When pg_autovacuum goes to vacuum or analyze one of my tables it runs... Also, has this been officially fixed? All I have heard so far is that you commented out the check and now now it works for you.
I assume the attached patch is what you want done to fix this. Applied. It quotes table names for vacuum and analyze, and uppercases the keywords for clarity. --------------------------------------------------------------------------- Matthew T. O'Connor wrote: > Ouch... sorry, my fault. I'll fix this tomorrow (Friday) and submit a > patch, or if you want to submit a patch that would be fine. All you > have to do is change the the sql statements to put quotes around the > relation name. > > Thanks for catching this. > > Matthew T. O'Connor > > On Thu, 2003-09-04 at 18:39, Adam Kavan wrote: > > Now that I have pg_autovacuum working I've bumped into another small > > bug. When pg_autovacuum goes to vacuum or analyze one of my tables it runs... > > > > analyze public.ConfigBackup > > > > Because ConfigBackup is mixed case it cannot find the relation. I fixed > > this by going to the function init_table_info and increasing the malloc for > > new_tbl->table_name by 2 and adding "'s to either side of the table > > name. Is there anything wrong with this approach? Is there a config I can > > set to make this non-case sensitive? > > > > Thanks again for your time. > > > > --- Adam Kavan > > --- akavan@cox.net > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: contrib/pg_autovacuum/pg_autovacuum.c =================================================================== RCS file: /cvsroot/pgsql-server/contrib/pg_autovacuum/pg_autovacuum.c,v retrieving revision 1.3 diff -c -c -r1.3 pg_autovacuum.c *** contrib/pg_autovacuum/pg_autovacuum.c 4 Aug 2003 00:43:11 -0000 1.3 --- contrib/pg_autovacuum/pg_autovacuum.c 10 Sep 2003 19:57:15 -0000 *************** *** 581,587 **** { PGresult *res = NULL; ! res = send_query("vacuum", dbi); /* FIXME: Perhaps should add a check for PQ_COMMAND_OK */ PQclear(res); return 1; --- 581,587 ---- { PGresult *res = NULL; ! res = send_query("VACUUM", dbi); /* FIXME: Perhaps should add a check for PQ_COMMAND_OK */ PQclear(res); return 1; *************** *** 733,739 **** PGresult *res = NULL; int ret = 0; ! res = send_query("show stats_row_level", dbi); ret = strcmp("on", PQgetvalue(res, 0, PQfnumber(res, "stats_row_level"))); PQclear(res); --- 733,739 ---- PGresult *res = NULL; int ret = 0; ! res = send_query("SHOW stats_row_level", dbi); ret = strcmp("on", PQgetvalue(res, 0, PQfnumber(res, "stats_row_level"))); PQclear(res); *************** *** 1082,1088 **** */ if ((tbl->curr_vacuum_count - tbl->CountAtLastVacuum) >= tbl->vacuum_threshold) { ! snprintf(buf, sizeof(buf), "vacuum analyze %s", tbl->table_name); if (args->debug >= 1) { sprintf(logbuffer, "Performing: %s", buf); --- 1082,1088 ---- */ if ((tbl->curr_vacuum_count - tbl->CountAtLastVacuum) >= tbl->vacuum_threshold) { ! snprintf(buf, sizeof(buf), "VACUUM ANALYZE \"%s\"", tbl->table_name); if (args->debug >= 1) { sprintf(logbuffer, "Performing: %s", buf); *************** *** 1096,1102 **** } else if ((tbl->curr_analyze_count - tbl->CountAtLastAnalyze) >= tbl->analyze_threshold) { ! snprintf(buf, sizeof(buf), "analyze %s", tbl->table_name); if (args->debug >= 1) { sprintf(logbuffer, "Performing: %s", buf); --- 1096,1102 ---- } else if ((tbl->curr_analyze_count - tbl->CountAtLastAnalyze) >= tbl->analyze_threshold) { ! snprintf(buf, sizeof(buf), "ANALYZE \"%s\"", tbl->table_name); if (args->debug >= 1) { sprintf(logbuffer, "Performing: %s", buf);
On Wed, 2003-09-10 at 15:57, Bruce Momjian wrote: > I assume the attached patch is what you want done to fix this. Applied. > > It quotes table names for vacuum and analyze, and uppercases the > keywords for clarity. Yeah, this is basically what I meant, sorry I didn't get to it quicker. However, I tested it out a little and the patch you made doesn't work because it produces commands like: VACUUM ANALYZE "public.FooBar" Which doesn't work, so I made my own patch that creates commands like: VACUUM ANALYZE "public"."FooBar" This allows for mixed case schema names as well as tables. Adam, can you please give this a test as you are the person who caught the bug in the first place. Thanks, Matthew T. O'Connor
Attachment
matthew@zeut.net ("Matthew T. O'Connor") wrote:
> On Wed, 2003-09-10 at 15:57, Bruce Momjian wrote:
>> I assume the attached patch is what you want done to fix this. Applied.
>>
>> It quotes table names for vacuum and analyze, and uppercases the
>> keywords for clarity.
>
> Yeah, this is basically what I meant, sorry I didn't get to it quicker.
>
> However, I tested it out a little and the patch you made doesn't work
> because it produces commands like:
>
> VACUUM ANALYZE "public.FooBar"
>
> Which doesn't work, so I made my own patch that creates commands like:
>
> VACUUM ANALYZE "public"."FooBar"
>
> This allows for mixed case schema names as well as tables.
>
> Adam, can you please give this a test as you are the person who caught
> the bug in the first place.
Something I am feeling a little suspicious of is that I haven't seen,
in the logs, pg_autovacuum looking at pg_ tables.
I know that if we don't periodically vacuum such system tables as
pg_class, pg_attribute, pg_statistic, and pg_type, they can get to
"pretty evil size."
[Rummaging around...] These tables are being added for template1, but
apparently not for "main" databases. That looks like a bit of a fly
in the ointment...
--
(format nil "~S@~S" "cbbrowne" "ntlug.org")
http://www.ntlug.org/~cbbrowne/rdbms.html
Signs of a Klingon Programmer - 16. "Klingon programs don't do
accountancy. For that, you need a Ferengi."
At 12:03 AM 9/11/03 -0400, Matthew T. O'Connor wrote: >Adam, can you please give this a test as you are the person who caught >the bug in the first place. > >Thanks, > >Matthew T. O'Connor I applied your patch and it works fine for me. --- Adam Kavan
Patch applied. You might want to look at pg_dump/dumputils.c::fmtId() for a function that does smart quoting. --------------------------------------------------------------------------- Matthew T. O'Connor wrote: > On Wed, 2003-09-10 at 15:57, Bruce Momjian wrote: > > I assume the attached patch is what you want done to fix this. Applied. > > > > It quotes table names for vacuum and analyze, and uppercases the > > keywords for clarity. > > Yeah, this is basically what I meant, sorry I didn't get to it quicker. > > However, I tested it out a little and the patch you made doesn't work > because it produces commands like: > > VACUUM ANALYZE "public.FooBar" > > Which doesn't work, so I made my own patch that creates commands like: > > VACUUM ANALYZE "public"."FooBar" > > This allows for mixed case schema names as well as tables. > > Adam, can you please give this a test as you are the person who caught > the bug in the first place. > > Thanks, > > Matthew T. O'Connor > [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Thu, 2003-09-11 at 15:02, Bruce Momjian wrote: > Patch applied. You might want to look at pg_dump/dumputils.c::fmtId() > for a function that does smart quoting. OK, thanks.
On Thu, 2003-09-11 at 08:12, Christopher Browne wrote: > Something I am feeling a little suspicious of is that I haven't seen, > in the logs, pg_autovacuum looking at pg_ tables. > > I know that if we don't periodically vacuum such system tables as > pg_class, pg_attribute, pg_statistic, and pg_type, they can get to > "pretty evil size." > > [Rummaging around...] These tables are being added for template1, but > apparently not for "main" databases. That looks like a bit of a fly > in the ointment... I designed it that way. It was my understanding that all of the system tables pg_class etc... are shared tables, available in all databases, but actually stored as only one central set of real tables. Hence vacuuming pg_class from template1 helps every database that accesses pg_class. Did I make a design error?
Matthew T. O'Connor wrote:
> On Thu, 2003-09-11 at 08:12, Christopher Browne wrote:
> > Something I am feeling a little suspicious of is that I haven't seen,
> > in the logs, pg_autovacuum looking at pg_ tables.
> >
> > I know that if we don't periodically vacuum such system tables as
> > pg_class, pg_attribute, pg_statistic, and pg_type, they can get to
> > "pretty evil size."
> >
> > [Rummaging around...] These tables are being added for template1, but
> > apparently not for "main" databases. That looks like a bit of a fly
> > in the ointment...
>
> I designed it that way. It was my understanding that all of the system
> tables pg_class etc... are shared tables, available in all databases,
> but actually stored as only one central set of real tables. Hence
> vacuuming pg_class from template1 helps every database that accesses
> pg_class.
>
> Did I make a design error?
Oops, no. Only a few pg_* tables are "global". pg_class isn't. In
fact, I am not sure how someone tells which are global. A grep in
/src/include/catalog shows:
$ grep BKI_SHARED_RELATION *.hpg_database.h:CATALOG(pg_database) BOOTSTRAP
BKI_SHARED_RELATIONpg_group.h:CATALOG(pg_group)BOOTSTRAP BKI_SHARED_RELATION
BKI_WITHOUT_OIDSpg_shadow.h:CATALOG(pg_shadow)BOOTSTRAP BKI_SHARED_RELATION BKI_WITHOUT_OIDS
so those are the only shared ones. I found a query to do it too:test=> select * from pg_class where relisshared = 't'
andrelkind = 'r'; relname | relnamespace | reltype | relowner | relam | relfilenode | relpages | reltuples |
reltoastrelid| reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys |
relfkeys| relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass |
relacl-------------+--------------+---------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+------------------------------------
pg_shadow | 11 | 86 | 1 | 0 | 1260 | 1 | 1 | 16677 |
0 | t | t | r | 8 | 0 | 1 | 0 | 0 | 0 | f
| f | f | f | {postgres=a*r*w*d*R*x*t*/postgres} pg_database | 11 | 88
| 1 | 0 | 1262 | 1 | 2 | 16662 | 0 | t | t | r
| 11 | 0 | 0 | 0 | 0 | 0 | t | f | f | f
| {=r/postgres} pg_group | 11 | 87 | 1 | 0 | 1261 | 0 | 0 |
16668 | 0 | t | t | r | 3 | 0 | 1 | 0 |
0 | 0 | f | f | f | f | {=r/postgres}(3 rows)
so those are the only ones that should be template1-only. All other
pg_* tables should be vacuumed in individual database.
I will wait for a patch. :-)
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> On Thu, 2003-09-11 at 08:12, Christopher Browne wrote:
>> [Rummaging around...] These tables are being added for template1, but
>> apparently not for "main" databases. That looks like a bit of a fly
>> in the ointment...
> I designed it that way. It was my understanding that all of the system
> tables pg_class etc... are shared tables, available in all databases,
> but actually stored as only one central set of real tables.
You are very badly mistaken.
Only the tables marked "relisshared" in pg_class (currently pg_shadow,
pg_group, pg_database, and their indexes and toast tables) are shared
across a cluster. The rest have separate copies per-database.
regards, tom lane
matthew@zeut.net ("Matthew T. O'Connor") writes:
> On Thu, 2003-09-11 at 08:12, Christopher Browne wrote:
>> Something I am feeling a little suspicious of is that I haven't seen,
>> in the logs, pg_autovacuum looking at pg_ tables.
>>
>> I know that if we don't periodically vacuum such system tables as
>> pg_class, pg_attribute, pg_statistic, and pg_type, they can get to
>> "pretty evil size."
>>
>> [Rummaging around...] These tables are being added for template1, but
>> apparently not for "main" databases. That looks like a bit of a fly
>> in the ointment...
>
> I designed it that way. It was my understanding that all of the system
> tables pg_class etc... are shared tables, available in all databases,
> but actually stored as only one central set of real tables. Hence
> vacuuming pg_class from template1 helps every database that accesses
> pg_class.
>
> Did I make a design error?
[rummaging around... Where's a suitable system? There it is...]
database3=# vacuum verbose pg_class;
NOTICE: --Relation pg_class--
NOTICE: Index pg_class_oid_index: Pages 11399; Tuples 165: Deleted 1408. CPU 2.00s/0.41u sec elapsed 2.51 sec.
NOTICE: Index pg_class_relname_index: Pages 30604; Tuples 171: Deleted 1408. CPU 5.81s/1.07u sec elapsed 7.07
sec.
NOTICE: Removed 1408 tuples in 24 pages. CPU 0.01s/0.02u sec elapsed 0.20 sec.
NOTICE: Pages 360: Changed 1, Empty 0; Tup 165: Vac 1408, Keep 0, UnUsed 21110. Total CPU 7.82s/1.50u sec
elapsed9.79 sec.
VACUUM
database3=# \c database1
You are now connected to database database1
database1=# vacuum verbose pg_class;
NOTICE: --Relation pg_class--
NOTICE: Pages 22768: Changed 0, Empty 0; Tup 158: Vac 0, Keep 0, UnUsed 1434149. Total CPU 1.35s/0.97u sec
elapsed2.38 sec.
VACUUM
database1=# \c template1
You are now connected to database template1.
template1=# vacuum verbose pg_class;
NOTICE: --Relation pg_class--
NOTICE: Pages 2: Changed 0, Empty 0; Tup 101: Vac 0, Keep 0, UnUsed 27. Total CPU 0.00s/0.00u sec elapsed 0.00
sec.
VACUUM
No, they aren't shared. (And why yes, some of those databases could
indeed use more regular vacuuming. :-))
Note, that was a 7.2.4 database, so pg_autovacuum won't work there
yet. C'est la vie.
At any rate, template1.pg_class seems pretty distinct from
database1.pg_class which is distinct from database3.pg_class. I think
there's a bit of an error there.
--
(reverse (concatenate 'string "ofni.smrytrebil" "@" "enworbbc"))
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)
On Thu, 2003-09-11 at 17:11, Tom Lane wrote: > "Matthew T. O'Connor" <matthew@zeut.net> writes: > > I designed it that way. It was my understanding that all of the system > > tables pg_class etc... are shared tables, available in all databases, > > but actually stored as only one central set of real tables. > > You are very badly mistaken. > > Only the tables marked "relisshared" in pg_class (currently pg_shadow, > pg_group, pg_database, and their indexes and toast tables) are shared > across a cluster. The rest have separate copies per-database. hrm.... OK. Patch forthcoming....
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> On Thu, 2003-09-11 at 17:11, Tom Lane wrote:
>> "Matthew T. O'Connor" <matthew@zeut.net> writes:
>>> I designed it that way. It was my understanding that all of the system
>>> tables pg_class etc... are shared tables, available in all databases,
>>> but actually stored as only one central set of real tables.
>>
>> Only the tables marked "relisshared" in pg_class (currently pg_shadow,
>> pg_group, pg_database, and their indexes and toast tables) are shared
>> across a cluster. The rest have separate copies per-database.
> hrm.... OK. Patch forthcoming....
BTW, I am not sure it is a good idea to suppress "redundant" vacuuming
of shared tables in the first place. The trouble with doing so is that
if you only vacuum pg_shadow through template1, then only template1 will
ever have up-to-date statistics about it. That's not good.
You might be able to get away with doing actual vacuums only through
template1, and doing just ANALYZEs every so often in other DBs.
regards, tom lane
On Thu, 2003-09-11 at 18:25, Tom Lane wrote: > BTW, I am not sure it is a good idea to suppress "redundant" vacuuming > of shared tables in the first place. The trouble with doing so is that > if you only vacuum pg_shadow through template1, then only template1 will > ever have up-to-date statistics about it. That's not good. > > You might be able to get away with doing actual vacuums only through > template1, and doing just ANALYZEs every so often in other DBs. ok I will see what I can do about that. So I assume that the vacuumdb script handle this just does redundant vacuums / analyzes on shared tables so that it doesn't have a problem with this. If we can supress "redundant" vacuuming I think that would be a good thing as pg_autovacuum is supposed to make the required vacuuming as efficient as possible.
On Thu, 2003-09-11 at 18:25, Tom Lane wrote: > "Matthew T. O'Connor" <matthew@zeut.net> writes: > > hrm.... OK. Patch forthcoming.... > > BTW, I am not sure it is a good idea to suppress "redundant" vacuuming > of shared tables in the first place. The trouble with doing so is that > if you only vacuum pg_shadow through template1, then only template1 will > ever have up-to-date statistics about it. That's not good. > > You might be able to get away with doing actual vacuums only through > template1, and doing just ANALYZEs every so often in other DBs. I made a patch to fix this, but in testing it I noticed that the stats system doesn't work on shared tables as I was expecting it too (as my latest patch requires it too :-). It treats instances of shared tables in separate databases as totally unique tables. This makes it hard to know how much activity has really gone on for a shared table. Is the behavior of the following example expected / desired? template1=# select ... (query details snipped) relname | relisshared | n_tup_ins | n_tup_upd | n_tup_del -------------+-------------+-----------+-----------+-----------pg_database | t | 28 | 0 | 28 (1 row) template1=# create database foo; drop database foo; CREATE DATABASE DROP DATABASE template1=# select relname | relisshared | n_tup_ins | n_tup_upd | n_tup_del -------------+-------------+-----------+-----------+-----------pg_database | t | 29 | 0 | 29 (1 row) template1=# \c matthew You are now connected to database "matthew". matthew=# select relname | relisshared | n_tup_ins | n_tup_upd | n_tup_del -------------+-------------+-----------+-----------+-----------pg_database | t | 2 | 0 | 2 (1 row) matthew=# create database foo; drop database foo; CREATE DATABASE DROP DATABASE matthew=# select relname | relisshared | n_tup_ins | n_tup_upd | n_tup_del -------------+-------------+-----------+-----------+-----------pg_database | t | 3 | 0 | 3 (1 row) matthew=# \c template1 You are now connected to database "template1". template1=# select relname | relisshared | n_tup_ins | n_tup_upd | n_tup_del -------------+-------------+-----------+-----------+-----------pg_database | t | 29 | 0 | 29 (1 row)
Matthew T. O'Connor wrote: > On Thu, 2003-09-11 at 18:25, Tom Lane wrote: > > "Matthew T. O'Connor" <matthew@zeut.net> writes: > > > hrm.... OK. Patch forthcoming.... > > > > BTW, I am not sure it is a good idea to suppress "redundant" vacuuming > > of shared tables in the first place. The trouble with doing so is that > > if you only vacuum pg_shadow through template1, then only template1 will > > ever have up-to-date statistics about it. That's not good. > > > > You might be able to get away with doing actual vacuums only through > > template1, and doing just ANALYZEs every so often in other DBs. > > I made a patch to fix this, but in testing it I noticed that the stats > system doesn't work on shared tables as I was expecting it too (as my > latest patch requires it too :-). It treats instances of shared tables > in separate databases as totally unique tables. This makes it hard to > know how much activity has really gone on for a shared table. > > Is the behavior of the following example expected / desired? I suspect is just a bug because no one noticed it before. As I understand it, the stats system is recorded per-database. We could add stuff so the global tables are only recorded in template1 or perhaps record in template1 but report template1's numbers for all databases. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> I made a patch to fix this, but in testing it I noticed that the stats
> system doesn't work on shared tables as I was expecting it too (as my
> latest patch requires it too :-). It treats instances of shared tables
> in separate databases as totally unique tables.
Hmm. The bufmgr and lower levels handle shared tables by always
associating a database number of zero with them, but I'll bet that the
pg_stats stuff does not do that. I'd call that a bug, yes. Jan, any
thoughts on how complex to fix?
regards, tom lane
Tom Lane wrote: > "Matthew T. O'Connor" <matthew@zeut.net> writes: >> I made a patch to fix this, but in testing it I noticed that the stats >> system doesn't work on shared tables as I was expecting it too (as my >> latest patch requires it too :-). It treats instances of shared tables >> in separate databases as totally unique tables. > > Hmm. The bufmgr and lower levels handle shared tables by always > associating a database number of zero with them, but I'll bet that the > pg_stats stuff does not do that. I'd call that a bug, yes. Jan, any > thoughts on how complex to fix? Need to take a look into, but I agree that this is a bug. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
On Fri, 2003-09-12 at 09:35, Bruce Momjian wrote: > Matthew T. O'Connor wrote: > > I made a patch to fix this, but in testing it I noticed that the stats > > system doesn't work on shared tables as I was expecting it too (as my > > latest patch requires it too :-). It treats instances of shared tables > > in separate databases as totally unique tables. This makes it hard to > > know how much activity has really gone on for a shared table. > > > > Is the behavior of the following example expected / desired? > > I suspect is just a bug because no one noticed it before. As I > understand it, the stats system is recorded per-database. We could add > stuff so the global tables are only recorded in template1 or perhaps > record in template1 but report template1's numbers for all databases. OK, well as we wait on the fix for the stats system, let me submit my patch for pg_autovacuum. This patch assumes that the stats system will be fixed so that all inserts, updates and deletes performed on shared tables reguardless of what database those commands were executed from, will show up in the stats shown in each database. Even if this the stats system isn't fixed, this patch still is much better about monitoring system tables that aren't shared, so it's an improvement no matter what. Matthew T. O'Connor
Attachment
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> Even if this the stats system isn't fixed, this patch still is much
> better about monitoring system tables that aren't shared, so it's an
> improvement no matter what.
How will it act with shared tables if the stats system isn't fixed?
We may decide that tracking shared tables correctly will have to wait
for 7.5.
regards, tom lane
On Fri, 2003-09-12 at 12:46, Tom Lane wrote: > How will it act with shared tables if the stats system isn't fixed? > We may decide that tracking shared tables correctly will have to wait > for 7.5. The behavior in the patch will vacuum a shared table only from template1, and only analyze from all other databases. If the stats system isn't fixed for 7.4, then I can easily change this to vacuum shared tables from all databases. The real problem is that each database will only see activity that was performed in it, so we might need to vacuum far before pg_autovacuum is shown the required amount of activity to do something about it. So we would have a problem if commands that effect these tables are done from lots of different databases. In reality, I don't think these tables change that much (pg_database, pg_shadow, and pg_group), and most of commands that do effect these tables are usually done from template1. I can hardwire in something to hedge this off like setting the threshold for shared tables much much lower than normal thresholds. I could also do something more complicated and try to aggregate all the activity seen by all the databases and when the sum exceeds the threshold then have then perform a vacuum from template1 and analyze from all other databases. Thoughts?
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> So we would have a problem if commands that effect these tables are done
> from lots of different databases. In reality, I don't think these
> tables change that much (pg_database, pg_shadow, and pg_group), and most
> of commands that do effect these tables are usually done from template1.
I agree that there is probably not a large problem here. I just wanted
to be sure that pg_autovacuum wouldn't go nuts if we can't fix pgstats
for 7.4.
> I can hardwire in something to hedge this off like setting the threshold
> for shared tables much much lower than normal thresholds. I could also
> do something more complicated and try to aggregate all the activity seen
> by all the databases and when the sum exceeds the threshold then have
> then perform a vacuum from template1 and analyze from all other
> databases.
That seems like more work than it's worth for a short-term stopgap.
If Jan concludes that fixing pgstats is *really* hard and will not
happen for awhile, then we could talk about more extensive workarounds
in pg_autovacuum, but right now I doubt it's needed.
regards, tom lane
On Fri, 2003-09-12 at 13:06, Tom Lane wrote: > > I can hardwire in something to hedge this off like setting the threshold > > for shared tables much much lower than normal thresholds. I could also > > do something more complicated and try to aggregate all the activity seen > > by all the databases and when the sum exceeds the threshold then have > > then perform a vacuum from template1 and analyze from all other > > databases. > > That seems like more work than it's worth for a short-term stopgap. > > If Jan concludes that fixing pgstats is *really* hard and will not > happen for awhile, then we could talk about more extensive workarounds > in pg_autovacuum, but right now I doubt it's needed. OK, I'll hold for now. The patches I just submitted should be fine, and if the stats system doesn't get fixed, I'll make the small change where vacuum is done from all databases, not just template1. Thanks
matthew@zeut.net ("Matthew T. O'Connor") writes:
> So we would have a problem if commands that effect these tables are done
> from lots of different databases. In reality, I don't think these
> tables change that much (pg_database, pg_shadow, and pg_group), and most
> of commands that do effect these tables are usually done from template1.
I wouldn't necessarily assume the latter ("usually done from
template1"), but these surely seem to be candidates for being fairly
infrequent.
And if they _were_ being touched frequently, would they not trigger
vacuums in the databse that they were being touched in?
In any case, if there are three tables that pg_autovacuum never
touches that _normally_ are pretty quiet, this does not appear to be a
grand disaster.
--
output = ("cbbrowne" "@" "libertyrms.info")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)
matthew@zeut.net ("Matthew T. O'Connor") writes:
> OK, well as we wait on the fix for the stats system, let me submit my
> patch for pg_autovacuum. This patch assumes that the stats system will
> be fixed so that all inserts, updates and deletes performed on shared
> tables reguardless of what database those commands were executed from,
> will show up in the stats shown in each database.
I had to make a further change to this to take quotes off the 'last
ANALYZE' in order for it to not overquote the relation name, so
there's a _little_ work left to get it to play well.
I have deployed it onto several boxes that should be doing some
vacuuming over the weekend, and it is now certainly hitting pg_
tables.
I would like to present a CVS-oriented patch; unfortunately, I had to
change the indentation patterns when editing some of it :-(. The
following _may_ be good; not sure...
? pg_autovacuum
Index: README.pg_autovacuum
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/pg_autovacuum/README.pg_autovacuum,v
retrieving revision 1.2
diff -c -b -r1.2 README.pg_autovacuum
*** README.pg_autovacuum 12 Jun 2003 01:36:44 -0000 1.2
--- README.pg_autovacuum 12 Sep 2003 22:45:37 -0000
***************
*** 2,67 **** -------------------- pg_autovacuum is a libpq client program that monitors all the
! databases associated with a postgresql server. It uses the stats collector to monitor insert, update and delete
activity.
! When a table exceeds its insert or delete threshold (more detail
! on thresholds below) then that table will be vacuumed or analyzed.
!
! This allows postgresql to keep the fsm and table statistics up to
! date, and eliminates the need to schedule periodic vacuums. The primary benefit of pg_autovacuum is that the FSM and
table
! statistic information are updated as needed. When a table is actively
! changing, pg_autovacuum will perform the necessary vacuums and
! analyzes, whereas if a table remains static, no cycles will be wasted
! performing unnecessary vacuums/analyzes. A secondary benefit of pg_autovacuum is that it ensures that a
! database wide vacuum is performed prior to xid wraparound. This is an important, if rare, problem, as failing to do
socan result in major
! data loss.
! KNOWN ISSUES: -------------
- pg_autovacuum has been tested under Redhat Linux (by me) and Solaris (by
- Christopher B. Browne) and all known bugs have been resolved. Please report
- any problems to the hackers list.
-
- pg_autovacuum does not get started automatically by either the postmaster or
- by pg_ctl. Along the sames lines, when the postmaster exits no one tells
- pg_autovacuum. The result is that at the start of the next loop,
- pg_autovacuum fails to connect to the server and exits. Any time it fails
- to connect pg_autovacuum exits.
-
- pg_autovacuum requires that the stats system be enabled and reporting row
- level stats. The overhead of the stats system has been shown to be
- significant under certain workloads. For instance a tight loop of queries
- performing "select 1" was nearly 30% slower with stats enabled. However,
- in practice with more realistic workloads, the stats system overhead is
- usually nominal. INSTALL: --------
! As of postgresql v7.4 pg_autovacuum is included in the main source tree
! under contrib. Therefore you just make && make install (similar to most other
! contrib modules) and it will be installed for you.
!
! If you are using an earlier version of postgresql just uncompress the tar.gz
! into the contrib directory and modify the contrib/Makefile to include the pg_autovacuum
! directory. pg_autovacuum will then be made as part of the standard
! postgresql install.
! make sure that the folowing are set in postgresql.conf stats_start_collector = true stats_row_level = true
! start up the postmaster, then execute the pg_autovacuum executable. Command line arguments:
-----------------------
--- 2,98 ---- -------------------- pg_autovacuum is a libpq client program that monitors all the
! databases associated with a PostgreSQL server. It uses the statistics collector to monitor insert, update and delete
activity.
! When a table exceeds a insert or delete threshold (for more detail on
! thresholds, see "Vacuum and Analyze" below) then that table will be
! vacuumed and/or analyzed.
!
! This allows PostgreSQL to keep the FSM (Free Space Map) and table
! statistics up to date, and eliminates the need to schedule periodic
! vacuums. The primary benefit of pg_autovacuum is that the FSM and table
! statistic information are updated more nearly as frequently as needed.
! When a table is actively changing, pg_autovacuum will perform the
! VACUUMs and ANALYZEs that such a table needs, whereas if a table
! remains static, no cycles will be wasted performing this
! unnecessarily. A secondary benefit of pg_autovacuum is that it ensures that a
! database wide vacuum is performed prior to XID wraparound. This is an important, if rare, problem, as failing to do
socan result in major
! data loss. (See the section in the _Administrator's Guide_ entitled
! "Preventing transaction ID wraparound failures" for more details.) KNOWN ISSUES: -------------
+ pg_autovacuum has been tested under Redhat Linux (by me) and Debian
+ GNU/Linux and Solaris (by Christopher B. Browne) and all known bugs
+ have been resolved. Please report any problems to the hackers list.
+
+ pg_autovacuum requires that the statistics system be enabled and
+ reporting row level stats. The overhead of the stats system has been
+ shown to be significant costly under certain workloads. For instance,
+ a tight loop of queries performing "select 1" was found to run nearly
+ 30% slower when stats were enabled. However, in practice, with more
+ realistic workloads, the stats system overhead is usually nominal.
+
+ pg_autovacuum does not get started automatically by either the
+ postmaster or by pg_ctl. Similarly, when the postmaster exits, no one
+ tells pg_autovacuum. The result of that is that at the start of the
+ next loop, pg_autovacuum will fail to connect to the server and
+ exit(). Any time it fails to connect pg_autovacuum exit()s.
+
+ While pg_autovacuum can manage vacuums for as many databases as you
+ may have tied to a particular PostgreSQL postmaster, it can only
+ connect to a single PostgreSQL postmaster. Thus, if you have multiple
+ postmasters on a particular host, you will need multiple pg_autovacuum
+ instances, and they have no way, at present, to coordinate between one
+ another to ensure that they do not concurrently vacuum big tables.
+
+ TODO:
+ -----
+
+ At present, there are no sample scripts to automatically start up
+ pg_autovacuum along with the database. It would be desirable to have
+ a SysV script to start up pg_autovacuum after PostgreSQL has been
+ started.
+
+ Some users have expressed interest in making pg_autovacuum more
+ configurable so that certain tables known to be inactive could be
+ excluded from being vacuumed. It would probably make sense to
+ introduce this sort of functionality by providing arguments to specify
+ the database and schema in which to find a configuration table. INSTALL: --------
! As of postgresql v7.4 pg_autovacuum is included in the main source
! tree under contrib. Therefore you merely need to "make && make
! install" (similar to most other contrib modules) and it will be
! installed for you.
!
! If you are using an earlier version of PostgreSQL, uncompress the
! tar.gz file into the contrib directory and modify the contrib/Makefile
! to include the pg_autovacuum directory. pg_autovacuum will then be
! built as part of the standard postgresql install.
! make sure that the following are set in postgresql.conf: stats_start_collector = true stats_row_level = true
! Start up the postmaster, then execute the pg_autovacuum executable.
!
! If you have a script that automatically starts up the PostgreSQL
! instance, you might add in, after that, something similar to the
! following:
+ sleep 10 # To give the database some time to start up
+ $PGBINS/pg_autovacuum -D -s $SBASE -S $SSCALE ... [other arguments] Command line arguments:
-----------------------
***************
*** 69,75 **** pg_autovacuum has the following optional arguments: -d debug: 0 silent, 1 basic info, 2 more debug
info, etc...
! -D dameonize: Detach from tty and run in background. -s sleep base value: see "Sleeping" below. -S sleep scaling
factor:see "Sleeping" below. -v vacuum base threshold: see Vacuum and Analyze.
--- 100,106 ---- pg_autovacuum has the following optional arguments: -d debug: 0 silent, 1 basic info, 2 more debug
info, etc...
! -D daemonize: Detach from tty and run in background. -s sleep base value: see "Sleeping" below. -S sleep scaling
factor:see "Sleeping" below. -v vacuum base threshold: see Vacuum and Analyze.
***************
*** 80,97 **** -U username: Username pg_autovacuum will use to connect with, if not specified the current username
isused. -P password: Password pg_autovacuum will use to connect with.
! -H host: host name or IP to connect too. -p port: port used for connection. -h help: list of command line options.
! All arguments have default values defined in pg_autovacuum.h. At the
! time of writing they are: -d 1 -v 1000 -V 2
! -a 500 (half of -v is not specified)
! -A 1 (half of -v is not specified) -s 300 (5 minutes) -S 2
--- 111,128 ---- -U username: Username pg_autovacuum will use to connect with, if not specified the current username
isused. -P password: Password pg_autovacuum will use to connect with.
! -H host: host name or IP to connect to. -p port: port used for connection. -h help: list of command line options.
! Numerous arguments have default values defined in pg_autovacuum.h. At
! the time of writing they are: -d 1 -v 1000 -V 2
! -a 500 (half of -v if not specified)
! -A 1 (half of -v if not specified) -s 300 (5 minutes) -S 2
***************
*** 99,111 **** Vacuum and Analyze: -------------------
! pg_autovacuum performs either a vacuum analyze or just analyze depending
! on the quantity and type of table activity (insert, update, or delete): - If the number of (inserts + updates +
deletes)> AnalyzeThreshold, then only an analyze is performed.
! - If the number of (deletes + updates ) > VacuumThreshold, then a vacuum analyze is performed. deleteThreshold is
equalto:
--- 130,143 ---- Vacuum and Analyze: -------------------
! pg_autovacuum performs either a VACUUM ANALYZE or just ANALYZE
! depending on the mixture of table activity (insert, update, or
! delete): - If the number of (inserts + updates + deletes) > AnalyzeThreshold, then only an analyze is performed.
! - If the number of (deletes + updates) > VacuumThreshold, then a vacuum analyze is performed. deleteThreshold is
equalto:
***************
*** 115,140 **** analyze_base_value + (analyze_scaling_factor * "number of tuples in the table") The
AnalyzeThresholddefaults to half of the VacuumThreshold since it
! represents a much less expensive operation (approx 5%-10% of vacuum), and
! running it more often should not substantially degrade system performance. Sleeping: --------- pg_autovacuum sleeps
fora while after it is done checking all the databases. It does this in order to limit the amount of system
! resources it consumes. This also allows the system administrator to configure pg_autovacuum to be more or less
aggressive. Reducing the sleep time will cause pg_autovacuum to respond more quickly to changes, whether they be
databaseaddition/removal, table addition/removal, or just normal table activity.
! On the other hand, setting pg_autovaccum to sleep values to agressivly
! (for too short a period of time) can have a negative effect on server
! performance. If a table gets vacuumed 5 times during the course of a
! large update, this is likely to take much longer than if the table was
! vacuumed only once, at the end. The total time it sleeps is equal to:
--- 147,173 ---- analyze_base_value + (analyze_scaling_factor * "number of tuples in the table") The
AnalyzeThresholddefaults to half of the VacuumThreshold since it
! represents a much less expensive operation (approx 5%-10% of vacuum),
! and running ANALYZE more often should not substantially degrade system
! performance. Sleeping: --------- pg_autovacuum sleeps for a while after it is done checking all the databases. It
doesthis in order to limit the amount of system
! resources it consumes. This allows the system administrator to configure pg_autovacuum to be more or less
aggressive. Reducing the sleep time will cause pg_autovacuum to respond more quickly to changes, whether they be
databaseaddition/removal, table addition/removal, or just normal table activity.
! On the other hand, setting pg_autovacuum to sleep values too
! aggressively (to too short periods of time) can have a negative effect
! on server performance. For instance, if a table gets vacuumed 5 times
! during the course of a large set of updates, this is likely to take a
! lot more work than if the table was vacuumed just once, at the end. The total time it sleeps is equal to:
***************
*** 142,156 **** loop" Note that timing measurements are made in seconds; specifying
! "pg_vacuum -s 1" means pg_autovacuum could poll the database upto 60 times
! minute. In a system with large tables where vacuums may run for several
! minutes, longer times between vacuums are likely to be appropriate. What pg_autovacuum monitors:
----------------------------
! pg_autovacuum dynamically generates a list of all databases and tables that
! exist on the server. It will dynamically add and remove databases and
! tables that are removed from the database server while pg_autovacuum is
! running. Overhead is fairly small per object. For example: 10 databases
! with 10 tables each appears to less than 10k of memory on my Linux box.
--- 175,191 ---- loop" Note that timing measurements are made in seconds; specifying
! "pg_vacuum -s 1" means pg_autovacuum could poll the database up to 60
! times minute. In a system with large tables where vacuums may run for
! several minutes, rather longer times between vacuums are likely to be
! appropriate. What pg_autovacuum monitors: ----------------------------
! pg_autovacuum dynamically generates a list of all databases and tables
! that exist on the server. It will dynamically add and remove
! databases and tables that are removed from the database server while
! pg_autovacuum is running. Overhead is fairly small per object. For
! example: 10 databases with 10 tables each appears to less than 10k of
! memory on my Linux box.
Index: pg_autovacuum.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/pg_autovacuum/pg_autovacuum.c,v
retrieving revision 1.5
diff -c -b -r1.5 pg_autovacuum.c
*** pg_autovacuum.c 11 Sep 2003 19:01:18 -0000 1.5
--- pg_autovacuum.c 12 Sep 2003 22:45:37 -0000
***************
*** 118,123 ****
--- 118,129 ---- new_tbl->reltuples = atoi(PQgetvalue(res, row, PQfnumber(res, "reltuples")));
new_tbl->relpages= atoi(PQgetvalue(res, row, PQfnumber(res, "relpages")));
+ log_entry(PQgetvalue(res, row, PQfnumber(res, "relisshared")));
+ if (strcmp("t", PQgetvalue(res, row, PQfnumber(res, "relisshared"))))
+ new_tbl->relisshared = 0;
+ else
+ new_tbl->relisshared = 1;
+ new_tbl->analyze_threshold = args->analyze_base_threshold + args->analyze_scaling_factor *
new_tbl->reltuples; new_tbl->vacuum_threshold =
***************
*** 213,219 **** * both remove tables from the list that no longer exist and add * tables to the list
thatare new */
! res = send_query(query_table_stats(dbi), dbi); t = PQntuples(res); /*
--- 219,225 ---- * both remove tables from the list that no longer exist and add * tables to the list
thatare new */
! res = send_query((char *) TABLE_STATS_QUERY, dbi); t = PQntuples(res); /*
***************
*** 353,359 **** { sprintf(logbuffer, " table name: %s.%s", tbl->dbi->dbname, tbl->table_name);
log_entry(logbuffer);
! sprintf(logbuffer, " relfilenode: %i", tbl->relfilenode); log_entry(logbuffer); sprintf(logbuffer, "
reltuples: %i; relpages: %i", tbl->reltuples, tbl->relpages); log_entry(logbuffer);
--- 359,365 ---- { sprintf(logbuffer, " table name: %s.%s", tbl->dbi->dbname, tbl->table_name);
log_entry(logbuffer);
! sprintf(logbuffer, " relfilenode: %i; relisshared: %i", tbl->relfilenode, tbl->relisshared);
log_entry(logbuffer); sprintf(logbuffer, " reltuples: %i; relpages: %i", tbl->reltuples, tbl->relpages);
log_entry(logbuffer);
***************
*** 688,706 **** /* End of DB List Management Function */
! /* Begninning of misc Functions */
!
!
! char *
! query_table_stats(db_info * dbi)
! {
! if (!strcmp(dbi->dbname, "template1")) /* Use template1 to
! * monitor the system
! * tables */
! return (char *) TABLE_STATS_ALL;
! else
! return (char *) TABLE_STATS_USER;
! } /* Perhaps add some test to this function to make sure that the stats we need are available */ PGconn *
--- 694,700 ---- /* End of DB List Management Function */
! /* Beginning of misc Functions */ /* Perhaps add some test to this function to make sure that the stats we need are
available*/ PGconn *
***************
*** 753,758 ****
--- 747,755 ---- if (NULL == dbi->conn) return NULL;
+ if (args->debug >= 4)
+ log_entry(query);
+ res = PQexec(dbi->conn, query); if (!res)
***************
*** 964,970 **** int j = 0, loops = 0;
! /* int numInserts, numDeletes, */ int sleep_secs; Dllist *db_list; Dlelem
*db_elem,
--- 961,967 ---- int j = 0, loops = 0;
! /* int numInserts, numDeletes, */ int sleep_secs; Dllist *db_list; Dlelem
*db_elem,
***************
*** 1055,1061 **** if (0 == xid_wraparound_check(dbs)); {
! res = send_query(query_table_stats(dbs), dbs); /* Get an updated
* snapshot of this dbs
* table stats */ for (j = 0; j < PQntuples(res); j++)
--- 1052,1058 ---- if (0 == xid_wraparound_check(dbs)); {
! res = send_query(TABLE_STATS_QUERY, dbs); /* Get an updated *
snapshotof this dbs * table stats */ for (j = 0; j < PQntuples(res); j++)
***************
*** 1087,1093 **** */ if ((tbl->curr_vacuum_count -
tbl->CountAtLastVacuum)>= tbl->vacuum_threshold) {
! snprintf(buf, sizeof(buf), "VACUUM ANALYZE \"%s\"", tbl->table_name);
if (args->debug >= 1) {
sprintf(logbuffer, "Performing: %s", buf);
--- 1084,1094 ---- */ if ((tbl->curr_vacuum_count - tbl->CountAtLastVacuum) >=
tbl->vacuum_threshold) {
! /* if relisshared = t and database != template1 then only do an analyze */
! if((tbl->relisshared > 0) && (strcmp("template1",dbs->dbname)))
! snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name);
! else
! snprintf(buf, sizeof(buf), "VACUUM ANALYZE %s", tbl->table_name); if (args->debug
>=1) { sprintf(logbuffer, "Performing: %s", buf);
***************
*** 1101,1107 **** } else if ((tbl->curr_analyze_count
-tbl->CountAtLastAnalyze) >= tbl->analyze_threshold) {
! snprintf(buf, sizeof(buf), "ANALYZE \"%s\"", tbl->table_name);
if (args->debug >= 1) {
sprintf(logbuffer,"Performing: %s", buf);
--- 1102,1108 ---- } else if ((tbl->curr_analyze_count - tbl->CountAtLastAnalyze) >=
tbl->analyze_threshold) {
! snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name); if (args->debug >= 1)
{ sprintf(logbuffer, "Performing: %s", buf);
Index: pg_autovacuum.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/pg_autovacuum/pg_autovacuum.h,v
retrieving revision 1.6
diff -c -b -r1.6 pg_autovacuum.h
*** pg_autovacuum.h 7 Aug 2003 21:11:57 -0000 1.6
--- pg_autovacuum.h 12 Sep 2003 22:45:37 -0000
***************
*** 34,41 **** #define VACUUM_ANALYZE 0 #define ANALYZE_ONLY 1
! #define TABLE_STATS_ALL "select
a.relfilenode,a.relname,a.relnamespace,a.relpages,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_delfrom
pg_classa, pg_stat_all_tables b where a.relfilenode=b.relid"
! #define TABLE_STATS_USER "select
a.relfilenode,a.relname,a.relnamespace,a.relpages,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_delfrom
pg_classa, pg_stat_user_tables b where a.relfilenode=b.relid" #define FRONTEND #define PAGES_QUERY "select
relfilenode,reltuples,relpagesfrom pg_class where relfilenode=%i" #define FROZENOID_QUERY "select oid,age(datfrozenxid)
frompg_database where datname = 'template1'"
--- 34,41 ---- #define VACUUM_ANALYZE 0 #define ANALYZE_ONLY 1
! #define TABLE_STATS_QUERY "select
a.relfilenode,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del
frompg_class a, pg_stat_all_tables b where a.relfilenode=b.relid and a.relkind = 'r'"
! #define FRONTEND #define PAGES_QUERY "select relfilenode,reltuples,relpages from pg_class where relfilenode=%i"
#defineFROZENOID_QUERY "select oid,age(datfrozenxid) from pg_database where datname = 'template1'"
***************
*** 86,91 ****
--- 86,92 ---- *table_name; int relfilenode, reltuples,
+ relisshared, relpages; long analyze_threshold,
vacuum_threshold;
***************
*** 132,138 **** static PGconn *db_connect(db_info * dbi); static void db_disconnect(db_info * dbi); static PGresult
*send_query(constchar *query, db_info * dbi);
- static char *query_table_stats(db_info * dbi); /* Other Generally needed Functions */ static void daemonize(void);
--- 133,138 ----
--
let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)
Patch applied as part of Christopher Browne's patch. --------------------------------------------------------------------------- Matthew T. O'Connor wrote: > On Fri, 2003-09-12 at 09:35, Bruce Momjian wrote: > > Matthew T. O'Connor wrote: > > > I made a patch to fix this, but in testing it I noticed that the stats > > > system doesn't work on shared tables as I was expecting it too (as my > > > latest patch requires it too :-). It treats instances of shared tables > > > in separate databases as totally unique tables. This makes it hard to > > > know how much activity has really gone on for a shared table. > > > > > > Is the behavior of the following example expected / desired? > > > > I suspect is just a bug because no one noticed it before. As I > > understand it, the stats system is recorded per-database. We could add > > stuff so the global tables are only recorded in template1 or perhaps > > record in template1 but report template1's numbers for all databases. > > OK, well as we wait on the fix for the stats system, let me submit my > patch for pg_autovacuum. This patch assumes that the stats system will > be fixed so that all inserts, updates and deletes performed on shared > tables reguardless of what database those commands were executed from, > will show up in the stats shown in each database. > > Even if this the stats system isn't fixed, this patch still is much > better about monitoring system tables that aren't shared, so it's an > improvement no matter what. > > Matthew T. O'Connor > > [ Attachment, skipping... ] [ Attachment, skipping... ] -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
This was a combined patch including Matthew T. O'Connor's Patch.
Applied. Thanks.
---------------------------------------------------------------------------
Christopher Browne wrote:
> matthew@zeut.net ("Matthew T. O'Connor") writes:
> > OK, well as we wait on the fix for the stats system, let me submit my
> > patch for pg_autovacuum. This patch assumes that the stats system will
> > be fixed so that all inserts, updates and deletes performed on shared
> > tables reguardless of what database those commands were executed from,
> > will show up in the stats shown in each database.
>
> I had to make a further change to this to take quotes off the 'last
> ANALYZE' in order for it to not overquote the relation name, so
> there's a _little_ work left to get it to play well.
>
> I have deployed it onto several boxes that should be doing some
> vacuuming over the weekend, and it is now certainly hitting pg_
> tables.
>
> I would like to present a CVS-oriented patch; unfortunately, I had to
> change the indentation patterns when editing some of it :-(. The
> following _may_ be good; not sure...
>
> ? pg_autovacuum
> Index: README.pg_autovacuum
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/contrib/pg_autovacuum/README.pg_autovacuum,v
> retrieving revision 1.2
> diff -c -b -r1.2 README.pg_autovacuum
> *** README.pg_autovacuum 12 Jun 2003 01:36:44 -0000 1.2
> --- README.pg_autovacuum 12 Sep 2003 22:45:37 -0000
> ***************
> *** 2,67 ****
> --------------------
>
> pg_autovacuum is a libpq client program that monitors all the
> ! databases associated with a postgresql server. It uses the stats
> collector to monitor insert, update and delete activity.
>
> ! When a table exceeds its insert or delete threshold (more detail
> ! on thresholds below) then that table will be vacuumed or analyzed.
> !
> ! This allows postgresql to keep the fsm and table statistics up to
> ! date, and eliminates the need to schedule periodic vacuums.
>
> The primary benefit of pg_autovacuum is that the FSM and table
> ! statistic information are updated as needed. When a table is actively
> ! changing, pg_autovacuum will perform the necessary vacuums and
> ! analyzes, whereas if a table remains static, no cycles will be wasted
> ! performing unnecessary vacuums/analyzes.
>
> A secondary benefit of pg_autovacuum is that it ensures that a
> ! database wide vacuum is performed prior to xid wraparound. This is an
> important, if rare, problem, as failing to do so can result in major
> ! data loss.
> !
>
> KNOWN ISSUES:
> -------------
> - pg_autovacuum has been tested under Redhat Linux (by me) and Solaris (by
> - Christopher B. Browne) and all known bugs have been resolved. Please report
> - any problems to the hackers list.
> -
> - pg_autovacuum does not get started automatically by either the postmaster or
> - by pg_ctl. Along the sames lines, when the postmaster exits no one tells
> - pg_autovacuum. The result is that at the start of the next loop,
> - pg_autovacuum fails to connect to the server and exits. Any time it fails
> - to connect pg_autovacuum exits.
> -
> - pg_autovacuum requires that the stats system be enabled and reporting row
> - level stats. The overhead of the stats system has been shown to be
> - significant under certain workloads. For instance a tight loop of queries
> - performing "select 1" was nearly 30% slower with stats enabled. However,
> - in practice with more realistic workloads, the stats system overhead is
> - usually nominal.
>
>
> INSTALL:
> --------
>
> ! As of postgresql v7.4 pg_autovacuum is included in the main source tree
> ! under contrib. Therefore you just make && make install (similar to most other
> ! contrib modules) and it will be installed for you.
> !
> ! If you are using an earlier version of postgresql just uncompress the tar.gz
> ! into the contrib directory and modify the contrib/Makefile to include the pg_autovacuum
> ! directory. pg_autovacuum will then be made as part of the standard
> ! postgresql install.
>
> ! make sure that the folowing are set in postgresql.conf
>
> stats_start_collector = true
> stats_row_level = true
>
> ! start up the postmaster, then execute the pg_autovacuum executable.
>
>
> Command line arguments:
> -----------------------
> --- 2,98 ----
> --------------------
>
> pg_autovacuum is a libpq client program that monitors all the
> ! databases associated with a PostgreSQL server. It uses the statistics
> collector to monitor insert, update and delete activity.
>
> ! When a table exceeds a insert or delete threshold (for more detail on
> ! thresholds, see "Vacuum and Analyze" below) then that table will be
> ! vacuumed and/or analyzed.
> !
> ! This allows PostgreSQL to keep the FSM (Free Space Map) and table
> ! statistics up to date, and eliminates the need to schedule periodic
> ! vacuums.
>
> The primary benefit of pg_autovacuum is that the FSM and table
> ! statistic information are updated more nearly as frequently as needed.
> ! When a table is actively changing, pg_autovacuum will perform the
> ! VACUUMs and ANALYZEs that such a table needs, whereas if a table
> ! remains static, no cycles will be wasted performing this
> ! unnecessarily.
>
> A secondary benefit of pg_autovacuum is that it ensures that a
> ! database wide vacuum is performed prior to XID wraparound. This is an
> important, if rare, problem, as failing to do so can result in major
> ! data loss. (See the section in the _Administrator's Guide_ entitled
> ! "Preventing transaction ID wraparound failures" for more details.)
>
> KNOWN ISSUES:
> -------------
>
> + pg_autovacuum has been tested under Redhat Linux (by me) and Debian
> + GNU/Linux and Solaris (by Christopher B. Browne) and all known bugs
> + have been resolved. Please report any problems to the hackers list.
> +
> + pg_autovacuum requires that the statistics system be enabled and
> + reporting row level stats. The overhead of the stats system has been
> + shown to be significant costly under certain workloads. For instance,
> + a tight loop of queries performing "select 1" was found to run nearly
> + 30% slower when stats were enabled. However, in practice, with more
> + realistic workloads, the stats system overhead is usually nominal.
> +
> + pg_autovacuum does not get started automatically by either the
> + postmaster or by pg_ctl. Similarly, when the postmaster exits, no one
> + tells pg_autovacuum. The result of that is that at the start of the
> + next loop, pg_autovacuum will fail to connect to the server and
> + exit(). Any time it fails to connect pg_autovacuum exit()s.
> +
> + While pg_autovacuum can manage vacuums for as many databases as you
> + may have tied to a particular PostgreSQL postmaster, it can only
> + connect to a single PostgreSQL postmaster. Thus, if you have multiple
> + postmasters on a particular host, you will need multiple pg_autovacuum
> + instances, and they have no way, at present, to coordinate between one
> + another to ensure that they do not concurrently vacuum big tables.
> +
> + TODO:
> + -----
> +
> + At present, there are no sample scripts to automatically start up
> + pg_autovacuum along with the database. It would be desirable to have
> + a SysV script to start up pg_autovacuum after PostgreSQL has been
> + started.
> +
> + Some users have expressed interest in making pg_autovacuum more
> + configurable so that certain tables known to be inactive could be
> + excluded from being vacuumed. It would probably make sense to
> + introduce this sort of functionality by providing arguments to specify
> + the database and schema in which to find a configuration table.
>
> INSTALL:
> --------
>
> ! As of postgresql v7.4 pg_autovacuum is included in the main source
> ! tree under contrib. Therefore you merely need to "make && make
> ! install" (similar to most other contrib modules) and it will be
> ! installed for you.
> !
> ! If you are using an earlier version of PostgreSQL, uncompress the
> ! tar.gz file into the contrib directory and modify the contrib/Makefile
> ! to include the pg_autovacuum directory. pg_autovacuum will then be
> ! built as part of the standard postgresql install.
>
> ! make sure that the following are set in postgresql.conf:
>
> stats_start_collector = true
> stats_row_level = true
>
> ! Start up the postmaster, then execute the pg_autovacuum executable.
> !
> ! If you have a script that automatically starts up the PostgreSQL
> ! instance, you might add in, after that, something similar to the
> ! following:
>
> + sleep 10 # To give the database some time to start up
> + $PGBINS/pg_autovacuum -D -s $SBASE -S $SSCALE ... [other arguments]
>
> Command line arguments:
> -----------------------
> ***************
> *** 69,75 ****
> pg_autovacuum has the following optional arguments:
>
> -d debug: 0 silent, 1 basic info, 2 more debug info, etc...
> ! -D dameonize: Detach from tty and run in background.
> -s sleep base value: see "Sleeping" below.
> -S sleep scaling factor: see "Sleeping" below.
> -v vacuum base threshold: see Vacuum and Analyze.
> --- 100,106 ----
> pg_autovacuum has the following optional arguments:
>
> -d debug: 0 silent, 1 basic info, 2 more debug info, etc...
> ! -D daemonize: Detach from tty and run in background.
> -s sleep base value: see "Sleeping" below.
> -S sleep scaling factor: see "Sleeping" below.
> -v vacuum base threshold: see Vacuum and Analyze.
> ***************
> *** 80,97 ****
> -U username: Username pg_autovacuum will use to connect with, if not
> specified the current username is used.
> -P password: Password pg_autovacuum will use to connect with.
> ! -H host: host name or IP to connect too.
> -p port: port used for connection.
> -h help: list of command line options.
>
> ! All arguments have default values defined in pg_autovacuum.h. At the
> ! time of writing they are:
>
> -d 1
> -v 1000
> -V 2
> ! -a 500 (half of -v is not specified)
> ! -A 1 (half of -v is not specified)
> -s 300 (5 minutes)
> -S 2
>
> --- 111,128 ----
> -U username: Username pg_autovacuum will use to connect with, if not
> specified the current username is used.
> -P password: Password pg_autovacuum will use to connect with.
> ! -H host: host name or IP to connect to.
> -p port: port used for connection.
> -h help: list of command line options.
>
> ! Numerous arguments have default values defined in pg_autovacuum.h. At
> ! the time of writing they are:
>
> -d 1
> -v 1000
> -V 2
> ! -a 500 (half of -v if not specified)
> ! -A 1 (half of -v if not specified)
> -s 300 (5 minutes)
> -S 2
>
> ***************
> *** 99,111 ****
> Vacuum and Analyze:
> -------------------
>
> ! pg_autovacuum performs either a vacuum analyze or just analyze depending
> ! on the quantity and type of table activity (insert, update, or delete):
>
> - If the number of (inserts + updates + deletes) > AnalyzeThreshold, then
> only an analyze is performed.
>
> ! - If the number of (deletes + updates ) > VacuumThreshold, then a
> vacuum analyze is performed.
>
> deleteThreshold is equal to:
> --- 130,143 ----
> Vacuum and Analyze:
> -------------------
>
> ! pg_autovacuum performs either a VACUUM ANALYZE or just ANALYZE
> ! depending on the mixture of table activity (insert, update, or
> ! delete):
>
> - If the number of (inserts + updates + deletes) > AnalyzeThreshold, then
> only an analyze is performed.
>
> ! - If the number of (deletes + updates) > VacuumThreshold, then a
> vacuum analyze is performed.
>
> deleteThreshold is equal to:
> ***************
> *** 115,140 ****
> analyze_base_value + (analyze_scaling_factor * "number of tuples in the table")
>
> The AnalyzeThreshold defaults to half of the VacuumThreshold since it
> ! represents a much less expensive operation (approx 5%-10% of vacuum), and
> ! running it more often should not substantially degrade system performance.
>
> Sleeping:
> ---------
>
> pg_autovacuum sleeps for a while after it is done checking all the
> databases. It does this in order to limit the amount of system
> ! resources it consumes. This also allows the system administrator to
> configure pg_autovacuum to be more or less aggressive.
>
> Reducing the sleep time will cause pg_autovacuum to respond more
> quickly to changes, whether they be database addition/removal, table
> addition/removal, or just normal table activity.
>
> ! On the other hand, setting pg_autovaccum to sleep values to agressivly
> ! (for too short a period of time) can have a negative effect on server
> ! performance. If a table gets vacuumed 5 times during the course of a
> ! large update, this is likely to take much longer than if the table was
> ! vacuumed only once, at the end.
>
> The total time it sleeps is equal to:
>
> --- 147,173 ----
> analyze_base_value + (analyze_scaling_factor * "number of tuples in the table")
>
> The AnalyzeThreshold defaults to half of the VacuumThreshold since it
> ! represents a much less expensive operation (approx 5%-10% of vacuum),
> ! and running ANALYZE more often should not substantially degrade system
> ! performance.
>
> Sleeping:
> ---------
>
> pg_autovacuum sleeps for a while after it is done checking all the
> databases. It does this in order to limit the amount of system
> ! resources it consumes. This allows the system administrator to
> configure pg_autovacuum to be more or less aggressive.
>
> Reducing the sleep time will cause pg_autovacuum to respond more
> quickly to changes, whether they be database addition/removal, table
> addition/removal, or just normal table activity.
>
> ! On the other hand, setting pg_autovacuum to sleep values too
> ! aggressively (to too short periods of time) can have a negative effect
> ! on server performance. For instance, if a table gets vacuumed 5 times
> ! during the course of a large set of updates, this is likely to take a
> ! lot more work than if the table was vacuumed just once, at the end.
>
> The total time it sleeps is equal to:
>
> ***************
> *** 142,156 ****
> loop"
>
> Note that timing measurements are made in seconds; specifying
> ! "pg_vacuum -s 1" means pg_autovacuum could poll the database upto 60 times
> ! minute. In a system with large tables where vacuums may run for several
> ! minutes, longer times between vacuums are likely to be appropriate.
>
> What pg_autovacuum monitors:
> ----------------------------
>
> ! pg_autovacuum dynamically generates a list of all databases and tables that
> ! exist on the server. It will dynamically add and remove databases and
> ! tables that are removed from the database server while pg_autovacuum is
> ! running. Overhead is fairly small per object. For example: 10 databases
> ! with 10 tables each appears to less than 10k of memory on my Linux box.
> --- 175,191 ----
> loop"
>
> Note that timing measurements are made in seconds; specifying
> ! "pg_vacuum -s 1" means pg_autovacuum could poll the database up to 60
> ! times minute. In a system with large tables where vacuums may run for
> ! several minutes, rather longer times between vacuums are likely to be
> ! appropriate.
>
> What pg_autovacuum monitors:
> ----------------------------
>
> ! pg_autovacuum dynamically generates a list of all databases and tables
> ! that exist on the server. It will dynamically add and remove
> ! databases and tables that are removed from the database server while
> ! pg_autovacuum is running. Overhead is fairly small per object. For
> ! example: 10 databases with 10 tables each appears to less than 10k of
> ! memory on my Linux box.
> Index: pg_autovacuum.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/contrib/pg_autovacuum/pg_autovacuum.c,v
> retrieving revision 1.5
> diff -c -b -r1.5 pg_autovacuum.c
> *** pg_autovacuum.c 11 Sep 2003 19:01:18 -0000 1.5
> --- pg_autovacuum.c 12 Sep 2003 22:45:37 -0000
> ***************
> *** 118,123 ****
> --- 118,129 ----
> new_tbl->reltuples = atoi(PQgetvalue(res, row, PQfnumber(res, "reltuples")));
> new_tbl->relpages = atoi(PQgetvalue(res, row, PQfnumber(res, "relpages")));
>
> + log_entry(PQgetvalue(res, row, PQfnumber(res, "relisshared")));
> + if (strcmp("t", PQgetvalue(res, row, PQfnumber(res, "relisshared"))))
> + new_tbl->relisshared = 0;
> + else
> + new_tbl->relisshared = 1;
> +
> new_tbl->analyze_threshold =
> args->analyze_base_threshold + args->analyze_scaling_factor * new_tbl->reltuples;
> new_tbl->vacuum_threshold =
> ***************
> *** 213,219 ****
> * both remove tables from the list that no longer exist and add
> * tables to the list that are new
> */
> ! res = send_query(query_table_stats(dbi), dbi);
> t = PQntuples(res);
>
> /*
> --- 219,225 ----
> * both remove tables from the list that no longer exist and add
> * tables to the list that are new
> */
> ! res = send_query((char *) TABLE_STATS_QUERY, dbi);
> t = PQntuples(res);
>
> /*
> ***************
> *** 353,359 ****
> {
> sprintf(logbuffer, " table name: %s.%s", tbl->dbi->dbname, tbl->table_name);
> log_entry(logbuffer);
> ! sprintf(logbuffer, " relfilenode: %i", tbl->relfilenode);
> log_entry(logbuffer);
> sprintf(logbuffer, " reltuples: %i; relpages: %i", tbl->reltuples, tbl->relpages);
> log_entry(logbuffer);
> --- 359,365 ----
> {
> sprintf(logbuffer, " table name: %s.%s", tbl->dbi->dbname, tbl->table_name);
> log_entry(logbuffer);
> ! sprintf(logbuffer, " relfilenode: %i; relisshared: %i", tbl->relfilenode, tbl->relisshared);
> log_entry(logbuffer);
> sprintf(logbuffer, " reltuples: %i; relpages: %i", tbl->reltuples, tbl->relpages);
> log_entry(logbuffer);
> ***************
> *** 688,706 ****
>
> /* End of DB List Management Function */
>
> ! /* Begninning of misc Functions */
> !
> !
> ! char *
> ! query_table_stats(db_info * dbi)
> ! {
> ! if (!strcmp(dbi->dbname, "template1")) /* Use template1 to
> ! * monitor the system
> ! * tables */
> ! return (char *) TABLE_STATS_ALL;
> ! else
> ! return (char *) TABLE_STATS_USER;
> ! }
>
> /* Perhaps add some test to this function to make sure that the stats we need are available */
> PGconn *
> --- 694,700 ----
>
> /* End of DB List Management Function */
>
> ! /* Beginning of misc Functions */
>
> /* Perhaps add some test to this function to make sure that the stats we need are available */
> PGconn *
> ***************
> *** 753,758 ****
> --- 747,755 ----
> if (NULL == dbi->conn)
> return NULL;
>
> + if (args->debug >= 4)
> + log_entry(query);
> +
> res = PQexec(dbi->conn, query);
>
> if (!res)
> ***************
> *** 964,970 ****
> int j = 0,
> loops = 0;
>
> ! /* int numInserts, numDeletes, */
> int sleep_secs;
> Dllist *db_list;
> Dlelem *db_elem,
> --- 961,967 ----
> int j = 0,
> loops = 0;
>
> ! /* int numInserts, numDeletes, */
> int sleep_secs;
> Dllist *db_list;
> Dlelem *db_elem,
> ***************
> *** 1055,1061 ****
>
> if (0 == xid_wraparound_check(dbs));
> {
> ! res = send_query(query_table_stats(dbs), dbs); /* Get an updated
> * snapshot of this dbs
> * table stats */
> for (j = 0; j < PQntuples(res); j++)
> --- 1052,1058 ----
>
> if (0 == xid_wraparound_check(dbs));
> {
> ! res = send_query(TABLE_STATS_QUERY, dbs); /* Get an updated
> * snapshot of this dbs
> * table stats */
> for (j = 0; j < PQntuples(res); j++)
> ***************
> *** 1087,1093 ****
> */
> if ((tbl->curr_vacuum_count - tbl->CountAtLastVacuum) >= tbl->vacuum_threshold)
> {
> ! snprintf(buf, sizeof(buf), "VACUUM ANALYZE \"%s\"", tbl->table_name);
> if (args->debug >= 1)
> {
> sprintf(logbuffer, "Performing: %s", buf);
> --- 1084,1094 ----
> */
> if ((tbl->curr_vacuum_count - tbl->CountAtLastVacuum) >= tbl->vacuum_threshold)
> {
> ! /* if relisshared = t and database != template1 then only do an analyze */
> ! if((tbl->relisshared > 0) && (strcmp("template1",dbs->dbname)))
> ! snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name);
> ! else
> ! snprintf(buf, sizeof(buf), "VACUUM ANALYZE %s", tbl->table_name);
> if (args->debug >= 1)
> {
> sprintf(logbuffer, "Performing: %s", buf);
> ***************
> *** 1101,1107 ****
> }
> else if ((tbl->curr_analyze_count - tbl->CountAtLastAnalyze) >=
tbl->analyze_threshold)
> {
> ! snprintf(buf, sizeof(buf), "ANALYZE \"%s\"", tbl->table_name);
> if (args->debug >= 1)
> {
> sprintf(logbuffer, "Performing: %s", buf);
> --- 1102,1108 ----
> }
> else if ((tbl->curr_analyze_count - tbl->CountAtLastAnalyze) >= tbl->analyze_threshold)
> {
> ! snprintf(buf, sizeof(buf), "ANALYZE %s", tbl->table_name);
> if (args->debug >= 1)
> {
> sprintf(logbuffer, "Performing: %s", buf);
> Index: pg_autovacuum.h
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/contrib/pg_autovacuum/pg_autovacuum.h,v
> retrieving revision 1.6
> diff -c -b -r1.6 pg_autovacuum.h
> *** pg_autovacuum.h 7 Aug 2003 21:11:57 -0000 1.6
> --- pg_autovacuum.h 12 Sep 2003 22:45:37 -0000
> ***************
> *** 34,41 ****
> #define VACUUM_ANALYZE 0
> #define ANALYZE_ONLY 1
>
> ! #define TABLE_STATS_ALL "select
a.relfilenode,a.relname,a.relnamespace,a.relpages,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_delfrom
pg_classa, pg_stat_all_tables b where a.relfilenode=b.relid"
> ! #define TABLE_STATS_USER "select
a.relfilenode,a.relname,a.relnamespace,a.relpages,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_delfrom
pg_classa, pg_stat_user_tables b where a.relfilenode=b.relid"
> #define FRONTEND
> #define PAGES_QUERY "select relfilenode,reltuples,relpages from pg_class where relfilenode=%i"
> #define FROZENOID_QUERY "select oid,age(datfrozenxid) from pg_database where datname = 'template1'"
> --- 34,41 ----
> #define VACUUM_ANALYZE 0
> #define ANALYZE_ONLY 1
>
> ! #define TABLE_STATS_QUERY "select
a.relfilenode,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del
frompg_class a, pg_stat_all_tables b where a.relfilenode=b.relid and a.relkind = 'r'"
> !
> #define FRONTEND
> #define PAGES_QUERY "select relfilenode,reltuples,relpages from pg_class where relfilenode=%i"
> #define FROZENOID_QUERY "select oid,age(datfrozenxid) from pg_database where datname = 'template1'"
> ***************
> *** 86,91 ****
> --- 86,92 ----
> *table_name;
> int relfilenode,
> reltuples,
> + relisshared,
> relpages;
> long analyze_threshold,
> vacuum_threshold;
> ***************
> *** 132,138 ****
> static PGconn *db_connect(db_info * dbi);
> static void db_disconnect(db_info * dbi);
> static PGresult *send_query(const char *query, db_info * dbi);
> - static char *query_table_stats(db_info * dbi);
>
> /* Other Generally needed Functions */
> static void daemonize(void);
> --- 133,138 ----
>
> --
> let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
> <http://dev6.int.libertyrms.com/>
> Christopher Browne
> (416) 646 3304 x124 (land)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073