返回 导航

其他

hangge.com

MySQL - 数据库配置参数、性能优化教程

作者:hangge | 2020-12-21 08:10
    将 MySQL 部署到普通的 X86 服务器上,在不经过任何优化情况下,MySQL 理论值正常可以处理 2000 左右 QPS。不过当访问量达到 1500 左右并发连接时,数据库处理性能就会变慢。
QPSQueries Per Second):每秒查询数,即一台数据库每秒能够处理的查询次数
   而经过优化后,有可能会提升到 2500 左右 QPS。下面演示如何通过参数配置优化,让数据库最大化发挥性能。

一、选择合适的存储引擎

(1)MySQL 常用有两种存储引擎:
  • 一个是 MyISAM,不支持事务处理,读性能处理快,表级别锁。
  • 另一个是 InnoDB,支持事务处理(ACID),设计目标是为处理大容量数据发挥最大化性能,行级别锁。
  • 表锁:开销小,锁定粒度大,发生死锁概率高,相对并发也低。
  • 行锁:开销大,锁定粒度小,发生死锁概率低,相对并发也高。

(2)根据以上对比看来,使用 InnoDB 存储引擎是最好的选择,这也是 MySQL5.5 以后版本中默认存储引擎。除了一些公共参数外,每个存储引擎也有各自相关联的参数也会影响数据库性能,下面分别进行介绍。

二、公共参数配置

修改 mysql 数据库配置方法:编辑配置文件 /etc/my.cnf,在 [mysqld] 段中添加或修改对应的参数值即可。

1,修改数据库最大并行连接数

(1)如果实际连接数超过数据库最大连接数,会出现“MySQL: ERROR 1040: Too many connections”的异常情况。默认配置下数据库最大连接数是 151,我们可以通过如下命令查看:
show variables like '%max_connections%';

(2)我们可以根据情况,通过 max_connections 参数修改最大连接数,比如下改成 600
max_connections=600

(3)究竟最大连接数设置多少合适,我们可以执行如下命令查看 Max_used_connections 值来判断,它指的是本次 mysql 服务启动到现在,同一时刻并行连接数的最大值(即历史并发请求峰值)。
show global status like 'Max_used_connections';


三、MyISAM 参数配置

提示:下面设置仅针对以 MyISAM 引擎为主的 DB。如果以 InnoDB 引擎为主的 DB 则跳过此节。


1,设置索引缓存区大小

索引缓存区大小一般设置物理内存的 30% - 40%
key_buffer_size = 2048M

2,设置读操作缓冲区大小

    读操作缓冲区大小默认为 128K。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。一般设置为 2M 就足够了。
read_buffer_size = 2M

3,设置查询缓存功能

(1)为了加快查询语句的执行性能,从MySQL早期的版本开始就提供了一种名叫 Query Cache 的缓存技术。
Query Cache 的工作原理:当某一个客户端连接(session)进行 SQL 查询并得到返回信息时,MySQL 数据库除了将查询结果返回给客户端外,还在特定的内存区域缓存这条 SQL 查询语句的结果,以便包括这个客户端在内的所有客户的再次执行相同查询请求时,MySQL 能够直接从缓存区返回结果。

(2)下面配置将 query_cache_type 设置为 1 表示开启查询缓存功能,同时设置 Query Cache 的缓冲区大小为 32M
query_cache_type = 1
query_cache_size = 32M

四、InnoDB 参数配置

提示:下面设置仅针对以 InnoDB 引擎为主的 DB。如果以 MyISAM 引擎为主的 DB 则跳过此节。


1,设置索引和数据缓冲区大小

    该设置对 InnoDB 表来说非常重要,因为 InnoDB 相比 MyISAM 表对缓冲更为敏感,如果使用默认值十分影响性能。一般设置物理内存的 60% - 70%
innodb_buffer_pool_size = 6G

2,设置缓冲池实例个数

    innodb_buffer_pool_instances 可以开启多个内存缓冲池,把需要缓冲的数据 hash 到不同的缓冲池中,这样可以并行的内存读写。该参数值默认为 1,推荐设置 4 个或 8 个。
innodb_buffer_pool_instances = 8

3,设置日志同步到磁盘方式

(1)MySQL 的事务日志中,为了性能和效率,可以配置每次事务提交后是否将日志缓冲刷到 log file 以进行数据持久化,这就是变量 innodb_flush_log_at_trx_commit 的作用。innodb_flush_log_at_trx_commit 3 种可选值:
  • 0:代表大约每秒写入到日志并同步到磁盘,数据库故障会丢失 1 秒左右事务数据。
  • 1:为每执行一条 SQL 后写入到日志并同步到磁盘,I/O 开销大,执行完 SQL 要等待日志读写,这种方式即使系统崩溃也不会丢失任何数据,但效率低。
  • 2:代表只把日志写入到系统缓存区,再每秒同步到磁盘,效率很高,如果服务器故障,才会丢失事务数据。
(2)对数据安全性要求不是很高的推荐设置 2,性能高,修改后效果明显。
innodb_flush_log_at_trx_commit = 2

4,设置独享表空间

    默认是共享表空间(参数值为 0),共享表空间 idbdata 文件不断增大,影响一定的 I/O 性能。推荐开启独立表空间模式(参数值为 1),每个表的索引和数据都存在自己独立的表空间中,可以实现单表在不同数据库中移动。
  • 共享表空间:某一个数据库的所有的表数据,索引文件全部放在一个文件中,默认这个共享表空间的文件路径在 data 目录下。默认的文件名为 :ibdata1 初始化为 10M
  • 独占表空间:每一个表都将会生成以独立的文件方式来进行存储,每一个表都有一个 .frm 表描述文件,还有一个 .ibd 文件。其中这个文件包括了单独一个表的数据内容以及索引内容,默认情况下它的存储位置也是在表的位置之中。
innodb_file_per_table = 1

附:系统内核优化

    大多数 MySQL 都部署在 linux 系统上,所以操作系统的一些参数也会影响到 MySQL 性能,比如:调小 TCP 协议的 time_wait 超时时间、调大服务器所支持的最大文件句柄数。具体方法参考我之前的文章:
评论

全部评论(0)

回到顶部