MySQL性能调优

最新推荐文章于 2024-05-30 14:37:38 发布

Echo_Blingbling

最新推荐文章于 2024-05-30 14:37:38 发布

阅读量831

点赞数 1

文章标签： mysql性能调优 MySQL参数

一.概述
作为DBA，数据库性能是我们工作中的重点，性能调优主要从Memory（内存）, DB settings（数据库设计）, I/O, Indexes（索引）等几方面下手。
二.找到问题或瓶颈

查看历史数据
没有历史数据，但是数据是可预见或者周期性的，则可以模拟出数据
使用以下命令可以帮我们发现一些问题
vmstat / dstat ：查看内核线程、虚拟内存、磁盘、陷阱和 CPU 活动的统计信息。由 vmstat 命令生成的报告可以用于平衡系统负载活动。系统范围内的这些统计信息（所有的处理器中）都计算出以百分比表示的平均值，或者计算其总和。即报告虚拟内存统计信息。
iostat：用于报告中央处理器（CPU）统计信息和整个系统、适配器、tty 设备、磁盘和 CD-ROM 的输入/输出统计信息。
top：监视进程和Linux整体性能。
三.性能调优过程
measure（测量出限制性能的资源），think（思考问题产生的原因），change（对症调整），test（测试调整效果），implement（实施应用）
注：一次只改一个
1.Measure:找到受限资源
I/O，内存，CPU，网络带宽

2.Think：思考资源为何受限
• I/O
– 谁导致了I/O性能弱?
– 是读操作还是写操作致使I/O变弱?
– 还是随意I/O或顺序I/O?
• Memory
– 数据库大小
– 或者是其他原因导致内存不够?
• CPU
– 谁在燃烧CPU?
– 谁导致CPU处理能力降低?
• Network bandwidth
– 谁导致了网络带宽慢?
– Sniff traffic?
3.Change：对症调整

哪些是可以改变的？
• Hardware硬件 -> I/O系统 (RAID5), RAM, CPU, NW（网络）
• O/S -> do not touch (kernel upgrade) 升级内核
• DB数据库 -> my.cnf 修改配置文件
• Application应用 -> Queries!!! 请求
3.1调整硬件
• 内存越大越好
• 如果CPU是瓶颈的话，则提高CPU处理速度
–RAID5不适用于数据库 !!!
– RAID10更适用于数据库
– 许多线程
– Battery buffered I/O system cache???
• 1 Gbit Network?
• 不要考虑在虚拟服务器上搭建数据库服务器!!!
3.2调整O/S
• 使用主流的O/S
• 使用(> 4 GB RAM)的64位数据结构
• 使用 (>= 2.6.12)版本的内核kernel
• 使用主流的文件系统 -> ext4 或 xfs
–> 但是关于O/S不能改变太多，因为它本身已经近乎最优状态。
3.3调整MySQL
3.3.1MySQL的体系结构

3.3.2MySQL的性能特点
• 魔性缓存:懒，会想尽办法少干活！！！
• 性能特点:
– Thread cache线程缓存
– Query cache 请求缓存
– Prepared statements 预编译语句
– Stored Procedures 存储过程
– delayed INSERT (MyISAM only)延迟插入，仅适用于Myisam
3.3.3MySQL的数据库设置
• 非常重要的3个参数
– key_buffer_size
– innodb_buffer_pool_size
– innodb_log_file_size
• 另外2个参数: query_cache_size, thread_cache_size
• 建议:
– 以上5个参数使用默认值
–除非已经被验证是有效的，否则不要修改
3.3.4对MySQL调优
在my.cnf里修改或添加参数；
参数查看命令：>show global status;
3.3.4.1针对以上非常重要的3个参数
对Myisam影响较大的5个参数设置建议：
key_buffer_size = 一般设为RAM内存的25-33%
Key_blocks_unused 实际值（actual value）
Key_blocks_used 高水位线（high water mark ）
Key_reads / Key_read_requests <= 1% 比较合理
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
关于这方面的详解可点击此处参考

key_buffer_size ：用于索引的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，但该参数值设置的过大反而会使服务器整体效率降低。key_buffer_size 对MyISAM表性能影响很大。
Key_read_requests：从缓存读取索引的请求次数。
Key_reads：从磁盘读取索引的请求次数。
Key_blocks_unused：未使用的缓存簇(blocks)数
Key_blocks_used：曾经用到的最大的blocks数

