mysql优化相关

从哪些角度做?

  1. 考虑商业模式,从产品需求上做。能不做的功能尽量别做;能不实时的就别实时;
  2. 能不存的东西就不要存;真的要存的话找对存储的工具(文件,sql,nosql,memory cache); 例如:多媒体,日志log,超大文本。
  3. 能不用到数据库,就别用到数据库,例如在业务层加缓存。例如:配置数据(云图书元信息)、活跃用户个人信息等。
  4. sql语句优化。

    例子:获取某类目下例如四级的所有的课,再获取这门课的购买人数,会涉及两张表的操作。一般获取目录下的所有的课,如果课多,可以加limit N,然后翻页。获取人数时,一种是一个个的id去查这门课的人数,另一种是用in 语句。语句解析是第二种的语句解析和通信消耗约是第一种的1/10,io约为1:1,分组和内存消耗比第一种多;结果集处理1:1,所以从整体的资源来看,第二种比第一种好。但目前我们的程序中大部分还是使用第一种的,一方面是面向对象开发服务端逻辑产生的影响。

    另外一个例子是,获取购买了某个课的全部用户的头像,那直接可以两表联接写出sql,另外也可以先得到用户,再得到头像,后者可以省掉联接相关操作

  5. 配置问题导致的浪费,我们的线上mysql开启了query cache,查是大小设置为0,导致了我们的重复查询都没有缓存,用了好几年。。。

  6. 对于表字段的优化,不容忍数据冗余,导致了一些查询效率降低。冗余有时是好事。

  7. 硬件优化:一块SSD才多少钱,如果数据不大但是访问多并发高,建议上这样的硬件。

  8. OLAP和OLTP作区分。OLAP的特点是数据量大,但并发不高,单次访问数据量大,访问数据集中。OLTP内存在大,CPU需要牛,磁盘的每秒进行读写(I/O)操作的次数需要上得去,而磁盘的吞吐量是其次。

  9. 业务压力起不来,一切优化都是笑话。


目前我工作中的情况

  1. 加了query cache,目前已生效。
  2. 分析了慢查询,发现是feedback表占了很大部分原因,发给运维了。另外一个lsa.user表发出建议不要再使用了。
  3. 调研了HA方案,目前还没有确定用哪一个。运维同事是建议现在这样就好了。现在用的是MMM即Master-Master Replication Manager for MySQL。看上去除非是想专门学习,否则性能上或者可靠性上也不需要动了。
  4. 开始折腾mysql cluster...

一、当前遇到的问题

我司的mysql由运维同事进行管理,开发人员使用。同时也可以自己单独找机器搭,但是自己启的服务不在运维的管理工作中,需要自己去进行管理,所以一般仅用户测试数据库中。 目前存在的问题就是运维同事比较忙,没有时间处理mysql性能调优的事。以至于有的表挂起几天了也没有人处理。 所以调优和监测是目前的主要目标,而HA是次要的。

Sharding

下图是**产品的数据库,分片其实目前还没有到非做不可的地步最大的几个也就几个G,并且应该是可以删除了的。
mysql使用 目前的主从备份从读写效率上来看,还是比集群式的更好一些的。只是疏于管理,现在性能也不是最优,但基本服务没啥问题。

HA

目前大家都急于做产品功能,对于性能和可用性其实都没有关注。

二、Sharding方案

如果要做的话,现在是垂直的切分的。目前用了多个hibernate配置,orz。

2.1 垂直和水平切分的优缺点

(转自这里)

垂直切分的好处:

数据库的拆分简单明了,拆分规则明确;
应用程序模块清晰明确,整合容易;
数据维护方便易行,容易定位

垂直切分的缺点:

部分表关联无法在数据库级别完成,需要在程序中完成;
对于访问极其频繁且数据量超大的表仍然存在性能瓶颈,不一定能满足要求;
事务处理相对更为复杂;
切分达到一定程度之后,扩展性会遇到限制;
过读切分可能会带来系统过渡复杂而难以维护

水平切分的优点:

表关联基本能够在数据库端全部完成;
不会存在某些超大型数据量和高负载的表遇到瓶颈的问题;
应用程序端整体架构改动相对较少;
事务处理相对简单;
只要切分规则能够定义好,基本上较难遇到扩展性限制;

水平切分的缺点:

切分规则相对更为复杂,很难抽象出一个能够满足整个数据库的切分规则;
后期数据的维护难度有所增加,人为手工定位数据更困难;
应用系统各模块耦合度较高,可能会对后面数据的迁移拆分造成一定的困难

联合切分:集合两者优点,但更为复杂。

2.2 sharding选择

  1. Mysql Proxy及其变种,例如使用改良版的Altas
  2. Hibernate Shards

