MySQL工作中常见的基本参数优化

原创 2016年05月30日 14:40:48

Cache缓存和buffer缓存

cache缓存的内容一般都是一些频繁访问但是变更较少的数据,如果cache缓存满了,mysql将淘汰最近最久没有使用的page页,然后将正在使用的page页加载到cache缓存。

 

Buuer缓存主要用于缓冲写,缓存的数据是即将被写入硬盘的,用于加速写。

 

全局缓冲  会话缓存 临时缓存

 

超时:连接超时 锁等待超时

 

show variables like '%timeout%';

 

 

 

innodb_rollback_on_timeout 希望整个事务回滚就开启吧少年

如果事务因为行级锁等待超时(innodb_lock_wait_timeout)会回滚上一条语句执行的操作,设置为ON,回滚整个事务的操作

 

 

lock_wait_timeout元数据的超时时间 默认1

 

slave_net_timeout 主从复制超时时间,可以设置为30s,较少网络问题导致的主从同步数据延迟

 

 

delayed_insert_timeout      myisam表的延迟插入超时

 

Mysql连接优化

 

show status like '%connections%';  查看当前服务实例连接的状态

show variables like '%connect%';   连接参数信息

 

max_connect_errors 连接失败过多 并且超过max_connect_errors值  ,可以执行flush hosts

 

 

 show variables like 'thread%';  查看连接线程参数

show status like 'thread%';      查看连接线程状态

 

