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

Commit9e93669

Browse files
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 VACUUMFULL or other drastic corrective measure is needed. Per reports from JeffFrost and others of unexpected changes in the claimed max_fsm_pages need.
1 parentb0d64a0 commit9e93669

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-
whilelastpagecount is the number of pagesVACUUM lasttried to store
69-
(ie, the number that VACUUM thought haduseful amounts of free space).
68+
whileinterestingpages is the number of pagesthe lastVACUUM thought had
69+
useful amounts of free space.
7070

71-
If storedpages is consistently less thanlastpagecount 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 thaninterestingpages then it'd be a
72+
goodidea 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
Oidreldatabase;
5454
Oidrelfilenode;
5555
Sizeavgrequest;
56-
intlastpagecount;
56+
BlockNumberinterestingpages;
5757
intstoredpages;
5858
intnextpage;
5959
boolisindex;
@@ -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
boolneedLock;
576576
BlockNumbernpages,
577577
blkno;
578-
BlockNumbernFreePages,
578+
BlockNumbertotFreePages,
579+
nFreePages,
579580
*freePages,
580581
maxFreePages;
581582
BlockNumberlastBlock=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
inti;
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
Relationrel=info->index;
492492
BlockNumbernpages,
493493
blkno;
494-
BlockNumbernFreePages,
494+
BlockNumbertotFreePages,
495+
nFreePages,
495496
*freePages,
496497
maxFreePages;
497498
BlockNumberlastBlock=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
Bufferbuffer;
@@ -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
BTCycleIdcycleid;
5555
BlockNumber*freePages;
56-
intnFreePages;
57-
intmaxFreePages;
56+
intnFreePages;/* number of entries in freePages[] */
57+
intmaxFreePages;/* allocated size of freePages[] */
58+
BlockNumbertotFreePages;/* true total # of free pages */
5859
MemoryContextpagedelcontext;
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
elseif (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)
[8]ページ先頭
©2009-2025 Movatter.jp