mysql服务优化参考

friht

　　http://www.cnblogs.com/xhyan/p/6530920.html
　　Mysql服务加速优化的6个阶段

• 　　硬件层面优化
  
• 　　操作系统层面优化
  
• 　　Mysql数据库层面优化
  
• 　　网站集群架构层面优化
  
• 　　安全优化
  
• 　　流程、制度控制优化
  

　　1.硬件层面优化

• 　　CPU          64CPU,至少2-4颗cpu，L2越大越好
  
• 　　MEMORY        96-128G跑3-4个实例；32-64，跑1-2个实例
  
• 　　DISK        SAS机械盘，数量越多越好
  

　　SSD(高并发)>sas(普通业务)>sata(线下)

• 　　RAID       RAID0> RAID10(推荐)> RAID5(少用)
  

　　主库Raid10.从库Raid5或Raid10

• 　　NETWORK      多网卡bond，buffer，tcp优化
  

　　千兆网线及千兆万兆交换机

• 　　数据库服务器数据IO密集型服务，尽量不要使用虚拟化
  

　　2.操作系统层面优化
　　操作系统及Mysql实例选择

• 　　一定选择x86_64位系统，推荐使用CentOS6.8，关闭NUMA特性
  
• 　　将操作系统喝数据分区分开
  
• 　　避免使用swap交换分区
  
• 　　避免使用软件磁盘阵列
  
• 　　避免使用LVM逻辑卷
  
• 　　删除服务器上未使用的安装包和守护进程
  

　　3.文件系统层优化

• 　　调整磁盘Cache mode
  

• 　　启用WCE＝1（write cache Enable）RCD＝0（Read cache Disable）模式 命令：sdparm －s WCE=1,RCD=0 -S /dev/sdb
  

• 　　采用Linux I/O scheuler算法deadline
  

• 　　采用deadline I/O调度起
  
• 　　deadline调度参数，对于Centos 建议：
  

　　read_expire = 1/2 write_expire,
　　echo 500>/sys/block/sdb/queue/iosched/read_expire
　　echo 1000>/sys/block/sdb/queue/iosched/write_expire

• 　　业务量很大建议采用xfs文件系统,业务量不是很大可采用ext4
  
• 　　mount挂载文件系统增加：async、noatime、nodiratime、nobarrier（不使用raid卡电池）等选项
  

　　4.Linux内核参数优化

• 　　将vm.swappiness设置为0.10
  
• 　　将vm.dirty_background_ratio设置为5-10,将vm.dirty_ratio设置为它的两倍左右，以确保能持续将脏数据刷新到磁盘，避免瞬间I/O写入，产生严重等待
  
• 　　优化tcp协议栈，
  

• 　　减少TIME_WAIT,提高tcp效率
  

　　net.ipv4.tcp_tw_recyle=1
　　net.ipv4.tcp_tw_reuse=1

• 　　减少处于FIN0Await-2连接状态等时间，是系统可以处理更多的连接
  

　　net.ipv4.tcp_fin_timeout=2

• 　　减少TCP KeepAlive连接侦测时间，是系统可以处理更多的连接
  

　　net.ipv4_tcp_keepalive_time=600

• 　　提高系统支持的最大SYN半连接数（默认1024）
  

　　net.ipv4.tcp_max_syn_backlog = 16384

• 　　减少系统SYN连接重试次数（默认5）
  

　　net.ipv4.tcp.synack_retries=1
　　5.系统网络优化

• 　　优化系统套接字缓冲区
  

　　net.core.rmem_max=16777216              #最大socket读buffer
　　net.core.wmem_max=16777216            #最大socket写buffer
　　net.core.wmem.default = 8388608
　　net.core.rmem.default = 8388608

• 　　优化TCP接受/发送缓冲区
  

　　net.ipv4.tcp_rmem=4096 87380 16777216
　　net.ipv4.tcp_wmem=4096 87380 16777216
　　net.ipv4.tcp_mem = 94500000 915000000 927000000

• 　　优化网络设备接收队列
  

　　net.core.netdev_max_backlog=3000
　　net.core.somaxconn = 32768

• 　　其他优化
  

　　net.ipv4.tcp_timestamps = 0
　　net.ipv4.tcp_max_orphans = 3276800
　　net.ipv4.tcp_max_tw_buckets = 360000
　　6.mysql数据库层面优化（my.cnf）

• 　　如果使用MyISAM引擎，需要key_buffer_size调大
  
• 　　建议设置default-storage-engine=InnoDB，强烈建议不要再使用MyISAM引擎。
  
• 　　调整innodb_buffer_pool_size的大小，如果是单实例且绝大多数是InnoDB引擎表的话，可考虑设置为物理内存的50% -70%左右。
  
• 　　设置innodb_file_per_table = 1，使用独立表空间。
  
• 　　调整innodb_data_file_path = ibdata1:1G:autoextend，不要用默认的10M,在高并发场景下，性能会有很大提升。
  
• 　　设置innodb_log_file_size=256M，设置innodb_log_files_in_group=2，基本可以满足大多数应用场景。
  
• 　　调整max_connection（最大连接数）、max_connection_error（最大错误数）设置，根据业务量大小进行设置。
  