对innodb性能影响较大的5个参数：
innodb_buffer_pool_size = 内存RAM的80%
Innodb_buffer_pool_pages_free 空页数
Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests <= 1% 比较合适
innodb_log_file_size = 32 -128 Mbyte
Innodb_os_log_pending_fsyncs 当前挂起的 fsync 日志文件数

innodb_flush_method控制innodb数据文件及redo log的打开、刷写模式，有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT：
fdatasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。
O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成
O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲
innodb_additional_mem_pool_size设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，一般使用默认值。
| Innodb_buffer_pool_pages_data | 496 |包含数据的页数(脏或干净)。
| Innodb_buffer_pool_pages_dirty | 5 |当前的脏页数
| Innodb_buffer_pool_pages_flushed | 1182773 |已经flush的页面数
| Innodb_buffer_pool_pages_free | 0 |空页数
| Innodb_buffer_pool_pages_misc | 16 |优先用作管理的页数
| Innodb_buffer_pool_pages_total | 512 |总页数
| Innodb_buffer_pool_read_ahead_rnd | 515979 |随机预读的次数(读大部分数据时)
| Innodb_buffer_pool_read_ahead_seq | 3408867 |顺序预读的次数(全表扫描时)
| Innodb_buffer_pool_read_requests | 10760142502 |InnoDB已经完成的逻辑读请求数
| Innodb_buffer_pool_reads | 6912521 |从磁盘上一页一页的读取的页数，从缓冲池中读取页面，但缓冲池里面没有，就会从磁盘读取
| Innodb_buffer_pool_wait_free | 0 |缓冲池等待空闲页的次数，当需要空闲块而系统中没有时，就会等待空闲页面
| Innodb_buffer_pool_write_requests | 8136890 |缓冲池总共发出的写请求次数
| Innodb_data_fsyncs | 1457304 |fsync()操作数
| Innodb_data_pending_fsyncs | 0 |innodb当前等待的fsync次数
| Innodb_data_pending_reads | 0 |innodb当前等待的读的次数
| Innodb_data_pending_writes | 0 |innodb当前等待的写的次数
| Innodb_data_read | 1009745694720 |总共读入的字节数
| Innodb_data_reads | 11756800 |innodb完成的读的次数
| Innodb_data_writes | 2308122 |innodb完成的写的次数
| Innodb_data_written | 40737600000 | 总共写出的字节数
| Innodb_dblwr_pages_written | 1182773 |双写已经写好的页数
| Innodb_dblwr_writes | 76828 |已经执行的双写操作数量
| Innodb_log_waits | 10 |因为日志缓冲区太小，我们在继续前必须先等待对它清空
| Innodb_log_write_requests | 2979109 |日志写请求数
| Innodb_log_writes | 1252587 |向日志文件的物理写数量
| Innodb_os_log_fsyncs | 1304054 |向日志文件完成的fsync()写数量
| Innodb_os_log_pending_fsyncs | 0 |挂起的日志文件fsync()操作数量。
| Innodb_os_log_pending_writes | 0 |挂起的日志文件写操作。
| Innodb_os_log_written | 1954254848 |写入日志文件的字节数
| Innodb_page_size | 16384 |编译的InnoDB页大小(默认16KB)
| Innodb_pages_created | 58540 |创建的页数
| Innodb_pages_read | 61630057 |从buffer_pool中读取的页数
| Innodb_pages_written | 1182773 |写入的页数
| Innodb_row_lock_current_waits | 0 |当前等待的待锁定的行数
| Innodb_row_lock_time | 595899 |行锁定花费的总时间，单位毫秒
| Innodb_row_lock_time_avg | 54 |行锁定的平均时间
| Innodb_row_lock_time_max | 7241 |行锁定的最长时间
| Innodb_row_lock_waits | 11032 |一行锁定必须等待的时间数
| Innodb_rows_deleted | 7199 |删除
| Innodb_rows_inserted | 736893 |插入
| Innodb_rows_read | 10400853035 |从InnoDB表读取的行数
| Innodb_rows_updated | 932768 |更新

3.3.4.2请求缓冲Query cache
query_cache_size = 32 -128 Mbyte (caution: 512!)指定MySQL查询缓冲区的大小
Qcache_total_blocks 已经分配好的块个数
Qcache_free_blocks 已经分配了块，但是尚未存储数据
Qcache_free_memory 没有被申请划分为数据块的部分（考虑分片处理）
Qcache_hits 缓存命中次数
Qcache_inserts执行向缓存中插入缓存对象的次数，Qcache_hits/Qcache_inserts>>2:1较理想
Qcache_lowmem_prunes因为内存太小不能继续存储的对象个数, 如果值比较大，表示查询缓存区大小设置太小，需要增大。

