您的位置:金沙游乐场85155 > 大数据库 > 一次磁盘IO过高分析过程

一次磁盘IO过高分析过程

发布时间:2019-12-06 00:53编辑:大数据库浏览(65)

    4、观察binlog 与 general log 发现 文件增长量不大,怀疑不是有Insert 与 update 与 delete 导致的 写IO过高

     

      left join (

     

      `自定义 SQL 查询`.`实还金额` AS `实还金额`,

     

    set global general_log = on ;

    完整原型:(主要看下面例子)

         Id: 337153

    INSERT INTO RPT_TR_CINEMA_TICKET_TRADE_DETAIL(
    ID,
    TRADE_DATE,
    SCINEMA_CD,
    OPT_TYPE,
    TRADE_CHANL,
    FILM_CD,
    HALL_CD,
    CINEMA_NAME,
    FILM_NAME,
    SHOW_DATE,
    SHOW_TIME,
    TICKET_TYPE,
    TICKET_GROUP,
    BOOKING_ID,
    TICKET_NO,
    SEQ_NO,
    ROW_ID,
    COL_ID,
    PAYMENT_SEQ,
    PAYMENT_TYPE,
    PAYMENT_AMT,
    PAYMENT_NUM,
    TICKET_AMT,
    MEMBCARD_NO,
    COUPON_TYPE,
    SESSION_CODE,
    THROUGH_FLG,
    OPTER_ID,
    OPTER,
    REFUND_REASON_DESC,
    CRT_DATETIME,
    IS_RETURN)
    select
    REPLACE(uuid(),'-',''),
    TRADE_DATE,
    '' , -- 影院内码
    OPT_TYPE,
    TRADE_CHANL,
    FILM_CD,
    HALL_CD,
    '' as CINEMA_NAME, -- 影院名称
    FILM_NAME,
    SHOW_DATE,
    SHOW_TIME,
    TICKET_TYPE,
    TICKET_GROUP,
    BOOKING_ID,
    TICKET_NO,
    SEQ_NO,
    ROW_ID,
    COL_ID,
    PAYMENT_SEQ,
    PAYMENT_TYPE,
    PAYMENT_AMT,
    PAYMENT_NUM,
    TICKET_AMT,
    MEMBCARD_NO,
    COUPON_TYPE,
    SESSION_CODE,
    THROUGH_FLG,
    OPTER_ID,
    OPTER,
    REFUND_REASON_DESC,
    CRT_DATETIME,
    IS_RETURN
    from RPT_TR_CINEMA_TICKET_TRADE_DETAIL
    where SCINEMA_CD='1843'

      group by date(n.SUBMIT_TIME)

    replace into rpt_ci_cinema_seller_shift_dt ( BIZ_DATE,CINEMA_CD,SELLER_CD,LOCATION_CD,SHIFT_DATETIME,TOTAL_EARNING_AMT,TOTAL_CASH,PETTY_CASH,TURN_IN_CASH,TICKET_TOTAL,TICKET_CASH,REFUND_AMT,ISSUE_CARD_TOTAL,ISSUE_CARD_CASH,ISSUE_CARD_CREDIT,ISSUE_CARD_CHEQUE,ISSUE_CARD_ALIPAY,ISSUE_CARD_WECHAT,ISSUE_CARD_DEBT,ADDMONEY_TOTAL,ADDMONEY_CASH,ADDMONEY_CREDIT,ADDMONEY_CHEQUE,ADDMONEY_ALIPAY,ADDMONEY_WECHAT,ADDMONEY_DEBT,ADDMONEY_DONATE,MEMBCARD_TICKET_NUM,CREDIT_INCOME,CHEQUE_INCOME,TICKET_DEBT_INCOME,PREPAY_INCOME,COLLECTION_INCOME,UNIONPAY_INCOME,THIRD_INCOME,ALIPAY_INCOME,WECHAT_INCOME,VOUCHER_AMT,GIFT_SWAP_AMT,CASH_VOUCHER_AMT,REFUND_THIRD_TICKET,SWAP_INTEGRAL,REFUND_INTEGRAL,CONFERENCE_AMT,CINEMA_INCOME ) select '20160729' AS BIZDATE,CINEMA_CD,SELLER_CD,LOCATION_CD,SHIFT_DATETIME,TOTAL_EARNING_AMT,TOTAL_CASH,PETTY_CASH,TURN_IN_CASH,TICKET_TOTAL,TICKET_CASH,REFUND_AMT,ISSUE_CARD_TOTAL,ISSUE_CARD_CASH,ISSUE_CARD_CREDIT,ISSUE_CARD_CHEQUE,ISSUE_CARD_ALIPAY,ISSUE_CARD_WECHAT,ISSUE_CARD_DEBT,ADDMONEY_TOTAL,ADDMONEY_CASH,ADDMONEY_CREDIT,ADDMONEY_CHEQUE,ADDMONEY_ALIPAY,ADDMONEY_WECHAT,ADDMONEY_DEBT,ADDMONEY_DONATE,MEMBCARD_TICKET_NUM,CREDIT_INCOME,CHEQUE_INCOME,TICKET_DEBT_INCOME,PREPAY_INCOME,COLLECTION_INCOME,UNIONPAY_INCOME,THIRD_INCOME,ALIPAY_INCOME,WECHAT_INCOME,VOUCHER_AMT,GIFT_SWAP_AMT,CASH_VOUCHER_AMT,REFUND_THIRD_TICKET,SWAP_INTEGRAL,REFUND_INTEGRAL,CONFERENCE_AMT,CINEMA_INCOME from rpt_ci_cinema_seller_shift_dt where biz_date='20160725' ;

      ) tt

     

    *************************** 6. row ***************************

     

      select date(payment_date) dt,count(DISTINCT APP_NO) c,sum(totle_repayment) totle_repayment,sum(fact_repayment) fact_repayment ,sum(if(payment_date=fact_payment_date,fact_repayment,0)) very_fact_repayment

    5、show full processlist ;发现有慢SQL

      on a.dt=t.dt

      where date(n.SUBMIT_TIME)>'2017-06-01'

       User: user_car_bill

      and ifnull(cut_chanl,0)<>3

      `自定义 SQL 查询`.`累计发过代扣客户数` AS `累计发过代扣客户数`,

     

      select a.dt 还款日期,a.c 应还客户数,a.totle_repayment 应还金额,a.fact_repayment 实还金额, a.fact_repayment/a.totle_repayment 累计还款收回率, a.very_fact_repayment/a.totle_repayment 还款日还款收回率,t.c 还款日发过代扣客户数,t.c/a.c 还款日客户覆盖率,tt.c 累计发过代扣客户数,tt.c/a.c 累计客户覆盖率

    2、iotop 分析确认io高峰是由mysql导致的

      `自定义 SQL 查询`.`还款日还款收回率` AS `还款日还款收回率`

     

      from fin_back_info n

      group by date(payment_date)

      from fin_repayment m

      `自定义 SQL 查询`.`累计还款收回率` AS `累计还款收回率`,

      left join (

      `自定义 SQL 查询`.`累计客户覆盖率` AS `累计客户覆盖率`,

    图片 1

       Time: 295

    7、执行该SQL,观看监控,产生IO高峰,确认为该SQL导致的IO瓶颈过高

      `自定义 SQL 查询`.`还款日期` AS `还款日期`,

    Command: Query

    3、开启general log,分析SQL

    本文由金沙游乐场85155发布于大数据库,转载请注明出处:一次磁盘IO过高分析过程

    关键词: