MySQL相关参数配置及性能优化

MySQL及其优化

数据库相关概念

系统吞吐量：一个系统的吞度量（承压能力）与request对CPU的消耗、外部接口、IO等等紧密关联。单个reqeust 对CPU消耗越高，外部系统接口、IO影响速度越慢，系统吞吐能力越低，反之越高。系统吞吐量几个重要参数：QPS（TPS）、并发数、响应时间(平均响应时间)；
QPS：每秒钟处理SQL的数量；
TPS：每秒处理的事务数量；包含了用户请求服务器、服务器自己的内部处理、服务器返回给用户；如果每秒能够完成N次这三个过程，则TPS就是N；

一个TPS可能包含多个QPS。
并发数：系统同时处理的请求数量；

事务的四大特性ACID

A-原子性，一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败,对于一个事务来说,不可能只执行其中的一部分操作。
C-一致性，一致性是指事务将数据库从一种一致性，状态转换到另外一种一致性状态,在事务开始之前和事务结束后数据库中数据的完整性没有被破坏。
I-隔离性，隔离性要求一个事务对数据库中数据的修改,在未提交完成前对于其它事务是不可见的。
- 事务的隔离级别：
  - 读未提交：Read Uncommitted
  - 读已提交： Read Committed
  - 可重复读： Repeatable Read
  - 可串行化：Serializerable
    
    以上隔离级别：隔离性由低到高；并发性由高到低；其中InnoDB存储引擎默认的事务隔离级别是Repeatable Read 可重复读；
D-持久性，一旦事务提交，则其所做的修改就会永久保存到数据库中，此时即使系统崩溃，已经提交的修改数据也不会丢失；

影响mysql数据库性能的几个方面

服务器硬件
- CPU
  - 64位的CPU一定要工作在64位的系统下
  - 选择更快的CPU还是选择更多的CPU？
    - web类应用(并发较高)：核心数比频率重要；
    - CPU密集型场景和复杂SQL: 频率更重要；
  - 很多老版本的MySQL(<=5.5)无法发挥多核心CPU的性能；
- 内存
  - 尽可能选择内存主频更高的内存；尽量选择相同品牌、存储颗粒、频率、电压等；
- 网络
  - 网络带宽，1M， 10M…
  - 网络质量，丢包率
  - 网卡
- I/O子系统 (PCIe > SSD > RAID10 > 传统机械硬盘 > SAN)
  - 磁盘的配置和选择
    - 使用传统机械硬盘；
    - 使用RAID增强传统机械因硬盘的性能；将多个小容量磁盘组成容量更大的磁盘，并提供数据冗余来保证数据完整性；
      - 常用的 RAID级别： RAID0、 RAID1、 RAID5、 RAID10
    - 使用固态存储SSD和PCIe卡；相比机械硬盘，有更好的随机读写性能、更好地支持并发、寿命较低(最大写入次数)
    - 使用网络存储NAS和SAN
服务器操作系统
- Windows，操作系统文件大小写不敏感
- FreeBSD
- Solaris
- Linux
  - CentOS
  - Redhat 企业版
所用的MySQL版本
数据库存储引擎
- MyISAM: 不支持事务，表级锁；(MyISAM会将索引缓存到内存当中，数据缓存在操作系统)
- InnoDB: 事务级存储引擎，完美支持行级锁；(InnoDB将索引和数据都缓存到内存中)
数据库参数配置（影响较大）
数据库结构设计和SQL语句（慢查询是大多数性能低的罪魁祸首）

数据库性能优化的重点

数据库表结构设计
SQL语句的编写和优化

CentOS系统参数优化

内核相关参数(/etc/sysctl.conf)

# 网络相关参数
net.core.somaxconn=65535

net.core.netdev_max_backlog=65535

net.ipv4.tcp_max_syn_backlog=65535

net.ipv4.tcp_fin_timeout=10

net.ipv4.tcp_tw_reuse=1

net.ipv4.tcp_tw_recycle=1

# 等等...

# 内存相关参数
kernel.shmmax=4294967295  # 用于定义单个共享内存段的最大值
# 这个参数应该设置得足够大，以便能在一个共享内存段下容纳整个的InnoDB缓冲池的大小；
# 这个值的大小对于64位linux系统，可取的最大值为物理内存值减去1Byte，建议值为大于物理内存的一半，一般取值大于InnoDB缓冲池的大小即可；

vm.swappiness=0
# 在MySQL服务器所在的linux上保留交换分区还是有必要的，但是要控制何时使用交换分区；
# 当上面的参数设置为0时，即告诉linux内核，除非虚拟内存满了，否则不要使用交换分区；

增加资源限制(/etc/security/limit.conf)

# 控制打开文件的数量的限制,追加到limit.conf结尾即可；
* soft nofile 65535
* hard nofile 65535

