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

文章目录

    • 一、前言
    • 二、查看服务器的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的个数

 //查看逻辑CPU的个数
cat /proc/cpuinfo| grep "processor"| wc -l            
40

 //查看物理CPU的个数
cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l   数
2

//查看物理CPU的内核数
 cat /proc/cpuinfo | grep "cores" | uniq 		//查看物理CPU的内核数
cpu cores	: 10

(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解读 ,注释部分只是列出来部分参数的含义,建议是先弄懂各个参数的含义再对比设置会比较好。

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

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

1、查看数据库版本

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

      这个版本具有MySQL 5.65.7的后端和重新实现的功能,可以参考文档:https://mariadb.com/kb/en/library/mariadb-10141-release-notes/
不过网上有种说法是10.1-35.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有些默认的值实在是太小了,根本发挥不出来机器的性能。明明内存那么大,结果却放着不用,这性能也是非常糟糕了。还有innodb_flush_log_at_trx_commitsync_binlog,这两个默认的设置恰恰是写入性能最低的搭配。

1、数据库偏向于写操作

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

2、数据库偏向于读操作

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

3、补充

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

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

end

你可能感兴趣的