三、HA方案

LVS+Keepalived+MySQL(脑裂的问题,还无法做到准确判断mysqld是否HANG的情况)
DRBD+Heartbeat+MySQL(脑裂的问题,还无法做到准确判断mysqld是否HANG的情况,且DRDB是不需要的,增加反而会出问题)
MySQL Proxy(方半途夭折了,不建议用,无法高可用,是一个写分离)
MySQL Cluster(商用案例确实不多,主要是跟其业务场景要求有关系、这几年发展有点乱不过现在已经上正规了、对网络要求高)
MySQL + MHA或者MySQL + MMM(可以解决脑裂的问题,需要的IP多,小集群是可以的,但是管理大的就麻烦,其次MySQL + MMM 的话且坑很多,有MHA就没必要采用MMM) 
网易DDB(内部不开源,发展缓慢)
360开源的Atlas(一个mysql中间层项目,在MySQL-Proxy 0.8.2版本的基础上,修改了大量bug,添加了很多功能特性,配置简单,且应用不需要修改代码。朱超主力开发)
Percona XTRADB Cluster
  • 以上的结论来源于网络(见参考资料),不是自己做实验得出的结论。

四、可用的解决方案

4.1 网易DDB

主要目标是基于关系数据库集群解决: 1) 海量结构化数据存储 2) 高并发访问 3) 数据扩容

目前2人在开发。主力维护的集群是云计算集群,在杭州。北京没法使用,除非像广告组一样自己搭。

4.2 mysql cluster

  1. 目前在用的公司,除了paypal、NEC、Nokia、加大伯克利外没听过别的。
  2. 适用场景,这个还是比较广的。

    大容量 OLTP;实时分析;支持欺诈检测的电子商务和金融交易;移动和小额支付;会话管理和缓存;串流派送、分析和推荐;内容管理和交付;大型多人在线游戏;通信和位node.js 置服务;使用者/用户资料管理和授权
    

    3.如何确定我们需要使用mysql cluster了?
    如果回答的是“是”比较多,说明可以采用了

    是否需要执行数据库分片来支持不断增加的写入(UPDATE、INSERT、DELETE)操作量?
    是否需要确保 SELECT 操作的结果始终保持一致,无论结果是从哪个节点返回的?
    数据库故障是否会导致应用程序停机从而造成业务中断?
    故障切换期间发生的数据丢失(即便只有几秒钟的数据)是否会导致业务中断?
    用户体验对响应时间敏感吗?
    是否需要跨多个地理区域复制数据库,且每个区域都需要处理读取和写入操作?
    是否运行多样化的应用程序,可从直接访问数据中受益,而不必总是依赖 SQL(即支持 node.js 的JavaScript、memcached API、Java 和 JPA 应用程序、HTTP/REST Web 服务和 C++ 应用程序)?
    应用程序是否主要由并行执行的“短”事务(即每个事务的操作时间为 10 秒,而非 1000 秒)构成?
    主键数据库访问,包含一些 JOIN 操作,而非对整个表执行常规扫描和 JOIN,返回数万行数据?
    

4.3 两者优缺点

4.3.1 劣势

1.1 维护和更新的投入不一样。DDB网易内部非开源。另外做私有平台对于程序员的技术发展也不太好,虽然原理是类似的。另外13年后文档不再更新了,估计就一个稳定版本然后团队做别的事去了,例如邱似峰去做视频中心了。
1.2 DDB某些sql语句不支持:

    多表级联查询时,连接的顺序是按照sql语句中表出现的顺序进行,所以相邻的表间需要有直接或者间接的JOIN条件;
    不支持子查询;
    不支持UNION/INTERSECT/EXCEPT等集合操作;
    不支持insert into ... select 语句; 
    (刚发布的4.8版本支持子查询和集合操作了)
    HAVING 不支持 2个或 2个以上的条件;不支持游标;不支持SQL语句统计功能。
    自增ID不能保证连续性和不能保证严格的自增;

更多的待做(缺失)功能可以看这里

1.3 DDB从版本关联上来看,协议修改可能出现兼容问题,例如4.7与4.5就不兼容。
1.4 DDB已知问题有点多,但可能别的问题更多只是我不知道。
1.5 mysql cluster网络需要万兆,千兆的话QPS上不去。

4.3.2 优势

2.1 公司内很多产品在用(但应用列表中全是06年到12年,13年之后估计文档没有更新过了)。遇到问题了可能还是可以找到答案的。产品团队承诺7*24小时在线运维。
2.2 支持事务。
2.3 DDB只是框架,扩展比较简单。
2.4 已经确定了在北京机房会有云计算平台,关系数据库RDS6月启用,负载均衡服务NLB7月启用,DDB在这两个组件启用之后就能在北京部署并服务了。现在有**人在开发云计算平台,杭研的投入还是很大的。底层有SAS磁盘和SSD磁盘,而主要业务都是SSD的。到时可以在北京使用云计算平台的DDB了。