# 解释
#	* 表示对所有用户有效
#	soft 指的是当前系统生效的设置
#	hard 表明系统中所能设定的最大值
# 	nofile 表示所限制的资源是打开文件的最大数目
# 	65535 即限制的数量

磁盘调度策略(/sys/block/devname/queue/scheduler)

# 查看当前所使用的磁盘调度策略
cat /sys/block/sda/queue/scheduler  

>>> noop anticipatory deadline [cfq]

# 默认策略是cfq，用于桌面操作系统还是比较合适，但是用于MySQL服务器不太合适；
# noop(电梯式调度策略)，适用于内存设备、RAM、嵌入式系统；
# deadline(截止时间调度策略)，适用于数据库类应用；
# anticipatory(预料I/O调度策略)，适用于写入较多的环境，如文件服务器；

# 改变磁盘调度策略
# 语法 echo <schedulername> > /sys/block/devname/queue/scheduler
# 如将sda盘的调度策略改为deadline：
echo deadline > /sys/block/sda/queue/scheduler

文件系统对性能的影响

Windows

FAT
NTFS

Linux

EXT3
EXT4

XFS，性能较高，优先选择

若使用EXT3/4系统，需要设置挂载参数(/etc/fstab)

data=writeback | ordered | journal
noatime
nodiratime
/dev/sda1/ext4 noatime,nodiratime,data=writeback 1 1

MySQL体系结构

整体架构分为：MySQL客户端、MySQL服务层、存储引擎层；

MySQL客户端
MySQL服务层
- 连接管理器
- 查询解析器
- 查询优化器
- 查询缓存器
存储引擎层（存储引擎针对表，而非库，即一个数据库中不同的表可以使用不同的存储引擎）
- InnoDB
  - MySQL5.5(具体点是5.5.8)版本及之后默认的存储引擎；
  - InnoDB使用表空间进行数据存储，innodb_file_per_table=ON时，独立表空间tablename.ibd；为OFF时，系统(共享)表空间ibdataX，这里的X是具体的数值，比如ibdata1、ibdata2；查看存储方式：show variables like 'innodb_file_per_table';强烈建议对于InnoDB存储引擎，使用独立表空间（MySQL5.6之后已经默认选用）；
  - 创建一个使用InnoDB引擎的表：create table tb_test(id int, c1 varchar(10)) engine='innodb';
  - 文件结构：myinnodb.frm，存储表结构；myinnodb.ibd，存储数据；
  - 实现事务的方式：Redo Log 和Undo Log；其中Redo Log 中存放了已经提交的事务【顺序写入】，Undo Log 中存放了未提交的事务(实现回滚)【随机读写】（MySQL5.6以后，Undo Log 可以独立存储在SSD等设备，以加快随机读写速度）；Redo Log 和 Undo Log实现了事务的原子性、一致性、持久性；
  - 支持行级锁，最大程度上支持并发；行级锁是由存储引擎层实现的；锁实现了事务的隔离性；
  - InnoDB状态检查：show engine innodb status;;
  - InnoDB存储引擎的适用场景：
    - InnoDB适合于大多数OLTP应用（On-Line Transaction Processing 在线事务处理）；
- MyISAM
  - MySQL5.5之前版本默认的存储引擎；
  - 系统表、临时表采用MyISAM存储引擎；
  - 文件结构：frm、MYD、MYI， MYI是索引文件， frm是表结构，MYD是存储数据；
  - 特性：
    - 1.并发性与锁级别角度：
      - MyISAM支持表级锁，写入相对较慢，对读操作影响不算大；
      - 并发性较差；
    - 2.表损坏修复：
      - 由于不支持事务，可能造成数据的丢失；
      - 可以使用：check table 表名来检查表；
      - 可以使用：repair table 表名来修复；（不保证完全修复）
      - 也可使用命令行工具myisamchk进行检查与修复，注意：在使用该命令行工具时，要先停掉MySQL服务，否则可能造成更大的损坏；
    - 3.支持的索引类型
      - 1.支持全文索引，是MySQL5.7版本之前唯一一个原生就支持全文索引的官方存储引擎；
      - 2.支持text类型的大文本的前缀索引；
    - 4.支持数据压缩 mysisampack -b -f myIsam.MYI；-f表示强制压缩；如果强制压缩特别小的文件，可能会出现压缩后体积更大的情况；压缩后只能读，不能写；
    - 5.限制：MySQL5.0版本之前，默认单个表大小不能超过4G，如果需要存储大表需要修改MAX_Rows 和 AVG_ROW_LENGTH；这两个参数乘积即为单表最大值；
    - 6.适用场景：
      - 非事务型应用；
      - 只读类应用；
      - 空间类应用；（空间函数，5.7之前唯一支持空间运算的存储引擎）
