linux运维笔记：LNMP之深度优化MySQL

1-硬件层面优化

1-数据库物理机采购

CPU： 64位CPU，一台机器2-16颗CPU。至少2-4颗，L2（缓存）越大越好

内存： 96-128G，MySQL 3-4个实例。32-64G，1-2实例

硬盘：机械：选SAS，数量越多越好，转速越高越好15k

性能：SSD（高并发） > SAS（普通业务线上） >SATA（线下）

选SSD：使用SSD或者PCIe SSD设备，可提升上千倍的IOPS效率。

随机IO：SAS单盘能力300IOPS SSD随机IO：单盘能力可达35000IOPS Flashcache HBA卡

raid磁盘阵列：4块盘：RAID0>RAID1(推荐)>RAID5(少用)>RAID10

主库选择raid10，从库可选raid5/raid0/raid10，从库配置等于或大于主库

网卡：使用多块网卡bond，以及buffer，tcp优化

千兆网卡及千兆、万兆交换机

提示：

数据库属于IO密集型服务，硬件尽量不要使用虚拟化。

Slave硬件要等于或大于Master的性能

2-企业案例

百度：某部门IBM服务器为48核CPU，内存96GB，一台服务器跑3~4个实例。

sina：服务器是DELL R510居多，CPU是E5210，48GB内存，硬盘12*300G SAS，做RAID10。

3-服务器硬件配置调整

1）Swap调整（不使用swap分区）

/proc/sys/vm/swappiness的内容改成0（临时），/etc/sysctl.conf上添加vm.swappiness=0（永久）

　　这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。

　　当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。

2）修改MySQL的配置参数innodb_flush_method，开启O_DIRECT模式。

　　这种情况下，InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，但是redo log依旧会使用文件系统cache。

　　值得注意的是，Redo log是覆写模式的，即使使用了文件系统的cache，也不会占用太多

3）IO调度策略

#echo deadline>/sys/block/sda/queue/scheduler   临时修改为deadline

   永久修改

vi /boot/grub/grub.conf

更改到如下内容:

kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet

2-操作系统层面优化

你认为到底负载高好，还是低好呢？在实际的生产中，一般认为 cpu只要不超过90%都没什么问题。

当然不排除下面这些特殊情况：

问题一：cpu负载高，IO负载低

内存不够

磁盘性能差

SQL问题  ------>去数据库层，进一步排查sql问题

IO出问题了（磁盘到临界了、raid设计不好、raid降级、锁、在单位时间内tps过高）

tps过高: 大量的小数据IO、大量的全表扫描

问题二：IO负载高，cpu负载低

大量小的IO 写操作：

autocommit   ，产生大量小IO

IO/PS,磁盘的一个定值，硬件出厂的时候，厂家定义的一个每秒最大的IO次数。

大量大的IO 写操作

SQL问题的几率比较大

问题三：IO和cpu负载都很高

硬件不够了或sql存在问题

1-操作系统及MySQL实例选择

1.一定要选择x86_64系统，推荐使用CentOS6.8 linux，关闭NUMA特性

2.将操作系统和数据分开，不仅仅是逻辑上，还包括物理上

3.避免使用Swap交换分区

4.避免使用软件磁盘阵列

5.避免使用LVM逻辑卷

6.删除服务器上未使用的安装包和守护进程

2-文件系统层优化

1）调整磁盘Cache mode

启用WCE=1(Write Cache Enable),RCD=0(Read Cache Disable)模式

命令：sdparm -s WCE=1,RCD=0 -S /dev/sdb

2）采用Linux I/O scheduler算法deadline

deadline调度参数

对于Centos Linux建议 read_expire = 1/2 write_expire

echo 500 > /sys/block/sdb/queue/iosched/read_expire

echo 1000 > /sys/block/sdb/queue/iosched/write_expire

3）采用xfs文件系统

