高性能MySQL读书笔记 (五)

Dean 发布于2019-07-31 17:25 / 2055人阅读

摘要：优化服务器设置有大量的可以修改的参数但不应该随便修改应该将更多时间花在的优化索引查询设计上配置文件路径通常在不建议动态修改变量因为可能导致意外的副作用通过基准测试迭代优化具体配置项设置请参照官网手册这里只提及部分配置内存使用确定可使用内存上

1. 优化服务器设置

MySQL有大量的可以修改的参数,但不应该随便修改.应该将更多时间花在schema的优化,索引,查询设计上

配置文件路径: 通常在/etc/my.cnf

不建议动态修改变量,因为可能导致意外的副作用

通过基准测试迭代优化

具体配置项设置请参照官网手册,这里只提及部分

1.1 配置内存使用

确定可使用内存上限

每个连接使用多少内存,如排序缓冲和临时表

确定操作系统内存使用量

把剩下的分配给缓存,如InnoDB缓存池

1.2 配置MySQL的I/O行为

有些配置项影响如何同步数据到磁盘及如何恢复操作,这对性能影响很大,而且表现了性能和数据安全之间的平衡

1.2.1 InnoDB I/O配置

重要配置: InnoDB日志文件大小,InnoDB怎样刷新日志缓冲,InnoDB怎样执行I/O

InnoDB使用日志减少提交事务时开销,不用每个事务提交时把缓冲池的脏块刷到磁盘中

事务日志可以把随机IO变成顺序IO,同时如果发生断电,InnoDB可以重放日志恢复已经提交的事务

sync_binlog选项控制MySQL怎么刷新二进制日志到磁盘

把二进制日志放到一个带有电池保护的写缓存的RAID卷可以极大的提升性能

1.2.2 MyISAM的I/O配置

因为MyISAM表每次写入都会将索引变更刷新到磁盘

批量操作时,通过设置delay_key_write可以延迟索引写入,可以提升性能

配置MyISAM怎样尝试从损坏中恢复

1.3 配置MySQL并发 1.3.1 InnoDB并发配置

如果在InnoDB并发方面有问题,解决方案通常是升级服务器

innodb_thread_concurrency: 限制一次性可以有多少线程进入内核(根据实践取合适值)

innodb_thread_sleep_delay: 线程第一次进入内核失败等的时间,如果还不能进入则放入等待线程队列

innodb_commit_concurrency: 控制有多少线程可以在同一时间提交

使用线程池限制并发: MariaDB已经实现

1.3.2 MyISAM并发配置

concurrency_insert: 配置MyISAM打开并发插入

1.4 其他

基于工作负载的配置: 利用工具分析并调整配置

max_connections: 保证服务器不会因应用程序激增的连接而不堪重负

安全和稳定的设置: 感兴趣者请自行google

高级InnoDB设置: 感兴趣者请自行google

InnoDB两个重要配置: innodb_buffer_pool_size和innodb_log_file_size

2. 复制

MySQL内建的复制功能是构建基于MySQL的大规模,高性能应用的基础.同时也是高可用性,可扩展性,灾难恢复,备份及数据仓库等工作的基础

2.1 概述

解决问题: 让一台服务器的数据与其他服务器保持同步.主库可以同步到多台备库,备库本身也可以配置为另一台服务器的主库

复制原理: 通过在主库上记录二进制日志,在备库重放日志的方式实现异步的数据复制

复制方式: 基于行的复制和基于语句的复制

向后兼容: 新版本只能作为老版本的备库,反之不行

2.2 用途

数据分布: 在不同地理位置分布数据备份,可以随意停止或开始复制.基于行比基于语句带宽压力更大

负载均衡: 将读操作分布到多个服务器上

备份: 复制是备份的一项有意义的技术补充

高可用性和故障切换: 避免单点失败

MySQL升级测试: 一种普遍做法是使用一个更高版本的MySQL作为备库保证实例升级前查询能够在备库按照预期执行

2.3 过程

主库把数据更改记录到二进制日志(Binary Log)

备库将主库上的日志复制到自己的中继日志(Relay Log)

备库读取中继日志中的事件,将其重放到备库数据上

局限: 主库上并发运行的查询在备库只能串行化执行,因为只有一个sql线程重放中继日志事件,这是很多工作负载的性能瓶颈

2.4 复制配置

在每台服务器上创建复制账号: 需要REPLICATION SLAVE权限