- XtraDB
- CSV
  - 存储的文件格式就是普通的csv文件，文本格式，方便查看和编辑（其他存储引擎存储的是二进制，无法直接在文件中查看和编辑）；
  - .CSV文件存储表内容
  - .CSM文件存储表的元数据，如表的状态和数据量；
  - .frm文件存储表结构信息；
  - 以逗号为分隔符；所有列都不能出现NULL(建表时候需要增加约束not null)；不支持索引；
  - 适用场景
    - 适合做为数据交换的中间表，如将Excel表格数据导出为CSV，然后存储到数据库表中(反之亦可)；
- Memory
  - 也称为HEAP(堆)存储引擎，数据保存在内存中，一旦MySQL数据库停止或重启，数据就会丢失，但是表结构会保存下来，原因是表结构保存在磁盘中，数据保存在内存中；
  - I/O效率较高；
  - 支持两种索引，HASH索引和BTree索引，默认使用HASH索引；HASH索引的特点:在做等值查询，效率很高，在做范围查询就无法使用了；创建默认索引：create index 索引名 on 表名(字段名);，创建BTree索引：create index 索引名 using btree on 表名(字段名);；查看索引：show index from 表名;
  - 索引的选用原则：如果等值查找较多，选择HASH索引，范围查找较多，选择BTree索引；
  - 所有字段都为固定长度，比如定义时候使用的是varchar(128)，则会变成char(128)，即使长度为5，也会占用128的空间，所以在定义的时候要尽量使用最小的长度，否则会非常占用内存空间；
  - 不支持BLOG和TEXT等大字段；
  - 使用的是表级锁，所以并发性能不一定高；
  - 表的最大大小是由max_heap_table_size参数决定的，默认值16M，对于已存在的表需要重建才能生效；
  - Memory存储引擎表和临时表容易混淆；临时表是系统使用的临时表，查询优化器为了优化查询时所使用；另一种临时表是使用create temporary 表名; 创建的临时表，临时表只对当前session可见；而Memory存储引擎表是使用Memory存储引擎所创建的普通表，对所有可见；
  - 适用场景：
    - 等值查找性能高，用于查找或者是映射表，比如邮编和地区的映射表；
    - 用于保存数据分析中产生的中间表；
    - 用于缓存周期性聚合数据的结果表；
- MRG_MYISAM
- Archive
  - 以zlib对表数据进行压缩，磁盘I/O更少；
  - 只支持insert和select操作；
  - 支持行级锁；
  - 只允许在自增ID列上加索引；
  - 适用场景
    - 日志和数据采集类应用；
- Federated
  - 特点
    - 提供了访问远程MySQL服务器上表的方法；
    - 本地不存储数据，数据全部存放到远程服务器上；
    - 本地需要保存表结构和远程服务器的连接信息；
  - 默认禁止该存储引擎，启用需要在创建表时增加federated参数；查看支持的引擎：show engines;；在my.cnf文件中增加一行federated=1然后重启MySQL服务器；
  - 适用场景：
    - 偶尔的统计分析及手工查询；关联关系查询效率低，不适合生产环境；
- Tokudb
- 等等等
如果选择存储引擎？

生产环境优先选择InnoDB存储引擎，除非InnoDB不能满足需求；

选择存储引擎的参考条件：1.事务；2.备份；3.崩溃回复；4.存储引擎特性；

除了必须，否则尽量不要使用混合存储引擎，不然很可能引发更多未知问题；

MySQL服务器参数

MySQL获取配置信息的路径

命令行参数
```
mysqld_safe –datadir=/data/sql_data
```

配置文件(不同操作系统不一定相同)

mysqld –help --verbose | grep -A 1 'Default options'

# /etc/my.cnf /etc/mysql/my.cnf /home/mysql/my.cnf ~/.my.cnf

MySQL配置参数的作用域

全局参数（需要在MySQL客户端内执行）

# set global 参数名=参数值;
# set @@global.参数名=参数值;

# 例如：
SET GLOBAL  wait_timeout = 604800;
SET @@GLOBAL.wait_timeout = 604800;

SET GLOBAL event_scheduler = ON; 
SET @@global.event_scheduler = ON; 
SET GLOBAL event_scheduler = 1; 
SET @@global.event_scheduler = 1; 

# 配置之后需要重新登录mysql客户端才能生效；

会话参数

# set [session] 参数名=参数值;
# set @@session.参数名=参数值;

内存配置相关参数
1. 确定可以使用的内存上限
  
  第一前提是不能超过物理内存；在32位操作系统中，单进程的最大内存为3GB，所以在32位操作系统上，单进程MySQL也不能超过3GB；
