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);
        }
Example #2
0
        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);
        }