75142913在线留言
mysql 配置文件my.cnf优化参数配置说明_服务器与网络_网络人

mysql 配置文件my.cnf优化参数配置说明

Kwok 发表于:2020-03-26 11:26:17 点击:64 评论: 0
skip-external-locking

跳过外部锁定,用于多进程条件下为MyISAM数据表进行锁定。现在我的实例数据库都使用了InnoDB,所以没有细细去品这个参数。

 

key_buffer_size = 64M

对MyISAM表性能影响很大,用于主健缓冲使用的。从内存读取索引率应高于 95%,使用的变量/公式:100 - (Key_reads / Key_read_requests * 100)

 

max_allowed_packet = 1M

最大允许提交的数据,比如你要插入一个很大的数据包到库里,大于1M就会报错。类似上传文件限制或者POST内容原理。如果设置超过1G,查看最终生效结果也只有1G。增大此配置的值并不会有太多危险,因为额外的内存仅仅只在需要的时候被分配。

 

table_open_cache = 2048

每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。

通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_open_cache的值。

使用:

SHOW GLOBAL STATUS LIKE 'open%tables'

 得到以下值:

Open_tables 2048

Opened_tables 84045

然后再使用:

show variables like '%table_open_cache%'

table_open_cache 2048

table_open_cache_instances 16

发现open_tables等于table_open_cache,都是2048,说明mysql正在将缓存的表释放以容纳新的表(通俗讲,就是缓存不够,需要加大了),4G内存的机器,建议设置为2048

比较适合的参考值:

Open_tables / Opened_tables >= 0.85

Open_tables / table_open_cache <= 0.95

 

sort_buffer_size = 1M

sort_buffer_size 是是一个connection级参数,意思是每个连接都分配内存的参数,调整这个参数要格外注意,不是越大越好,够用就行。过大的设置+高并发可能会耗尽系统内存资源。sort_buffer_size 来加速ORDER BY 或者GROUP BY 操作,不能通过查询或者索引优化的。如果你需要排序的语句比较多可以适当增加这个值。

 

net_buffer_length = 8K

每个客户端连接时。用于维持连接缓冲,初始分配预设值,在有需要时,则会自动扩大到max_allowed_packet大小,然后在回收预设的net_buffer_length大小 最小1k 最大1m默认16k

 

read_buffer_size = 2M

如果要对大型表进行快速的全表扫描,则应将此变量设置为较高的值。大内存大小的示例my.cnf值建议使用1M设置,MySQL内置默认值为128K。有些人拥有大量内存,并且很少有并发连接,因此将其设置为32M以希望获得更好的性能。让我们看看这是否真的是最好的策略。

 

read_rnd_buffer_size = 1M

read_rnd_buffer_size是MySQL的随机读缓冲区大小,当按任意顺序读取行时(列如按照排序顺序)将分配一个随机读取缓冲区,进行排序查询时,MySQL会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要大量数据可适当的调整该值,但MySQL会为每个客户连接分配该缓冲区所以尽量适当设置该值,以免内存开销过大。表的随机的顺序缓冲 提高读取的效率。

 

myisam_sort_buffer_size =64M

MyISAM表发生变化时重新排序所需的缓冲。一般64M足矣。

 

thread_cache_size = 128

线程池缓存大小(当客户端断开连接后,将当前线程缓存起来,当在接到新的连接请求时快速响应,无需创建新的线程。 )因为每个线程的连接/断开都需要。如果应用程序中有大量的跳跃并发连接并且线程较多的话,就要加大它的值。它的目的是在通常的操作中无需创建新线程。内存大于3G左右内存的机器设置64~128基本足够使用。

 

query_cache_type = 0

为1是使用缓冲,2是除非使用SQL_CACHE才进行缓冲。对于缓冲而言,数据并不是实时的,有一定的延时。但是对于实时性要求不高的查询短时间内多次执行,是不划算的,这个时候就需要缓存。并且缓存中是区分空格和大小写的,如果大小写不一致和空格不一致,也会认为是不同的SQL,不会利用到缓存。虽然不设置查询缓冲,有时可能带来性能上的损失,但有一些SQL语句需要实时地查询数据,或并不经常使用(可能一天就执行一两次),这样就需要把缓冲关了。

 

query_cache_size = 0