2. 确定MySQL的每个连接线程使用的内存
  
  对于每个连接单独分配缓存，当连接数量越来越多时，占用内存也就越多；并不是在连接初始化时为每个连接分配缓存区，而是在需要有查询操作时才会每个缓冲区分配内存；
  
  sort_buffer_size排序缓冲区大小，一旦查询需要排序，MySQL会为这个连接分配指定排序缓存区大小sort_buffer_size的全部内存，尽管该连接可能用不到那么多；
  
  join_buffer_size连接缓冲区大小，每个连接使用的缓冲区大小，如果一个查询中关联到多张表，会分配多个缓冲区；
  
  read_buffer_size读缓冲区大小，对一个MyISAM表进行全表扫描时分配的扫描缓冲区大小；MySQL只会在有查询需要的时候为其分配内存，也是一次性分配全部大小；另外，该参数的参数值是4K的整数倍数；
  
  read_rnd_buffer_size索引缓冲区大小，MySQL也只会在有查询需要时为其分配内存，但是分配的大小是所需大小，而不是参数指定的大小；
  
  以上参数都是针对每个连接线程分配的数值，如果连接数量太大，可能会造成内存溢出；
3. 确定需要为操作系统保留多少内存
  
  生产环境建议专机专用，MySQL服务器专门使用一台服务器，不要与其他应用服务器放在一起；同时，也不建议一台数据库服务器中运行多个MySQL实例；
4. 如何为缓冲池分配内存
  
  Innodb_buffer_pool_size，InnoDB缓冲池大小，该参数对InnoDB存储引擎的性能影响很大；InnoDB引擎会使用缓冲池延迟写入，将多个写入操作一起顺序地写入磁盘；
  
  InnoDB缓冲池大小 = 总内存 - (每个线程所需内存 * 连接数) - 系统保留内存
  
  MySQL手册推荐缓冲池内存大小为数据库服务器的内存容量的75%以上（实际中还需考虑其他因素）；
  
  key_buffer_size，MyISAM存储引擎用的缓冲区大小；

I/O相关配置参数

这部分数据决定了MySQL数据库如何将缓冲池中的数据同步到磁盘上，以实现持久化保存；如果是在数据发生变化之后立即写入到磁盘上，是比较低效的， I/O成本很高；

Innodb_log_file_size控制InnoDB存储引擎单个事务日志文件大小，即Redo Log文件；如果业务非常繁忙，建议将该数值设置的尽量大一些；

Innodb_log_files_in_group控制InnoDB存储引擎事务日志文件的个数；

事务日志的总大小即上述两个参数值的乘积；一般来说，事务日志的总大小应该能记录1个小时左右的事务信息；

事务日志并不是每次提交都写入到文件中，而是先写入到事务日志的缓冲区，Innodb_log_buffer_size的大小控制了事务日志的缓冲区大小；该数值不应设置太大，一般这个缓冲区至少保留1秒左右数据即可，参考范围(32MB~128MB)；

事务日志刷新的频繁程度Innodb_flush_log_at_trx_commit，这个参数有3个选择：0表示每秒进行一次log写入cache，并flush到磁盘，在MySQL进程崩溃时，至少会丢失1秒钟的事务；1是默认值，表示在每次事务提交执行log写入cache，并flush到磁盘，性能较差；2是建议值，表示每次事务提交，执行log数据写入到cache，每秒执行一次flush log到磁盘。其中0与2的区别是，0会在数据库进程崩溃时，丢失至少1秒的事务，而2则不会，只有在整个数据库服务器宕机时才会丢失至少1秒的事务；所以建议参数设置为2；

Innodb_flush_method=O_DIRECT，InnoDB刷新的方式，这个参数决定了InnoDB的日志文件和数据文件如何跟文件系统进行交互，不仅影响InnoDB怎么写入数据，还影响InnoDB怎么读数据；对于linux操作系统，建议将该参数的值设置为O_DIRECT方式，这个方式会告诉操作系统不要缓存数据，也不要预读，也就是说这个方式完全关闭了操作系统的缓存，并且使所有的读和写都直接通过存储设备来完成，来避免InnoDB和操作系统对数据的双重缓存；

Innodb_file_per_table=1，这个参数控制InnoDB如何使用表空间，如果设置了这个参数，InnoDB会为每个表设置单独的表空间，否则就会将InnoDB所有的表都存到系统表空间；建议使用这个参数；

Innodb_doublewrite=1，这个参数控制InnoDB是否使用双写缓存，是为了避免数据没有写完整导致数据损坏，建议使用这个参数；

InnoDB存储引擎是事务型存储引擎，为了减少在提交事务时产生的I/O开销，InnoDB采用了预写日志的方式，每次在提交事务的时候，先将数据写入到事务日志中，而不是将数据立即刷新到数据文件中，这样做是为了提高I/O性能，因为事务的修改使事务的数据和索引通常映射到表空间随机的位置，所以刷新数据变更到数据文件就会产生大量的随机I/O，而记录日志是顺序写入；所以相对于立即刷新数据到数据文件中，预写日志的方式提高了I/O性能。而且，一旦事务日志安全写入到磁盘中，事务就算是持久化了，这时即使数据的变更还没写入数据文件发生宕机情况，后来也能通过事务日志来恢复已经提交的事务；

MyISAM的I/O相关配置：

