private static void GetLatestStatisticsJob(object obj)
{
MultiQueryPostgresContext context = new MultiQueryPostgresContext();
context.Query = "select * from pg_stat_user_tables;select * from pg_stat_user_indexes;select * from pg_stat_user_functions; select * from pg_stat_get_snapshot_timestamp(); select * from pg_stat_reset();";
new MultiQueryPostgresCommand(context).Execute();
DatabasesUsage data = new DatabasesUsage();
string strData = redisDB.StringGet("IndexSuggestionsPOC.DatabasesUsage");
if (!String.IsNullOrEmpty(strData))
{
data = JsonConvert.DeserializeObject <DatabasesUsage>(strData);
}
data.GeneratedDate = context.Records[3].First().pg_stat_get_snapshot_timestamp;
DateTime dayToUse = data.GeneratedDate.Date;
if (!data.DayGenerationExecutions.ContainsKey(dayToUse))
{
data.DayGenerationExecutions.Add(dayToUse, 0);
}
data.DayGenerationExecutions[dayToUse] += 1;
foreach (var tableData in context.Records[0])
{
string dbName = tableData.schemaname;
string relName = tableData.relname;
long seqScansCount = tableData.seq_scan;
long idxScansCount = tableData.idx_scan;
if (!data.Databases.ContainsKey(dbName))
{
data.Databases.Add(dbName, new DatabaseUsage());
}
var database = data.Databases[dbName];
if (!database.Tables.ContainsKey(relName))
{
database.Tables.Add(relName, new TableUsage());
}
var table = database.Tables[relName];
if (idxScansCount > 0)
{
if (!table.IdxScans.ContainsKey(dayToUse))
{
table.IdxScans.Add(dayToUse, 0);
}
table.IdxScans[dayToUse] += tableData.idx_scan;
}
if (seqScansCount > 0)
{
if (!table.SeqScans.ContainsKey(dayToUse))
{
table.SeqScans.Add(dayToUse, 0);
}
table.SeqScans[dayToUse] += tableData.seq_scan;
}
}
foreach (var indexData in context.Records[1])
{
string dbName = indexData.schemaname;
string relName = indexData.relname;
string idxName = indexData.indexrelname;
long scansCount = indexData.idx_scan;
if (!data.Databases.ContainsKey(dbName))
{
data.Databases.Add(dbName, new DatabaseUsage());
}
var database = data.Databases[dbName];
if (!database.Tables.ContainsKey(relName))
{
database.Tables.Add(relName, new TableUsage());
}
if (scansCount > 0)
{
var table = database.Tables[relName];
if (!table.Indices.ContainsKey(idxName))
{
table.Indices.Add(idxName, new IndexUsage());
}
var index = table.Indices[idxName];
if (!index.IdxScans.ContainsKey(dayToUse))
{
index.IdxScans.Add(dayToUse, 0);
}
index.IdxScans[dayToUse] += scansCount;
}
}
foreach (var procedureData in context.Records[2])
{
string dbName = procedureData.schemaname;
string procName = procedureData.funcname;
long calls = procedureData.calls;
if (calls > 0)
{
if (!data.Databases.ContainsKey(dbName))
{
data.Databases.Add(dbName, new DatabaseUsage());
}
var database = data.Databases[dbName];
if (!database.Procedures.ContainsKey(procName))
{
database.Procedures.Add(procName, new ProcedureUsage());
}
var procedure = database.Procedures[procName];
if (!procedure.Calls.ContainsKey(dayToUse))
{
procedure.Calls.Add(dayToUse, 0);
}
procedure.Calls[dayToUse] += calls;
}
}
strData = JsonConvert.SerializeObject(data, Formatting.Indented);
redisDB.StringSet("IndexSuggestionsPOC.DatabasesUsage", strData);
PrintNotUsed(data);
}
private static void PrintNotUsed(DatabasesUsage dbUsage)
{
string dbName = "test";
TimeSpan notUsedThreshold = TimeSpan.FromDays(1);
DateTime atLeastAccessDay = DateTime.Now.Date.Subtract(notUsedThreshold);
ISet <string> allTables = new HashSet <string>();
QueryPostgresContext postgresContext = new QueryPostgresContext();
postgresContext.Query = String.Format("SELECT * FROM pg_catalog.pg_tables where schemaname = '{0}'", dbName);
new QueryPostgresCommand(postgresContext).Execute();
foreach (var item in postgresContext.Records)
{
allTables.Add(item.tablename);
}
var db = dbUsage.Databases[dbName];
foreach (var tableName in allTables)
{
if (!db.Tables.ContainsKey(tableName))
{
if (dbUsage.DayGenerationExecutions.FirstOrDefault().Key <= atLeastAccessDay)
{
Console.WriteLine("Not used table: {0} since: {1}", tableName, dbUsage.DayGenerationExecutions.FirstOrDefault().Key);
}
}
else
{
var table = db.Tables[tableName];
SortedList <DateTime, long> setToUse = table.IdxScans;
if (table.IdxScans.LastOrDefault().Key == default(DateTime))
{
setToUse = table.SeqScans;
}
DateTime lastUsedDay = table.IdxScans.LastOrDefault().Key == default(DateTime) ? dbUsage.DayGenerationExecutions.FirstOrDefault().Key : table.IdxScans.LastOrDefault().Key;
if (lastUsedDay <= atLeastAccessDay)
{
Console.WriteLine("Not used table: {0} since: {1}", tableName, lastUsedDay);
}
ISet <string> allIndices = new HashSet <string>();
postgresContext = new QueryPostgresContext();
postgresContext.Query = String.Format("SELECT * FROM pg_catalog.pg_indexes where schemaname = '{0}' and tablename = '{1}'", dbName, tableName);
new QueryPostgresCommand(postgresContext).Execute();
foreach (var item in postgresContext.Records)
{
allIndices.Add(item.indexname);
}
foreach (var indexName in allIndices)
{
if (!table.Indices.ContainsKey(indexName))
{
if (dbUsage.DayGenerationExecutions.FirstOrDefault().Key <= atLeastAccessDay)
{
Console.WriteLine("Not used index: {0} since: {1}", indexName, dbUsage.DayGenerationExecutions.FirstOrDefault().Key);
}
}
else
{
var index = table.Indices[indexName];
lastUsedDay = index.IdxScans.LastOrDefault().Key == default(DateTime) ? dbUsage.DayGenerationExecutions.FirstOrDefault().Key : index.IdxScans.LastOrDefault().Key;
if (lastUsedDay <= atLeastAccessDay)
{
Console.WriteLine("Not used index: {0} since: {1}", indexName, lastUsedDay);
}
}
}
}
}
ISet <string> allProcedures = new HashSet <String>();
postgresContext = new QueryPostgresContext();
postgresContext.Query = String.Format("SELECT n.nspname, p.proname FROM pg_catalog.pg_proc p INNER JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = '{0}'", dbName);
new QueryPostgresCommand(postgresContext).Execute();
foreach (var item in postgresContext.Records)
{
allProcedures.Add(item.proname);
}
foreach (var procName in allProcedures)
{
if (!db.Procedures.ContainsKey(procName))
{
if (dbUsage.DayGenerationExecutions.FirstOrDefault().Key <= atLeastAccessDay)
{
Console.WriteLine("Not used proc: {0} since: {1}", procName, dbUsage.DayGenerationExecutions.FirstOrDefault().Key);
}
}
else
{
var proc = db.Procedures[procName];
DateTime lastUsedDay = proc.Calls.LastOrDefault().Key == default(DateTime) ? dbUsage.DayGenerationExecutions.FirstOrDefault().Key : proc.Calls.LastOrDefault().Key;
if (lastUsedDay <= atLeastAccessDay)
{
Console.WriteLine("Not used proc: {0} since: {1}", procName, lastUsedDay);
}
}
}
}