业务量不是很大也可采用ext4，业务量很大，推荐使用xfs：调整XFS文件系统日志和缓冲变量

XFS高性能设置

4）mount挂载文件系统

增加：async,noatime,nodiratime,nobarrier等

noatime：访问文件时不更新inode的时间戳，高并发环境下，推线显示应用该选项，可以提高系统I/O性能

async：写入时数据会先写到内存缓冲区,只到硬盘有空档才会写入磁盘,这样可以提升写入效率！风险为若服务器宕机或不正常，会损失缓冲区中未写入磁盘的数据 解决办法：服务器主板电池或加UPS不间断电源

nodiratime：不更新系统上的directory inode时间戳，高并发环境，推荐显示该应用，可以提高系统I/O性能

nobarrier：不使用raid卡上电池

5）Linux内核参数优化

1.将vm，swappiness设置为0-10

2.将vm，dirty_background_ratio设置为5-10，将vm，dirty_ratio设置为它的两倍左右，以确保能持续将脏数据刷新到磁盘，避免瞬间I/O写，产生严重等待

3.优化TCP协议栈

#减少TIME_WAIT，提高TCP效率

net.ipv4.tcp_tw_recyle=1

net.ipv4.tcp_tw_reuse=1

#减少处于FIN-WAIT-2连接状态的时间，使系统可以处理更多的连接

net.ipv4.tcp_fin_timeout=2

#减少TCP KeepAlived连接侦测的时间，使系统可以处理更多的连接。

net.ipv4.tcp_keepalived_time=600

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

net.ipv4.tcp_max_syn_backlog = 16384

#减少系统SYN连接重试次数(默认5)

net,ipv4.tcp_synack_retries = 1

net.ipv4.tcp_sync_retries = 1

#在内核放弃建立的连接之前发送SYN包的数量

net.ipv4.ip_local_prot_range = 4500 65535

#允许系统打开的端口范围

4.网络优化

#优化系统套接字缓冲区

#Increase TCP max buffer size

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接收/发送缓冲区

# Increase Linux autotuning TCP buffer limits

net.ipv4.tcp_rmem=4096 87380 16777216

net.ipv4.tcp_wmem=4096 65536 16777216

net.ipv4.tcp_mem = 94500000 915000000 927000000

#优化网络设备接收队列

net.core.netdev_max_backlog=3000

5.其他优化

net.ipv4.tcp_timestamps = 0

net.ipv4.tcp_max_orphans = 3276800

net.ipv4.tcp_max_tw_buckets = 360000

提示：面试的时候说框架，然后说一两个小的优化参数即可

3-系统参数调整

1）Linux系统内核参数优化

vim /etc/sysctl.conf

    net.ipv4.ip_local_port_range = 1024 65535   # 用户端口范围

    net.ipv4.tcp_max_syn_backlog = 4096

    net.ipv4.tcp_fin_timeout = 30

    fs.file-max=65535          # 系统最大文件句柄，控制的是能打开文件最大数量  

2）用户限制参数（mysql可以不设置以下配置）

vim    /etc/security/limits.conf

    * soft nproc 65535

    * hard nproc 65535

    * soft nofile 65535

    * hard nofile 65535

4-应用优化

业务应用和数据库应用独立，防火墙：iptables、selinux等其他无用服务(关闭)：

    chkconfig --level 23456 acpid off

    chkconfig --level 23456 anacron off

    chkconfig --level 23456 autofs off

    chkconfig --level 23456 avahi-daemon off

    chkconfig --level 23456 bluetooth off

    chkconfig --level 23456 cups off

    chkconfig --level 23456 firstboot off

    chkconfig --level 23456 haldaemon off

    chkconfig --level 23456 hplip off

    chkconfig --level 23456 ip6tables off

    chkconfig --level 23456 iptables  off

    chkconfig --level 23456 isdn off

    chkconfig --level 23456 pcscd off

    chkconfig --level 23456 sendmail  off

