Frågor för tabellen PGSQLAutovacuumStats

Information om hur du använder dessa frågor i Azure-portalen finns i Log Analytics-självstudien. Information om REST-API:et finns i Fråga.

Uppsvälld förhållande

Sammanfattning av aktuellt uppsvälldhetsförhållande per databas.

// If required to get the snapshot on a certain date and time just filter on TimeGenerated column, like:
// where TimeGenerated between (datetime('2023-03-01 16:00') .. datetime('2023-03-01 19:00'))
PGSQLAutovacuumStats
| extend ts=bin(TimeGenerated,5m)
| summarize arg_max(ts,LiveRowsCount,DeadRowsCount) by DatabaseName, SchemaName
| where DatabaseName !in ('azure_maintenance', 'azure_sys') //exclude system database
| where SchemaName !in ('pg_catalog','information_schema', 'pg_toast') //exclude system schemas
| summarize TotalLiveRows=sum(LiveRowsCount), TotalDeadRows=sum(DeadRowsCount) by DatabaseName
| extend BloatRatio=toreal(TotalDeadRows)/toreal(TotalLiveRows)*100
| extend BloatRatio = iff( isnan(BloatRatio), 0.0, BloatRatio)
| project DatabaseName,TotalLiveRows,TotalDeadRows, BloatRatio
| order by BloatRatio desc 

Vakuumstatistik

Sammanfattning av totalt antal tabeller som har dammsugats per databas.

// If required to get the snapshot on a certain date and time just filter on TimeGenerated column, like:
// where TimeGenerated between (datetime('2023-03-01 16:00') .. datetime('2023-03-01 19:00'))
PGSQLAutovacuumStats
| extend ts=bin(TimeGenerated,5m)
| summarize arg_max(ts,TablesCount,TablesVacuumedCount,TablesAutovacuumedCount) by DatabaseName, SchemaName
| where DatabaseName !in ('azure_maintenance', 'azure_sys') //exclude system database
| where SchemaName !in ('pg_catalog','information_schema', 'pg_toast') //exclude system schemas
| summarize TotalTables=sum(TablesCount), TablesVaccumed=sum(TablesVacuumedCount), TablesAutoVaccumed=sum(TablesAutovacuumedCount) by DatabaseName
| order by TotalTables desc 

Analysera statistik

Sammanfattning av totalt antal tabeller som har analyserats per databas.

// If required to get the snapshot on a certain date and time just filter on TimeGenerated column, like:
// where TimeGenerated between (datetime('2023-03-01 16:00') .. datetime('2023-03-01 19:00'))
PGSQLAutovacuumStats
| extend ts=bin(TimeGenerated,5m)
| summarize arg_max(ts,TablesCount,TablesAnalyzedCount,TablesAutoanalyzedCount) by DatabaseName, SchemaName
| where DatabaseName !in ('azure_maintenance', 'azure_sys') //exclude system database
| where SchemaName !in ('pg_catalog','information_schema', 'pg_toast') //exclude system schemas
| summarize TotalTables=sum(TablesCount), TablesAnalyzed=sum(TablesAnalyzedCount), TablesAutoAnalyzed=sum(TablesAutoanalyzedCount) by DatabaseName
| order by TotalTables desc