mysql softerware + hardware

最新推荐文章于 2019-05-26 18:37:00 发布

原创最新推荐文章于 2019-05-26 18:37:00 发布 · 555 阅读

CC 4.0 BY-SA版权

本文深入探讨了MySQL性能优化的关键技术，包括日志管理、主从复制、数据一致性、缓存策略、复制延迟管理、高可用性设计，以及硬件优化、备份与恢复策略等。重点介绍了如何通过调整配置参数、合理利用索引、优化查询语句和采用高效的数据存储策略，显著提升MySQL系统的读写性能和数据一致性。

=================software=================================

tool explainshow 4 log 4 mysiam 6 innodb 5 skip 3 cache size 6

+数据：位置一致性日志复制恢复内存基础工具 mysql服务器变量 innodb变量

http://learner81.lofter.com/post/1d3ede11_782745a

--tool:

mysqladmin mysqlcheck mysql mysqldump mysqlbinlog mysqlimport http://qing.blog.sina.com.cn/1198551493/477071c5330051pu.html

Percona ToolKit+innotop Nagios+Cacti sphinx copy gzip+nc/ssh+rsync

--EXPLAIN:

select_type 【simple primary union dependent union union result subquery dependent subquery derived】

table#哪张表

type 【All--所有数据 index--索引树 range--区域查询 ref eq_ref--最好的连接表类型 const/system--常量 NULL】#性能提升

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL（优-->差）

possible_keys #可能用到的索引

key #实际使用的索引

key_len #索引长度

ref #索引的哪一列用到了

rows #返回请求数据的行数

filtered

Extra【Distinct Not exists Using index Using where Using temporary Using filesort】

--show:

show variables #运行中的变量

show status like #统计信息

show global status

show processlist#用这个命令看哪个sql语句占用资源比较多，进程列表信息

--log 4

log-error = /usr/local/mysql/log/error.log //错误日志
log = /usr/local/mysql/log/mysql.log //通用查询日志
long_query_time = 2 //慢查询时间
log-slow-queries = /usr/local/mysql/log/slowquery.log//慢查询日志

--MyISAM 6

myisam_max_sort_file_size = 100G mysql重建索引时允许使用的临时文件最大大小

myisam_sort_buffer_size = 68M #排序缓冲区大小

key_buffer_size = 54M #缓存索引

read_buffer_size = 64K #随机读(查询操作)缓冲区大小

read_rnd_buffer_size = 256K #类似于read_buffer_size选项，但针对的是按某种特定顺序(比如使用了ORDER BY子句的查询)输出的查询结果(默认设置是256K)

sort_buffer_size = 256K connection级参数（为每个线程配置），500个线程将消耗500*256K的sort_buffer_size。

--InnoDB 5

innodb_flush_log_at_trx_commit = 2 #是每一次事务提交或事务外的指令都需要把日志刷写到硬盘，设为2就是不写入硬盘而是写入系统缓存

innodb_log_buffer_size = 2M # log 缓存大小

innodb_buffer_pool_size = 105M #存入索引、数据和缓冲

innodb_log_file_size = 53M #日志信息，用于mysql crash

innodb_thread_concurrency = 8 #并发线程处理参数

#前端有100个连接，发来1000个sql，如果这个参数被设置成2。那么这1000个sql中，最多只有2个sql在innodb内核运行。其它都得等。

#skip 3

skip-locking #防止mysiam外部锁定

skip-name-resolve #只能用ip访问，不能用主机名访问，提高外网速度

skip-networking #只能内部访问，禁止外部访问


back_log = 384 #回答新请求之前的短时间内多少个请求可以被存在堆栈中

max_allowed_packet = 4M #防止大数据不能插入

thread_stack = 256K #堆栈大小

max_connections = 768 #最大连接数

wait_timeout = 10 #等待时间

thread_concurrency = 8 #多个CPU

#cache+size 6

join_buffer_size = 8M #联表缓存

table_open_cache = 256 #打开表缓存

thread_cache_size = 64 #线程缓存

query_cache_size = 64M #查询缓存

tmp_table_size = 256M #临时表缓存

sort_buffer_size = 32M#排序缓存

比率:

   1 连接数    mysql> show variables like 'max_connections';
   max_used_connections / max_connections * 100% = 99.6% （理想值 ≈ 85%）

  5, 查询缓存(query cache) mysql> show variables like 'query_cache%';
  query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，如果都是小数据查询，容易造成内存碎片和浪费。

  查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。

  查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明 query_cache_size可能有点小，要不就是碎片太多。

  查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%
示例服务器查询缓存碎片率＝ 20.46％，查询缓存利用率＝ 62.26％，查询缓存命中率＝ 1.94％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。

   2, key_buffer_size    MyISAM表性能影响最大
mysql> show variables like 'key_buffer_size';
计算索引未命中缓存的概率： key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% = 0.27% 需要适当加大key_buffer_size

mysql> show global status like 'key_blocks_u%';
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 18% （理想值 ≈ 80%）

   3，临时表
mysql> show global status like 'created_tmp%';
   Created_tmp_disk_tables / Created_tmp_tables * 100% ＝ 99% （理想值<= 25%）

4, open table 的情况
mysql> show global status like 'open%tables%';
   Open_tables / Opened_tables * 100% = 69% 理想值（>= 85%）