delay_key_write：控制关键字缓冲中的脏块什么时候刷新到磁盘中；OFF表示每次写操作后刷新键缓冲中的脏块到磁盘，这是最安全的操作，但是性能较差；ON只对在建表时指定了delay_key_write选项的表使用延迟刷新；ALL对所有MyISAM表都使用延迟键写入；需要注意的是：如果启用延迟键写入，当服务器崩溃并且缓存中有的块没有写入到磁盘文件，这就会造成MyISAM表索引文件的损坏，这是就需要使用repair table这个命令进行修复；

安全相关配置参数

expire_logs_days指定自动清理binlog(二进制日志)的天数；这个参数的设置应该至少可以覆盖2次全备所间隔的天数；

max_allowed_packet控制MySQL可以接收的包的大小；同时也会影响一个用户定义的变量的最大容量；参考值如32MB；如果存在主从配置，而从数据库配置的该值小于主数据库该值的配置，可能会出现同步失败的情况；

skip_name_resolve禁用DNS查找；当连接服务器的时候，默认情况下MySQL试图对连接的客户端主机所使用的主机的域名，进行域名的正向及反向查找，如果DNS服务器出现了问题，会出现大量的堆积和延时，严重降低性能，建议开启禁用DNS查找功能；

sysdata_is_now确保sysdate()返回确定性日期；建议增加该参数；

read_only禁止非super权限的用户写入权限，在主从同步架构中使用较多，避免从服务器的数据被破坏；

skip_slave_start禁用slave自动恢复，也用于主从同步架构中。建议启动该功能以防止在出现问题后从服务器自动恢复同步，因为这时候还可能存在不确定的问题；

sql_mode设置MySQL所使用的SQL模式；在默认的模式下，MySQL对于SQL语句的要求是比较宽松的，比如在执行分组查询时，允许查询中使用的非聚合函数的列不全部出现在group_by从句中，这其实不符合SQL的规范，如果修改sql_mode为严格模式，会使这样的语句出现语法错误的情况；可选选项：1. strict_trans_tables，在这种模式下，如果给定的数据不能插入到事务型存储引擎中，会中断此次操作，但对非事务型存储引擎无影响；2. no_engine_subtitution，在这种模式下，如果在进行创建表时指定存储引擎，而指定的存储引擎不可用，则不会使用默认的存储引擎来建表，即创建表失败而不是创建默认存储引擎的表；3. no_zero_date，在这种模式下，不能将类似于”0年0月0日”这种日期写入到数据库中日期对应的字段上；4. no_zero_in_date，在这种模式下，不能将含有0的日期写入到数据库表中日期对应的字段上；5. only_full_group_by，在这种模式下，查询中使用的非聚合函数的列需要全部写在group_by从句中，否则SQL语句不能执行；不建议改动生产环境的sql_mode参数值；

其他常用配置参数

sync_binlog控制MySQL如何向磁盘刷新binlog；默认值为0，表示MySQL不会主动刷新，而是由操作系统来决定什么时候刷新cache到磁盘；如果这个数值大于0，表示的是两次刷新之间间隔了几次二进制日志的写入操作；如果数值设置过大，可能会导致主从同步的数据库在出现问题时，数据不同步，而且很难恢复；

tmp_table_size和max_heap_table_size这两个蚕食一起使用，用于控制使用Memory存储引擎的内存临时表的大小；这两个参数的值应该保持一致，且都不要太大，以防内存溢出；

max_connections，控制允许的最大连接数；默认值只有100，参考值：2000，具体根据实际业务环境；

数据库设计对性能的影响

数据库设计对性能的影响
1. 过分地反范式化为表建立了太多的列（很不符合范式化原则）
  
  虽然MySQL允许为一个数据表建立很多列，但是由于MySQL的插件式架构的原因，MySQL服务器层和存储引擎层是分离的，MySQL的存储引擎层在工作时需要把在服务器层和存储引擎层之间通过缓冲格式来拷贝数据，然后在服务器层将缓冲的内容解析成各个列，这个过程成本是非常高的，特别是对于MyISAM这个变长结构，InnoDB这种行结构进行解析时还必须进行转换，这个转换的成本取决于列的数量，所以一个表的列太多，使用这个表时就会带来额外的CPU消耗，所以在设计表的时候，不要把所有的相关的列放在一个表中，而是要按照范式化对表进行拆分。简而言之，一个表有太多的列，会对性能有很大的影响；
2. 过分地范式化造成太多的表关联
  
  关联查询非常消耗性能，所以性能会随着关联表数量的增加而下降；MySQL限制一张表最多关联61张表，这个数量对于多数人来说足够了，但是为了提升性能，尽量做到关联表数量在10个以下，这个时候可以不必严格遵循范式化设计原则；比如常用的两张关联表可以设计成一张表；
