谷歌镜像 伍佰目录 短网址
  当前位置:海洋目录网 » 站长资讯 » 站长资讯 » 文章详细 订阅RssFeed

MySQL 18个难点解读:涉及性能问题排查、优化及高可用、安全、迁移等

来源:本站原创 浏览:234次 时间:2021-05-06

1、性能问题排查

Q:MySQL如何排查CPU占用高的问题?

问题描述:重点是关于通过哪些系统表或者常用的sql来确定导致问题的sql?感觉这方面的资料很少,不像Oralce的那些v$视图,网上资料很多,sql语句也很多。

答:可以通过将系统线程号与SQL对应来查看

top -H -p <mysqld进程id>

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

23974 mysql 20 0 1658m 358m 12m R 99.9 1.1 0:05.52 mysqld

12295 mysql 20 0 1658m 358m 12m S 0.3 1.1 0:02.44 mysqld

....


SELECT a.THREAD_OS_ID,b.user,b.host,b.db,b.command,b.time,b.state,b.info

FROM performance_schema.threads a,information_schema.processlist b

WHERE b.id = a.processlist_id;


THREAD_OS_ID USER HOST db command TIME state info

** 23974 ** root 10.10.18.201:21466 sys QUERY 29 Sending DATA SELECT a.* FROM test a,test b,test c ,test d ORDER BY a.value LIMIT 0, 1000

....

Q:MySQL数据库内存使用率高,应该如何进行排查?

问题描述:内存使用率,通过系统命令能定位到mysql占用的内存高,如何通过系统表或者相关的sql语句,定位到占用内存高的那部分sql?

答:Mysql Server Memory Usage= Sum of Global Buffers + (number of Connection * Per thread memory variables)

a)单个mysql连接线程的内存消耗统计,这里只是统计分配值(具体驻留内存占用值统计不到)

select b.thd_id, b.user, current_count_used,current_allocated, current_avg_alloc, current_max_alloc,total_allocated,current_statement from memory_by_thread_by_current_bytes a,session b where a.thread_id = b.thd_id limit 1; 

b)统计top 10的buffer pool占用内存的表

select * from innodb_buffer_stats_by_table order by pages desc limit 10;

Q:MySQL数据库磁盘IO使用高,请问如何进行排查?

问题描述:通过系统能确定是数据库的IO读写高,有哪些系统表或者sql联合起来可以把关键的sql定位出来?

答:mysql5.7版本为例,结合performance_schema来查看MySQL数据库的各种指标。相当于Oracle数据库中的各种性能视图,可以查看几乎所有的数据库状态。

IO的话,可以查看这张表:

performance_schema.file_instances:列出了文件I / O操作及其相关文件的工具实例

排查思路:

1、慢SQL排除

2、硬件问题-RAID降级,磁盘故障等排除

2、innodb_log、innodb_buffer_pool_wait相关配置和等待

3、IO相关参数配置

innodb_flush_method = O_DIRECT

innodb_file_per_table = 1

innodb_doublewrite = 1

delay_key_write

innodb_read_io_threads

innodb_read_io_threads

innodb_io_capacity

innodb_flush_neighbors

sync_binlog

主要关注:sync_binlog

建议:最好部署相关的监控平台或者对比历史性能记录,结合业务以及负载来分析。


2、优化方法

Q:MySQL优化的常用方法有哪些?

答:一、最常见是慢查询优化

1、打开慢查询记录,设置记录SQL的最短时间

2、使用pt工具,分类统计慢查询语句

3、针对执行次数多或者时间长的语句进行优化(索引优化、SQL改写、业务逻辑优化)

ps:也可以在系统表中,查看全表扫描多的表等

二、配置文件优化

1、内存使用量

2、各种方面写盘策略

Q:MySQL中执行计划如何解读?

问题描述:1:执行计划如何解读?

db2中按照从下往上,从左到右的顺序来解读

2:执行计划中需要关注的特殊标识有哪些?

例如:using where

using filesort

Using temporary

等等

答:

1、执行顺序,看ID列

id值相同执行顺序从上到下。

id值不同时id值大的先执行。

2、关注的特殊标识

SELECT_TYPE--执行查询类型,不同类型对应的

Type:访问类型,很重要

possible_keys:索引使用

关于explain输出参数,可参考官方文档:以MySQL5.7为例

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

Q:MySQL中关于表维护的操作(提升性能相关的)有哪些?

问题描述:MySQL中关于表维护的操作(提升性能相关的)有哪些?例如db2中的表重组,db2rbind 绑定包等操作

