高性能MySQL：一个诊断案例（3）

一个诊断案例（ ）

我们看到了两种可能性 要么是数据库导致了I/O（如果能找到源头的话 那么可能就找到了问题的原因） 要么不是数据库导致了所有的I/O 而是其他什么导致的 而系统因为缺少I/O 资源影响了数据库性能 我们也很小心地尽力避免引入另外一个隐式的假设 磁盘很忙并不一定意味着MySQL 会有问题 要记住 这个服务器主要的压力是内存读取 所以也很可能出现磁盘长时间无法响应但没有造成严重问题的现象

如果你一直跟随我们的推理逻辑 就可以发现还需要回头检查一下另外一个假设 我们已经知道磁盘设备很忙 因为其等待时间很高 对于固态硬盘来说 其I/O 平均等待时间一般不会超过 / 秒 实际上 从iostat 的输出结果也可以发现磁盘本身的响应还是很快的 但请求在块设备队列中等待很长的时间才能进入到磁盘设备 但要记住 这只是iostat 的输出结果 也可能是错误的信息

究竟是什么导致了性能低下？

当一个资源变得效率低下时 应该了解一下为什么会这样 有如下可能的原因

资源被过度使用 余量已经不足以正常工作

资源没有被正确配置

资源已经损坏或者失灵

回到上面的例子中 iostat 的输出显示可能是磁盘的工作负载太大 也可能是配置不正确（在磁盘响应很快的情况下 为什么I/O 请求需要排队这么长时间才能进入到磁盘？） 然而 比较系统的需求和现有容量对于确定问题在哪里是很重要的一部分 大量的基准测试证明这个客户使用的这种SSD 是无法支撑几百MB/s 的写 *** 作的 所以 尽管iostat 的结果表明磁盘的响应是正常的 也不一定是完全正确的 在这个案例中 我们没有办法证明磁盘的响应比iostat 的结果中所说的要慢 但这种情况还是有可能的 所以这不能改变我们的看法 可能是磁盘被滥用注 或者是错误的配置 或者两者兼而有之 是性能低下的罪魁祸首

在检查过所有诊断数据之后 接下来的任务就很明显了 测量出什么导致了I/O 消耗 不幸的是 客户当前使用的GNU/Linux 版本对此的支持不力 通过一些工作我们可以做一些相对准确的猜测 但首先还是需要探索一下其他的可能性 我们可以测量有多少I/O来自MySQL 但客户使用的MySQL 版本较低以致缺乏一些诊断功能 所以也无法提供确切有利的支持

作为替代 基于我们已经知道MySQL 如何使用磁盘 我们来观察MySQL 的I/O 情况 通常来说 MySQL 只会写数据 日志 排序文件和临时表到磁盘 从前面的状态计数器和其他信息来看 首先可以排除数据和日志的写入问题 那么 只能假设MySQL 突然写入大量数据到临时表或者排序文件 如何来观察这种情况呢？有两个简单的方法 一是观察磁盘的可用空间 二是通过lsof 命令观察服务器打开的文件句柄 这两个方法我们都采用了 结果也足以满足我们的需求 下面是问题期间每秒运行df–h 的结果

下面则是lsof 的数据 因为某些原因我们每五秒才收集一次 我们简单地将mysqld 在/tmp 中打开的文件大小做了加总 并且把总大小和采样时的时间戳一起输出到结果文件中

$ awk

/mysqld *tmp/ {

total += $

}

/^Sun Mar / &&total {

printf %s % f MB\n $ total/ /

total =

} lsof txt

: : MB

: : MB

: : MB

: : MB

: : MB

从这个数据可以看出 在问题之初MySQL 大约写了 GB 的数据到临时表 这和之前在SHOW PROCESSLIST 中有大量的 Copying to tmp table 相吻合 这个证据表明可能是某些效率低下的查询风暴耗尽了磁盘资源 根据我们的工作直觉 出现这种情况比较普遍的一个原因是缓存失效 当memcached 中所有缓存的条目同时失效 而又有很多应用需要同时访问的时候 就会出现这种情况 我们给开发人员出示了部分采样到的查询 并讨论这些查询的作用 实际情况是 缓存同时失效就是罪魁祸首（这验证了我们的直觉） 一方面开发人员在应用层面解决缓存失效的问题 另一方面我们也修改了查询 避免使用磁盘临时表 这两个方法的任何一个都可以解决问题 当然最好是两个都实施

       返回目录 高性能MySQL

       编辑推荐

       ASP NET开发培训视频教程