3. 在OLTP环境中使用不恰当的分区表
  
  分区表可以将一张大表从物理存储上按照分区键分成多个小表，这里说的分区表与常说的分库分表还是有差别的；分区表是在同一个数据库实例下所进行的，而在物理存储上分成了多个小表，但是在使用时逻辑上还是一个表；而分库分表不仅是在物理上进行拆分，在逻辑上也进行了拆分，而且分库分表后，一般这些小表不是在同一个数据库实例下；
  
  建立分区表时，分区键的选择非常关键，选择的不好，会造成查询的时候需要跨多个分区进行查询，反而降低了性能；
4. 使用外键保证数据的完整性
  
  使用外键约束来保证数据的完整性，但是这样的效率是非常低的，因为在对使用外键的表进行数据修改时，MySQL都要对外键进行检查，这样就带来了额外的锁的开销，降低了数据库的修改的效率，另外，在进行数据库备份恢复、归档维护时，如果存在外键，不能对表进行快速清空操作，只能使用delete来执行，这就加大了对大表的清理复杂度；所以，尽量少使用外键约束；
总结
1. 性能优化顺序
  1. 数据库结构设计和SQL语句；（这一优化可能会随着业务的升级，需要持续地进行优化）
  2. 数据库存储引擎的选择和参数的配置；
  3. 系统选择及优化；
  4. 硬件升级；

MySQL性能测试

如何知道是否该进行数据库优化？需要先对MySQL性能进行测试。

MySQL基准测试

定义

基准测试是一种测量和评估软件性能指标的活动，用于建立某个时刻的性能基准，以便当系统发生硬件变化时重新进行基准测试以评估变化对性能的影响；
基准测试与压力测试的关联及区别

基准测试是针对系统设置的一种压力测试，可以检验改变配置参数后对性能的影响；可以观察系统在不同压力的情况下的行为、评估系统的容量；但是基准测试与通常所说的压力测试还是有一定的区别的；基准测试的特点是：直接、简单、易于比较，基准测试的结果通常是用测试工具所生成的，只能用于评估服务器的处理能力，一般不关心业务逻辑，所使用的的查询和业务的真实性可能没关系；而压力测试则是通过对真实的业务数据进行测试，获得真实系统所能承受的压力，一般需要针对不同的主题，比如对购物车模块进行压力测试，所使用的的数据和查询是真实业务中用到的；也可以说基准测试是简化的压力测试；
基准测试的目的

建立MySQL服务器的性能基准线，主要是为了测试MySQL服务器的当前运行状况；可以检验改变配置参数后对性能的影响；

模拟比当前系统更高的负载，以找出系统随着压力的增加所遇到的扩展瓶颈；通过增加数据库并发，观察QPS、TPS变化，确定并发量与性能的最优关系；

测试不同的硬件、软件和操作系统配置；比如测试linux系统下不同磁盘分区格式对于数据库性能是否由影响；

判断新的硬件设备是否配置正确；
如何进行基准测试
1. 对整个系统进行基准测试
  
  从系统入口进行测试，比如网站的web前端，手机APP前端；
  
  优点：能够测试整个系统的性能，包括web服务器缓存、数据库等；
  
  缺点：测试设计复杂、消耗时间长；
2. 单独对某一组件进行基准测试，如只对MySQL服务器进行必要的基准测试；
  
  优点：测试设计简单、所需耗费时间短；
  
  缺点：无法全面了解整个系统的性能基线；
MySQL基准测试的常见指标
1. 单位时间内所处理的事务数(TPS)
2. 单位时间内所处理的查询数(QPS)
3. 响应时间
4. 并发量：同时处理的查询请求的数量(不等于同时在线人数等指标)，正在工作中的并发的操作数或同时工作的数量，web服务器的并发量也不一定等于MySQL数据库的并发量；
基准测试的步骤
1. 计划和设计基准测试
  1. 是对整个系统还是对某一组件进行基准测试；
  2. 使用什么样的数据来进行基准测试，比如希望基准测试能反映实际生产环境的情况，那么最好使用生产环境的数据库的完全备份来进行；而如果只想知道某个配置参数的调整对于性能的影响，也可以使用简单的数据进行测试；
  3. 准备基准测试的数据及数据搜集脚本；包括CPU、IO、网络流量、状态与计数器信息等；
  4. 运行基准测试；
  5. 保存及分析基准测试结果；最好能以可视化展示分析结果；

基准测试的工具（常用）

