MYSQL调优常用的方法

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

一、Percona Server、MariaDB和MYSQL的区别

1、Mysql三种存储引擎
MySQL提供了两种存储引擎:MyISAM和 InnoDB,MySQL4和5使用默认的MyISAM存储引擎。从MYSQL5.5开始,MySQL已将默认存储引擎从MyISAM更改为InnoDB。MyISAM没有提供事务支持,而InnoDB提供了事务支持。XtraDB是InnoDB存储引擎的增强版本,被设计用来更好的使用更新计算机硬件系统的性能,同时还包含有一些在高性能环境下的新特性。

2、Percona Server分支
Percona Server由领先的MySQL咨询公司Percona发布。
Percona Server是一款独立的数据库产品,其可以完全与MySQL兼容,可以在不更改代码的情况了下将存储引擎更换成XtraDB。是最接近官方MySQL Enterprise发行版的版本。
Percona提供了高性能XtraDB引擎,还提供PXC高可用解决方案,并且附带了percona-toolkit等DBA管理工具箱。

3、MariaDB
MariaDB由MySQL的创始人开发,MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。
MariaDB提供了MySQL提供的标准存储引擎,即MyISAM和InnoDB,10.0.9版起使用XtraDB(名称代号为Aria)来代替MySQL的InnoDB。

4、如何选择
一般综合多年使用经验和性能对比,首选Percona分支,其次是MariaDB,如果你不想冒一点风险,那就选择MYSQL官方版本。

二、常用的MYSQL调优策略

1、硬件层相关优化
修改服务器BIOS设置
选择Performance Per Watt Optimized(DAPC)模式,发挥CPU最大性能。
Memory Frequency(内存频率)选择Maximum Performance(最佳性能)
内存设置菜单中,启用Node Interleaving,避免NUMA问题。

2、磁盘I/O相关
使用SSD硬盘
如果是磁盘阵列存储,建议阵列卡同时配备CACHE及BBU模块,可明显提升IOPS。
raid级别尽量选择raid10,而不是raid5。

3、文件系统层优化
使用deadline/noop这两种I/O调度器,千万别用cfq
使用xfs文件系统,千万别用ext3;ext4勉强可用,但业务量很大的话,则一定要用xfs;
文件系统mount参数中增加:noatime, nodiratime, nobarrier几个选项(nobarrier是xfs文件系统特有的)。

4、内核参数优化
修改vm.swappiness参数,降低swap使用率。RHEL7/centos7以上则慎重设置为0,可能发生OOM。调整vm.dirty_background_ratio、vm.dirty_ratio内核参数,以确保能持续将脏数据刷新到磁盘,避免瞬间I/O写。产生等待。调整net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse都设置为1,减少TIME_WAIT,提高TCP效率。

5、Mysql参数优化建议
建议设置default-storage-engine=InnoDB,强烈建议不要再使用MyISAM引擎。
调整innodb_buffer_pool_size的大小,如果是单实例且绝大多数是InnoDB引擎表的话,可考虑设置为物理内存的50% -70%左右。
设置innodb_file_per_table = 1,使用独立表空间。
调整innodb_data_file_path = ibdata1:1G:autoextend,不要用默认的10M,在高并发场景下,性能会有很大提升。
设置innodb_log_file_size=256M,设置innodb_log_files_in_group=2,基本可以满足大多数应用场景。
调整max_connection(最大连接数)、max_connection_error(最大错误数)设置,根据业务量大小进行设置。
另外,open_files_limit、innodb_open_files、table_open_cache、table_definition_cache可以设置大约为max_connection的10倍左右大小。
key_buffer_size建议调小,32M左右即可,另外建议关闭query cache。
mp_table_size和max_heap_table_size设置不要过大,另外sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等设置也不要过大。













本文转自lq201151CTO博客,原文链接: http://blog.51cto.com/liuqun/2043783,如需转载请自行联系原作者


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
MySQL的全文索引查询方法
【8月更文挑战第26天】MySQL的全文索引查询方法
106 0
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
399 66
MySQL复制以及调优
本文介绍了MySQL自带复制方案的实现及其注意事项。复制方案能提供数据备份、负载均衡与分布式数据管理的优势。文章详细描述了复制步骤:主库(master)记录更改到二进制日志,发送同步消息给从库(slave),从库接收后将日志复制到本地并执行。实现复制包括配置主库的server-id和二进制日志、创建复制账号、初始化主库数据、设置从库参数及开启复制。此外,还探讨了三种日志格式(row、statement、mixed)的特点及选择建议,并分析了主从复制延迟的优化方法,如控制事务大小、优化日志传输和多线程还原日志等。最后,文中列出了搭建过程中需要注意的关键点。
环比、环比增长率、同比、同比增长率 ,占比,Mysql 8.0 实例(最简单的方法之一)(sample database classicmodels _No.2 )
环比、环比增长率、同比、同比增长率 ,占比,Mysql 8.0 实例(最简单的方法之一)(sample database classicmodels _No.2 )
278 1
提高MySQL查询性能的方法有很多
提高MySQL查询性能的方法有很多
363 7
Python中使用MySQL模糊查询的方法
本文介绍了两种使用Python进行MySQL模糊查询的方法:一是使用`pymysql`库,二是使用`mysql-connector-python`库。通过这两种方法,可以连接MySQL数据库并执行模糊查询。具体步骤包括安装库、配置数据库连接参数、编写SQL查询语句以及处理查询结果。文中详细展示了代码示例,并提供了注意事项,如替换数据库连接信息、正确使用通配符和关闭数据库连接等。确保在实际应用中注意SQL注入风险,使用参数化查询以保障安全性。
MySQL/SqlServer跨服务器增删改查(CRUD)的一种方法
通过上述方法,MySQL和SQL Server均能够实现跨服务器的增删改查操作。MySQL通过联邦存储引擎提供了直接的跨服务器表访问,而SQL Server通过链接服务器和分布式查询实现了灵活的跨服务器数据操作。这些技术为分布式数据库管理提供了强大的支持,能够满足复杂的数据操作需求。
138 12
MySQL的count()方法慢
MySQL的 `COUNT()`方法在处理大数据量时可能会变慢,主要原因包括数据量大、缺乏合适的索引、InnoDB引擎的设计以及复杂的查询条件。通过创建合适的索引、使用覆盖索引、缓存机制、分区表和预计算等优化方案,可以显著提高 `COUNT()`方法的执行效率,确保数据库查询性能的提升。
862 12
Mysql 中日期比较大小的方法有哪些?
在 MySQL 中,可以通过多种方法比较日期的大小,包括使用比较运算符、NOW() 函数、DATEDIFF 函数和 DATE 函数。这些方法可以帮助你筛选出特定日期范围内的记录,确保日期格式一致以避免错误。
152 1
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
763 1