mysql配置参数调优(8GB内存和64GB内存)

淡淡的烟草味﹌ 2024-04-18 21:20 64阅读 0赞

### 文章目录 ###

*   *  一、前言
     *  二、查看服务器的CPU和内存
     *   *  1、查看CPU的个数
         *  2、查看服务器的内存
         *  3、数据库版本
     *  三、64GB内存的mysql配置参数
     *  四、本机8GB内存的mysql配置参数
     *   *  1、查看数据库版本
         *  2、具体的配置参数
     *  五、总结
     *   *  1、数据库偏向于写操作
         *  2、数据库偏向于读操作
         *  3、补充

## 一、前言 ##

我们新搭建一个`LNMP`环境之后该如何设置配置文件来达到最佳性能呢？众所周知，`mysql`的默认配置参数性能是比较底下的，如果我们的服务器配置比较高，那么势必发挥不出来`mysql`的真正性能。所以，我们必须要设置合适的`mysql`参数来达到最完美的性能，最起码不能拖了后腿。

## 二、查看服务器的CPU和内存 ##

博主在配置完`LNMP`之后，也是一时之间有些恍惚，虽然知道要设置`mysql`的内存参数，设置连接数之类的，但是一时还真不知道该从何处下手。所幸公司有专门的`mysql`服务器，并且参数都是经过`DBA`设置过的，性能强劲，不如咱们就参考`mysql`服务器上的配置，从而对比咱们的服务器配置，设计出适合咱们`mysql`的一套配置参数。

下面先查询出来`mysql`服务器的配置，方便作为对比：

### 1、查看CPU的个数 ###

**（1）物理`cpu`数：** 主板上实际插入的`cpu`数量，可以数不重复的 `physical id` 有几个`（physical id）`

**（2） `cpu`核数：** 单块`CPU`上面能处理数据的芯片组的数量，如双核、四核等 （`cpu cores）`

**（3）逻辑cpu数：** 一般情况下，`逻辑cpu=物理CPU个数×每颗核数`，如果不相等的话，则表示服务器的`CPU`支持超线程技术（HT：简单来说，它可使处理器中的1 颗内核如2 颗内核那样在操作系统中发挥作用。这样一来，操作系统可使用的执行资源扩大了一倍，大幅提高了系统的整体性能，此时`逻辑cpu=物理CPU个数×每颗核数x2`）

也就是说，实际用到的核数是：`40 /2 = 20核` ，比着上面的`10`核多，相当于服务器支持`CPU`超线程技术

### 2、查看服务器的内存 ###

grep MemTotal /proc/meminfo
    MemTotal:       65936452 kB             //大概是62.8G，也就是64G的机器

### 3、数据库版本 ###

select version();
    +------------+
    | version()  |
    +------------+
    | 5.6.34-log |

OK，根据咱们上面的这些操作，已经知道公司的`Mysql`服务器配置是：`10核64GB`内存的。

## 三、64GB内存的mysql配置参数 ##