五、调优计划

5.0 善用工具

Orzdba

我们公司在用,修改/global/share/eadop/orzdba 第160行,写死password(否则会让你不停的输入密码)
/global/share/eadop/orzdba -lazy -innodb -mysql -C 1000 -i 1 运行1000次,间隔1秒打印一次

Percona Toolkit for MySQL

注意点:看INSTALL,最后面有:perl Makefile.PL PREFIX=非linux管理员指定一下这个目录,否则没有写文件的权限。
实验:
分析慢查询:
tail -n 100000 /disk1/dict_project/mysql/data/nc005-slow.log > nc005-slow
./bin/pt-query-digest nc005-slow  > slow_analy
从nc005的线上机上copy了十万条慢查询,然后分析了一下。

分析系统:
 ./bin/pt-summary

 分析mysql:
 ./bin/pt-mysql-summary  --host=nc005x.corp.youdao.com --user=discovery --password=***  --port 3306 > mysql_summary
 这个命令如果出现了:Cannot execute mysqldump.  Check that it is in PATH,说明个人帐号路径中不包含mysql,加一下:export PATH=$PATH:/usr/local/mysql/bin/ 

 分析my.cnf配置:
  ./bin/pt-variable-advisor --user=discovery --password=dictcourse123outfox nc005x.corp.youdao.com

 其他功能:
 检查 master 和 slave 数据的一致性
 有效地对记录进行归档
 查找重复的索引
 对服务器信息进行汇总
 分析来自日志和 tcpdump 的查询
 当系统出问题的时候收集重要的系统信息

5.1 基本设置优化

  1. sql 优化

    只有一个结果时加limit 1
    避免select *
    
  2. 为需要的字段加索引,例如user表的name字段

  3. select @@innodb_buffer_pool_size :10G 正常

    select @@innodb_log_file_size :64M,有点小,频繁写入的一般也512M或者1G

    select @@max_connections :连接数1000,正常

    select @@skipnameresolve 服务器会进行主机名解析,并且当DNS很慢时,建立连接也会很慢。因此建议在启动服务器时关闭skipnameresolve选项而不进行DNS查找。这外局域网没所谓了。

5.2 慢查询

是否开启了慢查询日志: show variables like 'log_slow_queries';

慢查询定义时间:show global variables like 'long_query_time';现在是2秒

查看慢查询所在的文件:show global  variables like 'slow_query_log_file';

从长查询语句来看,反馈系统获取反馈的查询耗时太多了,都在20多秒,数目占了慢查询的70%,时间上占的比例更大。

5.3 query cache优化

现在的情况:

现在的query cache

    查询:show variables like "%query_cache%"
    查询现在是否有打开:select @@query_cache_type
    查询大小:select @@global.query_cache_size,目前是0,这样和没有打开是一样的效果,mysql6.0
    query_cache_limit:如果查询结果大于这个,就不缓存了。单位Byte,默认1M
    查看cache状态:show status where Variable_name like "%Qcache%",可以看到都是0

现在的query cache hit

   修改query\_cache\_size Qcache_hit立即有值了。

现在的query cache hit

查看使用情况:show status like 'Qcache%';
参数作用
Qcache_free_blocksQuery Cache 中目前还有多少剩余的blocks。如果该值显示较大, 则说明Query Cache 中的内存碎片较多了,可能需要寻找合适的机会进行整理()
Qcache_free_memoryQuery Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够,是需要增加还是过多了
Qcache_hits多少次命中。通过这个参数我们可以查看到Query Cache 的基本效果
Qcache_inserts多少次未命中然后插入。通过“Qcache_hits”和“Qcache_inserts”两个参数我们就可以算出Query Cache 的命中率了Query Cache 命中率= Qcache_hits / ( Qcache_hits + Qcache_inserts );
Qcache_lowmem_prunes多少条Query 因为内存不足而被清除出Query Cache。通过 “Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合,能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够,是否非常频繁的出现因为内存不足而有Query 被换出
Qcache_not_cached因为query_cache_type 的设置或者不能被cache 的Query 的数量
Qcache_queries_in_cache当前Query Cache 中cache 的Query 数量
Qcache_total_blocks当前Query Cache 中的block 数量

存放逻辑result set而不是物理数据页,会产生一些限制点:所有子查询的外部查询不会缓存;在procedure、function、trigger中的query不能被cache;每次执行可能得到不一定的结果的query不能被cache

5.4 网络连接优化

 查看状态:show status like 'connection%';

