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
This repository was archived by the owner on Jul 6, 2021. It is now read-only.

Commit83d7555

Browse files
committed
F004 and F005 Recommendations: provide the full lists of tables/indexes for maintenance
In Recommendations sections of F004 and F005 reports:* provide the full lists of tables/indexes that have high (>40%) level of estimated bloat, to simplify their processing,* the list highly bloated indexes which belong to highly bloated tables is provided separately,* always use fully-qualified names when the schema is not `public`.
2 parents6deae87 +be9e3ca commit83d7555

File tree

8 files changed

+109
-12
lines changed

8 files changed

+109
-12
lines changed

‎pghrep/src/checkup/checkuputil.go

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -320,3 +320,19 @@ func SortItemsByFloat64(data interface{}, field string, reverse bool) []string {
320320
funcGetItemsSortedByNum(datainterface{}) []string {
321321
returnSortItemsByInt(data,"Num",false)
322322
}
323+
324+
// Check if the string exists in the array. If it is so, return its index
325+
funcStringInArray(valstring,array []string) (existsbool,indexint) {
326+
exists=false
327+
index=-1
328+
329+
fori,v:=rangearray {
330+
ifval==v {
331+
index=i
332+
exists=true
333+
return
334+
}
335+
}
336+
337+
return
338+
}

‎pghrep/src/checkup/f004/f004.go

Lines changed: 38 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -13,6 +13,7 @@ import (
1313
constF004_TOTAL_BLOAT_EXCESSstring="F004_TOTAL_BLOAT_EXCESS"
1414
constF004_TOTAL_BLOAT_LOWstring="F004_TOTAL_BLOAT_LOW"
1515
constF004_BLOAT_CRITICALstring="F004_BLOAT_CRITICAL"
16+
constF004_BLOATED_TABLESstring="F004_BLOATED_TABLES"
1617
constF004_BLOAT_WARNINGstring="F004_BLOAT_WARNING"
1718
constF004_BLOAT_INFOstring="F004_BLOAT_INFO"
1819
constF004_GENERAL_INFOstring="F004_GENERAL_INFO"
@@ -35,6 +36,7 @@ func F004Process(report F004Report) checkup.ReportResult {
3536
// check total values
3637
varcriticalTables []string
3738
varwarningTables []string
39+
varbloatedTables []string
3840
totalBloatIsCritical:=false
3941
vartotalDataF004HeapBloatTotal
4042
vardatabaseSizeint64
@@ -56,6 +58,9 @@ func F004Process(report F004Report) checkup.ReportResult {
5658
(heapBloatData.BloatRatioFactor>0) {
5759
criticalTables=appendTable(criticalTables,heapBloatData)
5860
}
61+
if (heapBloatData.RealSizeBytes>MIN_TABLE_SIZE_TO_ANALYZE)&& (heapBloatData.BloatRatioPercent>=WARNING_BLOAT_RATIO) {
62+
bloatedTables=append(bloatedTables,"`"+heapBloatData.TableName+"`")
63+
}
5964
}
6065
}
6166
iftotalBloatIsCritical {
@@ -87,6 +92,9 @@ func F004Process(report F004Report) checkup.ReportResult {
8792
}
8893
result.P2=true
8994
}
95+
iflen(bloatedTables)>0 {
96+
result.AppendRecommendation(F004_BLOATED_TABLES,MSG_BLOAT_WARNING_RECOMMENDATION_TABLES,WARNING_BLOAT_RATIO,strings.Join(bloatedTables,", "))
97+
}
9098
iflen(result.Recommendations)>0 {
9199
result.AppendRecommendation(F004_GENERAL_INFO,MSG_BLOAT_GENERAL_RECOMMENDATION_1)
92100
result.AppendRecommendation(F004_GENERAL_INFO,MSG_BLOAT_GENERAL_RECOMMENDATION_2)
@@ -97,13 +105,41 @@ func F004Process(report F004Report) checkup.ReportResult {
97105
returnresult
98106
}
99107

100-
funcF004PreprocessReportData(datamap[string]interface{}) {
108+
funcF004LoadReportData(filePathstring) (F004Report,error) {
101109
varreportF004Report
102-
filePath:=data["source_path_full"].(string)
103110
jsonRaw:=checkup.LoadRawJsonReport(filePath)
111+
104112
if!checkup.CheckUnmarshalResult(json.Unmarshal(jsonRaw,&report)) {
113+
returnreport,fmt.Errorf("Unable to load F004 report.")
114+
}
115+
116+
returnreport,nil
117+
}
118+
119+
funcF004PreprocessReportData(datamap[string]interface{}) {
120+
varreportF004Report
121+
varerrerror
122+
filePath:=data["source_path_full"].(string)
123+
124+
report,err=F004LoadReportData(filePath)
125+
iferr!=nil {
105126
return
106127
}
128+
107129
result:=F004Process(report)
108130
checkup.SaveReportResult(data,result)
109131
}
132+
133+
funcF004GetBloatedTables(reportF004Report) []string {
134+
varbloatedTables []string
135+
for_,hostData:=rangereport.Results {
136+
sortedTables:=checkup.GetItemsSortedByNum(hostData.Data.HeapBloat)
137+
for_,table:=rangesortedTables {
138+
heapBloatData:=hostData.Data.HeapBloat[table]
139+
if (heapBloatData.RealSizeBytes>MIN_TABLE_SIZE_TO_ANALYZE)&& (heapBloatData.BloatRatioPercent>=WARNING_BLOAT_RATIO) {
140+
bloatedTables=append(bloatedTables,heapBloatData.TableName)
141+
}
142+
}
143+
}
144+
returnbloatedTables
145+
}

‎pghrep/src/checkup/f004/f004messages.go

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,7 @@ const MSG_TOTAL_BLOAT_LOW_CONCLUSION string = "The estimated table (heap) bloat
99
constMSG_BLOAT_CRITICAL_RECOMMENDATIONstring="[P1] Reduce and prevent the high level of table bloat:\n"+
1010
" - to prevent a high level of bloat in the future, tune autovacuum: consider more aggressive autovacuum settings (see F001);\n"+
1111
" - eliminate or reduce the current table bloat using one of the approaches listed below.\n"
12+
constMSG_BLOAT_WARNING_RECOMMENDATION_TABLESstring="The following tables have size > 1 MiB and table bloat estimate > %.2f%%. Use this list to reduce the bloat applying one of the approaches described below. Here are these tables: %s."
1213
constMSG_BLOAT_WARNING_RECOMMENDATIONstring="[P2] Consider the following:\n"+
1314
" - to prevent a high level of bloat in the future, tune autovacuum: consider more aggressive autovacuum settings (see F001);\n"+
1415
" - eliminate or reduce the current table bloat using one of the approaches listed below.\n"

‎pghrep/src/checkup/f005/f005.go

Lines changed: 44 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -3,10 +3,12 @@ package f005
33
import (
44
"encoding/json"
55
"fmt"
6+
"os"
67
"strings"
78

89
checkup".."
910
"../../fmtutils"
11+
"../f004"
1012
"github.com/dustin/go-humanize/english"
1113
)
1214

@@ -18,26 +20,34 @@ const F005_BLOAT_CRITICAL_INFO string = "F005_BLOAT_CRITICAL_INFO"
1820
constF005_BLOAT_INFOstring="F005_BLOAT_INFO"
1921
constF005_GENERAL_INFOstring="F005_GENERAL_INFO"
2022
constF005_BLOAT_EXCESS_INFOstring="F005_BLOAT_EXCESS_INFO"
23+
constF005_BLOATED_INDEXESstring="F005_BLOATED_INDEXES"
24+
constF005_BLOATED_TABLE_INDEXESstring="F005_BLOATED_TABLE_INDEXES"
2125

2226
constWARNING_BLOAT_RATIOfloat32=40.0
2327
constCRITICAL_BLOAT_RATIOfloat32=90.0
2428
constCRITICAL_TOTAL_BLOAT_RATIOfloat32=20.0
2529
constMIN_INDEX_SIZE_TO_ANALYZEint64=1024*1024
2630

2731
funcappendIndex(list []string,indexBloatDataF005IndexBloat) []string {
28-
returnappend(list,fmt.Sprintf(INDEX_DETAILS,indexBloatData.IndexName,
32+
varindexName=indexBloatData.IndexName
33+
ifindexBloatData.SchemaName!="" {
34+
indexName=indexBloatData.SchemaName+"."+indexName
35+
}
36+
returnappend(list,fmt.Sprintf(INDEX_DETAILS,indexName,
2937
fmtutils.ByteFormat(float64(indexBloatData.RealSizeBytes),2),
3038
indexBloatData.BloatRatioFactor,fmtutils.ByteFormat(float64(indexBloatData.ExtraSizeBytes),2),
3139
indexBloatData.BloatRatioPercent))
3240
}
3341

3442
// Generate conclusions and recommendatons
35-
funcF005Process(reportF005Report) checkup.ReportResult {
43+
funcF005Process(reportF005Report,bloatedTables []string) checkup.ReportResult {
3644
varresult checkup.ReportResult
3745
// check total values
3846
vartop5Indexes []string
3947
varcriticalIndexes []string
4048
varwarningIndexes []string
49+
varbloatedIndexes []string
50+
varbloatedTableIndexes []string
4151
totalBloatIsCritical:=false
4252
vartotalDataF005IndexBloatTotal
4353
vardatabaseSizeint64
@@ -53,18 +63,29 @@ func F005Process(report F005Report) checkup.ReportResult {
5363
for_,index:=rangesortedIndexes {
5464
indexBloatData:=hostData.Data.IndexBloat[index]
5565
iftotalBloatIsCritical&&indexBloatData.RealSizeBytes>MIN_INDEX_SIZE_TO_ANALYZE&&i<5&&
56-
(indexBloatData.BloatRatioFactor>0) {
66+
(indexBloatData.BloatRatioFactor>0) {
5767
top5Indexes=appendIndex(top5Indexes,indexBloatData)
5868
i++
5969
}
6070
ifindexBloatData.RealSizeBytes>MIN_INDEX_SIZE_TO_ANALYZE&&indexBloatData.BloatRatioPercent>=CRITICAL_BLOAT_RATIO&&
61-
(indexBloatData.BloatRatioFactor>0) {
71+
(indexBloatData.BloatRatioFactor>0) {
6272
criticalIndexes=appendIndex(criticalIndexes,indexBloatData)
6373
}
6474
if (indexBloatData.RealSizeBytes>MIN_INDEX_SIZE_TO_ANALYZE)&& (indexBloatData.BloatRatioPercent>=WARNING_BLOAT_RATIO)&&
6575
(indexBloatData.BloatRatioPercent<CRITICAL_BLOAT_RATIO)&& (indexBloatData.BloatRatioFactor>0) {
6676
warningIndexes=appendIndex(warningIndexes,indexBloatData)
6777
}
78+
if (indexBloatData.RealSizeBytes>MIN_INDEX_SIZE_TO_ANALYZE)&& (indexBloatData.BloatRatioPercent>=WARNING_BLOAT_RATIO) {
79+
varindexName=indexBloatData.IndexName
80+
ifindexBloatData.SchemaName!="" {
81+
indexName=indexBloatData.SchemaName+"."+indexName
82+
}
83+
ifok,_:=checkup.StringInArray(indexBloatData.TableName,bloatedTables);ok {
84+
bloatedTableIndexes=append(bloatedTableIndexes,"`"+indexName+"`")
85+
}else {
86+
bloatedIndexes=append(bloatedIndexes,"`"+indexName+"`")
87+
}
88+
}
6889
}
6990
}
7091
iftotalBloatIsCritical {
@@ -101,6 +122,14 @@ func F005Process(report F005Report) checkup.ReportResult {
101122
}
102123
result.P2=true
103124
}
125+
iflen(bloatedIndexes)>0 {
126+
result.AppendRecommendation(F005_BLOATED_INDEXES,MSG_BLOAT_WARNING_RECOMMENDATION_INDEXES,WARNING_BLOAT_RATIO,
127+
strings.Join(bloatedIndexes,", "))
128+
}
129+
iflen(bloatedTableIndexes)>0 {
130+
result.AppendRecommendation(F005_BLOATED_TABLE_INDEXES,MSG_BLOAT_WARNING_RECOMMENDATION_TABLE_INDEXES,
131+
WARNING_BLOAT_RATIO,strings.Join(bloatedTableIndexes,", "))
132+
}
104133
iflen(result.Recommendations)>0 {
105134
result.AppendRecommendation(F005_GENERAL_INFO,MSG_BLOAT_GENERAL_RECOMMENDATION_1)
106135
result.AppendRecommendation(F005_GENERAL_INFO,MSG_BLOAT_GENERAL_RECOMMENDATION_2)
@@ -118,6 +147,16 @@ func F005PreprocessReportData(data map[string]interface{}) {
118147
if!checkup.CheckUnmarshalResult(json.Unmarshal(jsonRaw,&report)) {
119148
return
120149
}
121-
result:=F005Process(report)
150+
151+
i:=strings.LastIndex(filePath,string(os.PathSeparator))
152+
path:=filePath[:i+1]
153+
f004FilePath:=path+string(os.PathSeparator)+"F004_heap_bloat.json"
154+
f004Report,err:=f004.F004LoadReportData(f004FilePath)
155+
varbloatedTables []string
156+
iferr==nil {
157+
bloatedTables=f004.F004GetBloatedTables(f004Report)
158+
}
159+
160+
result:=F005Process(report,bloatedTables)
122161
checkup.SaveReportResult(data,result)
123162
}

‎pghrep/src/checkup/f005/f005_test.go

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -56,7 +56,7 @@ func TestF005Success(t *testing.T) {
5656
},
5757
}
5858
report.Results=F005ReportHostsResults{"test-host":hostResult}
59-
result:=F005Process(report)
59+
result:=F005Process(report, []string{})
6060
ifresult.P1||result.P2||result.P3||
6161
!checkup.ResultInList(result.Conclusions,F005_TOTAL_BLOAT_LOW) {
6262
t.Fatal("TestF005Success failed")
@@ -82,7 +82,7 @@ func TestF005TotalExcess(t *testing.T) {
8282
}
8383
hostResult.Data.IndexBloat=map[string]F005IndexBloat{}
8484
report.Results=F005ReportHostsResults{"test-host":hostResult}
85-
result:=F005Process(report)
85+
result:=F005Process(report, []string{})
8686
if!result.P1||
8787
!checkup.ResultInList(result.Conclusions,F005_TOTAL_BLOAT_EXCESS)||
8888
!checkup.ResultInList(result.Recommendations,F005_BLOAT_CRITICAL_INFO) {
@@ -142,7 +142,7 @@ func TestF005Warnig(t *testing.T) {
142142
}
143143

144144
report.Results=F005ReportHostsResults{"test-host":hostResult}
145-
result:=F005Process(report)
145+
result:=F005Process(report, []string{})
146146
if!result.P2||
147147
!checkup.ResultInList(result.Conclusions,F005_BLOAT_WARNING)||
148148
!checkup.ResultInList(result.Recommendations,F005_BLOAT_WARNING) {
@@ -202,7 +202,7 @@ func TestF005Critical(t *testing.T) {
202202
}
203203

204204
report.Results=F005ReportHostsResults{"test-host":hostResult}
205-
result:=F005Process(report)
205+
result:=F005Process(report, []string{})
206206
if!result.P1||
207207
!checkup.ResultInList(result.Conclusions,F005_BLOAT_CRITICAL)||
208208
!checkup.ResultInList(result.Recommendations,F005_BLOAT_CRITICAL_INFO) {

‎pghrep/src/checkup/f005/f005messages.go

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@ const MSG_BLOAT_WARNING_RECOMMENDATION string = "[P2] Consider the following:\n"
1515
" - to prevent a high level of bloat in the future, tune autovacuum: consider more aggressive autovacuum settings (see F001);\n"+
1616
" - eliminate or reduce the current index bloat using one of the approaches listed below.\n"
1717
constMSG_BLOAT_GENERAL_RECOMMENDATION_1string="If you want to get exact bloat numbers, clone the database, get index sizes, then apply "+
18-
"database-wide `VACUUM FULL` (it eliminates all the bloat), andgets new table sizes. Then compare old and new numbers.\n"
18+
"database-wide `VACUUM FULL` (it eliminates all the bloat), andget new table sizes. Then compare old and new numbers.\n"
1919
constMSG_BLOAT_GENERAL_RECOMMENDATION_2string="To reduce the index bloat, consider one of the following approaches:\n"+
2020
" - [`VACUUM FULL`](https://www.postgresql.org/docs/current/sql-vacuum.html) (:warning: requires downtime / maintenance window),\n"+
2121
" - [`REINDEX`](https://www.postgresql.org/docs/current/sql-reindex.html) (`REINDEX INDEX`, `REINDEX TABLE`; :warning: requires downtime / maintenance window),\n"+
@@ -37,3 +37,6 @@ const MSG_BLOAT_WARNING_CONCLUSION_N string = "[P2] There are %d indexes with si
3737
constMSG_BLOAT_CRITICAL_CONCLUSION_Nstring="[P1] The following %d indexes have significant size (>1 MiB) and bloat estimate > %.2f%%:\n%s\n"
3838

3939
constINDEX_DETAILSstring=" - `%s`: size %s, can be reduced %.2f times, by ~%s (~%.2f%%)\n"
40+
41+
constMSG_BLOAT_WARNING_RECOMMENDATION_INDEXESstring="The following indexes have size > 1 MiB and index bloat estimate > %.2f%%. Use this list to reduce the bloat applying one of the approaches described below. Here are these indexes: %s."
42+
constMSG_BLOAT_WARNING_RECOMMENDATION_TABLE_INDEXESstring="And the following indexes also have size > 1 MiB and index bloat estimate > %.2f%%. However, they belong to the highly bloated tables (see F004), so if you plan to process those tables you may not need to use this additional list. Here are these indexes: %s."

‎pghrep/src/checkup/f005/f005struct.go

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,7 @@ type F005IndexBloat struct {
88
Numint`json:"num"`
99
IsNastring`json:"is_na"`
1010
IndexNamestring`json:"index_name"`
11+
SchemaNamestring`json:"schema_name"`
1112
TableNamestring`json:"table_name"`
1213
IndexTableNamestring`json:"index_table_name"`
1314
RealSizeBytesint64`json:"real_size_bytes"`

‎resources/checks/F005_index_bloat.sh

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -124,6 +124,7 @@ with data as (
124124
select
125125
case is_na when true then 'TRUE' else '' end as "is_na",
126126
index_name as "index_name",
127+
coalesce(nullif(step4.schema_name, 'public'), '') as "schema_name",
127128
coalesce(nullif(step4.schema_name, 'public') || '.', '') || step4.table_name as "table_name",
128129
left(index_name, 50) || case when length(index_name) > 50 then '…' else '' end || '(' || coalesce(nullif(step4.schema_name, 'public') || '.', '') || step4.table_name || ')'as "index_table_name",
129130
real_size as "real_size_bytes",

0 commit comments

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