show global variables like 'key_buffer_size';  					256M
    
    show global variables like 'max_allowed_packet';  				32M
    
    show global variables like 'table_open_cache';  				16384	
    
    show global variables like 'sort_buffer_size';  				32M
    
    show global variables like 'net_buffer_length'; 				16384
    
    show global variables like 'read_buffer_size'; 					16M
    
    show global variables like 'read_rnd_buffer_size';				32M
    
    show global variables like 'myisam_sort_buffer_size';			128M  
    （经常需要调用临时表，所以大了一些）
    
    show global variables like 'thread_cache_size';					64
    
    根据物理内存设置规则如下：
    1G ---> 8
    2G ---> 16
    3G ---> 32
    >3G ---> 64
    
    show global variables like 'query_cache_size';					0 
    
    当开启了 Query Cache 之后，尤其是当我们的 query_cache_type 参数设置为 1 以后，
    MySQL 会对每个 SELECT 语句都进行Query Cache 查找，查找操作虽然比较简单，
    但仍然也是要消耗一些 CPU 运算资源的。而由于 Query Cache 的失效机制的特性，
    可能由于表上的数据变化比较频繁，大量的 Query Cache 频繁的被失效，所以 
    Query Cache 的命中率就可能比较低下。所以有些场景下
    ，Query Cache 不仅不能提高效率，反而可能造成负面影响。 
    
    参考：https://blog.csdn.net/u014044812/article/details/78924315
    
    show global variables like 'tmp_table_size';					128M  
    //临时表相关，不常用的话可以小一点
    
    show global variables like 'explicit_defaults_for_timestamp';	ON  
    //是否显示默认时间戳
    
    show global variables like 'max_connections';					100000  
     //最大连接数，并发连接数
    
    show global variables like 'max_connect_errors';				10000000  
    
    show global variables like 'open_files_limit';					500000  
    //mysql打开最大文件数
    
    show global variables like 'expire_logs_days';					10
    启用二进制日志后，保留日志的天数。
    
    show global variables like 'innodb_file_per_table';				ON
    
    
    show global variables like 'innodb_data_file_path';				ibdata1:1G:autoextend
    用来容纳InnoDB为数据表的表空间: 可能涉及一个以上的文件; 每一个表空间文件
    的最大长度都必须以字节(B)、兆字节(MB)或千兆字节(GB)为单位给出; 
    表空间文件的名字必须以分号隔开; 最后一个表空间文件还可以带一个autoextend
    属性和一个最大长度(max:n)。
    
    ===innodb引擎相关配置====
    
    show global variables like 'innodb_buffer_pool_size';           32G
    
    show global variables like 'innodb_log_file_size'; 				4G  
    //事务日志文件写操作缓存区的最大长度
    
    show global variables like 'innodb_log_buffer_size'; 			64M  
    //经常用到事务的场景，这个值应该大一些
    
    show global variables like 'innodb_flush_log_at_trx_commit'; 	2  
    这个参数要配合sync_binlog参数来设置，在设置为：
    innodb_flush_log_at_trx_commit=2 
    sync_binlog=1000
    mysql的写入性能最高，可以参考：https://www.jianshu.com/p/74b03a792ff8
    
    
    show global variables like 'innodb_lock_wait_timeout';			30s
    
    show global  variables like 'back_log';							4096   	
    //参考：https://www.cnblogs.com/angryprogrammer/p/6667741.html

**注意：以上参数均为博主计算后的数额，通过show variables查出来的数值都是以字节为单位，这块大家自己到时候自己转换一下就行，博文为了方便展示，故而选用MB来展示。**

上面这些参数是影响mysql服务器性能的主要原因，关于这些参数的具体含义，大家可以参考：[MariaDB/MySQL配置文件my.cnf解读][MariaDB_MySQL_my.cnf] ,注释部分只是列出来部分参数的含义，建议是先弄懂各个参数的含义再对比设置会比较好。

## 四、本机8GB内存的mysql配置参数 ##

咱们的服务器是`2核8G`，当然是不能像专门的数据库服务器一样，绝大部分内存都分配给`mysql`，由于业务会有大量的连接，所以决定留下一半的内存给`mysql`，也就是`4GB`。剩下的`4GB`内存交给服务器的线程。

### 1、查看数据库版本 ###

MariaDB [(none)]> select version();
    +--------------------------+
    | version()                |
    +--------------------------+
    | 10.1.41-MariaDB-0+deb9u1 |
    +--------------------------+

这个版本具有`MySQL 5.6`和`5.7`的后端和重新实现的功能，可以参考文档：https://mariadb.com/kb/en/library/mariadb-10141-release-notes/  
不过网上有种说法是`10.1-3`和`5.7`对应，也就是说咱们这个版本也是对标的`mysql5.7`。

### 2、具体的配置参数 ###