重点  (Connections-Threads_created/Connections*100% 代表缓存命中率,命中率较低增加thread_cache_size的值

 

 

show status like 'Aborted%';查看连接异常状态信息

 

 

 

Aborted_clients 客户机被异常关闭次数 增大max_allowed_packet 网络原因可以增加wait_timeout或者interactive_timeout

 

 

show status like 'Slow%';查看其它连接状态

 

Slow_launch_threads  改值较大 说明服务器存在过载问题

 

 

 

缓存的优化

查询缓存

show variables like '%query_cache%';

 

show status like 'Qcache%'; 查看查询缓存状态信息,确认query_cache_size值是否合理

查询缓存命中率计算

show status like 'Com_select';

 

Qcache_hits/(Qcache_hits+ Com_select)

 

 

Innodb 排序缓存

 

 

show variables like 'join_buffer_size';   join连接缓存   提升表之间join连接速度

 

 

show variables like 'table%';

 

table_definition_cache  设置了表结构定义缓存表或者视图结构定义frm文件的上限

 

table_open_cache   该值表示设置了表缓存可以缓存表和视图的数量上限

 

 

show variables like '%open%';  当前打开文件数量的上限

 

show open tables;  查看表缓存缓存了那些表

 

 

show variables like '%buffer_size%';  read_rnd_buffer_size  随机扫描   增加该值 提升order by 排序操作的性能

read_buffer_size  做很多全表扫描查询 可以增加该值 不过只对myisam生效

 

 

二进制日志缓存

为了减少硬盘IO次数,更新操作产生的二进制日志不会立即被写入磁盘,首先写入二进制缓存中,更新提交后才会写入磁盘

 

show variables like '%binlog%cache%';

 

 

 

 

 binlog_cache_size 针对innodb做批量更新时可以适当提升

 

binlog_stmt_cache_size  这是针对myisam

 

 

show status like '%binlog%cache%';  查看二进制日志缓存的使用状态

 

Binlog_cache_disk_use    针对innodb、该值较大的话 应当适当提升binlog_cache_size   因为这是由于更新操作产生的日志信息较多,超过了binlog_cache_size 值,就会将缓存的二进制信息写入到一个临时文件

 

重做日志缓存

事务在commit之前会将重做日志写入重做日志缓存,再择机写入ib_logfile0ib_logfile1重做日志文件

 

show variables like 'innodb_log_buffer_size';  查看重做日志缓存大小

 

重点:不过缓存中的重做日志不可能无限增长,这时候大神就出现了   innodb_flush_log_at_trx_commit   特别重要的一个参数   0 1 2的状态    默认是1

 

默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘,这是很费时的。特别是使用电 池供电缓存(Battery backed up cache)时。设成2对于很多运用,特别是从MyISAM表转过来的是可以的,它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘,所以你一般不会丢失超过1-2秒的更新。设成0会更快一点,但安全方面比较差,即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。

 

 

 

show variables like 'innodb_buffer_pool_size';   innodb核心

这个参数对innodb整体性能影响较大,可以增加大小,将频繁访问的数据都放到内存中去,尽可能减少对硬盘的访问。

 

 

Innodb缓存池预热问题

 

Innodb会将经常需要访问的数据访问缓存池,当缓存池特别大的时候,由于某些原因,服务器需要重启,这时候所有缓存再缓存池的数据将会被清空,,所以5.6开始后支持将内存中的热数据保存到硬盘,重启后再将硬盘的热数据加载到缓存池

 

show variables like '%innodb%pool%'

 

show engine innodb status\G

 

版权声明:本文为博主原创文章,未经博主允许不得转载。

MySQL性能优化之参数配置

1、目的: 通过根据服务器目前状况,修改Mysql的系统参数,达到合理利用服务器现有资源,最大合理的提高MySQL性能。   2、服务器参数: 32G内存、4个CPU,每个CP...
  • nightelve
  • nightelve
  • 2013年12月18日 13:34
  • 49442

mysql服务性能优化—my.cnf配置说明详解(16G内存)

此配置是老男孩生产线上使用的配置,在培训的时候,他给的,我在这里,对各参数添加了中文说明 这配置已经优化的不错了,如果你的mysql没有什么特殊情况的话,可以直接使用该配置参数 MYSQL...
  • orichisonic
  • orichisonic
  • 2015年08月27日 12:55
  • 24534

mysql优化之参数调优

在MYSQL的优化过程中,我们至少有三种办法,可以达到优化的目的,效率从低到高依次是 替换有问题的硬件。对MYSQL进程的设置进行调优。对查询进行优化。 前面我们讨论过通过索引...
  • zmxiangde_88
  • zmxiangde_88
  • 2012年09月08日 11:17
  • 972

mysql性能优化之配置优化

1、目的: 通过根据服务器目前状况,修改MySQL的系统参数,达到合理利用服务器现有资源,最大合理的提高MySQL性能。   2、服务器参数: 32G内存、4个CPU,每个CP...
  • Steven_liwen
  • Steven_liwen
  • 2016年11月16日 17:20
  • 4200

MySql参数优化

MySql参数优化 windows系统配置文件在xxx\MySQL\MySQL Server 下 my.ini show global status可以列出mysql服务器运行各种...
  • u012104435
  • u012104435
  • 2016年03月17日 17:36
  • 1164

MySQL性能优化 SQL优化方法技巧

  • 2016年05月23日 14:15
  • 1.08MB
  • 下载

MYSQL SHOW VARIABLES简介

mysqld服务器维护两种变量。全局变量影响服务器的全局操作。会话变量影响具体客户端连接相关操作。 服务器启动时,将所有全局变量初始化为默认值。可以在选项文件或命令行中指定的选项来更改这些默认值。服...
  • hemeinvyiqiluoben
  • hemeinvyiqiluoben
  • 2015年12月28日 09:58
  • 1424

mysql常用参数汇总

mysql数据库的参数以及我对这些参数用法的思想总结。
  • TearsGod
  • TearsGod
  • 2016年12月20日 10:04
  • 177

mysql中各种类型变量的定义以及赋值使用

该文总体框架http://www.cnblogs.com/zhuawang/p/4090916.htmlhttp://blog.csdn.net/seteor/article/details/1768...
  • JQ_AK47
  • JQ_AK47
  • 2016年08月01日 17:19
  • 25885

MYSQL性能优化之参数配置

1、目的: 通过根据服务器目前状况,修改MySQL的系统参数,达到合理利用服务器现有资源,最大合理的提高MySQL性能。   2、服务器参数: 32G内存、4个CPU,每个CP...
  • yzg001212
  • yzg001212
  • 2016年07月21日 15:52
  • 2090
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySQL工作中常见的基本参数优化
举报原因:
原因补充:

(最多只允许输入30个字)