chkconfig --level 23456 yum-updatesd  off

　　安装图形界面的服务器不要启动图形界面 runlevel 3

　　另外，思考将来我们的业务是否真的需要MySQL，还是使用其他种类的数据库，用数据库的最高境界就是不用数据库。

3-MySQL数据库层面优化

my.cnf参数优化，此优化主要针对innodb引擎：

default-storage-engine

innodb_buffer_pool_size       # 没有固定大小，50%测试值，看看情况再微调。但是尽量设置不要超过物理内存70%

innodb_file_per_table=(1,0)

innodb_flush_log_at_trx_commit=(0,1,2) # 1是最安全的，0是性能最高，2折中

binlog_sync

Innodb_flush_method=(O_DIRECT, fdatasync)

innodb_log_buffer_size        # 100M以下

innodb_log_file_size          # 100M 以下

innodb_log_files_in_group     # 5个成员以下,一般2-3个够用（iblogfile0-N）

innodb_max_dirty_pages_pct   # 达到百分之75的时候刷写 内存脏页到磁盘。

log_bin

max_binlog_cache_size         # 可以不设置

max_binlog_size               # 可以不设置

innodb_additional_mem_pool_size    #小于2G内存的机器，推荐值是20M。32G内存以上100M

SQL优化方向：

　　执行计划、索引、SQL改写

架构优化方向：

　　高可用架构、高性能架构、分库分表

MySQL工具mysqlreport，可以用来分析MySQL性能

检查问题常用工具

mysql

msyqladmin                                mysql客户端，可进行管理操作

mysqlshow                                 功能强大的查看shell命令

show [SESSION | GLOBAL] variables             查看数据库参数信息

SHOW [SESSION | GLOBAL] STATUS             查看数据库的状态信息

information_schema                         获取元数据的方法

SHOW ENGINE INNODB STATUS                 nnodb引擎的所有状态

SHOW PROCESSLIST                          查看当前所有连接session状态

explain                                    获取查询语句的执行计划

show index                                 查看表的索引信息

slow-log                                   记录慢查询语句

mysqldumpslow                             分析slowlog文件的

不常用但好用的工具

zabbix                  监控主机、系统、数据库（部署zabbix监控平台）

pt-query-digest           分析慢日志

mysqlslap               分析慢日志

sysbench                压力测试工具

mysql profiling           统计数据库整体状态工具    

Performance Schema      mysql性能状态统计的数据

workbench              管理、备份、监控、分析、优化工具（比较费资源）

4-关于库表的设计规范

1）推荐utf-8字符集，虽然有人说它没有latin1快

2）固定字符串的列尽可能多用定长char，少用varchar

3）存储可变长度的字符串使用VARCHAR而不是CAHR---节省空间，因为CHAR固定长度，而VARCHAR长度不固定（UTF8不受此影响）

4）所有的InnoDB表都设计一个无业务的用途的自增列做主键

5）字段长度满足需求前提下，尽可能选择长度小的

6）字段属性尽量都加NOT NULL约束

7）对于某些文本字段，例如“省份”或者“性别”我们可以将他们定义为ENUM类型

8）尽可能不使用TEXT/BLOB类型，确实需要的话，建议拆分到子表中，不要和主表放在一起，避免SELECT *的时候读性能太差。

9）读取数据时，只选取所需要的列，不要每次都SELECT * 避免产生严重的随机读问题，尤其是读到一些TEXT/BLOB类型，确实需要的话，建议拆分到子表中，不要和主表放在一起，避免SELECT *的时候读性能太差

10）对一个VARCHAR（N）列创建索引时，通常取其50%（甚至更小）左右长度创建前缀索引就足以满足80%以上的查询需求了，没必要创建整列的全长度索引。

11）多用符合索引，少用多个独立索引，尤其是一些基础（Cardinality）太小（如果说：该列的唯一值总数少于255）的列就不要创建独立索引了。

