[sql]大型网站MySQL深度优化揭秘

第1章优化的思路和线路

1.1 网站优化的思路 2

1.2 MySQL优化，nginx这样的东西怎么优化？

第2章硬件层面优化

2.1 数据库物理机

2.1.1 CPU

2.1.2 Memory

2.1.3 disk(磁盘IO)

2.1.4 RAID阵列

2.1.5 网卡

2.1.6 案例

2.2 硬件调整

2.2.1 BIOS调整提高CPU性能

2.2.2 阵列卡调整

第3章软件层面优化

3.1 操作系统

3.2 文件系统层优化

3.3 内核层面优化

第4章 MySQL层面优化

4.1 my.cnf参数的优化

4.2 库和表的设计优化

4.3 SQL语句的优化

4.3.1 抓出来慢查询

4.3.2 每天生成slow.log

4.3.3 尽量不用子查询,用join替代

第5章网站集群架构上来优化数据库

5.1 服务器跑多实例2-4个

5.2 业务拆分:

5.3 数据库前端加缓存

5.4 备份策略

第6章流程制度安全

大型网站MySQL深度优化揭秘

优化的思路和线路

网站优化的思路

解答：从用户打开浏览器，到返回网页内容这个过程优化每一个细节

MySQL优化，nginx这样的东西怎么优化？

建议：根据OSI 7层模型，从下到上。。。以这个为线路

硬件层面优化

数据库物理机

CPU

64位的CPU,服务器2-16,CPU 一般2-4颗 L1,L2越大越好

Memory

48G-96G-128G-256G

48G 2-3个实例

96G 3-4个实例

disk(磁盘IO)

数据库是IO密集应用

机械盘

SAS(不选择SATA),300G*12块,磁盘数量越多IO越高,SAS 15k的硬盘

SSD

测试对比:SAS单盘随机IO,3000IOPS, SSD单盘随机IO达到上万

RAID阵列

选硬件 RAID (0>10>5>1)

网卡

至少千兆(bond), 万兆交换机

数据库服务器尽量不用虚拟化

SLAVE服务器配置最好是大于等于Master

从库会接替主库,从库配置太低，导致延迟

案例

百度:IBM服务器,内存96G-128G, CPU48核心

SINA:dell r510 内存48G,磁盘300*12块, RAID10

硬件调整

BIOS调整提高CPU性能

打开(DAPC)模式,发挥CPU性能

启动Node Interleaving避免NUMA问题

关闭C1E和State等

阵列卡调整

配置CACHE和BBU模块(机械盘)

写策略(always write back)

不要用(wt)策略

关闭阵列预读策略

软件层面优化

操作系统

选择x86_64位系统

系统盘和数据盘分开

极端情况下不分swap分区

避免使用操作系统的软raid

避免使用LVM

专库专用不要跑(LNMP,TOMCAT)

文件系统层优化

调整Cache mode

启动wce=1(Write Cache Enable)

RCD=0(Read Cache Disable)

系统调度算法默认cfq(比较中庸),数据库选择noop,deadline.针对deadline可以调节参数(内核参数)

Centos 6.8 默认ext4可以作为数据库的文件系统,房屋呢量大的话,XFS就更好

Centos7默认也选择了XFS,调整XFS日志,缓冲参数.

mount参数很重要 –o async,noatime,nodirname,nobarrier等

内核层面优化

参考链接:http://oldboy.blog.51cto.com/2561410/1336488
vm.swappiness设置为0,或者0-5,让数据库尽量不使用swap

vm.dirty_background_ratio设置5-10,vm.dirty_ratio设置前面的2倍.持续将系统数据刷到磁盘.
参考链接:http://blog.sina.com.cn/s/blog_448574810101k1va.html

减少time_wait
net.ipv4.tcp_tw_recyle=1,net.ipv4.tcp_tw_reuse=1,
net.ipv4.tcp_fin_timeout=2,net.ipv4.tcp_keepalived_time=600

MySQL层面优化

参考链接:http://oldboy.blog.51cto.com/2561410/1726517

my.cnf参数的优化

如果我们采用myisam引擎,key_buffer_size加大.采用innodb