Open_tables / table_cache * 100% = 100% 理想值 (<= 95%)

   6.文件打开数(open_files) mysql> show global status like 'open_files';
比较合适的设置： Open_files / open_files_limit * 100% <= 75％正常

   7. 表锁情况 mysql> show global status like 'table_locks%';

Table_locks_immediate / Table_locks_waited > 5000

8. 表扫描情况 mysql> show global status like 'handler_read%'; mysql> show global status like 'com_select';
表扫描率＝ Handler_read_rnd_next / Com_select
如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB。

复制流程：

主库锁表 flush tables with readlock

备库只读 read only

主库二进制日志的位置show-master-status

备库延迟 heartbeat record

在备库上执行SELECT MASTER POST_WAIT()阻塞

主备一致 pt-table-checksum

主备切换 change master to,计划内外--选备库

问题：日志+主备库+复制+数据

主库关闭--sync_binlog

备库关闭通过pt-slave-restart重启

重启备库：执行STOP SLAVE,检查变量，关闭备库。

如果innodb事务日志和二进制日志不同步,而中继日志也没有，没办法

二进制日志损坏，通过mysqlbinlog获取偏移量

中继日志：CHANGE_MASTER_TO丢弃并重新获取损坏事件

复制延迟：读写分离写备库 trx_commit=2 单独归档

复制中断：不要在主库创建备库没有的表

数据不一致:innodb_lock_unsafe_for_binlog=1会导致数据不一致，提交和执行顺序不一致

配置：

innodb_flush_logs_at_trx_commit=2

innodb_support_xa=1

innodb_safe_binlog

log_bin=绝对路径/mysql-bin

==================================

relay_log=绝对路径/relay-bin

skip_slave_start//防止备库崩溃修复后自动同步

read_only//权限控制

文件：

mysql-bin.index（识别二进制文件）

mysql-relay-bin-index(中继日志的索引文件) master.info（备库连接到主库所需要的信息）

relay-log.info(备库查找从主库开始的位置) expire_logs_days(日志过期清理)

扩展：表名分片号库名分片号库名分片号+表名分片号；固定取模，动态存储分片号；负载：随机轮询权重最少连接最快响应哈希权重，web负载和mysql负载均衡

高可用性

--提高时效时间（应该把InnoDB当做默认存储引擎，禁用查询缓存、复制完整性-同步复制、备库只读、mysql审查，归档，服务器配置、参数和性能监控）

--缩短恢复时间，,冗余+故障转移（a出问题->替换成b->修复a->替换成a）,故障转移主要是修复，比负载均衡多了替换的功能,用双主结构

故障转移：主备切换 IP接管中间件代理应用程序

硬件

类型：CPU 内存硬盘 | iostat vmstat

固态存储优化：增加InnoDBd I/O容量--innodb_io-capacity ，和日志文件（单独存放），配置刷新算法--innodb_flush_neighbor_pages=0，禁用预读，禁用双写缓冲，限制插入缓冲，禁用DNS

备份
分类--备份方式（完全+增量+差异备份）| 备份内容（复制配置+服务器配置+系统文件）
修复--二级（optimize table）聚集（innodb-force_recovery）系统结构（SELECT INTO OUTFILE）
删除-- expire_log_days清除,PURGE MASTER LOGS BEFORE CURRENT_DATE - INTERVAL N DAY执行。
读取--（整体和时间点--恢复时间,恢复点--备份频次|快照LVM）
工具--mysqldump --opt --datebases + --lock-all-tables --single-transaction --master-data | XtraBackup
脚本--连接停启锁日志数据

整体恢复：载入备份文件（加载SQL文件和加载符号分隔文件）并检查和重放二进制日志（HOW TABLE STATUS检测错误日志）
时间点恢复：停掉mysql，恢复备份数据，更改mysql连接，备库读取日志服务器的二进制日志，使其成为日志服务器备库，用mysqlbinlog | grep检查日志，START SLAVE UNTIL重放至问题语句，SET GLOBAL SQL_SLAVE_SKIP_CONUTER=1跳过问题语句，START SLAVE备库执行完中继日志。

=============hardware======================

最常见的瓶颈是CPU、内存和I/O资源，MySQL倾向更快的CPU而不是更多，比如复制是单线程的，不会利用多核来执行；增加内存是解决I/O的方法，多次写入、一次刷新以及I/O合并把随机I/O转化成连续I/O，合理的内存和硬盘比例要看工作集大小。

固态存储优化mysql，增加InnoDBd I/O容量--innodb_io-capacity ，增加InnoDB日志文件，禁用预读，配置刷新算法--innodb_flush_neighbor_pages=0，禁用双写缓冲，限制插入缓冲，日志文件单独存放，备库如果延迟大也可以用

mysql文件存放，数据和日志分开存放，（数据和索引事务日志二进制日志常规日志临时文件），网络配置开启skip_name_resolve

选择操作系统更多要考虑故障恢复时间，状态监控命令有vmstat iostat等，如通过vmstat能区分CPU密集-us列 I/O密集--wa列内存交换密集--swpd列空闲机器--id列，同时配合iostat显示硬盘使用率--%util列，本质上，写入可以异步，但读取是同步的，系统能满足大量写入需求，却无法满足读请求

原因转移：由于内存不足导致I/O频繁，所以要查找主要原因

顶