“Mysql性能优化”的版本间的差异
docker>Evan |
(没有差异)
|
2019年6月10日 (一) 02:43的版本
目录
服务器优化
优化服务器硬件
配置较大的内存
足够大的内存,是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多,可以增加系统的缓冲区容量,使数据在内存停留的时间更长,以减少磁盘的IO。
磁盘I/O相关
配置高速磁盘,比如SSD。
合理分配磁盘IO,把磁盘IO分散到多个设备上,以减少资源的竞争,提高并行操作能力。
配置多核处理器,MySQL是多线程的数据库,多处理器可以提高同时执行多个线程的能力。
系统层相关优化
文件系统层优化
在文件系统层,下面几个措施可明显提升IOPS性能:
1、使用deadline/noop这两种I/O调度器,千万别用cfq(它不适合跑DB类服务);
2、使用xfs文件系统,千万别用ext3;ext4勉强可用,但业务量很大的话,则一定要用xfs;
3、文件系统mount参数中增加:noatime, nodiratime, nobarrier几个选项(nobarrier是xfs文件系统特有的);
其他内核参数优化
针对关键内核参数设定合适的值,目的是为了减少swap的倾向,并且让内存和磁盘I/O不会出现大幅波动,导致瞬间波峰负载:
1、将vm.swappiness设置为5-10左右即可, 建议为1 少用,甚至设置为0(RHEL 7以上则慎重设置为0,除非你允许OOM kill发生),以降低使用SWAP的机会; swappiness的值的大小对如何使用swap分区是有着很大的联系的。swappiness=0的时候表示最大限度使用物理内存,然后才是 swap空间,swappiness=100的时候表示积极的使用swap分区,并且把内存上的数据及时的搬运到swap空间里面。linux的基本默认设置为60,具体如下:
一般默认值都是60 我的kali也是 [root@tr ~]# cat /proc/sys/vm/swappiness 60
也就是说,你的内存在使用到100-60=40%的时候,就开始出现有交换分区的使用。大家知道,内存的速度会比磁盘快很多,这样子会加大系统io,同时造的成大量页的换进换出,严重影响系统的性能,所以我们在操作系统层面,要尽可能使用内存,对该参数进行调整
2、将vm.dirty_background_ratio设置为5-10,将vm.dirty_ratio设置为它的两倍左右,以确保能持续将脏数据刷新到磁盘,避免瞬间I/O写,产生严重等待(和MySQL中的innodb_max_dirty_pages_pct类似);
3、将net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse都设置为1,减少TIME_WAIT,提高TCP效率;
4、至于网传的read_ahead_kb、nr_requests这两个参数,我经过测试后,发现对读写混合为主的OLTP环境影响并不大(应该是对读敏感的场景更有效果),不过没准是我测试方法有问题,可自行斟酌是否调整;
4.优化MySQL server
4.1MySQL内存管理和优化
4.2InnoDB log机制及优化
4.3调整跟并发相关的MySQL参数
(1)max_connections (2)back_log (3)table_open_cache (4)thread_cache_size (5)innodb_lock_wait_timeout
优化MySQL的参数
默认是这样的,安装目录 有配置模版可以看一下 my-*.cnf my-huge.cnf : 用于高端产品服务器,包括1到2GB RAM,主要运行mysql my-innodb-heavy-4G.ini : 用于只有innodb的安装,最多有4GB RAM,支持大的查询和低流量 my-large.cnf : 用于中等规模的产品服务器,包括大约512M RAM my-medium.cnf : 用于低端产品服务器,包括很少内存(少于128M) my-small.cnf : 用于最低设备的服务器,只有一点内存(少于512M) 设置为内存的50% 差不多了 只跑一个mysql的机器 innodb_buffer_pool_size = 2048M # 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以,然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大,并且不会暴增,那么无需把 innodb_buffer_pool_size 设置的太大了 MariaDB [(none)]> show variables like '%innodb_buffer_pool_size%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | innodb_flush_log_at_trx_commit = 2 # 如果将此参数设置为1,将在每次提交事务后将日志写入磁盘。为提供性能,可以设置为0或2,但要承担在发生故障时丢失数据的风险。设置为0表示事务日志写入日志文件,而日志文件每秒刷新到磁盘一次。设置为2表示事务日志将在提交时写入日志,但日志文件每次刷新到磁盘一次。 #innodb_max_dirty_pages_pct = 90 使用默认75观察 #推荐阅读 http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html innodb_io_capacity默认是200,单位是页,该参数的设置大小取决于硬盘的IOPS,即每秒每秒的输入输出量(或读写次数)。 #innodb_data_file_path = ibdata1:1024M:autoextend 设置过大导致报错, 一般为1G左右 #表空间文件 重要数据 long_query_time = 1 可以设置为0.1~0.5s binlog_format = row #复制过程不会出现丢数据的情况 interactive_timeout = 28800 wait_timeout = 28800 交互等待时间和非交互等待时间 两个要一致 建议为300~500s 不要默认的8小时 max_connections = 1000 不在太大 小心OOM #innodb_log_file_size = 128M 使用默认48M redo log #此参数确定数据日志文件的大小,以M为单位,更大的设置可以提高性能,但也会增加恢复故障数据库所需的时间 gerenal log 全局日志建议关闭 浪费磁盘空间 降低mysql性能 #innodb_file_io_threads = 4 不明确,使用默认值 #文件IO的线程数,一般为 4,但是在 Windows 下,可以设置得较大。
常见参数参考
bulk_insert_buffer_size 专用于myisam引擎,用一个特别的类似的树形结构体缓存,用于提高 insertselect insert…values(…)(….)以及load data写数据到非空表的情景 MySQL relay_log_purge=0 时的风险
MHA 清理relay log(purge_relay_logs)
MySQL的my.cnf文件(解决5.7.18^下没有my-default.cnf)
mysql参数之innodb_buffer_pool_size大小设置
2.优化数据库对象
2.1选择合适的存储引擎 2.2字段选择合适的数据类型 procedure analyse() 可以对当前应用的表进行分析,对数据表中列的数据类型提出优化建议。
2.3三范式和反三范式 2.4对表进行水平或者垂直拆分
3.针对存储引擎的优化
优化特定参数
SQL和索引优化
① 为查询缓存优化查询 ② EXPLAIN 我们的SELECT查询(可以查看执行的行数) ③ 当只要一行数据时使用LIMIT 1 ④ 为搜索字段建立索引 ⑤ 在Join表的时候使用相当类型的列,并将其索引 ⑥ 千万不要 ORDER BY RAND () ⑦ 避免SELECT * ⑧ 永远为每张表设置一个ID ⑨ 可以使用ENUM 而不要VARCHAR ⑩ 尽可能的使用NOT NULL ⑪ 固定长度的表会更快 ⑫ 垂直分割 ⑬ 拆分打的DELETE或INSERT语句 ⑭ 越小的列会越快 ⑮ 选择正确的存储引擎 ⑯ 小心 "永久链接"
原文:https://blog.csdn.net/u013087513/article/details/77899412
5.磁盘I/O优化
5.1使用磁盘阵列(RAID)
5.2使用Linux虚拟文件卷模拟RAID
5.3符号连接(Symbolic Links)分布I/O
利用操作系统的符号连接(Symbolic Links)将不同的数据库、表或索引指向不同的物理磁盘,从而达到分布磁盘I/O的目的。
5.4禁止操作系统更新文件的atime属性
5.5用裸设备(Raw Device)存放InnoDB的共享表空间
5.6调整I/O调度算法
5.7RAID卡电池的充放电引起的性能波动
5.8NUMA架构优化
非一致存储访问结构(Non-Uniform Memory Access, NUMA)
6.应用优化
6.1使用连接池
6.2减少对MySQL的访问
①理清应用逻辑,能一次取出的数据不用两次; ②使用查询缓存 MySQL的查询缓存(MySQL query cache)是4.1版本之后新增的功能,作用是存储select的查询文本和相应结果。如果随后收到一个相同的查询,服务器会从查询缓存中重新得到查询结果,而不再需要解析和执行查询。 查询缓存适用于更新不频繁的表,当表更改(包括表结构和数据)后,查询缓存会被清空。 ③在应用端增加cache层
④负载均衡