菜单
当前位置:首页>>解决方案>>硬件及运维解决方案

Mysql数据库巡检方案


一、索引设计

合理的索引设计能够有效加速数据库的访问,提高查询的执行效率,减少用户查询对服务端的资源消耗。

主键索引缺失

主键索引业务相关

冗余索引

低效索引

无效索引

二、容量规划

cpu

通常使用CPU利用率衡量CPU的繁忙程度,通过top命令,开发者可以查看CPU利用率实时变化。CPU 利用率持续超过80%,预示计算资源已经接近饱和,如果开发者已经做过SQL优化,则需要使用更高配置的CPU。通过查看7天内CPU利用率超过80%的时间占整体时间的百分比,以及单次持续时间超过一定阈值,则可视为CPU扩容的触发条件。

IO

大部分数据库应用都是的IO Bound类型,IO 处理能力直接决定了数据库的性能。IO 利用率统计了一秒内IO请求队列非空的时间比例,IO利用率越高就表示硬盘越繁忙。

存储空间

存储空间不足会导致严重的系统故障,数据库可能会宕机,更为严重的是数据库进程存活,但是无法响应服务,从而造成基于进程的宕机监控失效。

内存

使用InnoDB存储引擎的MySQL数据库在实例启动时,就会预分配一块固定大小的内存空间,所有读写请求都会在该空间中完成,如果内存中缓存了用户读写的数据,则直接读取内存,如果内存中没有用户读写的数据,则需要将数据先从硬盘中load进内存中,由于内存的读写速度远远快于硬盘,这就使得读写请求是否命中内存决定了读写请求的处理速度。内存空间越大,缓存数据越多,命中的几率也就越大。

网络

网络带宽在数据库返回记录较多的情况下,也可能会成为系统的瓶颈。一般我们使用每秒网络流入和流出字节数来衡量网络流量是否达到带宽限制。

三、服务安全

弱口令

MySQL的登陆认证使用的是IP和账户密码的方式,很多开发者为了方便记忆,习惯将数据库密码设置为弱密码,这实际是非常危险的。数据库中的数据很多涉及敏感业务,弱密码非常容易被破解,对数据库中的数据是一个严重安全隐患。MySQL系统库mysql库下的user表的password字段保存了所有用户的密码,MySQL使用的是两次sha-1的不可逆加密算法,所以我们无法通过password字段获取用户的密码内容,但是我们可以通过将常见弱密码制成彩虹表,模拟MySQL的加密算法,匹配password字段,即可发现数据库中的弱密码账号。

网络安全

在一般的业务架构中,数据库都不会直接服务于终端用户,而是服务于运行业务逻辑的应用程序。所以数据库和业务程序之间出于安全的考虑,会选择使用私有网络。即便如此,为了避免数据库连错,也需要在设置数据库账号时,增加IP来源限制。在一些特定的场景下,如果数据访问必须借助公网来实现,就会将数据库暴漏在公网上。使用公网数据库实例,必须要配置防火墙,否则存在被攻击的隐患。通过iptables我们可以控制访问数据库的来源IP

权限检查

MySQL提供了多种权限配置,为了方便管理以及避免误操作,一般会将管理权限和访问权限配置成两个不同的账号,禁止使用管理权限作为业务程序访问数据库的账号。通过系统库mysql库的user表可以确认各个账号拥有的权限,尽量避免业务账号拥有super权限。

四、参数配置

内存相关参数

MySQL数据库的内存使用包括两个部分:共享内存与连接独占内存。每一个用户新建连接,数据库都要分配一块固定大小的内存空间保存用户的临时数据,这些空间为单个连接独占。在MySQL实例启动时,系统同时也会预先分配一些实例级别的共享内存空间,例如Innodb_buffer_poolInnodb_log_buffer_pool等,供所有连接共享。独占内存空间乘以最大连接数加上共享内存空间,我们可以计算出MySQL最大可使用的内存空间,如果超过实际物理内存大小,就存在MySQL进程被Linux操作系统强行oom kill风险,导致实例宕机。MySQL的这些内存空间都可以通过配置参数指定大小,如果超过实际内存空间,应该调整相应参数配置,最常见的是调整Innodb_buffer_pool和最大连接数。

