Ejemplo n.º 1
0
def check_qps(host,port,user,passwd,db="",charset="utf8"):
    """统计各MySQL实例的QPS
    """
    db = DB(host,port,user,passwd,db,charset,cursorclass="")
    en = Encrypt()
    db.execute("SELECT ID,INET_NTOA(IP) AS IPADDR,PORT,USER,PASSWD FROM T_INSTANCE")
    rows = db.fetchall()
    for row in rows:
        db.execute("""SELECT UPTIME,QUESTIONS FROM T_QPS WHERE INSTID = {0}
            ORDER BY ID DESC LIMIT 1""".format(row.get('ID')))
        insertrow = db.fetchone()
        if insertrow == None:
            insertrow = dict()
        tmpdb = DB(row.get('IPADDR'),row.get('PORT'),row.get('USER'),en.decrypt(row.get('PASSWD')))
        tmpdb.execute("show global status like 'Uptime'")
        uprow = tmpdb.fetchone()
        tmpdb.execute("show global status like 'Questions'")
        qurow = tmpdb.fetchone()
        db.execute("""INSERT INTO T_QPS(INSTID,UPTIME,QUESTIONS,DIFFUPTIME,DIFFQUESTIONS)
            VALUES({0},{1},{2},{3},{4})""".format(
            row.get('ID'), uprow[1], qurow[1],
            int(uprow[1])-int(insertrow.get('UPTIME',0)),
            int(qurow[1])-int(insertrow.get('QUESTIONS', 0))
        ))
        db.commit()
Ejemplo n.º 2
0
def main():
    cg = parser_config()
    en = Encrypt()
    monitordb = DB(cg.get("host"), int(cg.get("port")), cg.get("user"), en.decrypt(cg.get("passwd")), cg.get("db"))
    monitordb.execute("SELECT ID,INET_NTOA(IP) AS IPADDR,PORT,USER,PASSWD FROM T_INSTANCE")
    # check_qps(cg.get("host"), int(cg.get("port")), cg.get("user"), en.decrypt(cg.get("passwd")), cg.get("db"))
    insts = monitordb.fetchall()
    for inst in insts:
        # rows = check_slow('10.1.2.9',3306,'root','password')
        rows = check_slow(inst[1], inst[2], inst[3], en.decrypt(inst[4]))
        rtc = store_slow(rows, inst[0], monitordb)
        if rtc:
            truncate_slow(inst[1], inst[2], inst[3], en.decrypt(inst[4]))
Ejemplo n.º 3
0
def main():
    en = Encrypt()
    cg = parser_config()
    db = DB(cg.get("host"), int(cg.get("port")), cg.get("user"), en.decrypt(cg.get("passwd")), cg.get("db"))
    SQL = "SELECT ID,INET_NTOA(IP),PORT,USER,PASSWD FROM T_INSTANCE"
    db.execute(SQL)
    rows = db.fetchall()
    for row in rows:
        tmpdb = DB(row[1],row[2],row[3],en.decrypt(row[4]))
        tmpdb.execute("SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST")
        cnt = tmpdb.fetchone()
        db.execute("""INSERT INTO T_CONNECTION(INSTANCE,CNT)
            VALUES({0}, {1})""".format(row[0], cnt[0]))
        db.commit()
Ejemplo n.º 4
0
def main():
    # 建议读取外部配置文件以获取监控服务器配置
    cg = parser_config()
    en = Encrypt()
    mon_db = DB(cg.get("host"), int(cg.get("port")), cg.get("user"), en.decrypt(cg.get("passwd")), cg.get("db"))
    mon_db.execute("SELECT ID,INET_NTOA(IP),PORT,USER,PASSWD FROM T_INSTANCE WHERE INSTTYPE='SLAVE'")
    rows = mon_db.fetchall()
    for row in rows:
        replicat_status = check(row[1],row[2],row[3],en.decrypt(row[4]))
        if replicat_status:
            mon_db.execute("""INSERT INTO T_REPLICAT(INSTANCE,IOTHREAD,SQLTHREAD,BEHIND)
                VALUES('{0}','{1}','{2}',{3})""".format(row[0],replicat_status.get('SLAVE_IO_RUNNING'),
                replicat_status.get('SLAVE_SQL_RUNNING'),replicat_status.get('SECONDS_BEHIND_MASTER')))
    mon_db.commit()
Ejemplo n.º 5
0
def check_hitrate(host, port, user, passwd, db="", charset="utf8"):
    """
    """
    db = DB(host, port, user, passwd, db, charset, cursorclass="")
    en = Encrypt()
    db.execute("SELECT ID,INET_NTOA(IP) AS IPADDR,PORT,USER,PASSWD FROM T_INSTANCE")
    rows = db.fetchall()
    sql = "INSERT INTO T_HITRATE VALUES(NULL,'%s','%s','%s')"
    for row in rows:
        tmpdb = DB(row.get("IPADDR"), row.get("PORT"), row.get("USER"), en.decrypt(row.get("PASSWD")))
        tmpdb.execute("show global status like 'Qcache_hits'")
        hits = tmpdb.fetchone()
        tmpdb.execute("show global status like 'Com_select'")
        selects = tmpdb.fetchone()
        try:
            hit_rate = int(hits[1]) / int(hits[1] + selects[1])
        except ZeroDivisionError:
            hit_rate = 0
        # 00.0%
        db.execute(sql % (row.get("ID"), datetime.datetime.now(), int(hit_rate)))
        db.commit()
Ejemplo n.º 6
0
def main():
    cg = parser_config()
    en = Encrypt()
    check_qps(cg.get("host"), int(cg.get("port")), cg.get("user"), en.decrypt(cg.get("passwd")), cg.get("db"))
Ejemplo n.º 7
0
        data_collect = ""
        while True:
            data = s.recv(size)
            if not data: break
            data_collect += data
        json_collect = json.loads(data_collect)
        for row in json_collect:
            try:
                insert_row = [ i[1] for i in sorted(row.items())]
                insert_row.append(inst[0])
                db.executemany("""INSERT INTO
                    T_SLOW(ONDB,LOCKTIME,QUERYTIME,ROWS_EXAMINED,ROWS_SENT,SQL_TEXT,STARTTIME,STATE,USER_HOST,OBJID,INSTID)
                    VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
                    insert_row)
            except MySQLdb.IntegrityError as e:
                print 'row:{0} occur error:{1}'.format(tuple(insert_row[0:]),e)
            state = db.commit()
            if not state:
                print 'UP ID:{0}'.format(insert_row[9])
                tmps = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
                tmps.connect((inst[1],inst[3]))
                tmps.send('UP ID:{0}'.format(insert_row[9]))
                data = tmps.recv(size)
                tmps.close()
        s.close()

if __name__ == "__main__":
    cg = parser_config()
    en = Encrypt()
    check_slowsql(cg.get("host"), int(cg.get("port")), cg.get("user"), en.decrypt(cg.get("passwd")), cg.get("db"))