分配查询缓存的大小。如果应用程序有大量读,而且没有应用程序级别的缓存,那么这很有用。不过不要设置太大,因为维护它也需要不少开销,这会导致MySQL变慢。由于我使用了REDIS做为缓存,所以这里我直接关闭了MYSQL的缓存。

注:mysql8.0+已删除了这个参数。我猜官方也觉得这个并没有什么太大的作用,反而还拖累了稳定性。造成的问题比它解决问题要多的多, 弊大于利就直接砍掉了。

 

tmp_table_size = 128M
max_heap_table_size = 128M

规定了内部内存临时表的最大值,每个线程都要分配。(实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。)如果内存临时表超出了限制,MySQL就会自动地把它转化为基于磁盘的MyISAM表,存储在指定的tmpdir目录下。

 

performance_schema_max_table_instances = 2048

这个参数是检测的表对象的最大数目。这台服务器可能会有最多表的情况,比如我的系统只有60个表,我就可以适当改下这个值为600或者400即可,会大大减少Mysql的内存占用哦。官方默认12500

 

table_definition_cache = 2048

这个参数是缓存frm文件。

 

max_connections = 512

最大连接数,也就是支持多少个客户端的同时连接数,并不是越大越好。

 

max_connect_errors = 1000

如果MySQL服务器连续接收到了来自于同一个主机的请求,而且这些连续的请求全部都没有成功的建立连接就被中断了,当这些连续的请求的累计值大于max_connect_errors的设定值时,MySQL服务器就会阻止这台主机后续的所有请求。如果是远程服务器可适当的调高这个值。

 

open_files_limit = 65535

文件最大的打开数。系统一般默认可打开65535个

 

innodb_file_per_table = 1

独立表空间开关,如果设置为0即使用共享表空间。

共享表空间:可以将表空间分成多个文件存放到各个磁盘上。所有的数据和索引存放到一个文件中,虽然可以把一个大文件分成多个小文件,但是多个表及索引在表空间中混合存储,这样对于一个表做了大量删除操作后表空间中将会有大量的空隙,特别是对于统计分析,日值系统这类应用最不适合用共享表空间。

独立表空间:每个表都有自已独立的表空间。每个表的数据和索引都会存在自已的表空间中。可以实现单表在不同的数据库中移动。

 

innodb_buffer_pool_size = 6G

看到这个值的时候是不是有点小惊呀!没错,我还算设置得比较小的,因为我还需要分配给Redis使用的内存,因为我是16G内存,如果我只跑Mysql的情况下这个值设置在12G左右是比较合适的。差不多是总内存的75%。

innodb_buffer_pool_size对InnoDB的效率影响很大。因为InnoDB会把尽可能多的数据和索引缓存在缓冲区。

 

innodb_log_file_size = 2G

为InnoDB引擎设置合适的Redo log空间对于写敏感的工作负载来说是非常重要的。然而,这项工作是要做出权衡的。你配置的Redo空间越大,InnoDB就能更好的优化写操作;然而,增大Redo空间也意味着更长的恢复时间当出现崩溃或掉电等意外时。

高写入负载尤其是数据集很大的时候,这个值非常重要,值越高性能越好。

 

innodb_log_buffer_size = 16M

默认的设置在中等强度的写入负载及短事物处理时,性能还可以。但是存在大量更新操作或者负载较大时,就要慢慢增加这个参数的值了。不过不要设置太大,会浪费内存。它每秒都会刷新一次,所以不用设置超过1s所需的内存空间,16M足够了。

 

innodb_flush_log_at_trx_commit = 2

设置为0:log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候,不会主动触发写入磁盘的操作。

设置为1:每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去,该模式为系统默认。

设置为2:每次事务提交时MySQL都会把log buffer的数据写入log file,但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

设置为2兼顾效率与安全。设置为1最安全!!!

 

innodb_lock_wait_timeout = 50

默认参数:innodb_lock_wait_timeout设置锁等待的时间是50s,一旦数据库锁超过这个时间就会报错。

除非注明,网络人的文章均为原创,转载请以链接形式标明本文地址:https://www.55mx.com/post/65
标签:mysql优化Kwok最后编辑于:2020-03-26 19:26:55
0
感谢打赏!

《mysql 配置文件my.cnf优化参数配置说明》的网友评论(0)

本站推荐阅读

热门点击文章