欢迎访问IT教程网

Mysql 优化

Mysql admin 624℃ 0评论

参数优化

max_connections

最大连接数,并发链接,默认151,

调整依据看show status like 'Max_used_connections';

select @@max_connections;

back_log  

暂存的连接数 ,达到最大连接数多出来的连接数,不建议设置

wait_timeout 和 interactive_timeout

select @@wait_timeout;   非活动超时时间默认8小时,建议设置60s

select @@interactive_timeout;     活动超时时间默认8小时,比如正在进行的事务处理,根据事务时间设置

key_buffer_size

索引缓存大小 ,临时表会使用

select @@key_buffer_size; 默认8M

设置依据

show status like "created_tmp%";

+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 11 |
+-------------------------+-------+

通常地,我们习惯以 Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 

或者已各自的一个时段内的差额计算,来判断基于内存的临时表利用率。所以,我们会比较关注 Created_tmp_disk_tables 是否过多,从而认定当前服务器运行状况的优劣。
Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) 
控制在5%-10%以内

query_cache_size

查询缓存  show variables like "query_cache_size";默认1M,默认关闭

show variables like "query_cache%";

判断依据

show status like "%Qcache%";
Qcache_free_blocks:缓存中相邻内存块的个数。 如果该值显示较大,则说明Query Cache 中的内存碎片较多了,FLUSH QUERY CACHE会对缓存中的碎片进行整理,从而得到一个空闲块。 注:当一个表被更新之后,和它相关的cache blocks将被free。但是这个block依然可能存在队列中,除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks
Qcache_free_memory:Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够,是需要增加还是过多了。
Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大,缓存效果越理想。
Qcache_inserts:表示多少次未命中然后插入,意思是新来的SQL请求在缓存中未找到,不得不执行查询处理,执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多,表示查询缓存应用到的比较少,效果也就不理想。当然系统刚启动后,查询缓存是空的,这很正常。
Qcache_lowmem_prunes: 多少条Query因为内存不足而被清除出QueryCache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合,能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够,是否非常频繁的出现因为内存不足而有Query 被换出。这个数字最好长时间来看;如果这个数字在不断增长,就表示可能碎片非常严重,或者内存很少。(上面的free_blocks和free_memory可以告诉您属于哪种情况)
Qcache_not_cached:不适合进行缓存的查询的数量,通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache:当前Query Cache 中cache 的Query 数量;
Qcache_total_blocks:当前Query Cache 中的block 数量;。
Qcache_hits / (Qcache_inserts+Qcache_not_cached+Qcache_hits)
90/ 10000 0 90 如果出现hits比例过低,其实就可以关闭查询缓存了。

max_connect_errors

最大尝试连接错误数,没有什么意义

sort_buffer_size

排序查询的线程分配的一个缓冲区,增加这个加速 建议配置1M

sort_buffer_size=1M  如果有500个连接则会立即消耗500M内存

max_allowed_packet

 服务端接受的数据包大小 ,建议设置大一点  

max_allowed_packet=512M

join_buffer_size

使用join查询使用的缓存大小 ,不需要太大,建议设置1M

thread_cache_size

服务器线程缓存个数 

show status like "%threads_%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 1 |
| Threads_connected | 5 |
| Threads_created | 6 |
| Threads_running | 4 |
+-------------------+-------+
4 rows in set (0.00 sec)

Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected:代表当前已建立连接的数量,因为一个连接就需要一个线程,所以也可以看成当前被使用的线程数。
Threads_created:代表从最近一次服务启动,已创建线程的数量,如果发现Threads_created值过大的话,表明MySQL服务器一直在创建线程,这也是比较耗cpu SYS资源,可以适当增加配置文件中thread_cache_size值。
Threads_running :代表当前激活的(非睡眠状态)线程数。并不是代表正在使用的线程数,有时候连接已建立,但是连接处于sleep状态。

建议1G内存设置8 依次类推,CPU特别繁忙设置

innodb_buffer_pool_size

对于单独的MySQL数据库服务器,最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%

innodb_buffer_pool_size=2048M

innodb_flush_log_at_trx_commit

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点,取值分别为0、1、2三个。

0,表示当事务提交时,不做日志写入操作,而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次;