1.hits rate(命中率) = Qcache_hits/(Qcache_hits+Com_select)，不过，这个未必能反应真实情况！命中率>30%就可以认为有效
2. 命中和写入的比率=Qcache_hits/Qcache_inserts，比例大于3:1时通常查询缓存是有效的，能达到甚至大于10:1就更好了。

3.3.4.3线程缓冲 Thread cache
thread_cache_size = 8 -128（单纯从物理内存考虑，一个内存对应8，16个内存对应128，对于较大的web项目，可在相应递增一个内存计算，简单考虑可直接将此值设与Threads_connected相等
Threads_cached 当前线程缓存中空闲线程数
Threads_created从最近一次服务启动，已创建线程的数量。不应随着时间推移增长太多。
Threads_connected 当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数

3.3.4.4日志相关参数
sync_binlog = 0
innodb_flush_log_at_trx_commit = 2
log_slow_queries=on 开启慢日志

innodb_flush_log_at_trx_commit是将事务日志从innodb log buffer写入到redo log中，sync_binlog是将二进制日志文件刷新到磁盘上。
innodb_flush_log_at_trx_commit = N：
N=0 每隔一秒，把事务日志缓存区的数据写到日志文件中，以及把日志文件的数据刷新到磁盘上；
N=1 每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上；
N=2 每事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不一定刷新到磁盘上，而是取决于操作系统的调度；
sync_binlog = N：
N>0 每向二进制日志文件写入N条SQL或N个事务后，则把二进制日志文件的数据刷新到磁盘上；
N=0 不主动刷新二进制日志文件的数据到磁盘上，而是由操作系统决定；
推荐配置组合：
N=1,1 — 适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如充值消费系统；
N=1,0 — 适合数据安全性要求高，磁盘IO写能力支持业务不富余，允许备库落后或无复制；
N=2,0或2,m(m介于0-100) — 适合数据安全性有要求，允许丢失一点事务日志，复制架构的延迟也能接受；
N=0,0 — 磁盘IO写能力有限，无复制或允许复制延迟稍微长点能接受，例如：日志性登记业务；
注：当两个参数设置为双1的时候，写入性能最差，sync_binlog=N(N>1)innodb_flush_log_at_trx_commit=2 时，(在当前模式下)MySQL的写操作才能达到最高性能。
数据安全性：
当innodb_flush_log_at_trx_commit和sync_binlog 都为1时是最安全的，在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，都为1会导致频繁的IO操作，因此该模式也是最慢的一种方式。
当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。
双1适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如订单,交易,充值,支付消费系统。双1模式下，当磁盘IO无法满足业务需求时，推荐的做法是innodb_flush_log_at_trx_commit=2 ，sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache，防止系统断电异常。

3.3.4.5表缓存Table cache
table_cache = 64 -2048(5.1.3及以后版本又名TABLE_OPEN_CACHE) 作用是缓存表文件描述符，降低打开关闭表的频率。如果Opened_tables远大于Open_tables，并且Open_tables很接近table_cache，那么就说明table_cache偏小。
Open_tables当前打开的table总和，即所有connection打开的table总数，应设置为<= table_cache
Opened_tables打开过的表的数量总和

4.调整应用Application
Binlog_cache_disk_use（事务类）二进志日志缓存的已经存在硬盘的条数
Innodb_log_waits 因日志缓存太小而必须等待其被写入所造成的等待数
max_heap_table_size = 16 - 256 Mbyte 定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值
tmp_table_size = 32 - 512 Mbyte规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）
Created_tmp_disk_tables 内部基于磁盘的临时表的总数
Created_tmp_tables创建在内存中的临时表的总数一般的比例关系是:Created_tmp_disk_tables/Created_tmp_tables<5%
sort_buffer_size = 2 -16 Mbyte 是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。
Sort_merge_passes 磁盘排序归并次数，减少sort_buffer_size大小会显著减少Sort_merge_passes值，并且临时文件也会变少

Echo_Blingbling

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL性能调优

一.概述作为DBA，数据库性能是我们工作中的重点，性能调优主要从Memory（内存）, DB settings（数据库设计）, I/O, Indexes（索引）等几方面下手。二.找到问题或瓶颈查看历史数据没有历史数据，但是数据是可预见或者周期性的，则可以模拟出数据使用以下命令可以帮我们发现一些问题 vmstat / dstat ：查看内核线程、虚拟内存、磁盘、陷阱...
复制链接

扫一扫