答:MySQL的表维护语句:

ANALYZE TABLE:更新表统计信息。执行该语句的时候innodb及myisam表会加上读锁,停止数据更新。该语句支持innodb,myisam及ndb表,针对myisam表,该语句等同myisamchk --analyze

OPTIMIZE TABLE:整理数据,表碎片

CHECK TABLE:用来检查数据库表和索引是否损坏

REPAIR TABLE:check table语句可以检查一个表中的的问题,若表或索引损坏,可以使用repair table语句尝试修正它

Q:有哪些工具可以帮助优化MySQL的?

答1:SQL优化主要还是看经验和对慢查询梳理。

配置文件优化,一般来说就几个参数需要优化,其他可以不动

https://github.com/major/MySQLTuner-perl

答2:以下工具可以参考:

pt-mysql-summary

pt-variable-advisor

pt-duplicate-key-checker

pt-deadlock-logger 

或者

tuning-primer.sh


3、高可用问题

Q:MySQL原厂有Oracle的cluster集群,有哪些主流的开源适合高并发集群呢?

答:一、MySQL高可用方案

MySQL以及各种开源数据库,也有自身的集群方案,但是大多需要和业务以及借助第三方工具来实现。或者通过分布式来均衡高并发。主要的高可用集群架构可以分为如下几种:

1、基于共享存储的高可用方案--SAN

基于共享存储的高可用,及使用传统的基于SAN共享存储,结合开源的Keeplive做主从同步,可避免除存储外的组件损坏引起的宕机,部署相对简单,对应用透明,但是存储时单点,且存在性能瓶颈

2、基于磁盘复制的高可用方案-DRBD

保证主备的数据一致性,不依赖共享存储,此方案处理failover的方式上依旧需要借https://www.3tt.net/?mod=artinfo&aid=297助主机层面的高可用组件,如keeplive,Heartbeat等。不同的是,在数据共享方面,采用了基于块级别的数据同步软件DRBD来实现,,但是可扩展性较差。它并不共享存储,而是通过服务器之间的网络复制数据。

适用于数据库访问量不太大,短期内访问量增长不会太快,对数据库可用性要求非常高的场景。

3、基于MySQL自身的主从复制-Replication

基于MySQL自身的主从复制,5.7以后的GTID,以及之前的replication。主从复制,部署简单,但是只能有一个Master进行读写,其余都为备库,还需要结合业务。并发量不大的情况下,可采取主从,管理简单。

4、MHA高可用方案

MHA是一套MySQL高可用管理软件,除了检测Master宕机后,提升候选Slave为New Master之外(漂虚拟IP),还会自动让其他Slave与New Master 建立复制关系。MHA Manager可以单独部署在一台独立的机器上,并管理多个master-slave集群。但是,只支持一主多从架构,集群中必须最少有三台数据库服务器,要保持切换对应用透明,依然依赖于VIP,不适用于大规模集群部署,配置比较复杂。且MHA管理节点本身的HA无法保证。

MySQL 5.7 之前数据不丢的前提是Master服务器还可以被MHA Manager进行SSH连接,通过应用保存的binlog 的方式来保证。

MySQL 5.7 之后通过无损复制, 仅仅是减少了丢数据的可能性,假如此时的状态为切成异步的状态,那就和之前一样了(可以设置超时的时间很大);当Master恢复的时候,最后一部分数据是否需要Flashback,MHA也是不负责这个事情,需要人工介入。

5、基于zookeeper/consul的高可用方案

借助zookeeper组件,结合MHA或者其他高可用架构场景,实现强制一致性的高可用集群分布,可适应大规模高并发场景,需要一定的技术实力,引入zookeeper,架构复杂度上升,但是整体扩展性非常好,可以管理大规模集群。保证了整个系统的高可用,主从的强一致依赖于MySQL本身,比如半同步,或者外围工具的回补策略

6、基于MMM高可用方案

MMM提供了MySQL主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件。在MMM高可用方案中,典型的应用是双主多从架构,通过MySQL replication技术可以实现两个服务器互为主从,且在任何时候只有一个节点可以被写入,避免了多点写入的数据冲突。同时,当可写的主节点故障时,MMM套件可以立刻监控到,然后将服务自动切换到另一个主节点,继续提供服务,从而实现MySQL的高可用。可以灵活选择VIP方案或者全局目录数据库方案(更改Master IP映射)来进行切换。