5-SQL语句的优化

索引优化

1）白名单机制一百度，项目开发啊，DBA参与，减少上线后的慢SQL数据

抓出慢SQL，配置my.cnf

long_query_time = 2

log-slow-queries=/data/3306/slow-log.log

log_queries_not_using_indexs

按天轮询：slow-log.log

2）慢查询的日志分析工具——mysqlsla或pt-query-digest（推荐）

pt-quey-diges,mysqldumpslow,mysqlsla,myprofi,mysql-explain-slow-log,mysqllogfileter

3）每天晚上0点定时分析慢查询，发到核心开发，DBA分析，及高级运维，CTO的邮箱里

DBA分析给出优化建议–>核心开发确认更新–>DBA线上操作处理

4）定期使用pt-duplicate-key-checker检查并删除重复的索引

定期使用pt-index-usage工具检查并删除使用频率很低的索引

5）使用pt-online-schema-change来完成大表的ONLINE DDL需求

6）有时候MySQL会使用错误的索引，对于这种情况使用USE INDEX

7）使用explain及set profile优化SQL语句

大的复杂的SQL语句拆分成多个小的SQL语句

　　子查询，JOIN连表查询，某个表4000万条记录

数据库是存储数据的地方，但不是计算数据的地方

　　对数据计算，应用类处理，都要拿到前端应用解决，禁止在数据库上处理

搜索功能，like ‘%oldboy%’ 一般不要用MySQL数据库

　　使用连接(JOIN)来代替子查询（Sub_Queries）

　　避免在整个表上使用cout(*)，它可能锁住整张表

　　多表联接查询时，关联字段类型尽量一致，并且都要有索引。

　　在WHERE子句中使用UNION代替子查询

　　多表连接查询时，把结果集小的表（注意，这里是指过滤后的结果集，不一样是全表数据量小的）作为驱动表

6-网站集群架构的优化

1.服务器上跑多实例，2-4个（具体需要看服务器的硬件信息）

2.主从复制一主无从，采用mixed模式，尽量不要跨机房同步（进程远程读本地写）

3.定期使用pt-table-checksum、pt-table-sync来检查并修复mysql主从复制的数据差异

4.业务拆分：搜索功能，like '%oldboy% ' 一般不要用MySQL数据库

5.业务拆分：某些业务应用使用nosql持久化存储，例如：memcached、redis、ttserver

例如：粉丝关注，好友关系等

6.数据库前端必须要加cache，例如：memcached，用户登录，商品查询

7.动态的数据库静态化，整个文件静态化，页面片段静态化

8.数据库集群与读写分离。一主多从，通过程序或dbproxy进行集群读写分离

9.单表超过800万，拆库拆表，人工拆表拆库（登录、商品、订单）

10.百度、阿里国内前三公司，会选择从库进行备份，对数据库进行分库分表

7-MySQL流程、制度控制优化

任何一次人为数据库记录的更新，都要走一个流程：

a.人的流程：开发-->核心开发-->运维或DBA

b.测试流程：内网测试-->IDC测试-->线上执行

c.客户端管理，phpmyadmin

8-MySQL基础安全

1.启动程序700，属主和用户组为MySQL

2.为MySQL超级用户root设置密码

3.如果要求严格可以删除root用户，创建其他管理用户，例如admin

4.登录时尽量不要在命令行暴露密码，备份脚本中如果有密码，给设置700，属主和密码组为mysql或root

5.删除默认存在的test库

6.初始删除无用的用户，只保留

| root | 127.0.0.1 |

| root | localhost |

7.不要一个用户管理所有的库，尽量专库专用户

8.清理mysql操作日志文件~/.mysql_history（权限600，可以不删）

9.禁止开发获得web连接的密码，禁止开发连接操作生产对外的库

10.phpmyadmin安全

11.服务器禁止设置外网IP

12.防SQL注入（WEB）php.ini或web开发插件监控，waf控制