maxconecctions:整个MySQL 允许的最大连接数;这个参数主要影响的是整个MySQL 应用的并发处理能力,当系统中实际需要的连接量大于maxconecctions 的情况下,由于MySQL 的设置限制,那么应用中必然会产生连接请求的等待,从而限制了相应的并发量。所以一般来说,只要MySQL 主机性能允许,都是将该参数设置的尽可能大一点。一般来说500 到800 左右是一个比较合适的参考值。

maxuserconnections:每个用户允许的最大连接数; 上面的参数是限制了整个MySQL 的连接数,而maxuserconnections 则是针对于单个用户的连接限制。在一般情况下我们可能都较少使用这个限制,只有在一些专门提供MySQL 数据存储服务,或者是提供虚拟主机服务的应用中可能需要用到。除了限制的对象区别之外,其他方面和max_connections 一样。这个参数的设置完全依赖于应用程序的连接用户数,对于普通的应用来说,完全没有做太多的限制,可以尽量放开一些。

netbufferlength:网络包传输中,传输消息之前的net buffer 初始化大小; 这个参数主要可能影响的是网络传输的效率,由于该参数所设置的只是消息缓冲区的初始化大小,所以造成的影响主要是当我们的每次消息都很大的时候MySQL 总是需要多次申请扩展该缓冲区大小。系统默认大小为16KB,一般来说可以满足大多数场景,当然如果我们的查询都是非常小,每次网络传输量都很少,而且系统内存又比较紧缺的情况下,也可以适当将该值降低到8KB。

maxallowedpacket:在网络传输中,一次传消息输量的最大值; 这个参数与netbufferlength 相对应,只不过是net buffer 的最大值。当我们的消息传输量 大于netbufferlength 的设置时,MySQL 会自动增大net buffer 的大小,直到缓冲区大小达 到maxallowedpacket 所设置的值。系统默认值为1MB,最大值是1GB,必须设定为1024 的倍 数,单位为字节。 back_log:在MySQL 的连接请求等待队列中允许存放的最大连接请求数。

5.4 线程优化

查看参数设置:show variables like 'thread%';
查看状态:show global  status like 'thread%';

threadcachesize:Thread Cache 池中应该存放的连接线程数

thread_cache_size 的值应该设置的相对大一些,不应该小于应用系统对数据库的实际并发请求
数。在长连接的环境中我们不需要将threadcachesize 参数设置太大,一般来说 可能50 到100 之间应该就可以了。

thread_stack:每个连接线程被创建的时候,MySQL 给他分配的内存大小 使用系统的默认值(192KB)基本上可以所有的普通应用环境。

可以计算出系统新建连接连接的ThreadCache 命中率,也就是通过Thread Cache 池中取得连接线程的次数与系统接收的总连接次数的比率,如下: Threads_Cache_Hit = (Connections - Threads_created) / Connections * 100%

5.5 buffer

查看变量设置:show variables like '%buffer%';

join_buffer_size :当我们的Join 是ALL , index ,rang 或者index_merge 的时候使用的Buffer;

sort_buffer_size:系统中对数据进行排序的时候使用的Buffer;
Sort Buffer 同样是针对单个Thread 的,所以当多个Thread 同时进行排序的时候,系统中就会出现
多个Sort Buffer。一般我们可以通过增大Sort Buffer 的大小来提高ORDER BY 或者是GROUP BY 的处理性能。

一般8M差不多够了,注意这个不是系统中共享的,而是每个线程就会开一个这样的buffer。

5.6 innoDB的buffer

show status like 'Innodb_buffer_pool_%';

read命中率:(Innodbbufferpoolreadrequests-Innodbbufferpoolreads)/Innodbbufferpoolread_requests

存储效率:Innodbbufferpoolpagesdata/'Innodbbufferpoolpagestotal

预读:

Innodb_buffer_pool_read_ahead_rnd:记录进行随机读的时候产生的预读次数;
Innodb_buffer_pool_read_ahead_seq:记录连续读的时候产生的预读次数;

Innodb log buffer

innodb_log_buffer_size:来设置Innodb 的Log Buffer 大小的,系统默认值为1MB。Log Buffer的主要作用就是缓冲Log 数据,提高写Log 的IO 性能。一般来说,如果你的系统不是写负载非常高且以大事务居多的话,8MB 以内的大小就完全足够了。

六、HA使用

以后再mysql cluster~目前硬伤导致性能还不如单机的。硬做就单纯了为了学习而做了。


资料来源

  1. mysql cluster官网
  2. mysql cluster基准测试
  3. 与云阅读、花田开发同事沟通,与DDB运维沟通;与广告组做过mysql优化以及DDB运维的同事沟通;

(注:文中出现的**表示内部信息)

comments powered by Disqus