Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit87808ae

Browse files
committed
Allow the pgstat views to show toast tables as well as regular tables
(the stats system has always collected this info, but the views werefiltering it out). Modify autovacuum so that over-threshold activityin a toast table can trigger a VACUUM of the parent table, even if theparent didn't appear to need vacuuming itself. Per discussion a monthor so back about "short, wide tables".
1 parent2498d82 commit87808ae

File tree

6 files changed

+107
-44
lines changed

6 files changed

+107
-44
lines changed

‎src/backend/catalog/system_views.sql

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2005, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.19 2005/08/13 19:02:33 tgl Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.20 2005/08/15 16:25:17 tgl Exp $
77
*/
88

99
CREATEVIEWpg_rolesAS
@@ -190,7 +190,7 @@ CREATE VIEW pg_stat_all_tables AS
190190
FROM pg_class CLEFT JOIN
191191
pg_index IONC.oid=I.indrelid
192192
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
193-
WHEREC.relkind='r'
193+
WHEREC.relkindIN ('r','t')
194194
GROUP BYC.oid,N.nspname,C.relname;
195195

196196
CREATEVIEWpg_stat_sys_tablesAS
@@ -223,7 +223,7 @@ CREATE VIEW pg_statio_all_tables AS
223223
pg_class TONC.reltoastrelid=T.oidLEFT JOIN
224224
pg_class XONT.reltoastidxid=X.oid
225225
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
226-
WHEREC.relkind='r'
226+
WHEREC.relkindIN ('r','t')
227227
GROUP BYC.oid,N.nspname,C.relname,T.oid,X.oid;
228228

229229
CREATEVIEWpg_statio_sys_tablesAS
@@ -248,7 +248,7 @@ CREATE VIEW pg_stat_all_indexes AS
248248
pg_index XONC.oid=X.indrelidJOIN
249249
pg_class IONI.oid=X.indexrelid
250250
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
251-
WHEREC.relkind='r';
251+
WHEREC.relkindIN ('r','t');
252252

253253
CREATEVIEWpg_stat_sys_indexesAS
254254
SELECT*FROM pg_stat_all_indexes
@@ -272,7 +272,7 @@ CREATE VIEW pg_statio_all_indexes AS
272272
pg_index XONC.oid=X.indrelidJOIN
273273
pg_class IONI.oid=X.indexrelid
274274
LEFT JOIN pg_namespace NON (N.oid=C.relnamespace)
275-
WHEREC.relkind='r';
275+
WHEREC.relkindIN ('r','t');
276276

277277
CREATEVIEWpg_statio_sys_indexesAS
278278
SELECT*FROM pg_statio_all_indexes

‎src/backend/postmaster/autovacuum.c

Lines changed: 89 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -10,15 +10,15 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/postmaster/autovacuum.c,v 1.3 2005/08/11 21:11:44 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/postmaster/autovacuum.c,v 1.4 2005/08/15 16:25:17 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
1717
#include"postgres.h"
1818

1919
#include<signal.h>
20-
#include<time.h>
2120
#include<sys/types.h>
21+
#include<time.h>
2222
#include<unistd.h>
2323

2424
#include"access/genam.h"
@@ -86,6 +86,7 @@ typedef struct autovac_dbase
8686
typedefstructautovac_table
8787
{
8888
Oidrelid;
89+
Oidtoastrelid;
8990
booldovacuum;
9091
booldoanalyze;
9192
intvacuum_cost_delay;
@@ -101,8 +102,10 @@ static void process_whole_db(void);
101102
staticvoiddo_autovacuum(PgStat_StatDBEntry*dbentry);
102103
staticList*autovac_get_database_list(void);
103104
staticvoidtest_rel_for_autovac(Oidrelid,PgStat_StatTabEntry*tabentry,
104-
Form_pg_classclassForm,Form_pg_autovacuumavForm,
105-
List**vacuum_tables);
105+
Form_pg_classclassForm,
106+
Form_pg_autovacuumavForm,
107+
List**vacuum_tables,
108+
List**toast_table_ids);
106109
staticvoidautovacuum_do_vac_analyze(List*relids,booldovacuum,
107110
booldoanalyze,boolfreeze);
108111

@@ -386,12 +389,21 @@ AutoVacMain(int argc, char *argv[])
386389