配置主库和备库: 每个服务器的ID需要唯一不能冲突

通知备库连接到主库并从主库复制数据

CHANGE MASTER TO: 指定备库连接的主库设置

SHOW SLAVE STATUS: 检查复制是否正确执行

START SLAVE: 开始复制

SHOW PROCESSLIST: 查看复制线程,IO线程(发送或获取日志),SQL线程(重放日志)

推荐配置: 开启sync_binlog

2.5 从另一个服务器开始复制

问题: 主库已经运行一段时间,用一台新安装的备库与之同步
保持同步条件:

某个时间点的主库的数据快照

主库当前的二进制日志文件,和获得数据快照时在该二进制日志文件中的偏移量.通过这两个可以确定二进制日志的位置

从快照时间到现在的二进制日志

克隆备库方法:

冷备份: 关闭主库,复制数据.主库重启后会使用新的二进制文件,在备库指向这个文件的起始处

热备份:如果只有MyISAM,可以通过mysqlhotcopy或rsync来复制数据

如果只包含InnoDB: 可以使用mysqldump转储主库数据并加载到备库,然后设置相应的二进制日志坐标

使用快照或备份: 使用主库的快照或者备份初始化备库,然后指定二进制日志坐标

使用Percona Xtrabackup: 备份时不阻塞服务器操作,可以在不影响主库情况下设置备库

使用另外的备库: 实质就是把另外的备库当成主库进行数据克隆

2.6 复制的原理 2.6.1 基于语句的复制

主库会记录那些造成数据更改的查询

MySQL5.0之前只支持基于语句的复制

对于函数,存储过程和触发器在基于语句的复制模式可能存在问题

更新必须是串行,需要更多的锁

2.6.2 基于行的复制

将实际的数据记录在二进制日志

能够更高效复制数据

基于行的复制事件格式,对人不可读,可以使用mysqlbinlog

很难进行时间点恢复

有些操作,如全表更新(update)复制开销会很大

2.7 复制拓扑 2.7.1 基本原则

一个MySQL备库实例只能有一个主库

每个备库必须有一个唯一的服务器id

一个主库可以有多个备库

如果打开log_slave_update一个备库可以把其主库上的数据变化传播到其他备库

2.7.2 一主多备

适用于少量写和大量读,可以把读分摊到多个备库上

当作待用的主库

放到远程数据中心,用作灾难恢复

作为备份,培训,开发或测试服务器

2.7.3 双主复制

个数据库互为主库和备库

容易造成数据不同步

通常并不建议使用这种模式

2.7.4 主动被动的双主模式

类似双主复制,把其中一台配置为只读

类似于创建一个热备份

可以用作执行读操作,备份,离线维护及升级

2.7.5 有备库的双主模式

双主模式下,各自有备库

2.7.6 主库,分发主库和备库

问题: 备库足够多时会对主库造成很大的负载

方案: 将其中部分备库当成主库,分发给更多的备库

通过分发主库,可以对二进制日志事件执行过滤和重写规则

2.8 复制管理和维护

监控复制: SHOW MASTER STATUS查看主库状态, SHOW BINLOG EVENTS查看复制事件

测量备库延迟: 可以使用Percona Toolkit里的pt-hearbeat

确定主备是否一致

备库换主库: 难点在于获取新主库合适的二进制日志位置

备库提升为主库分为计划内提升和计划外提升

2.8.1 计划内提升

停止向老的主库写入

备库赶上主库

备库设置为主库

将备库和写操作指向新主库,然后开启主库的写入

2.8.2 计划外提升

当主库崩溃时,需要提升一台备库替代

确定最新的备库

让所有备库执行完从崩溃前主库获得的中继日志,如果未完成则更换主库,会丢失原先的日志事件

重新完成主备的配置

2.9 复制的问题和解决方案 2.9.1 数据损坏或丢失

主库意外关闭: 主库开启sync_binlog避免事件丢失,使用Percona Toolkit中的pt-table-checksum检查主备一致性

备库意外关闭: 重启后观察MySQL错误日志,想方法获取备库指向主库的日志偏移量

主库上的二进制日志损坏: 跳过所有损坏的事件,手动找到一个完好的事件开始

备库上的中继日志损坏: MySQL5.5后能在崩溃后自动重新获取中继日志

二进制日志于InnoDB事务日志不同步: 除非备库中继日志有保存,否则自求多福

2.9.2 其他