• 　　另外，open_files_limit、innodb_open_files、table_open_cache、table_definition_cache可以设置大约为max_connection的10倍左右大小。
  
• 　　key_buffer_size建议调小，32M左右即可
  
• 　　建议关闭query cache功能或降低设置不要超过512M（前端使用redis或memcached）
  
• 　　mp_table_size、max_heap_table_size、sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等设置也不要过大。
  

　　7.Mysql语句优化
　　Mysql语句优化的多种思路

• 　　白名单机制－百度，项目开发，DAB参与，减少上线后的慢sql数量
  
• 　　抓出慢sql：配置my.cnf
  

　　long_query_time = 2
　　log-slow-queries=/data/3306/slow-log.log
　　log_queries_not_using_indexes
　　按天轮询slow-log.log日志
　　慢查询日志分析工具——mysqlsla或pt-query-digest（推荐）

• 　　定时分析慢查询，发到核心开封，DBA分析及高级韵味，CTO邮箱
  
• 　　定期使用pt-duplicate-key-checker检查并删除重复的索引
  
• 　　定期使用pt-index-usage工具检查并删除使用频率低低索引
  
• 　　使用pt-online-schema-change来完成达标的online ddl需求
  
• 　　搜索功能：like %baidu%，一般不要用Mysql数据主库
  
• 　　避免在整表上使用count(*)，他有可能导致锁表
  
• 　　使用连接join来代替子查询
  
• 　　多表连接查询时，把结果集笑的表作为驱动表
  
• 　　多表连接并且有排序时，排序字段必须时驱动表例的，否则排序列无法用到索引
  
• 　　使用explain及set profile优化sql语句
  

　　8.使用explain 优化sql语句

• 　　慢查询sql语句方法(紧急处理)
  

　　使用show full processlist;(登录数据库后现场抓，连续执行2次，超过2秒)
　　mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show full processlist;"|grep -vi "sleep"

• 　　记录并分析慢查询日志（日常处理）
  

　　配置my.cnf参数中记录慢查询语句
　　long_query_time = 2                                                       #查询语句超过2秒记录到日志
　　log_quries_not_using_indexes                                         #没有使用索引的查询记录到日志
　　log-slow-quries = /data/3306/slow.log                               #记录日志存放的路径
　　定时切割日志后使用mysqlsla分析
　　mv /data/3306/slow.log /opt/$(date +%F)_slow.log
　　mysqladmin -uroot -poldboy -S data/3306/mysql.sock flush-logs

• 　　explain语句检查索引执行情况
  

　　explain select * from test where name='oldboy'\G
　　通过对慢查询语句where后面的字段建立索引的条件建立索引（单索引及联合索引）
　　9.日常优化
　　由开发，DBA，总监等优化
　　使用profile优化sql语句
　　查看是否启用profile，如果值为0，表示没有启用
　　SELECT @@profiling;
　　+-------------+
　　| @@profiling |
　　+-------------+
　　|           0 |
　　+-------------+
　　1 row in set (0.00 sec)
　　打开profile功能
　　SET profiling = 1;
　　打开profile功能后执行一条查询语句
　　select count(user) from user;
　　+-------------+
　　| count(user) |
　　+-------------+
　　|           2 |
　　+-------------+
　　1 row in set (0.00 sec)
　　使用show profiles查看(可以看到每个执行细节所消耗的时间)
　　show profiles;
　　+----------+------------+------------------------------+
　　| Query_ID | Duration   | Query                        |
　　+----------+------------+------------------------------+
　　|       1 | 0.00007400 | select @@profiling            |
　　|       2 | 0.00016250 | select count(user) from user |
　　+----------+------------+------------------------------+
　　2 rows in set (0.00 sec)
　　查看sql占用cpu、io、内存等情况
　　show profile cpu,block io,memory,swaps for query 4;
　　+--------------------+----------+----------+------------+--------------+---------------+-------+
　　| Status             | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps |
　　+--------------------+----------+----------+------------+--------------+---------------+-------+
　　| starting           | 0.000057 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| Opening tables     | 0.000050 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| System lock        | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| init               | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| optimizing         | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| statistics         | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| preparing          | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| executing          | 0.000159 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| Sending data       | 0.000032 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| end                | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| query end          | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| closing tables     | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| removing tmp table | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| closing tables     | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| freeing items      | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| logging slow query | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　| cleaning up        | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |     0 |
　　+--------------------+----------+----------+------------+--------------+---------------+-------+
　　17 rows in set (0.00 sec)
　　10.网站集群架构优化

• 　　服务器上跑多实例，2-4个
  
• 　　主从复制一主五从，采用mixed模式，尽量不要夸机房同步（尽量远程写本地读）
  
• 　　定期pt-table-checksum、pt-table-sync来检查并修复mysql主从复制的数据差异
  
• 　　业务拆分：搜索功能一般不要用Mysql数据库，某些业务应用应使用nosql持久化存储，如memcached、redis、ttserver等
  
• 　　数据库前端必须加cache
  
• 　　动态的数据静态化（整个文件静态化，而非文件中的片段静态化）
  
• 　　数据库集群与读写分离。
  
• 　　选择从库进行备份
  
• 　　对数据库进行分库分表备份