V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
hellohacker
V2EX  ›  程序员

Mysql Io 读取数据数值上百亿高,求解

  •  
  •   hellohacker · 2014-07-05 10:59:18 +08:00 · 3111 次点击
    这是一个创建于 3789 天前的主题,其中的信息可能已经有所发展或是发生改变。
    网站三天两头的变慢,找了很多原因,但是问题没能解决。网站变慢的时候,服务器变得很卡.

    [show processlis] 命令 会打印出
    +----------+------+-----------------+------+---------+------+--------------------+------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +----------+------+-----------------+------+---------+------+--------------------+------------------+
    | 19021447 | root | 127.0.0.1:28253 | o2o | Query | 17 | removing tmp table | DESC fanwe_youhui_log |
    | 19025616 | root | 127.0.0.1:32578 | NULL | Query | 0 | NULL | show processlist |
    +----------+------+-----------------+------+---------+------+--------------------+------------------+


    分析慢查询日志 N多下面的数据:
    # Time: 140621 14:10:15
    # User@Host: root[root] @ [127.0.0.1]
    # Query_time: 24.078125 Lock_time: 0.000000 Rows_sent: 11 Rows_examined: 11
    SET timestamp=1403331015;
    DESC fanwe_youhui_log;
    # Time: 140621 14:11:51
    # User@Host: root[root] @ [127.0.0.1]
    # Query_time: 14.312500 Lock_time: 0.015625 Rows_sent: 11 Rows_examined: 11
    SET timestamp=1403331111;
    DESC fanwe_youhui_log;
    # Time: 140621 14:12:06
    # User@Host: root[root] @ [127.0.0.1]
    # Query_time: 10.843750 Lock_time: 0.015625 Rows_sent: 11 Rows_examined: 11
    SET timestamp=1403331126;
    DESC fanwe_youhui_log;
    # Time: 140621 14:13:14
    # User@Host: root[root] @ [127.0.0.1]
    # Query_time: 26.656250 Lock_time: 0.000000 Rows_sent: 11 Rows_examined: 11
    SET timestamp=1403331194;
    DESC fanwe_youhui_log;
    # Time: 140621 14:14:32
    # User@Host: root[root] @ [127.0.0.1]
    # Query_time: 10.312500 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
    SET timestamp=1403331272;
    select group_concat(focused_user_id) from fanwe_user_focus where focus_user_id = 33073 order by rand() limit 50;
    # Time: 140621 14:15:10
    # User@Host: root[root] @ [127.0.0.1]
    # Query_time: 21.359375 Lock_time: 1.953125 Rows_sent: 11 Rows_examined: 11
    SET timestamp=1403331310;
    DESC fanwe_youhui_log;
    # Time: 140621 14:16:28
    # User@Host: root[root] @ [127.0.0.1]
    # Query_time: 16.984375 Lock_time: 0.000000 Rows_sent: 11 Rows_examined: 11
    SET timestamp=1403331388;
    DESC fanwe_youhui_log;
    ===========================================
    按道理说 DESC Table 命令应该是很快的命令,今天发现 Mylsq IO数据特别高,对这块我也不了解,想请大家分析下:

    我打开任务管理器,然后查看进程,在I /O 读取 这一列 数值是 43,658,476,259 I/O 写入 数值是:75,652,817
    6 条回复    2014-07-05 19:50:34 +08:00
    fredcc
        1
    fredcc  
       2014-07-05 11:43:04 +08:00
    您磁盘还正常么
    liprais
        2
    liprais  
       2014-07-05 12:27:02 +08:00
    这是磁盘有问题了吧.....
    wdlth
        3
    wdlth  
       2014-07-05 13:17:07 +08:00
    IO次数疯长可能磁盘坏了。
    hellojinjie
        4
    hellojinjie  
       2014-07-05 15:21:55 +08:00 via Android
    你是不是建了超多的数据库?内存够吗,在做swap吗
    hellohacker
        5
    hellohacker  
    OP
       2014-07-05 19:29:23 +08:00
    @hellojinjie
    没有 数据库也就是6、7个的样子
    fredcc
        6
    fredcc  
       2014-07-05 19:50:34 +08:00
    @hellohacker 先看下磁盘健康状态,阵列状态
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3083 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 13:27 · PVG 21:27 · LAX 05:27 · JFK 08:27
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.