mysql优化

mysql优化
调优思路：
0.硬件优化
• 1.磁盘io优化
• 2.操作系统的优化
• 3.纵向拆解、横向拆解
• 4.数据库设计与规划
• 5. my.cnf参数的优化
• 6.mysql查询优化
• 7. Mysql存储引擎
硬件优化
CPU：64 位、高主频、高缓存，高并行处理能力
内存：大内存、主频高，尽量不要用SWAP
硬盘：建议是15000转或更大转数，使用RAID10、raid5磁盘阵列或SSD固态磁盘
网络：服务器标配的千兆网卡，建议是10G网卡，使用网卡bond技术。msyql服务器尽可能和使用它的web服务器在同一局域网内，尽量避免诸如防火墙策略等不必要的开销。
注：网卡bond是通过把多张网卡绑定为一个逻辑网卡，实现本地网卡的冗余，带宽扩容和负载均衡。
磁盘 io 规划，io相关的技术
1、raid 技术：raid10或raid5
2、建议是15000转或更大转数。有条件的可以使用SSD固态磁盘
3、swap 分区：最好使用raid0或SSD
4、磁盘分区：将数据库目录放到一个磁盘或分区。存储数据的硬盘或分区和系统所在的硬盘分开。
5、由于binlog日志频繁记录操作，开销非常大，需要把binlog日志放到单独的硬盘分区上。
操作系统的优化–内核、tcp 连接数量
tcp连接数量、系统打开文件的最大限制
1、设置tcp 连接数量限制
修改内核对TCP连接的有关限制:
内核编译时默认设置的本地端口号范围可能太小，因此需要修改此本地端口范围限制。
第一步，修改/etc/sysctl.conf文件，在文件中添加如下行：
net.ipv4.ip_local_port_range = 1024 65000
将系统对本地端口范围限制设置为1024~65000之间
第二步，执行sysctl命令使修改生效：
sysctl -p
2）Linux的最大并发允许的连接
查看使用了多少连接数：
cat /proc/sys/net/netfilter/nf_conntrack_count
查看总数多少
cat /proc/sys/net/netfilter/nf_conntrack_max
注：确保模块nf_conntrack被加载
临时设置增加并发数
echo 524288 > /proc/sys/net/netfilter/nf_conntrack_max
永久设置增加并发数：
/etc/sysctl.conf中加入：net.netfilter.nf_conntrack_max = 102400
执行sysctl -p使修改生效
sysctl -p
3）linux的并发过程中，time_wait的数量太大，引起连接的挂起等待，需要在服务器增加以下配置：
编辑文件/etc/sysctl.conf，加入以下内容：
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 30
然后执行 /sbin/sysctl -p 让参数生效。
net.ipv4.tcp_syncookies = 1
表示开启SYN Cookies。当出现SYN等待队列溢出时，启用cookies来处理，可防范少量SYN攻击，默认为0，表示关闭；
net.ipv4.tcp_tw_reuse = 1
表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接，默认为0，表示关闭；
net.ipv4.tcp_tw_recycle = 1
表示开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭。
net.ipv4.tcp_fin_timeout 修改系默认的 TIMEOUT 时间

2、系统打开文件的最大限制
用户级别：
（1）查看Linux系统用户最大打开的文件限制

ulimit -n

（2）修改打开文件限制

vim /etc/security/limits.conf

mysql soft nofile 102400
mysql hard nofile 102400
mysql soft nproc 102400
mysql hard nproc 102400
其中mysql指定了要修改那个用户的打开文件数限制，可用“*”号表示修改所有用户的限制；soft或hard指定要修改软限制还是硬限制，102400则指定了想要修改的新的限制值，即最大打开文件数（请注意软限制要小于或等于硬限制）。
（3）修改/etc/pam.d/login

vim /etc/pam.d/login

session required /usr/lib64/security/pam_limits.so
这是告诉linux在用户完成系统登录后，应该调用pam_limits.so模块来设置系统对该用户可使用的各种资源数量的最大限制（包括用户可打开的最大文件数限制），而pam_limits.so模块就会从/etc/security/limits.conf文件中读取配置来设置这些限制值
Linux系统级别：
查看linux系统对同时打开文件数的硬限制
sysctl -a | grep file-max
（1）修改file-max限制

vim /etc/sysctl.conf

fs.file-max = 1000000
执行sysctl -p生效

禁用不必要启动的服务
文件系统调优，
可以考虑在挂载分区时启用 noatime 选项
UUID=46cb104c-e4dc-4f84-8afc-552f21279c65 /data xfs defaults,noatime 0 0
最小化原则:
1） 安装系统最小化
2） 开启程序服务最小化原则
3） 登录最小化原则
4） 权限最小化