数据仓库与数据挖掘培训视频教程

lishixinzhi/Article/program/MySQL/201311/29695

捕获诊断数据（ ）

堆栈需要自下而上来看 也就是说 线程当前正在执行的是pthread_cond_wait 函数 这是由os_event_wait_low 调用的 继续往下 看起来是线程试图进入到InnoDB 内核（srv_conc_enter_innodb） 但被放入了一个内部队列中（os_event_wait_low） 原因应该是内核中的线程数已经超过innodb_thread_concurrency 的限制 当然 要真正地发挥堆栈跟踪的价值需要将很多的信息聚合在一起来看 这种技术是由Domas Mituzas推广的 他以前是MySQL 的支持工程师 开发了著名的穷人剖析器 poor man sprofiler 他目前在Facebook 工作 和其他人一起开发了更多的收集和分析堆栈跟踪的工具 可以从他的这个网站发现更多的信息 // poormansprofiler

在Percona Toolkit 中我们也开发了一个类似的穷人剖析器 叫做pt pmp 这是一个用shell 和awk 脚本编写的工具 可以将类似的堆栈跟踪输出合并到一起 然后通过sort|uniq|sort 将最常见的条目在最前面输出 下面是一个堆栈跟踪的完整例子 通过此工具将重要的信息展示了出来 使用了 l 选项指定了堆栈跟踪不超过 层 以免因太多前面部分相同而后面部分不同的跟踪信息而导致无法聚合到一起的情况 这样才能更好地显示到底在哪里产生了等待

$ pt pmp l stacktraces txt

pthread_cond_wait one_thread_per_connection_end handle_one_connection

start_thread clone

pthread_cond_wait os_event_wait_low srv_conc_enter_innodb

innodb_srv_conc_enter_innodb ha_innodb::index_read

pthread_cond_wait os_event_wait_low sync_array_wait_event mutex_spin_wait

mutex_enter_func

pthread_cond_wait os_event_wait_low os_aio_simulated_handle fil_aio_wait

io_handler_thread

pthread_cond_wait os_event_wait_low srv_conc_enter_innodb

innodb_srv_conc_enter_innodb ha_innodb::general_fetch

pthread_cond_wait os_event_wait_low sync_array_wait_event rw_lock_s_lock_spin

rw_lock_s_lock_func

sigwait signal_hand start_thread clone ??

select os_thread_sleep srv_lock_timeout_and_monitor_thread start_thread clone

select os_thread_sleep srv_error_monitor_thread start_thread clone

select handle_connections_sockets main

read vio_read_buff ::?? my_net_read cli_safe_read

pthread_cond_wait os_event_wait_low sync_array_wait_event rw_lock_x_lock_low

rw_lock_x_lock_func

pthread_cond_wait MYSQL_BIN_LOG::wait_for_update mysql_binlog_send

dispatch_mand do_mand

fsync os_file_fsync os_file_flush fil_flush log_write_up_to

第一行是MySQL 中非常典型的空闲线程的一种特征 所以可以忽略 第二行才是最有意思的地方 看起来大量的线程正在准备进入到InnoDB 内核中 但都被阻塞了 从第三行则可以看到许多线程都在等待某些互斥锁 但具体的是什么锁不清楚 因为堆栈跟踪更深的层次被截断了 如果需要确切地知道是什么互斥锁 则需要使用更大的 l 选项重跑一次 一般来说 这个堆栈跟踪显示很多线程都在等待进入到InnoDB 这是为什么呢？这个工具并不清楚 需要从其他的地方来入手

从前面的堆栈跟踪和oprofile 报表来看 如果不是MySQL 和InnoDB 源码方面的专家 这种类型的分析很难进行 如果用户在进行此类分析时碰到问题 通常需要求助于这样的专家才行

在下面的例子中 通过剖析和等待分析都无法发现服务器的问题 需要使用另外一种不同的诊断技术

       返回目录 高性能MySQL

       编辑推荐

       ASP NET开发培训视频教程

数据仓库与数据挖掘培训视频教程

lishixinzhi/Article/program/MySQL/201311/29698

---