mysqlslap，MySQL5.1版本之后自带的基准测试工具，无需额外安装；可以模拟服务器负载，并输出相关统计信息；可以指定也可以自动生成查询语句；
1. 常用参数说明：
  1. –auto-generate-sql，由系统自动生成SQL脚本进行测试；
  2. –auto-generate-sql-add-autoincrement，在生成的表中增加自增ID；
  3. –auto-generate-sql-load-type，指定测试中使用的查询类型；默认使用混合类型同时包括删除、查询、更新等；
  4. –auto-generate-sql-write-number，指定初始化数据时生成的数据量；
  5. –concurrency，指定并发线程的数量；
  6. –engine，指定要测试表的存储引擎，可以用逗号分隔多个存储引擎；
  7. –no-drop，指定不清理测试数据，默认是测试完成后清理测试数据的；
  8. --iterations，指定测试运行的次数；
  9. --number-of-queries，指定每一个线程执行的查询数量；
  10. --debug-info，指定输出额外的内存及CPU统计信息；
  11. --number-int-cols，指定测试表中包含的int类型列的数量；
  12. –number-char-cols，指定测试表中包含的varchar类型的数量；
  13. –create-schema，指定了用于执行测试的数据库的名字；注意在生产环境中测试时，不要将数据生成到生产环境中；
  14. –query，指定自定义的SQL脚本；
  15. –only-print，并不运行测试脚本，而是把生成的脚本打印出来；
2. 示例：（在终端命令行下执行）
```
mysqlslap -u用户 -p密码 --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=10 --create-schema=test


# 结果
Benchmark
	Running for engine myisam
	Average number of seconds to run all queries: 0.036 seconds
	Minimum number of seconds to run all queries: 0.025 seconds
	Maximum number of seconds to run all queries: 0.043 seconds
	Number of clients running queries: 1
	Average number of queries per client: 10

Benchmark
	Running for engine myisam
	Average number of seconds to run all queries: 1.121 seconds
	Minimum number of seconds to run all queries: 0.948 seconds
	Maximum number of seconds to run all queries: 1.339 seconds
	Number of clients running queries: 50
	Average number of queries per client: 0
...
		
		
# 当出现如下显示时，表示已经超出最大连接数，需要调整最大连接数
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
```

sysbench，需要额外安装

安装， github地址： https://github.com/akopytov/sysbench

分为编译安装和直接使用包管理工具安装

# 下载最新版本，注意现在可能更新了
cd /home/
wget https://github.com/akopytov/sysbench/archive/1.0.19.zip

# 解压，文件名可能不一样
unzip sysbench-0.5.zip

# 编译
./autogen.sh

# 具体路径视情况而定
./configure --with-mysql-includes=/usr/local/mysql/include/ --with-mysql-libs=/usr/local/mysql/lib/

# 安装
make && make install

# 根据该工具github说明：
# The easiest way to download and install sysbench on Linux is using binary package repositories hosted by packagecloud. The repositories are automatically updated on each sysbench release. Currently x86_64, i386 and aarch64 binaries are available.

# Multiple methods to download and install sysbench packages are available and described at https://packagecloud.io/akopytov/sysbench/install.

# RHEL/CentOS 
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

sudo yum -y install sysbench

常用参数
1. –test，用于指定索要执行的测试类型，支持以下参数：
  1. Fileio，文件系统I/O性能测试；
  2. cpu，CPU性能测试；
  3. memory，内存性能测试；
  4. Oltp，测试要指定具体的lua脚本；lua脚本位于sysbench-0.5/sysbench/tests/db；
2. –mysql-db，用于指定执行基准测试的数据库名；
3. –mysql-table-engine，用于指定所使用的存储引擎；
4. –oltp-tables-count，执行测试的表的数量；
5. –oltp-table-size，指定每个表中的数据行数；
6. –max-time，指定最大的测试时间；
7. –report-interval，指定间隔多长时间输出一次统计信息；
8. –mysql-user，指定执行测试的MySQL用户；
9. –mysql-password，指定执行测试的MySQL用户的密码；
10. prepare，用于准备测试数据；
11. run，用于实际进行测试；
12. cleanup，用于清理测试数据；

sysbench基准测试示例

# 测试cpu
sysbench --test=cpu --cpu-max-prime=10000 run

# 输出结果如下
sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Prime numbers limit: 10000

Initializing worker threads...

Threads started!

CPU speed:
    events per second:   869.93

General statistics:
    total time:                          10.0001s
    total number of events:              8701

Latency (ms):
         min:                                    1.12
         avg:                                    1.15
         max:                                   12.88
         95th percentile:                        1.21
         sum:                                 9994.71

Threads fairness:
    events (avg/stddev):           8701.0000/0.00
    execution time (avg/stddev):   9.9947/0.00

# 测试文件IO， 文件总大小1G
# 准备数据，生成总大小为1G的多个文件
sysbench --test=fileio --file-total-size=1G prepare

# 输出如下
# sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)

# 128 files, 8192Kb each, 1024Mb total
# Creating files for the test...
# Extra file open flags: (none)
# Creating file test_file.0
# Creating file test_file.1
# Creating file test_file.2
# ......
# Creating file test_file.127
# 1073741824 bytes written in 8.51 seconds (120.26 MiB/sec).



# 开始测试,每隔1s输出一个统计信息，测试模式混合，包含随机读写和顺序读写
sysbench --test=fileio --num-threads=8 --init-rng=on --file-total-size=1G --file-test-mode=rndrw --report-interval=1 run