show global variables like 'key_buffer_size';  		64M 	//默认16M		//64G的机器设置：256M
    
    show global variables like 'max_allowed_packet';  	32M  	// 默认16M		//64G的机器设置：32M
    
    show global variables like 'table_open_cache';  	2048	// 默认2000		//64G的机器设置：16384	
     
    show global variables like 'sort_buffer_size';  	4M		//默认是2M		//64G的机器设置：32M
    
    show global variables like 'net_buffer_length'; 	 16384  //默认16384		//64G的机器设置：16384
    
    show global variables like 'read_buffer_size'; 	   1M       //默认128k		//64G的机器设置：16M
    
    show global variables like 'read_rnd_buffer_size';	512k	//默认256k		//64G的机器设置：32M
    
    show global variables like 'myisam_sort_buffer_size';128M  	//默认128M		//64G的机器设置：128M
    
    show global variables like 'thread_cache_size';		64		//默认8			//64G的机器设置：64
    
    show global variables like 'query_cache_size';	0 			//默认16M		//64G的机器设置：0
    
    show global variables like 'tmp_table_size';		32M  	//默认16M		//64G的机器设置：128M
    
    show xx like 'explicit_defaults_for_timestamp';	 ON		    //默认OFF		//64G的机器设置：ON
    
    show global variables like 'max_connections';		5000 	//默认151		//64G的机器设置：100000
    
    show global variables like 'max_connect_errors';	500000  //默认100		//64G的设置：10000000
    
    show global variables like 'open_files_limit';		65535	//默认4184		//64G的机器设置：500000	
    
    show global variables like 'expire_logs_days';		10		//默认10			//64G的机器设置：10
    
    show global variables like 'innodb_file_per_table';	ON		//默认ON			//64G的机器设置：ON
    
    show global variables like 'innodb_data_file_path';	64M		//默认12M   		//64G的机器设置：1G
    
    ===innodb相关====
    
    show global variables like 'innodb_buffer_pool_size';   2G	//默认128M   	//64G的机器设置：32G
    
    show global variables like 'innodb_log_file_size'; 	256M	//默认48M   		//64G的机器设置：4G
    
    show global variables like 'innodb_log_buffer_size'; 32M	//默认16M   		//64G的机器设置：64M
    
    show xx like 'innodb_flush_log_at_trx_commit'; 		2  		//默认1   		//64G的机器设置：2
    
    show global variables like 'sync_binlog';		1000		//默认0   		//64G的机器设置：1000
    
    show global variables like 'innodb_lock_wait_timeout';30s	//默认50   		//64G的机器设置：30
    
    show global  variables like 'back_log';				500		//默认80			//64G的机器设置：4096

**（PS：对齐实在是太难了，这个样式调了老半天。。）**

`mysql`的参数很多，但主要影响性能的就是上面这些，针对大部分的情况(中小型业务)，修改这些设置可以有效提升`mysql`的性能，其他的设置  
用默认的就可以。关于查看`mysql`各种性能指标，可以参考：[通过查看mysql 配置参数、状态来优化你的mysql][mysql _mysql]

## 五、总结 ##

通过对比可以看到,`mysql`有些默认的值实在是太小了，根本发挥不出来机器的性能。明明内存那么大，结果却放着不用，这性能也是非常糟糕了。还有`innodb_flush_log_at_trx_commit` 和`sync_binlog`，这两个默认的设置恰恰是写入性能最低的搭配。

### 1、数据库偏向于写操作 ###

具体的配置还是要根据自己数据库的偏好来的，如果数据库用到的事务比较多，那么建议加大事务相关的配置。如果更偏向于写并且表很大的话，要增加表空间大小，并且增加缓冲区大小，同时事务相关的也要大一些，因为每次插入也是要经过事务处理的，主要是`innodb_log_file_size`参数要设置的大一些。

### 2、数据库偏向于读操作 ###

如果是更偏向于读，那么事务相关的空间可以小一些，`table_open_cache`和`open_files_limit`也可以设置的大一些，当 `Mysql` 访问一个表时，如果该表在缓存中已经被打开，则可以直接访问缓存。临时表空间可以适当增大，因为大数据量的查询，用到临时表也很正常，还有`read_buffer_size`等参数也要注意。

### 3、补充 ###

以上更多的是一种大致的建议，设计要设置的参数很多，偏重于读或者写的配置侧重点也是不同的，大家先了解各个配置代表的意思，这样就能设计出最合适自己机器的配置了。

根据上面的分析，我们可以发现，`mysql`的性能是根据不同的配置而变化的，并不是单一的调大某个参数，性能就能来个飞速上涨。还是木桶原理，**木桶能装多少水，取决的是最短的那个板子**，我们只能尽量让板子都均衡一些，尽量多装一些水而已。

**end**

[MariaDB_MySQL_my.cnf]: https://www.centos.bz/2018/02/mariadb-mysql%E9%85%8D%E7%BD%AE%E6%96%87%E4%BB%B6my-cnf%E8%A7%A3%E8%AF%BB/
[mysql _mysql]: https://www.cnblogs.com/zengkefu/p/5634858.html