MMM提供了自动和手动两种方式移除一组服务器中复制延迟较高的服务器的虚拟ip,同时它还可以备份数据,实现两节点之间的数据同步等。由于MMM无法完全的保证数据一致性,所以MMM适用于对数据的一致性要求不是很高,但是又想最大程度的保证业务可用性的场景。对于那些对数据的一致性要求很高的业务,非常不建议采用MMM这种高可用架构。

7、基于中间件proxy高可用组件的集群方案

中间件:

阿里 Cobar、MyCAT

360 Atlas

淘宝 Tddl

网易 Cutus

MySQLProxy

ProxySQL(Percona)

KingShard

MaxScale(MariaDB)

OneProxy

切换对应用透明,可扩展性强,方便分片扩展,可以跨机房部署切换,但是需要有一定自研能力,或者选择有完整的后期技术支持的中间件,以及社区活跃度较高的,有一定能力,后期可自研或者自己优化开发相关的中间件。以适应自身的业务需求。

二、集群/分布式

基于集群或者分布式的HA包括:

Mysql Group Replication

Mysql InnoDB Cluster

Percona XtraDB Cluster

MariaDB Galera Cluster

1、MGR

关于MGR原理,可参考可以参考阿里的数据库内核月报,关于mgr的文档。http://mysql.taobao.org/monthly/2017/08/01/

基于传统异步复制和半同步复制的缺陷——数据的一致性问题无法保证,MySQL官方在5.7.17版本正式推出组复制(MySQL Group Replication,简称MGR)。由若干个节点共同组成一个复制组,一个事务的提交,必须经过组内大多数节点(N / 2 + 1)决议并通过,才能得以提交。如上图所示,由3个节点组成一个复制组,Consensus层为一致性协议层,在事务提交过程中,发生组间通讯,由2个节点决议(certify)通过这个事务,事务才能够最终得以提交并响应。

引入组复制,主要是为了解决传统异步复制和半同步复制可能产生数据不一致的问题。组复制依靠分布式一致性协议(Paxos协议的变体),实现了分布式下数据的最终一致性,提供了真正的数据高可用方案。

2、MySQL InnoDB Cluster

Cluster解决方案其实是由MySQL的几个不同产品和技术组成的,比如MySQL Shell, MySQL Router, Group Replication. 一组MySQL服务器可以配置为一个MySQL集群。在默认的单主节点模式下,集群服务器具有一个读写主节点和多个只读辅节点。辅助服务器是主服务器的副本。客户端应用程序通过MySQL Router连接到主服务程序。如果主服务连接失败,则次要的节点自动提升为主节点,MySQL Router请求到新的主节点。InnoDB Cluster不提供NDB Cluster支持

3、Percona XtraDB Cluster

官网地址:https://www.percona.com/doc/percona-xtradb-cluster/5.7/intro.html

节点在接收sql 请求后,对于ddl 操作,在commit之前,由WSREP API 调用galera 库进行集群内广播,所有其他节点验证成功后事务在集群所有节点进行提交,反之rollback。pxc 保证整个集群所有数据的强一致性,满足CAP理论中满足:Consistency 和 Availability。

PXC提供的特性

同步复制,事务要么在所有节点提交或不提交

多主复制,可以在任意节点进行写操作

  • 上一篇: 大数据平台建设需要掌握的 14 个知识
  • 下一篇: Oracle RAC 建设过程中各个层面的关键点和优化项总结
  •   推荐站点

    • At-lib分类目录At-lib分类目录

      At-lib网站分类目录汇集全国所有高质量网站,是中国权威的中文网站分类目录,给站长提供免费网址目录提交收录和推荐最新最全的优秀网站大全是名站导航之家

      www.at-lib.cn
    • 中国链接目录中国链接目录

      中国链接目录简称链接目录,是收录优秀网站和淘宝网店的网站分类目录,为您提供优质的网址导航服务,也是网店进行收录推广,站长免费推广网站、加快百度收录、增加友情链接和网站外链的平台。

      www.cnlink.org
    • 35目录网35目录网

      35目录免费收录各类优秀网站,全力打造互动式网站目录,提供网站分类目录检索,关键字搜索功能。欢迎您向35目录推荐、提交优秀网站。

      www.35mulu.com
    • 就要爱网站目录就要爱网站目录

      就要爱网站目录,按主题和类别列出网站。所有提交的网站都经过人工审查,确保质量和无垃圾邮件的结果。

      www.912219.com
    • 伍佰目录伍佰目录

      伍佰网站目录免费收录各类优秀网站,全力打造互动式网站目录,提供网站分类目录检索,关键字搜索功能。欢迎您向伍佰目录推荐、提交优秀网站。

      www.wbwb.net