# 新版本会提示 --init-rng=on 无效

# 输出内容如下：
# WARNING: --num-threads is deprecated, use --threads instead
# sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)
# 
# Running the test with following options:
# Number of threads: 8
# Report intermediate results every 1 second(s)
# Initializing random number generator from current time
# 
# 
# Extra file open flags: (none)
# 128 files, 8MiB each
# 1GiB total file size
# Block size 16KiB
# Number of IO requests: 0
# Read/Write ratio for combined random IO test: 1.50
# Periodic FSYNC enabled, calling fsync() each 100 requests.
# Calling fsync() at the end of test, Enabled.
# Using synchronous I/O mode
# Doing random r/w test
# Initializing worker threads...
# 
# Threads started!
# 
# [ 1s ] reads: 54.35 MiB/s writes: 36.24 MiB/s fsyncs: 7384.73/s latency (ms,95%): 1.759
# [ 2s ] reads: 44.91 MiB/s writes: 29.87 MiB/s fsyncs: 6046.54/s latency (ms,95%): 1.996
# [ 3s ] reads: 44.09 MiB/s writes: 29.45 MiB/s fsyncs: 6127.80/s latency (ms,95%): 1.667
# [ 4s ] reads: 41.25 MiB/s writes: 27.42 MiB/s fsyncs: 5536.19/s latency (ms,95%): 1.759
# [ 5s ] reads: 43.12 MiB/s writes: 28.75 MiB/s fsyncs: 5871.15/s latency (ms,95%): 2.106
# [ 6s ] reads: 42.04 MiB/s writes: 28.11 MiB/s fsyncs: 5751.68/s latency (ms,95%): 1.791
# [ 7s ] reads: 40.48 MiB/s writes: 26.98 MiB/s fsyncs: 5631.02/s latency (ms,95%): 1.759
# [ 8s ] reads: 28.06 MiB/s writes: 18.70 MiB/s fsyncs: 3719.03/s latency (ms,95%): 1.791
# [ 9s ] reads: 48.80 MiB/s writes: 32.45 MiB/s fsyncs: 6660.93/s latency (ms,95%): 1.759
# [ 10s ] reads: 43.94 MiB/s writes: 29.36 MiB/s fsyncs: 6005.30/s latency (ms,95%): 1.925
# 
# File operations:
#     reads/s:                      2727.33
#     writes/s:                     1817.69
#     fsyncs/s:                     5906.41
# 
# Throughput:
#     read, MiB/s:                  42.61
#     written, MiB/s:               28.40
# 
# General statistics:
#     total time:                          10.1189s
#     total number of events:              104750
# 
# Latency (ms):
#          min:                                    0.00
#          avg:                                    0.76
#          max:                                  898.02
#          95th percentile:                        1.82
#          sum:                                79958.46
# 
# Threads fairness:
#     events (avg/stddev):           13093.7500/292.10
#     execution time (avg/stddev):   9.9948/0.00

扩展：什么是锁？
- 锁主要作用是管理共享资源的并发访问；锁用于实现事务的隔离性；
- 锁的分类
  - 共享锁（也称读锁）
  - 独占锁（也称写锁）
    
    同一个数据，可以多个同时读，但是若有其中一个在写入数据，其他都不可读、不可写；简而言之：读读可以，读写不行，写写不行；
    
    举例：两个客户端同时对某一张表的某一条记录进行操作，如果其中一个客户端对该数据进行事务操作，比如原来的数据为1，要修改为2，事务未提交，这时候另一个客户端读取的数据还是1，这个1是从在Undo Log中读取到的；
- 锁的粒度（被加锁的资源的最小单位，比如行级锁、页级锁、表级锁；粒度越小，并发越高）
  - 表级锁（MySQL服务器层实现）
    - 为某张表(如：tb_stu)增加表级锁（InnoDB引擎也行）: lock table tb.stu write;，这时候其他的客户端去查询的时候就会被阻塞，直到锁被释放unolock tables;；
    - 系统资源开销小，并发性能弱；
  - 行级锁（存储引擎层所实现）
    - 系统资源开销大，并发性能强；
- 阻塞和死锁
  - 阻塞
    - 阻塞是因为不同锁之间的兼容关系导致，在有些时刻，一个事务中的锁需要等待另一个事务中的锁释放，这就形成了阻塞；
    - 阻塞是为了事务的并发且正常运行，大量的阻塞表明系统出现了问题，可能是在一个频繁更新的表出现了慢查询；也可能是其他的一些操作，如表备份等，在一些频繁被访问的表上加了排它锁；
  - 死锁
    - 相互占用了对方阻塞的资源可能出现死锁的情况；
    - 系统可以自动识别少量死锁的出现，并回滚占用资源最少的事务，来解决死锁的情况；

posted on 2020-03-10 00:02 ixuer 阅读(900) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

ixuer