🌐 AI搜索 & 代理 主页
Skip to content

Commit 9e93669

Browse files
tglsfdctglsfdc
committed
Fix free space map to correctly track the total amount of FSM space needed
even when a single relation requires more than max_fsm_pages pages. Also, make VACUUM emit a warning in this case, since it likely means that VACUUM FULL or other drastic corrective measure is needed. Per reports from Jeff Frost and others of unexpected changes in the claimed max_fsm_pages need.
1 parent b0d64a0 commit 9e93669

File tree

10 files changed

+199
-113
lines changed

10 files changed

+199
-113
lines changed

‎contrib/pg_freespacemap/README.pg_freespacemap‎

Lines changed: 41 additions & 41 deletions
Original file line numberDiff line numberDiff line change
@@ -36,19 +36,19 @@ Notes
3636

3737
pg_freespacemap_relations
3838

39-
Column | references | Description
40-
----------------+----------------------+------------------------------------
41-
reltablespace | pg_tablespace.oid | Tablespace oid of the relation.
42-
reldatabase | pg_database.oid | Database oid of the relation.
43-
relfilenode | pg_class.relfilenode | Relfilenode of the relation.
44-
avgrequest | | Moving average of free space
45-
| | requests (NULL for indexes)
46-
lastpagecount | | Count of pages last reported as
47-
| | containing useful free space.
48-
storedpages | | Count of pages actually stored
49-
| | in free space map.
50-
nextpage | | Page index (from 0) to start next
51-
| | search at.
39+
Column | references | Description
40+
------------------+----------------------+----------------------------------
41+
reltablespace | pg_tablespace.oid | Tablespace oid of the relation.
42+
reldatabase | pg_database.oid | Database oid of the relation.
43+
relfilenode | pg_class.relfilenode | Relfilenode of the relation.
44+
avgrequest | | Moving average of free space
45+
| | requests (NULL for indexes)
46+
interestingpages | | Count of pages last reported as
47+
| | containing useful free space.
48+
storedpages | | Count of pages actually stored
49+
| | in free space map.
50+
nextpage | | Page index (from 0) to start next
51+
| | search at.
5252

5353

5454
pg_freespacemap_pages
@@ -65,11 +65,11 @@ Notes
6565

6666
For pg_freespacemap_relations, there is one row for each relation in the free
6767
space map. storedpages is the number of pages actually stored in the map,
68-
while lastpagecount is the number of pages VACUUM last tried to store
69-
(ie, the number that VACUUM thought had useful amounts of free space).
68+
while interestingpages is the number of pages the last VACUUM thought had
69+
useful amounts of free space.
7070

71-
If storedpages is consistently less than lastpagecount then it'd be a good
72-
idea to increase max_fsm_pages. Also, if the number of rows in
71+
If storedpages is consistently less than interestingpages then it'd be a
72+
good idea to increase max_fsm_pages. Also, if the number of rows in
7373
pg_freespacemap_relations is close to max_fsm_relations, then you should
7474
consider increasing max_fsm_relations.
7575

@@ -96,36 +96,36 @@ Sample output - pg_freespacemap_relations
9696

9797
regression=# \d pg_freespacemap_relations
9898
View "public.pg_freespacemap_relations"
99-
Column | Type | Modifiers
100-
---------------+---------+-----------
101-
reltablespace | oid |
102-
reldatabase | oid |
103-
relfilenode | oid |
104-
avgrequest | integer |
105-
lastpagecount | integer |
106-
storedpages | integer |
107-
nextpage | integer |
99+
Column | Type | Modifiers
100+
------------------+---------+-----------
101+
reltablespace | oid |
102+
reldatabase | oid |
103+
relfilenode | oid |
104+
avgrequest | integer |
105+
interestingpages | integer |
106+
storedpages | integer |
107+
nextpage | integer |
108108
View definition:
109-
SELECT p.reltablespace, p.reldatabase, p.relfilenode, p.avgrequest, p.lastpagecount, p.storedpages, p.nextpage
110-
FROM pg_freespacemap_relations() p(reltablespace oid, reldatabase oid, relfilenode oid, avgrequest integer, lastpagecount integer, storedpages integer, nextpage integer);
109+
SELECT p.reltablespace, p.reldatabase, p.relfilenode, p.avgrequest, p.interestingpages, p.storedpages, p.nextpage
110+
FROM pg_freespacemap_relations() p(reltablespace oid, reldatabase oid, relfilenode oid, avgrequest integer, interestingpages integer, storedpages integer, nextpage integer);
111111