频繁卡顿

如果设置过大,会导致数据库实例重启或者故障恢复花费大量的时间。一般,对于使用固态硬盘等高配置的存储设备的数据库,可以将重做日志设置大一些,对于使用机械硬盘的数据库,应该设置小一些,一般在512M4G之间。innodb_flush_log_at_trx_commit定义了重做日志的刷新节奏,如果该参数非1,会导致数据库宕机重启后丢失部分更新数据,对于数据可靠性要求较高的应用造成严重影响。

二进制日志相关参数

binlog 主要用于MySQL集群复制以及故障恢复担任协调者的作用。binlog_format定义了binlog的格式,主要包括ROWSTATEMENTMIXED三种格式,ROW格式是最安全的一种日志格式,会保证主从数据的严格一致,建议开发者选用ROW格式。但是ROW格式的binlog会占用更多的存储空间,通过expire_logs_days可以控制保存binlog的天数,如果binlog占用的存储空间比例超过50%,则应考虑适当减少binlog的保存天数。sync_binlog 参数定义了binlog刷新硬盘的节奏,如果非1,会导致宕机重启后最近的更新数据丢失。

连接数相关参数

MySQL有最大连接数限制max_connections,如果应用连接超过max_connetions限制,则会得到out of max connections异常,无法建立连接。showprocesslist可以查看当前的连接数,如果接近最大限制,则存在无法新建连接的风险。通过在应用端使用连接池可以控制数据库的连接数。

五、用户访问

慢连接

慢查询数量是最直观的反映数据库处理能力是否满足业务需求的指标。通过设置slow_query_log可以开启慢查询日志,MySQL数据库会将执行时间超过long_query_time的查询记入慢查询日志,如果某个时间段内,慢查询数量急剧增加,则开发者就必须要关注数据库的性能问题,首先就需要进行SQL优化,其次考虑资源是否需要扩容,最后可能需要数据库水平扩展方案,包括创建只读从节点。

死锁数量

两个事务涉及的数据库记录有重叠,如果SQL语句的加锁顺序不一致,就会导致事务之间的死锁。虽然MySQL数据库会自动的检测死锁并强制回滚系统认为代价较小的事务,但是死锁的检测与事务回滚都有较大的代价,会严重拖慢数据库的性能,所以当系统中出现大量死锁时,开发者必须引起重视,要分析发生死锁的事务的SQL语句的加锁规则,调整SQL语句。通过show engin innodb status可以查看死锁的相关信息以及系统的处理过程。

集群复制

数据安全

复制是MySQL多个节点之间实现数据同步的重要机制,主要用于搭建高可用实例主从节点以及提供多个只读从节点提高读扩展能力。节点之间的数据是否最终一致对于高可用方案是否生效,只读实例读取的数据是否正确有着严重影响。从机执行show slave status可以获取从机的复制状态,Slave_IO_RunningSlave_SQL_Running分别表示IOSQL线程是否正常运行,如果不正常,则应及时处理。参数relay_log_recoveryrelay_log_info_repository影响从节点宕机重启后,与主机的复制位置是否正确,如果位置错误,则可能导致数据错误。

复制性能

复制延迟经常用来评估复制性能是否满足业务需求。Show slave statusSeconds behind master字段标识了从机落后主机的延迟时间。如果延迟较长,则会影响高可用实例主从切换的时间以及只读从节点是否能够及时读到最新数据。通过使用并行复制技术可以提高从节点的复制性能。MySQL 5.6提供了基于Database级别的并行复制,通过slave_parallel_workers 设置并行线程数;MySQL 5.7提供了基于LOGICAL_CLOCK的并行复制, 主机上同一个Group提交的binlog中包含事务在从机并行执行,相比database,具备更高的并发性,除了设置slave_parallel_workers,还需要将slave-parallel-type设置为LOGICAL_CLOCKslave_preserve_commit_order=1可以确保从机并行执行的事务按序提交。同时从机的log_binlog_slave_updates参数必须同时开启。