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); }