112-
regression=# SELECT c.relname, r.avgrequest, r.lastpagecount, r.storedpages
112+
regression=# SELECT c.relname, r.avgrequest, r.interestingpages, r.storedpages
113113
FROM pg_freespacemap_relations r INNER JOIN pg_class c
114114
ON c.relfilenode = r.relfilenode INNER JOIN pg_database d
115115
ON r.reldatabase = d.oid AND (d.datname = current_database())
116116
ORDER BY r.storedpages DESC LIMIT 10;
117-
relname | avgrequest | lastpagecount | storedpages
118-
---------------------------------+------------+---------------+-------------
119-
onek | 256 | 109 | 109
120-
pg_attribute | 167 | 93 | 93
121-
pg_class | 191 | 49 | 49
122-
pg_attribute_relid_attnam_index | | 48 | 48
123-
onek2 | 256 | 37 | 37
124-
pg_depend | 95 | 26 | 26
125-
pg_type | 199 | 16 | 16
126-
pg_rewrite | 1011 | 13 | 13
127-
pg_class_relname_nsp_index | | 10 | 10
128-
pg_proc | 302 | 8 | 8
117+
relname | avgrequest | interestingpages | storedpages
118+
---------------------------------+------------+------------------+-------------
119+
onek | 256 | 109 | 109
120+
pg_attribute | 167 | 93 | 93
121+
pg_class | 191 | 49 | 49
122+
pg_attribute_relid_attnam_index | | 48 | 48
123+
onek2 | 256 | 37 | 37
124+
pg_depend | 95 | 26 | 26
125+
pg_type | 199 | 16 | 16
126+
pg_rewrite | 1011 | 13 | 13
127+
pg_class_relname_nsp_index | | 10 | 10
128+
pg_proc | 302 | 8 | 8
129129
(10 rows)
130130

131131

‎contrib/pg_freespacemap/pg_freespacemap.c‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* pg_freespacemap.c
44
* display some contents of the free space relation and page maps.
55
*
6-
* $PostgreSQL: pgsql/contrib/pg_freespacemap/pg_freespacemap.c,v 1.6 2006/05/30 22:12:13 tgl Exp $
6+
* $PostgreSQL: pgsql/contrib/pg_freespacemap/pg_freespacemap.c,v 1.7 2006/09/21 20:31:21 tgl Exp $
77
*-------------------------------------------------------------------------
88
*/
99
#include "postgres.h"
@@ -53,7 +53,7 @@ typedef struct
5353
Oid reldatabase;
5454
Oid relfilenode;
5555
Size avgrequest;
56-
int lastpagecount;
56+
BlockNumber interestingpages;
5757
int storedpages;
5858
int nextpage;
5959
bool isindex;
@@ -303,7 +303,7 @@ pg_freespacemap_relations(PG_FUNCTION_ARGS)
303303
OIDOID, -1, 0);
304304
TupleDescInitEntry(tupledesc, (AttrNumber) 4, "avgrequest",
305305
INT4OID, -1, 0);
306-
TupleDescInitEntry(tupledesc, (AttrNumber) 5, "lastpagecount",
306+
TupleDescInitEntry(tupledesc, (AttrNumber) 5, "interestingpages",
307307
INT4OID, -1, 0);
308308
TupleDescInitEntry(tupledesc, (AttrNumber) 6, "storedpages",
309309
INT4OID, -1, 0);
@@ -334,7 +334,7 @@ pg_freespacemap_relations(PG_FUNCTION_ARGS)
334334
fctx->record[i].reldatabase = fsmrel->key.dbNode;
335335
fctx->record[i].relfilenode = fsmrel->key.relNode;
336336
fctx->record[i].avgrequest = (int64)fsmrel->avgRequest;
337-
fctx->record[i].lastpagecount = fsmrel->lastPageCount;
337+
fctx->record[i].interestingpages = fsmrel->interestingPages;
338338
fctx->record[i].storedpages = fsmrel->storedPages;
339339
fctx->record[i].nextpage = fsmrel->nextPage;
340340
fctx->record[i].isindex = fsmrel->isIndex;
@@ -380,7 +380,7 @@ pg_freespacemap_relations(PG_FUNCTION_ARGS)
380380
values[3] = UInt32GetDatum(record->avgrequest);
381381
nulls[3] = false;
382382
}
383-
values[4] = Int32GetDatum(record->lastpagecount);
383+
values[4] = Int32GetDatum(record->interestingpages);
384384
nulls[4] = false;
385385
values[5] = Int32GetDatum(record->storedpages);
386386
nulls[5] = false;

‎contrib/pg_freespacemap/pg_freespacemap.sql.in‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -30,7 +30,7 @@ CREATE VIEW pg_freespacemap_relations AS
3030
reldatabase oid,
3131
relfilenode oid,
3232
avgrequest integer,
33-
lastpagecount integer,
33+
interestingpages integer,
3434
storedpages integer,
3535
nextpage integer);
3636

‎src/backend/access/gin/ginvacuum.c‎