1, 每次事务的提交都会引起redo日志文件写入、flush磁盘的操作,确保了事务的ACID;

2,每次事务提交引起写入日志文件的动作,但每秒钟完成一次flush磁盘操作。

实际测试发现,该值对插入数据的速度影响非常大,设置为2时插入10000条记录只需要2秒,设置为0时只需要1秒,而设置为1时则需要229秒。因此,MySQL手册也建议尽量将插入操作合并成一个事务,这样可以大幅提高速度。 根据MySQL官方文档,在允许丢失最近部分事务的危险的前提下,可以把该值设为0或2。

innodb_thread_concurrency

此参数用来设置innodb线程的并发数量,默认值为0表示不限制。

依据 通过show processlist 查看线程数

在官方doc上,对于innodb_thread_concurrency的使用,也给出了一些建议,
如下: 如果一个工作负载中,并发用户线程的数量小于64,建议设置innodb_thread_concurrency=0;
如果工作负载一直较为严重甚至偶尔达到顶峰,建议先设置innodb_thread_concurrency=128, 并通过不断的降低这个参数,96, 80, 64等等,直到发现能够提供最佳性能的线程数

innodb_log_buffer_size

此参数确定些日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,对于较大的事务,可以增大缓存大小。

innodb_log_buffer_size=128M

设定依据:

1、大事务: 存储过程调用 CALL

2、多事务

innodb_log_file_size = 100M

设置 ib_logfile0 ib_logfile1

此参数确定redo数据日志文件的大小,以M为单位,更大的设置可以提高性能.默认50M

innodb_log_file_size = 100M

innodb_log_files_in_group = 3 

为提高性能,MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

read_buffer_size = 1M

MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样,该参数对应的分配内存也是每个连接独享

read_rnd_buffer_size = 1M

MySql的随机读(查询操作)缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。 注:顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据,而辅助索引和主键所在的数据段不同,因此访问方式是随机的。

bulk_insert_buffer_size = 8M

批量插入数据缓存大小,可以有效提高插入效率,默认为8M

binary log

log-bin=/data/mysql-bin

binlog_cache_size = 2M //为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务,dml也不是很频繁的情况下可以设置小一点,如果事务大而且多,dml操作也频繁,则可以适当的调大一点。前者建议是--1M,后者建议是:即 2--4M

max_binlog_cache_size = 8M //表示的是binlog 能够使用的最大cache 内存大小

max_binlog_size= 512M //指定binlog日志文件的大小,如果当前的日志大小达到max_binlog_size,还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时,建议关闭sql_log_bin,否则硬盘扛不住,而且建议定期做删除。

expire_logs_days = 7 //定义了mysql清除过期日志的时间。 二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。

log-bin=/data/mysql-bin

binlog_format=row sync_binlog=1

双1标准(基于安全的控制):

sync_binlog=1 什么时候刷新binlog到磁盘,每次事务commit

innodb_flush_log_at_trx_commit=1

set sql_log_bin=0;

show status like 'com_%';

安全参数 

Innodb_flush_method=(O_DIRECT, fsync) 
1、fsync    :
(1)在数据页需要持久化时,首先将数据写入OS buffer中,然后由os决定什么时候写入磁盘
(2)在redo buffuer需要持久化时,首先将数据写入OS buffer中,然后由os决定什么时候写入磁盘
但,如果innodb_flush_log_at_trx_commit=1的话,日志还是直接每次commit直接写入磁盘
2、 Innodb_flush_method=O_DIRECT
(1)在数据页需要持久化时,直接写入磁盘
(2)在redo buffuer需要持久化时,首先将数据写入OS buffer中,然后由os决定什么时候写入磁盘
但,如果innodb_flush_log_at_trx_commit=1的话,日志还是直接每次commit直接写入磁盘

最安全模式:
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
最高性能模式:
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync
        
一般情况下,我们更偏向于安全。 
“双一标准”
innodb_flush_log_at_trx_commit=1        ***************
sync_binlog=1                                   ***************
innodb_flush_method=O_DIRECT

参数优化结果2G内存

[mysqld]
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock  

本文固定链接:IT教程网 » Mysql 优化
本站内容除特别标注外均为原创,欢迎转载,但请保留出处!

喜欢 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址