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控制