Lines changed: 8 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/gin/ginvacuum.c,v 1.5 2006/07/31 20:08:59 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gin/ginvacuum.c,v 1.6 2006/09/21 20:31:21 tgl Exp $
1212
*-------------------------------------------------------------------------
1313
*/
1414

@@ -575,7 +575,8 @@ ginvacuumcleanup(PG_FUNCTION_ARGS) {
575575
bool needLock;
576576
BlockNumber npages,
577577
blkno;
578-
BlockNumber nFreePages,
578+
BlockNumber totFreePages,
579+
nFreePages,
579580
*freePages,
580581
maxFreePages;
581582
BlockNumber lastBlock = GIN_ROOT_BLKNO,
@@ -610,7 +611,7 @@ ginvacuumcleanup(PG_FUNCTION_ARGS) {
610611
if (maxFreePages > MaxFSMPages)
611612
maxFreePages = MaxFSMPages;
612613

613-
nFreePages = 0;
614+
totFreePages = nFreePages = 0;
614615
freePages = (BlockNumber *) palloc(sizeof(BlockNumber) * maxFreePages);
615616

616617
for (blkno = GIN_ROOT_BLKNO + 1; blkno < npages; blkno++) {
@@ -626,6 +627,7 @@ ginvacuumcleanup(PG_FUNCTION_ARGS) {
626627
if ( GinPageIsDeleted(page) ) {
627628
if (nFreePages < maxFreePages)
628629
freePages[nFreePages++] = blkno;
630+
totFreePages++;
629631
} else
630632
lastFilledBlock = blkno;
631633

@@ -638,7 +640,7 @@ ginvacuumcleanup(PG_FUNCTION_ARGS) {
638640
int i;
639641
for (i = 0; i < nFreePages; i++)
640642
if (freePages[i] >= lastFilledBlock) {
641-
nFreePages = i;
643+
totFreePages = nFreePages = i;
642644
break;
643645
}
644646

@@ -648,8 +650,8 @@ ginvacuumcleanup(PG_FUNCTION_ARGS) {
648650
stats->pages_removed = lastBlock - lastFilledBlock;
649651
}
650652

651-
RecordIndexFreeSpace(&index->rd_node, nFreePages, freePages);
652-
stats->pages_free = nFreePages;
653+
RecordIndexFreeSpace(&index->rd_node, totFreePages, nFreePages, freePages);
654+
stats->pages_free = totFreePages;
653655

654656
if (needLock)
655657
LockRelationForExtension(index, ExclusiveLock);

‎src/backend/access/gist/gistvacuum.c‎

Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/gist/gistvacuum.c,v 1.26 2006/07/31 20:08:59 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gist/gistvacuum.c,v 1.27 2006/09/21 20:31:21 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -491,7 +491,8 @@ gistvacuumcleanup(PG_FUNCTION_ARGS)
491491
Relation rel = info->index;
492492
BlockNumber npages,
493493
blkno;
494-
BlockNumber nFreePages,
494+
BlockNumber totFreePages,
495+
nFreePages,
495496
*freePages,
496497
maxFreePages;
497498
BlockNumber lastBlock = GIST_ROOT_BLKNO,
@@ -563,8 +564,9 @@ gistvacuumcleanup(PG_FUNCTION_ARGS)
563564
if (maxFreePages > MaxFSMPages)
564565
maxFreePages = MaxFSMPages;
565566

566-
nFreePages = 0;
567+
totFreePages = nFreePages = 0;
567568
freePages = (BlockNumber *) palloc(sizeof(BlockNumber) * maxFreePages);
569+
568570
for (blkno = GIST_ROOT_BLKNO + 1; blkno < npages; blkno++)
569571
{
570572
Buffer buffer;
@@ -579,10 +581,8 @@ gistvacuumcleanup(PG_FUNCTION_ARGS)
579581
if (PageIsNew(page) || GistPageIsDeleted(page))
580582
{
581583
if (nFreePages < maxFreePages)
582-
{
583-
freePages[nFreePages] = blkno;
584-
nFreePages++;
585-
}
584+
freePages[nFreePages++] = blkno;
585+
totFreePages++;
586586
}
587587
else
588588
lastFilledBlock = blkno;
@@ -597,7 +597,7 @@ gistvacuumcleanup(PG_FUNCTION_ARGS)
597597
for (i = 0; i < nFreePages; i++)
598598
if (freePages[i] >= lastFilledBlock)
599599
{
600-
nFreePages = i;
600+
totFreePages = nFreePages = i;
601601
break;
602602
}
603603

@@ -606,11 +606,11 @@ gistvacuumcleanup(PG_FUNCTION_ARGS)
606606
stats->std.pages_removed = lastBlock - lastFilledBlock;
607607
}
608608

609-
RecordIndexFreeSpace(&rel->rd_node, nFreePages, freePages);
609+
RecordIndexFreeSpace(&rel->rd_node, totFreePages, nFreePages, freePages);
610610
pfree(freePages);
611611

612612
/* return statistics */
613-
stats->std.pages_free = nFreePages;
613+
stats->std.pages_free = totFreePages;
614614
if (needLock)
615615
LockRelationForExtension(rel, ExclusiveLock);
616616
stats->std.num_pages = RelationGetNumberOfBlocks(rel);

‎src/backend/access/nbtree/nbtree.c‎

Lines changed: 11 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
* Portions Copyright (c) 1994, Regents of the University of California
1313
*
1414
* IDENTIFICATION
15-
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtree.c,v 1.150 2006/08/24 01:18:34 tgl Exp $
15+
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtree.c,v 1.151 2006/09/21 20:31:22 tgl Exp $
1616
*
1717
*-------------------------------------------------------------------------
1818
*/
@@ -53,8 +53,9 @@ typedef struct
5353
void *callback_state;
5454
BTCycleId cycleid;
5555
BlockNumber *freePages;
56-
int nFreePages;
57-
int maxFreePages;
56+
int nFreePages; /* number of entries in freePages[] */
57+
int maxFreePages; /* allocated size of freePages[] */
58+
BlockNumber totFreePages; /* true total # of free pages */
5859
MemoryContext pagedelcontext;
5960
} BTVacState;
6061

@@ -636,6 +637,7 @@ btvacuumscan(IndexVacuumInfo *info, IndexBulkDeleteResult *stats,
636637
vstate.freePages = NULL; /* temporarily */
637638
vstate.nFreePages = 0;
638639
vstate.maxFreePages = 0;
640+
vstate.totFreePages = 0;
639641

640642
/* Create a temporary memory context to run _bt_pagedel in */
641643
vstate.pagedelcontext = AllocSetContextCreate(CurrentMemoryContext,
@@ -716,6 +718,7 @@ btvacuumscan(IndexVacuumInfo *info, IndexBulkDeleteResult *stats,
716718
new_pages--;
717719
stats->pages_deleted--;
718720
vstate.nFreePages--;
721+
vstate.totFreePages = vstate.nFreePages; /* can't be more */
719722
}
720723
if (new_pages != num_pages)
721724
{
@@ -736,15 +739,16 @@ btvacuumscan(IndexVacuumInfo *info, IndexBulkDeleteResult *stats,
736739
* pages in the index, discarding any old info the map may have. We do not
737740
* need to sort the page numbers; they're in order already.
738741
*/
739-
RecordIndexFreeSpace(&rel->rd_node, vstate.nFreePages, vstate.freePages);
742+
RecordIndexFreeSpace(&rel->rd_node, vstate.totFreePages,
743+
vstate.nFreePages, vstate.freePages);
740744

741745
pfree(vstate.freePages);
742746

743747
MemoryContextDelete(vstate.pagedelcontext);
744748

745749
/* update statistics */
746750
stats->num_pages = num_pages;
747-
stats->pages_free = vstate.nFreePages;
751+
stats->pages_free = vstate.totFreePages;
748752
}
749753

750754
/*
@@ -816,6 +820,7 @@ btvacuumpage(BTVacState *vstate, BlockNumber blkno, BlockNumber orig_blkno)
816820
/* Okay to recycle this page */
817821
if (vstate->nFreePages < vstate->maxFreePages)
818822
vstate->freePages[vstate->nFreePages++] = blkno;
823+
vstate->totFreePages++;
819824
stats->pages_deleted++;
820825
}
821826
else if (P_ISDELETED(opaque))
@@ -954,6 +959,7 @@ btvacuumpage(BTVacState *vstate, BlockNumber blkno, BlockNumber orig_blkno)
954959
{
955960
if (vstate->nFreePages < vstate->maxFreePages)
956961
vstate->freePages[vstate->nFreePages++] = blkno;
962+
vstate->totFreePages++;
957963
}
958964

959965
MemoryContextSwitchTo(oldcontext);

‎src/backend/commands/vacuum.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
*
1414
*
1515
* IDENTIFICATION
16-
* $PostgreSQL: pgsql/src/backend/commands/vacuum.c,v 1.339 2006/09/17 22:16:22 tgl Exp $
16+
* $PostgreSQL: pgsql/src/backend/commands/vacuum.c,v 1.340 2006/09/21 20:31:22 tgl Exp $
1717
*
1818
*-------------------------------------------------------------------------
1919
*/
@@ -3314,7 +3314,7 @@ vac_update_fsm(Relation onerel, VacPageList fraged_pages,
33143314
}
33153315
}
33163316

3317-
RecordRelationFreeSpace(&onerel->rd_node, outPages, pageSpaces);
3317+
RecordRelationFreeSpace(&onerel->rd_node, outPages, outPages, pageSpaces);
33183318

33193319
pfree(pageSpaces);
33203320
}

0 commit comments

Comments
 (0)