欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Mysql优化配置

程序员文章站 2022-04-09 14:21:32
...
Mysql配置优化

一、环境介绍

Mysql版本:5.5.27

二、优化内容

字段

介绍

推荐值

skip-locking

避免MySQL的外部锁定,减少出错几率增强稳定性

back_log

MySQL可能的连接数量(linux下推荐小于512)

384

key_buffer_size

key_buffer_size指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。

对于内存在4GB左右的服务器该参数可设置为256M或384M。

注意:该参数值设置的过大反而会是服务器整体效率降低!

4G服务器

256M

max_allowed_packet

当MySQL客户端或mysqld服务器收到大于max_allowed_packet字节的信息包时,将发出“信息包过大”错误,并关闭连接

4M

thread_stack

主要用来存放每一个线程自身的标识信息,如线程id,线程运行时基本信息等等,我们可以通过 thread_stack 参数来设置为每一个线程栈分配多大的内存

192kb

table_cache

表高速缓存的数目

512

sort_buffer_size

n第一次需要使用这个buffer的时候,一次性分配设置的内存

512K

read_buffer_size

读查询操作所能使用的缓冲区大小

4M

join_buffer_size

联合查询操作所能使用的缓冲区大小

8M

myisam_sort_buffer_size

当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区。

64M

thread_cache_size

表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求,那么请求将从缓存中读取,(3G以上内存推荐为64)

64

query_cache_size

查询缓存区的最大长度

64M

tmp_table_size

如果一张临时表超出该大小,MySQL产生一个 The table tbl_name is full 形式的错误

256M

max_connections

最大用户连接数

最大连接数占上限连接数的85%左右

3000

max_connect_errors

它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。max_connect_errors的值与性能并无太大关系

10000000

wait_timeout

指定一个请求的最大连接时间,对于4GB左右内存的服务器可以设置为5-10。

10

thread_concurrency

该参数取值为服务器逻辑CPU数量×2

4

innodb_log_file_size

如果对 Innodb 数据表有大量的写入操作,那么选择合适的 innodb_log_file_size 值对提升MySQL性能很重要

256M

innodb_log_buffer_size

事务日志文件写操作缓存区的最大长度

8M

innodb_flush_logs_at_trx_commit

1) =1时,在每个事务提交时,日志缓冲被写到日志文件,对日志文件做到磁盘操作的刷新。Truly ACID。速度慢。
2) =2时,在每个事务提交时,日志缓冲被写到文件,但不对日志文件做到磁盘操作的刷新。只有操作系统崩溃或掉电才会删除最后一秒的事务,不然不会丢失事务。
3) =0时, 日志缓冲每秒一次地被写到日志文件,并且对日志文件做到磁盘操作的刷新。任何mysqld进程的崩溃会删除崩溃前最后一秒的事务

2

innodb_buffer_pool_size

innodb_buffer_pool_size 定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小

在专用数据库服务器上,可以考虑该值为物理内存大小的 60%-80%

1G

innodb_additional_mem_pool_size

除了缓存表数据和索引外,可以为操作所需的其他内部项分配缓存来提升InnoDB的性能。这些内存就可以通过此参数来分配。推荐此参数至少设置为2MB

2M

三、优化重点

1:max_connections

经常会遇见”MySQL: ERROR 1040: Too many connections”的情况,一种是访问量确实很高,MySQL服务器抗不住,这个时候就要考虑增加从服务器分散读压力,另外一种情况是MySQL配置文件中max_connections值过小:


比较理想的设置是
Max_used_connections / max_connections * 100% ≈ 85%
最大连接数占上限连接数的85%左右,如果发现比例在10%以下,MySQL服务器连接数上限设置的过高了。

2:Key_buffer_size

key_buffer_size是对MyISAM表性能影响最大的一个参数:

key_cache_miss_rate = Key_reads / Key_read_requests * 100%

key_cache_miss_rate在0.1%以下都很好(每1000个请求有一个直接读硬盘),如果key_cache_miss_rate在0.01%以下的话,key_buffer_size分配的过多,可以适当减少

3:临时表

比较理想的配置是:
Created_tmp_disk_tables / Created_tmp_tables * 100%

4:open table

Open_tables / Opened_tables * 100% >= 85%
Open_tables / table_cache * 100%

5:进程使用情况

如果发现Threads_created值比较大,那么就可以考虑把thread_cache_size的值设大一些

6:查询缓存

查询缓存利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size * 100%

查询缓存利用率在25%以下的话说明query_cache_size设置的过大,可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小,要不就是碎片太多。
查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%
查询缓存碎片率 = 20.46%,查询缓存利用率 = 62.26%,查询缓存命中率 = 1.94%,命中率很差,可能写操作比较频繁吧,而且可能有些碎片。

7:文件打开数

比较合适的设置:Open_files / open_files_limit * 100%

8:表锁情况

Table_locks_immediate表示立即释放表锁数,Table_locks_waited表示需要等待的表锁数,如果Table_locks_immediate / Table_locks_waited > 5000,最好采用InnoDB引擎,因为InnoDB是行锁而MyISAM是表锁,对于高并发写入的应用InnoDB效果会好些。

9:表扫描情况

计算表扫描率:
表扫描率 = Handler_read_rnd_next / Com_select
如果表扫描率超过4000,说明进行了太多表扫描,很有可能索引没有建好,增加read_buffer_size值会有一些好处,但最好不要超过8MB。

四、Mysql指定ip用户访问

主机部分就是代表允许的主机访问,%符号代表允许所有的主机
添加用户授权IP命令例子:

使用myuser/mypassword从ip为61.129.51.8的主机连接到mysql服务器:

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'61.129.0.0' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

五、总结

在实际配置中,每一台服务器性能是不一样,因此Mysql优化配置主要参照第三部分为主,先测试运行一段时间游戏,然后在进入Mysql去查看各个变量的值,然后根据公式去计算各个变量的值,是否在标准范围内,不在标准范围内的,都相应的上下调动一下.

相关标签: linux 稳定性