387390
if (db)
388391
{
392+
/*
393+
* Report autovac startup to the stats collector. We deliberately
394+
* do this before InitPostgres, so that the last_autovac_time will
395+
* get updated even if the connection attempt fails. This is to
396+
* prevent autovac from getting "stuck" repeatedly selecting an
397+
* unopenable database, rather than making any progress on stuff
398+
* it can connect to.
399+
*/
400+
pgstat_report_autovac(db->oid);
401+
389402
/*
390403
* Connect to the selected database
391404
*/
392405
InitPostgres(db->name,NULL);
393406
SetProcessingMode(NormalProcessing);
394-
pgstat_report_autovac();
395407
set_ps_display(db->name);
396408
ereport(LOG,
397409
(errmsg("autovacuum: processing database \"%s\"",db->name)));
@@ -538,6 +550,7 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
538550
HeapTupletuple;
539551
HeapScanDescrelScan;
540552
List*vacuum_tables=NIL;
553+
List*toast_table_ids=NIL;
541554
ListCell*cell;
542555
PgStat_StatDBEntry*shared;
543556

@@ -558,9 +571,25 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
558571
classRel=heap_open(RelationRelationId,AccessShareLock);
559572
avRel=heap_open(AutovacuumRelationId,AccessShareLock);
560573

574+
/*
575+
* Scan pg_class and determine which tables to vacuum.
576+
*
577+
* The stats subsystem collects stats for toast tables independently
578+
* of the stats for their parent tables. We need to check those stats
579+
* since in cases with short, wide tables there might be proportionally
580+
* much more activity in the toast table than in its parent.
581+
*
582+
* Since we can only issue VACUUM against the parent table, we need to
583+
* transpose a decision to vacuum a toast table into a decision to vacuum
584+
* its parent. There's no point in considering ANALYZE on a toast table,
585+
* either. To support this, we keep a list of OIDs of toast tables that
586+
* need vacuuming alongside the list of regular tables. Regular tables
587+
* will be entered into the table list even if they appear not to need
588+
* vacuuming; we go back and re-mark them after finding all the
589+
* vacuumable toast tables.
590+
*/
561591
relScan=heap_beginscan(classRel,SnapshotNow,0,NULL);
562592

563-
/* Scan pg_class looking for tables to vacuum */
564593
while ((tuple=heap_getnext(relScan,ForwardScanDirection))!=NULL)
565594
{
566595
Form_pg_classclassForm= (Form_pg_class)GETSTRUCT(tuple);
@@ -571,9 +600,9 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
571600
ScanKeyDataentry[1];
572601
Oidrelid;
573602

574-
/*Skip non-table entries. */
575-
/* XXX possibly allow RELKIND_TOASTVALUE entries here too? */
576-
if (classForm->relkind!=RELKIND_RELATION)
603+
/*Consider only regular and toast tables. */
604+
if (classForm->relkind!=RELKIND_RELATION&&
605+
classForm->relkind!=RELKIND_TOASTVALUE)
577606
continue;
578607

579608
/*
@@ -607,7 +636,7 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
607636
HASH_FIND,NULL);
608637

609638
test_rel_for_autovac(relid,tabentry,classForm,avForm,
610-
&vacuum_tables);
639+
&vacuum_tables,&toast_table_ids);
611640

612641
systable_endscan(avScan);
613642
}
@@ -625,6 +654,22 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
625654

626655
CHECK_FOR_INTERRUPTS();
627656

657+
/*
658+
* Check to see if we need to force vacuuming of this table because
659+
* its toast table needs it.
660+
*/
661+
if (OidIsValid(tab->toastrelid)&& !tab->dovacuum&&
662+
list_member_oid(toast_table_ids,tab->toastrelid))
663+
{
664+
tab->dovacuum= true;
665+
elog(DEBUG2,"autovac: VACUUM %u because of TOAST table",
666+
tab->relid);
667+
}
668+
669+
/* Otherwise, ignore table if it needs no work */
670+
if (!tab->dovacuum&& !tab->doanalyze)
671+
continue;
672+
628673
/* Set the vacuum cost parameters for this table */
629674
VacuumCostDelay=tab->vacuum_cost_delay;
630675
VacuumCostLimit=tab->vacuum_cost_limit;
@@ -643,7 +688,7 @@ do_autovacuum(PgStat_StatDBEntry *dbentry)
643688
* test_rel_for_autovac
644689
*
645690
* Check whether a table needs to be vacuumed or analyzed. Add it to the
646-
* output list if so.
691+
*appropriateoutput list if so.
647692
*
648693
* A table needs to be vacuumed if the number of dead tuples exceeds a
649694
* threshold. This threshold is calculated as
@@ -670,7 +715,8 @@ static void
670715
test_rel_for_autovac(Oidrelid,PgStat_StatTabEntry*tabentry,
671716
Form_pg_classclassForm,
672717
Form_pg_autovacuumavForm,
673-
List**vacuum_tables)
718+
List**vacuum_tables,
719+
List**toast_table_ids)
674720
{
675721
Relationrel;
676722
float4reltuples;/* pg_class.reltuples */
@@ -764,12 +810,10 @@ test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
764810
* will be reset too.
765811
*/
766812

767-
elog(DEBUG2,"%s: vac: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)",
813+
elog(DEBUG3,"%s: vac: %.0f (threshold %.0f), anl: %.0f (threshold %.0f)",
768814
RelationGetRelationName(rel),
769815
vactuples,vacthresh,anltuples,anlthresh);
770816

771-
Assert(CurrentMemoryContext==AutovacMemCxt);
772-
773817
/* Determine if this table needs vacuum or analyze. */
774818
dovacuum= (vactuples>vacthresh);
775819
doanalyze= (anltuples>anlthresh);
@@ -778,23 +822,40 @@ test_rel_for_autovac(Oid relid, PgStat_StatTabEntry *tabentry,
778822
if (relid==StatisticRelationId)
779823
doanalyze= false;
780824

781-
if (dovacuum||doanalyze)
825+
Assert(CurrentMemoryContext==AutovacMemCxt);
826+
827+
if (classForm->relkind==RELKIND_RELATION)
782828
{
783-
autovac_table*tab;
829+
if (dovacuum||doanalyze)
830+
elog(DEBUG2,"autovac: will%s%s %s",
831+
(dovacuum ?" VACUUM" :""),
832+
(doanalyze ?" ANALYZE" :""),
833+
RelationGetRelationName(rel));
784834

785-
elog(DEBUG2,"will%s%s %s",
786-
(dovacuum ?" VACUUM" :""),
787-
(doanalyze ?" ANALYZE" :""),
788-
RelationGetRelationName(rel));
835+
/*
836+
* we must record tables that have a toast table, even if we currently
837+
* don't think they need vacuuming.
838+
*/
839+
if (dovacuum||doanalyze||OidIsValid(classForm->reltoastrelid))
840+
{
841+
autovac_table*tab;
789842

790-
tab= (autovac_table*)palloc(sizeof(autovac_table));
791-
tab->relid=relid;
792-
tab->dovacuum=dovacuum;
793-
tab->doanalyze=doanalyze;
794-
tab->vacuum_cost_limit=vac_cost_limit;
795-
tab->vacuum_cost_delay=vac_cost_delay;
843+
tab= (autovac_table*)palloc(sizeof(autovac_table));
844+
tab->relid=relid;
845+
tab->toastrelid=classForm->reltoastrelid;
846+
tab->dovacuum=dovacuum;
847+
tab->doanalyze=doanalyze;
848+
tab->vacuum_cost_limit=vac_cost_limit;
849+
tab->vacuum_cost_delay=vac_cost_delay;
796850

797-
*vacuum_tables=lappend(*vacuum_tables,tab);
851+
*vacuum_tables=lappend(*vacuum_tables,tab);
852+
}
853+
}
854+
else
855+
{
856+
Assert(classForm->relkind==RELKIND_TOASTVALUE);
857+
if (dovacuum)
858+
*toast_table_ids=lappend_oid(*toast_table_ids,relid);
798859
}
799860

800861
RelationClose(rel);

‎src/backend/postmaster/pgstat.c

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
*
1414
*Copyright (c) 2001-2005, PostgreSQL Global Development Group
1515
*
16-
*$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.105 2005/08/11 21:11:44 tgl Exp $
16+
*$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.106 2005/08/15 16:25:17 tgl Exp $
1717
* ----------
1818
*/
1919
#include"postgres.h"
@@ -651,18 +651,20 @@ pgstat_beterm(int pid)
651651
* pgstat_report_autovac() -
652652
*
653653
* Called from autovacuum.c to report startup of an autovacuum process.
654+
*We are called before InitPostgres is done, so can't rely on MyDatabaseId;
655+
*the db OID must be passed in, instead.
654656
* ----------
655657
*/
656658
void
657-
pgstat_report_autovac(void)
659+
pgstat_report_autovac(Oiddboid)
658660
{
659661
PgStat_MsgAutovacStartmsg;
660662

661663
if (pgStatSock<0)
662664
return;
663665

664666
pgstat_setheader(&msg.m_hdr,PGSTAT_MTYPE_AUTOVAC_START);
665-
msg.m_databaseid=MyDatabaseId;
667+
msg.m_databaseid=dboid;
666668
msg.m_start_time=GetCurrentTimestamp();
667669

668670
pgstat_send(&msg,sizeof(msg));

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.298 2005/08/13 19:02:34 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.299 2005/08/15 16:25:18 tgl Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200508131
56+
#defineCATALOG_VERSION_NO200508151
5757

5858
#endif

‎src/include/pgstat.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@
55
*
66
*Copyright (c) 2001-2005, PostgreSQL Global Development Group
77
*
8-
*$PostgreSQL: pgsql/src/include/pgstat.h,v 1.35 2005/08/11 21:11:49 tgl Exp $
8+
*$PostgreSQL: pgsql/src/include/pgstat.h,v 1.36 2005/08/15 16:25:18 tgl Exp $
99
* ----------
1010
*/
1111
#ifndefPGSTAT_H
@@ -384,7 +384,7 @@ extern void pgstat_bestart(void);
384384
externvoidpgstat_ping(void);
385385
externvoidpgstat_report_activity(constchar*what);
386386
externvoidpgstat_report_tabstat(void);
387-
externvoidpgstat_report_autovac(void);
387+
externvoidpgstat_report_autovac(Oiddboid);
388388
externvoidpgstat_report_vacuum(Oidtableoid,boolshared,
389389
boolanalyze,PgStat_Countertuples);
390390
externvoidpgstat_report_analyze(Oidtableoid,boolshared,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp