Zabbix 的 MySQL 优化

标签: zabbix mysql 性能优化 高可用 监控 | 发表时间:2014-11-15 17:18 | 作者:yexiaobai
出处:http://segmentfault.com/blogs

为 Zabbix 优化 MySQL

标签(空格分隔): Zabbix MySQL Optimizing 优化


该文作者是 Aurimas Mikalauskas,原文是 Optimizing MySQL for Zabbix

Zabbix 和 MySQL

在大型的 Zabbix 环境中,遇到的挑战大部分是 MySQL 以及更具体的说是 MySQL 磁盘 IO。

考虑到这一点,我将提出一些优化,这将有助于你的 MySQL 最好的使用磁盘(必然的将帮助你的 Zabbix 最好的利用 MySQL)和可用的硬件资源。

SSD 是一个转折

“MySQL 在 SSD 上是否会运行的更好?”我已经一次次的在公共场合或私人场合听到这个问题。

我可以毫不怀疑的告诉你,如果 IO 是你当前的瓶颈 - 要么因为一些查询花费了太多时间和直到查询完成(延迟)你看到 diskstat 报告每秒 100-250 次读,要么因为你的请求超过磁盘负载和长时间等待(吞吐量), SSD 将肯定会对你有极大的帮助,而不是一点点

考虑这个:最快的旋转磁盘最高可以每秒做 250 随机 IO 操作(这一点上,它是物理限制),并且单个的查询只会从一个磁盘读取,即使你用 16 块盘做了 RAID10,因此如果你需要读 15,000 数据点来展示一个图形,从磁盘读取这些数据点将花费 60s。

企业级的 SSD 磁盘,使用单个线程可以做 15,000 或者甚至超过每秒 16k 随机读(16k 是一个 InnoDB 块大小),并且当你增加吞吐量,它会表现的更好!因此这意味着在前面示例中的查询仅仅只会花费 1s 而不是 60s,这是一个很大的不同之处。好处是在同样的 SSD 上,相同时间内你可以运行更多的请求,并且总的 IO 操作数量将仅仅增加。而一个旋转盘将不得不在多个请求之间共享 250 个可用的 IO 操作。

SSD 不会打败旋转盘的唯一区域是顺序操作,特别是单个线程的顺序写,如果这是你的工作特点(可能的情况就是你主要在收集数据而几乎从不读它),这时你或许需要考虑其他的策略。

MySQL 配置

除了提升你的磁盘 IO 子系统,其他的方式是降低你的 IO 压力,并且我准备介绍一些 my.cnf 变量来帮助你。