如果使用myisam,在关闭Mysql前需要确保已经运行了stop slave,否则在服务器关闭时会kill所有正在运行的查询.

如果是事务型,失败的更新会在主库上回滚而且不会记录到二进制日志

避免混用事务和非事务: 如果备库发生死锁而主库没有,事务型会回滚而非事务型则不会造成不同步

主库和备库使用不同存储引擎容易导致问题

不唯一和未定义备库服务器id

避免在主库上创建备库上没有的表,因为复制可能中断

基于语句复制时,主库上没有安全使用临时表的方法.丢失临时表: 备库崩溃时,任何复制线程拥有的临时表都会丢失,重启备库后所有依赖临时表的语句都会失败

InnoDB加锁读引起的锁争用: 将大命令拆成小命令可以有效减少锁竞争

过大的复制延迟: 定位执行慢的语句,改善机器配置

其他: 查看官网手册

2.10 复制高级特性

半同步复制: 当提交事务,客户端收到查询结束反馈前必须保证二进制日志已经传输到至少一台备库上,主库将事务提交到磁盘上之后会增加一些延迟

复制心跳: 保证备库一直与主库相联系,如果出现断开的网络连接,备库会注意到丢失的心跳数据

2.11 其他复制技术

Percona XtraDB Cluster的同步复制

Tungsten

云数据库MySQL 读书笔记编程思想读书笔记大数据时代读书笔记 vue3 学习笔记 (五)——vue3

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/47315.html

高性能MySQL读书笔记 (二)

摘要：基准测试应用验证系统的一些假设重现系统异常测试系统当前的运行情况模拟更高的负载规划业务增长测试应用适应可变环境的能力测试不同硬件软件和操作系统的配置策略针对整个系统的整体测试集成式单独测试单组件式测试指标吞吐量在线事务处理单位为每秒事务数响 1. 基准测试 1.1 应用验证系统的一些假设重现系统异常测试系统当前的运行情况模拟更高的负载规划业务增长测试应用适应可变环境的能力测试不同硬件...

xietao3 2019-07-31 17:26 评论0 收藏0
高性能MySQL读书笔记 (一)

摘要：服务器逻辑架构连接线程处理基于的工具类似实现连接处理授权认证安全等查询缓存解析器实现查询解析分析优化缓存内置函数和跨存储引擎如存储过程触发器视图等存储引擎数据的存储和提取不会解析独立与上层服务器通过进行通信并发控制每种存储引擎有不同的锁策略 1. MySQL服务器逻辑架构连接/线程处理: 基于C/S的工具类似,实现连接处理,授权认证,安全等.查询缓存/解析器: 实现查询解析,分析,优...

wind5o 2019-07-31 17:24 评论0 收藏0
高性能MySQL读书笔记---查询优化

摘要：查询优化查询缓慢的原因是否向数据库请求了不必要的数据查询时是否返回了全部或者大部分数据然后再进行处理的。进行单查或者多表联查时是否返回了全部列数据。将一次处理大量数据的操作，分解为多个小操作。尽量减少在数据库中进行排序操作查询优化查询缓慢的原因是否向数据库请求了不必要的数据1.查询时是否返回了全部或者大部分数据然后再进行处理的。2.进行单查或者多表联查时是否返回了全部列数据。...

keithyau 2019-07-31 17:15 评论0 收藏0
高性能MySQL读书笔记---建表及数据类型优化

摘要：高效，简单，准确，只能满足其中两个。计数器表计数器表在应用程序中使用很频繁例如统计朋友圈的点赞数某个文件的下载量空间访问人数等。数据类型优化 1.数据类型优化尽量使用可以正确储存数据的最小数据类型,例如状态字段时只有只需要几个数字就使用tinyint而不是int类型尽量使用简单的方式去存储数据,可以用整形表示的时候就不要用字符串类型例如性别,IP等、避免NULL值的使用...

Aldous 2019-07-31 17:15 评论0 收藏0
高性能MySQL读书笔记(2)--MySQL基准测试

摘要：当并发性增加时，需要测量吞吐量是否下降，响应时间是否变长可扩展性可扩展性不是压力测试的指标，可扩展性指标对于容量规范非常有用，它可以提供其他测试无法提供的信息，来帮助发现应用的瓶颈归根结底，应该测试那些对用户来说最重要的指标。 ...

233jl 2021-09-09 09:32 评论0 收藏0