Linux:MYSQL（十七）问题及解决方案

复制的问题和解决方案

(1) 数据损坏或丢失

Master： MHA + semi repl（MHA+半同步复制：提升令一个主并利用令半同步保证数据不丢书）
Slave： 重新复制

(2) 混合使用存储引擎(不考虑，直接用InnoDB)

MyISAM：不支持事务
InnoDB： 支持事务

(3) 不惟一的server id(不要犯这个错误，每个服务器都设置好唯一ID）

重新复制

(4) 复制延迟

需要额外的监控工具的辅助
一从多主:mariadb10版后支持
多线程复制：对多个数据库复制

性能衡量指标

数据库服务衡量指标：

qps: query per second 每秒钟查询次数
tps: transaction per second 每秒钟执行的事务次数

压力测试工具：

• mysqlslap
• Sysbench：功能强大
  https://github.com/akopytov/sysbench
• tpcc-mysql
• MySQL Benchmark Suite
• MySQL super-smack
• MyBench

MYSQL压力测试

• Mysqlslap：来自于mariadb包，测试的过程默认生成一个mysqlslap的schema,生成测试表t1，查询和插入测试数据，mysqlslap库自动生成，如果已经存在则先删除。用–only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹
• 使用格式：mysqlslap [options]
• 常用参数 [options] 说明：

  --auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力
  
  --auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)
  
  --auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持
  
  --number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1
  
  --number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1
  
  --number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
  
  --query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的存储过程或者sql语句来执行测试
  
  --create-schema 代表自定义的测试库名称，测试的schema
  
  --commint=N 多少条DML后提交一次	
  
  --compress, -C 如服务器和客户端都支持压缩，则压缩信息
  
  --concurrency=N, -c N 表示并发量，即模拟多少个客户端同时执行select。可指定多个值，以逗号或者--delimiter参数指定值做为分隔符
  
  --concurrency=100,200,500
  
  --engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开。例如：--engines=myisam,innodb
  
  --iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次
  
  --only-print 只打印测试语句而不实际执行。
  
  --detach=N 执行N条语句后断开重连
  
  --debug-info, -T 打印内存和CPU的相关信息
  

mysqlslap示例

• 单线程测试
  mysqlslap -a -uroot -pdushan

• 多线程测试。使用–concurrency来模拟并发连接
  mysqlslap -a -c 100 -uroot -pdushan

• 迭代测试。用于需要多次执行测试得到平均值
  mysqlslap -a -i 10 -uroot -pdushan
  mysqlslap —auto-generate-sql-add-autoincrement -a
  mysqlslap -a --auto-generate-sql-load-type=read
  mysqlslap -a --auto-generate-secondary-indexes=3
  mysqlslap -a --auto-generate-sql-write-number=1000
  mysqlslap --create-schema world -q "select count(*) from City”
  mysqlslap -a -e innodb -uroot -pdushan
  mysqlslap -a --number-of-queries=10 -uroot -pdushan

mysqlslap示例

• 测试同时不同的存储引擎的性能进行对比(对myisam和innodb测试同时并发50和100情况下）
  mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -pdushan

• 执行一次测试，分别50和100个并发，执行1000次总查询
  mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -pdushan

• 50和100个并发分别得到一次测试结果(Benchmark)，并发数越多，执行完所有查询的时间越长。为了准确起见，可以多迭代测试几次
  mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -pdushan

生产环境my.cnf配置示例

• 硬件：内存32G

innodb_file_per_table = 1
打开独立表空间

max_connections = 8000
#MySQL 服务所允许的同时会话数的上限，经常出现Too Many Connections的错误提示，则需要增大此值

back_log = 300
#back_log 是操作系统在监听队列中所能保持的连接数

max_connect_errors = 1000
#每个客户端连接最大的错误允许数量，当超过该次数，MYSQL服务器将禁止此主机的连接请求，直到MYSQL服务器重启或通过flush hosts命令清空此主机的相关信息

open_files_limit = 10240
#所有线程所打开表的数量

max_allowed_packet = 32M
#每个连接传输数据大小.最大1G，须是1024的倍数，一般设为最大的BLOB的值

wait_timeout = 10
#指定一个请求的最大连接时间

sort_buffer_size = 16M
# 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序

join_buffer_size = 16M
#不带索引的全表扫描.使用的buffer的最小值

query_cache_size = 128M
#查询缓冲大小

query_cache_limit = 4M
#指定单个查询能够使用的缓冲区大小，缺省为1M

transaction_isolation = REPEATABLE-READ
# 设定默认的事务隔离级别

thread_stack = 512K
# 线程使用的堆大小. 此值限制内存中能处理的存储过程的递归深度和SQL语句复杂性，此容量的内存在每次连接时被预留.

log-bin
# 二进制日志功能

binlog_format=row
#二进制日志格式

innodb_buffer_pool_size = 24G
#InnoDB使用一个缓冲池来保存索引和原始数据, 可设置这个变量到服务器物理内存大小的80%

innodb_file_io_threads = 4
#用来同步IO操作的IO线程的数量

innodb_thread_concurrency = 16
#在InnoDb核心内的允许线程数量，建议的设置是CPU数量加上磁盘数量的两倍

innodb_log_buffer_size = 16M
# 用来缓冲日志数据的缓冲区的大小

innodb_log_file_size = 512M
在日志组中每个日志文件的大小

innodb_log_files_in_group = 3
# 在日志组中的文件总数 ,默认2个

innodb_lock_wait_timeout = 120
# SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间

long_query_time = 2
#慢查询时长，默认10秒

log-queries-not-using-indexes
#将没有使用索引的查询也记录下来，即使不到时间，没有利用索引的也可能可以优化的

MYSQL配置最佳实践

• 高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”

• 参考：
  阿里巴巴Java开发手册
  58到家数据库30条军规解读
  http://zhuanlan.51cto.com/art/201702/531364.htm