注意,大部分的调优对任何高性能 MySQL 设置是通用的,尽管有些是明确适用于 Zabbix,因为你可以以放宽一些参数为代价获取更大的影响,最糟糕的情况是,丢失收集数据的 1s,从会议期间讨论,对任何人来说没有什么大不了的。

  • innodb_buffer_pool_size - 如果你有一个专属的 MySQL 服务器,尽可能设置的越高越好(上学是整个可用内存的 75% 左右),否则,你将同服务器上的其他进程平衡它。但是如果它仅仅是 zabbix 服务器,我依然建议设置的比较高,接近总的内存的 75%。
  • innodb_buffer_pool_instances - 在 MySQL 5.5, 设置它为 4, 在 MySQL 5.6 – 设置它为 8 或者甚至是 16。
  • innodb_flush_log_at_trx_commit = 0 - 这是折中的显著改善写入吞吐量的方案,特别是你没有一个非易失性高速缓存的磁盘子系统。基本上你引发的是在 MySQL 或是服务器 crash 时的 1s 的写损失。很多网站的实际运行它(很多网站依然运行在 MyISAM 上),我十分确定这不是一个 Zabbix 设置问题。
  • innodb_flush_method = O_DIRECT - 如果你是运行在 Linux,设置它。
  • innodb_log_file_size - 你想要这些事务日志(默认是有两份)保持 1 - 2 小时有价值的写入数据。为了做决定,你可能需要看下你的 MySQL 服务器的 Zabbix graphs,但是你也可以从 mysql 命令行运行以下的命令:

        mysql> pager grep seq; show engine innodb statusG select     sleep(3600); show engine innodb statusG PAGER set to     'grep seq'
    Log sequence number 8373513970951
    ...
    Log sequence number 8373683996767
    

    这两个数字的不同的之处就是 InnoDB 在上一个小时写入的字节数。因此在以上的服务器,我将设置 innodb_log_file_size=128M 并且以 256M 日志大小空间结束以允许我存储超过 1 个小时有价值的写入数据到事务日志中( See this on changing the log file size if you run MySQL 5.5 or earlier

  • innodb_read_io_threads, innodb_write_io_threads - 不要深思这些,它们不像看起来那么重要,特别是如果你使用异步 IO(你可以通过在 mysql cli 运行 “show global variables like ‘innodb_use_native_aio'” 来检查)。在 MySQL 5.5 和 5.6 你通常想使用异步 IO(AIO),因此检查 mysql log 来明白为什么。如果没有,那就是说,如果你没有使用 AIO 并且不准备使用,仅仅设置这些值为 8 即可。

  • innodb_old_blocks_time = 1000 - 这个可以帮助你防止 由于偶尔的 scans 造成的 buffer pool 污染。这个目前在 MySQL 5.6 中是默认的(在 5.5,需要明确设置)。
  • innodb_io_capacity - 设置这个是为了你的磁盘 IO 子系统能处理更多的写 iops。对于 SSDs,这个应该最少为几千(2000 可能是一个好的开始),然而对于一些旋转磁盘值稍微有点低 - 500-800,依赖于磁盘数量。对于今天的大多数系统,默认的 200 明确是太低的。
  • sync_binlog=0 - 这是默认设置,但以防万一它是大于 0,关闭它,除非你运行的不是 Zabbix。不同步 binary logs 的代价是万一 master 宕机,副本没有同步,但是如果由于 binary log 同步,你不断触及 IO 瓶颈,仅仅因为你想避免每五年一次同步到备机的麻烦,当 master 宕机了,你应该重新考虑这个选项。
  • query_cache_size=0, query_cache_type=0 - 这是禁止查询缓存。大部分时间你不需要查询缓存。并且如果你没有通过这些设置在内核中禁止它,查询(尤其是小的)可能会受到影响,由于查询缓存的互斥竞争。
  • sort_buffer_size, join_buffer_size, read_rnd_buffer_size - 如果你配置了这些变量,取消这些改变(仅仅移除它们或是注释它们)。我发现在大多数客户端服务器,这是失调的前三名变量。然而在大部分情况下,不改变它们是最好的。仅仅让它们保留默认值。
  • tmpdir - 有时候指定 tmpdir 为 /dev/shm 是一个好注意,以至于 on-disk temporary 表实际是写入内存中,但是在 MySQL 5.5 有一个重要的警告:如果你这样做了,它禁用了 AIO acorss the board,因为 tmpfs 不支持 AIO。因此我将监控在当前 tmpdir(/tmp) 的活跃性,并且如果我发现它有问题的时候,切换到 /dev/shm。

MySQL Partitioning

我知道 Zabbix 支持 Partitioning,为了更容易的数据裁剪。尽管如此,我认为这里有一些特别的好处你可以从 partitions 获得。如果你已经通过 date 来 partitions,实际上是 subpartitions。

对于 Zabbix 的 KPI,你可能听了一次又一次,是 “每秒的新值”数,你可以在 Zabbix 状态中发现的。基本上,该值越高,你的 zabbix 的吞吐量越好。并且这就是很多人遇到的 zabbix 的限制 - MySQL 不能在每秒插入足够的新值。

除了我上面已经提到的优化(它们应该大大增加你的写吞吐量),我将鼓励你尝试分片(如果你还没有使用分片)或者是通过 BASH subpartitions,正如我们在 partitioning in some cases can increase the throughput of InnoDB 发现的那样。

我没有特别的在 Zabbix 中测试它,因为它不被 Zabbix 支持。你可以 hack 它使得它能正常工作。但是如果你已经做了所有的变更,依然没有得到足够的 new values per second (并且这不是你的硬件限制)。尝试通过 hash partitioning 或 subpartitioning 表的 key。

On MySQL High Availability

这有使得 MySQL 高可用的选项,虽然很多人认为它不是这样的。我们已经在我们的博客中写了很多,因此我就不重复了,我将给你一个资源参考:

相关 [zabbix mysql 优化] 推荐:

Zabbix 的 MySQL 优化

- - SegmentFault 最新的文章
为 Zabbix 优化 MySQL. 标签(空格分隔): Zabbix MySQL Optimizing 优化. Aurimas Mikalauskas,原文是. Zabbix 和 MySQL. 在大型的 Zabbix 环境中,遇到的挑战大部分是 MySQL 以及更具体的说是 MySQL 磁盘 IO.

mysql监控工具:zabbix+MPM(Performance Monitor for MySQL)

- - CSDN博客数据库推荐文章
MPM主要用于监控mysql的各种参数性能指标,下面简单说一下他与zabbix的配置:. 下面是它的配置文件关系图. 1、zabbix 模板: Template_FromDual.MySQL.*.xml. 2、MPM agent perl 模块: FromDualMySQL*.pm. 下面是在linux 6.4下安装,先安装如下包:.

使用Zabbix监控MySQL服务器方法

- - 行业应用 - ITeye博客
      从Zabbix 2.2开始,Zabbix官方已经支持了MySQL监控,但是MySQL监控默认是不可用的,需要经过额外的设置才可以使用. Kaijia将Zabbix换到了新的服务器时候性能绰绰有余,于是决定充分发挥剩余的内存和SSD性能,把MySQL、Apache、PHP-FPM等的监控也开起来.

mysql优化

- - 数据库 - ITeye博客
公司网站访问量越来越大,MySQL自然成为瓶颈,因此最近我一直在研究 MySQL  的优化,第一步自然想到的是 MySQL 系统参数的优化,作为一个访问量很大的网站(日20万人次以上)的数据库系统,不可能指望 MySQL  默认的系统参数能够让 MySQL运行得非常顺畅. 在Apache, PHP,  MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.

mysql优化

- - 数据库 - ITeye博客
      1.通过 show (session 或者 global) status 来查看( 当前连接 或者 数据库上次开机以来 )的服务器状态信息,默认是session.         例如:show status like '%com_%' : com_XXX表示XXX语句执行的总次数,这总次数是针对所有引擎的总和.

MySQL性能优化

- sun - IT程序员面试网
在笔试面试中,尤其是像百度,淘宝这些数据量非常大,而且用LAMP架构的公司,数据库优化方面就显得特别重要了. 此外,除了数据库索引之外,在LAMP结果如此流行的今天,数据库(尤其是MySQL)性能优化也是海量数据处理的一个热点. 下面就结合自己的经验,聊一聊MySQL数据库优化的几个方面. 首先,在数据库设计的时候,要能够充分的利用索引带来的性能提升,至于如何建立索引,建立什么样的索引,在哪些字段上建立索引,上面已经讲的很清楚了,这里不在赘述.

mysql 引擎优化

- - CSDN博客推荐文章
MySQL数 据库引擎取决于MySQL在安装的时候是如何被编译的. 要添加一个新的引擎,就必须重新编译MYSQL. 在缺省情况下,MYSQL支持三个引擎:ISAM、MYISAM和HEAP. 另外两种类型INNODB和BERKLEY(BDB),也常常可以使用. 如果技术高超,还可以使用MySQL++ API自己做一个引擎.

mysql参数优化

- - CSDN博客推荐文章
### 用来存放InnoDB的内部目录,对于大数据设置16M足够用. ### InnoDB 缓存总大小设置,一般设置为系统内存的70%-80%. ### 指定所有InnoDB数据文件的路径和大小分配. ### 文件读写io数设置:. ### InnoDB内核的并发线程数设置. ### 设置日值的大小.

mysql优化方法

- - 数据库 - ITeye博客
通过show status和应用特点了解各种SQL的执行频率. 通过SHOW STATUS可以提供服务器状态信息,也可以使用mysqladmin extended-status命令获得. SHOW STATUS可以根据需要显示session级别的统计结果和global级别的统计结果. 以下几个参数对Myisam和Innodb存储引擎都计数:.

Mysql性能优化

- - 数据库 - ITeye博客
MySQL性能优化.   性能优化是通过某些有效的方法来提高MySQL的运行速度,减少占用的磁盘空间. 性能优化包含很多方面,例如优化查询速度,优化更新速度和优化MySQL服务器等.   数据库管理人员可以使用SHOW STATUS语句来查询MySQL数据库的性能. 语法:SHOW STATUE LIKE ‘value’;其中value参数是常用的几个统计参数.