推荐使用innodb, 5.5.5以后默认都是innodb引擎

innodb_buffer_pool_size, 调整为内存的50%,单实例.多实例各25%

innodb_flush_log_at_trx_commit, sync_binlog, 设置为1, 数据可以丢失的话(不重要),可以设置为0, 从库都设置为0,事物的log,多长时间刷入到硬盘里

使用独立表空间. innodb_file_per_table=1. 默认共享表文件效率低

innodb_log_file_size=256M 不要给过大

log_query_time=1 ,log的日志查询,超过1秒的SQL语句,记录到日志里,回头看这个日志,进行优化.

一些session参数,不要设置过大,一个连接就会占用参数设置的大小.不要给过大

Sort_buffer_size, join_buffer_size,read_buffer_size,tmp_table_size,max_heap_table_size这类参数都是session,级别参数.2M6M8M就可以了.

查询缓存参数要设置小一些:query_cache_size = 64M,想要缓存,前端加mc,redis

库和表的设计优化

字符集UTF-8

固定字符串的内容,可以选择char

数据库都要给一个自增的主页,没什么用途.

字段长度,在满足要求前提下,最短的.Varchar(16)

省份,性别,这样内容字段可以设置ENUM类型,mysql系统表(char,ENUM)

尽可能不用text/blob比较大的字段类型(博文帖子),如果使用的话,可以放到子表里

一般针对字段索引,尽量采用字段的前N个字符索引,不要整个字段索引,效率低

多用联合索引,有前缀特性,少用独立索引,性别列,不要建立索引了.效果差

SQL语句的优化

索引优化(运维最常用的)

抓出来慢查询

百度:白名单的方法,设计程序时参与设计,程序上线连接数据库,有个控制查库的东西,请示放我库里,才能查询,数据库没有或者减少慢查询

经常给开发培训,DB水平更高

现在网站慢了,show full processlist; 抓慢查询,连续执行两下,间隔1-2秒,如果还有,怀疑他是慢查询.

日常:把慢查询语句记录到log里面

my.cnf

long_query_time=2 超过两秒的查询
log_queryies_not_using_indexes没有超过两秒没有走索引
log_slow_queries=/data/3306/slow.log

每天生成slow.log

按天切割slow.log,切割后分析软件分析(mysqlsla,-pt-query-digest)

mysqldump slow,myprofi. 优化的语句,不一定是单条占用时间长的,频率搞,单条不长,但是总时间很长的,这些可能也是优化的重点.

对于运维来讲,慢查询SQL发给开发.

有能力和开发一起搞

用explain测试语句是否走索引,set profile深度查看语句执行情况.

检查删除重复的索引,工具pt-duplicate-key-checker

效率很低的索引,检查删除,pt-index-usage工具

尽量不用子查询,用join替代

数据库是存放数据的地方,不是计算数据的地方,计算放在web

搜索功能,like "%daf%",不用数据库搜索

在语句中尽量去掉in or <> 字符

网站集群架构上来优化数据库

参考链接:http://oldboy.blog.51cto.com/2561410/775056

服务器跑多实例2-4个

主从复制最多9个,建议1主5从, 主库采用mixed模式,不要跨机房复制(如果是,远程写,本地读

业务拆分:

搜索功能,like "%daf%",不用数据库搜索

搜索软件：Sphinx,Xapian,Solr

粉丝关注,好关系,统计这类应用比较简单,不用数据库,放到redis(想要持久haunted)

数据库前端加缓存

动态内容转静态化(数据库的数据,转成html文件,放到存储上),好处就是可以使用CDN缓存

数据库采用读写分离,读从库,写主库.

相关软件:MyCat,atlas,cobar,amoeba,MySQL-proxy

单表超过800W,拆库拆表,自动扩容,自动收缩.

备份策略

选择从库备份,锁表,备份时间长,影响数据访问

备份时采用分表分库,不分非常费劲.

流程 制度 安全

50%的故障都是人为造成的.

操作流程:开放-->核心开发-->运维或DBA

测试流程:办公室测试-->IDC测试环境测试-->生产环境