public Dictionary <string, List <LogBlockNetLatency_Sec> > GetPerfPointData(string sqliteDBFile)
{
Dictionary <string, List <LogBlockNetLatency_Sec> > dict = new Dictionary <string, List <LogBlockNetLatency_Sec> >();
string dbfile = sqliteDBFile;// @"C:\AGLatency\AGLatency\bin\Debug\SQLiteDB\LocalHarden_Primary_2__2018-07-27_22_06_38.175.SQLiteDB";
SQLiteDB db = new SQLiteDB();
db.Open(dbfile);
String databaseNum = "SELECT DISTINCT target_availability_replica_id FROM hadr_receive_harden_lsn_message";
SQLiteDataReader replicaDr =
db.ExecuteReader(databaseNum);
List <string> replicas = new List <string>();
if (null == replicaDr)
{
return(dict);
}
while (replicaDr.Read())
{
replicas.Add(replicaDr.GetString(0));
}
replicas.Sort();
foreach (string r in replicas)
{
dict.Add(r, new List <LogBlockNetLatency_Sec>());
}
string idx1 = "CREATE INDEX lb ON hadr_receive_harden_lsn_message (log_block_id,target_availability_replica_id )";
db.Execute(idx1);
string idx2 = "CREATE INDEX lb2 ON hadr_send_harden_lsn_message (log_block_id)";
db.Execute(idx2);
//record which replica to caculate. Only one is valid since we just allow two replicas
string replicaId = NetworkLatency.replicaId;
//Update time delta for each log blocks
//warning:i found that there could be two hadr_receive_harden_lsn_message events fired for same log block , all mode=1. maybe due to resend from replica?
string update = @"UPDATE hadr_receive_harden_lsn_message set TimeDelta=
(SELECT hadr_receive_harden_lsn_message.EventTimeStamp - hadr_send_harden_lsn_message.EventTimeStamp
FROM hadr_send_harden_lsn_message
WHERE hadr_send_harden_lsn_message.log_block_id = hadr_receive_harden_lsn_message.log_block_id AND hadr_receive_harden_lsn_message.target_availability_replica_id='" + replicaId + "')"
+ "WHERE EXISTS (SELECT * FROM hadr_send_harden_lsn_message WHERE hadr_send_harden_lsn_message.log_block_id = hadr_receive_harden_lsn_message.log_block_id)";
db.Execute(update);
string select = @"
SELECT (EventTimeStamp/10000000) as EventTimeStamp, target_availability_replica_id,COUNT(*) as LogBlocks, AVG(TimeDelta) as Avg_latency,SUM(TimeDelta) as Sum_latency, max(TimeDelta) as Max_latency, min(TimeDelta) as Min_latency
FROM hadr_receive_harden_lsn_message
WHERE TimeDelta is not null AND target_availability_replica_id='" + replicaId + "'"
+ "GROUP BY EventTimeStamp/10000000,target_availability_replica_id ORDER BY EventTimeStamp / 10000000,target_availability_replica_id";
SQLiteDataReader dr =
db.ExecuteReader(select);
if (dr == null)
{
return(dict);
}
bool isFirst = true;
Int64 firstTimeStamp = 0;
while (dr.Read())
{
LogBlockNetLatency_Sec pfp = new LogBlockNetLatency_Sec();
Int64 EventTimeStamp = dr.GetInt64(0) + 1;//Add one more second , say, 1.220 should be map to 2.00
pfp.EventTimeStamp = new DateTime(EventTimeStamp * 10000000);
pfp.availability_replica_id = dr.GetString(1);
pfp.LogBlocks = dr.GetInt64(2);
pfp.Avg_Latency = Math.Max(0, (Int64)(dr.GetFloat(3) / 10000));
pfp.Sum_Latency = Math.Max(0, dr.GetInt64(4) / 10000);
pfp.Max_Latency = Math.Max(0, dr.GetInt64(5) / 10000);
pfp.Min_Latency = Math.Max(0, dr.GetInt64(6) / 10000);
if (isFirst)
{
firstTimeStamp = EventTimeStamp;
isFirst = false;
}
pfp.secondDistance = EventTimeStamp - firstTimeStamp;
dict[pfp.availability_replica_id].Add(pfp);
}
db.CloseConnection();
return(dict);
}
public Dictionary <string, List <LogBlockNetLatency_Sec> > GetPerfPointData(string sqliteDBFile)
{
Dictionary <string, List <LogBlockNetLatency_Sec> > dict = new Dictionary <string, List <LogBlockNetLatency_Sec> >();
string dbfile = sqliteDBFile;// @"C:\AGLatency\AGLatency\bin\Debug\SQLiteDB\LocalHarden_Primary_2__2018-07-27_22_06_38.175.SQLiteDB";
SQLiteDB db = new SQLiteDB();
db.Open(dbfile);
String databaseNum = "SELECT DISTINCT local_availability_replica_id FROM hadr_transport_receive_log_block_message";
SQLiteDataReader replicaDr =
db.ExecuteReader(databaseNum);
List <string> replicas = new List <string>();
if (replicaDr == null)
{
return(dict);
}
while (replicaDr.Read())
{
replicas.Add(replicaDr.GetString(0));
}
replicas.Sort();
if (replicas.Count > 1)
{
Logger.LogMessage("[WARNING] Two many replicas found! num:" + replicas.Count);
}
//Save this id, since SyncReceiveNetLatency will reference this id to know which replica to use in hadr_receive_harden_lsn_message
replicaId = replicas[0];
foreach (string r in replicas)
{
dict.Add(r, new List <LogBlockNetLatency_Sec>());
}
string idx1 = "CREATE INDEX lb ON hadr_capture_log_block (log_block_id,availability_replica_id )";
db.Execute(idx1);
string idx2 = "CREATE INDEX lb2 ON hadr_transport_receive_log_block_message (log_block_id,local_availability_replica_id )";
db.Execute(idx2);
//Update time delta for each log blocks
string update = @"UPDATE hadr_transport_receive_log_block_message set TimeDelta=
(SELECT hadr_transport_receive_log_block_message.EventTimeStamp - hadr_capture_log_block.EventTimeStamp
FROM hadr_capture_log_block
WHERE hadr_capture_log_block.log_block_id = hadr_transport_receive_log_block_message.log_block_id and hadr_capture_log_block. availability_replica_id=hadr_transport_receive_log_block_message.local_availability_replica_id)
WHERE EXISTS (SELECT * FROM hadr_capture_log_block WHERE hadr_capture_log_block.log_block_id = hadr_transport_receive_log_block_message.log_block_id and hadr_capture_log_block. availability_replica_id=hadr_transport_receive_log_block_message.local_availability_replica_id)";
db.Execute(update);
string select = @"
SELECT (EventTimeStamp/10000000) as EventTimeStamp,local_availability_replica_id, COUNT(*) as LogBlocks, AVG(TimeDelta) as Avg_latency,SUM(TimeDelta) as Sum_latency, max(TimeDelta) as Max_latency, min(TimeDelta) as Min_latency
FROM hadr_transport_receive_log_block_message
WHERE TimeDelta is not null
GROUP BY EventTimeStamp/10000000,local_availability_replica_id ORDER BY EventTimeStamp / 10000000,local_availability_replica_id";
SQLiteDataReader dr =
db.ExecuteReader(select);
if (dr == null)
{
return(dict);
}
bool isFirst = true;
Int64 firstTimeStamp = 0;
while (dr.Read())
{
LogBlockNetLatency_Sec pfp = new LogBlockNetLatency_Sec();
Int64 EventTimeStamp = dr.GetInt64(0) + 1;//Add one more second , say, 1.220 should be map to 2.00
pfp.EventTimeStamp = new DateTime(EventTimeStamp * 10000000);
pfp.availability_replica_id = dr.GetString(1);
pfp.LogBlocks = dr.GetInt64(2);
pfp.Avg_Latency = Math.Max(0, (Int64)(dr.GetFloat(3) / 10000));
pfp.Sum_Latency = Math.Max(0, dr.GetInt64(4) / 10000);
pfp.Max_Latency = Math.Max(0, dr.GetInt64(5) / 10000);
pfp.Min_Latency = Math.Max(0, dr.GetInt64(6) / 10000);
if (isFirst)
{
firstTimeStamp = EventTimeStamp;
isFirst = false;
}
pfp.secondDistance = EventTimeStamp - firstTimeStamp;
dict[pfp.availability_replica_id].Add(pfp);
}
db.CloseConnection();
return(dict);
}