MySQL优化必须调整的10项配置

当我们被人雇来监测MySQL性能时,人们希望我们能够检视一下MySQL配置然后给出一些提高建议。许多人在事后都非常惊讶,因为我们建议他们仅仅改动几个设置,即使是这里有好几百个配置项。这篇文章的目的在于给你一份非常重要的配置项清单。

我们曾在几年前在博客里给出了这样的建议,但是MySQL的世界变化实在太快了!
写在开始前…
即使是经验老道的人也会犯错,会引起很多麻烦。所以在盲目的运用这些推荐之前,请记住下面的内容:

一次只改变一个设置!这是测试改变是否有益的唯一方法。

大多数配置能在运行时使用SET GLOBAL改变。这是非常便捷的方法它能使你在出问题后快速撤销变更。但是,要永久生效你需要在配置文件里做出改动。

一个变更即使重启了MySQL也没起作用?请确定你使用了正确的配置文件。请确定你把配置放在了正确的区域内(所有这篇文章提到的配置都属于 [mysqld])

服务器在改动一个配置后启不来了:请确定你使用了正确的单位。例如,innodb_buffer_pool_size的单位是MB而max_connection是没有单位的。

不要在一个配置文件里出现重复的配置项。如果你想追踪改动,请使用版本控制。

不要用天真的计算方法,例如”现在我的服务器的内存是之前的2倍,所以我得把所有数值都改成之前的2倍“。

基本配置
你需要经常察看以下3个配置项。不然,可能很快就会出问题。

innodb_buffer_pool_size:这是你安装完InnoDB后第一个应该设置的选项。缓冲池是数据和索引缓存的地方:这个值越大越好,这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存),20-25GB(32GB内存),100-120GB(128GB内存)。

innodb_log_file_size:这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。一直到MySQL 5.1,它都难于调整,因为一方面你想让它更大来提高性能,另一方面你想让它更小来使得崩溃后更快恢复。幸运的是从MySQL 5.5之后,崩溃恢复的性能的到了很大提升,这样你就可以同时拥有较高的写入性能和崩溃恢复性能了。一直到MySQL 5.5,redo日志的总尺寸被限定在4GB(默认可以有2个log文件)。这在MySQL 5.6里被提高。

一开始就把innodb_log_file_size设置成512M(这样有1GB的redo日志)会使你有充裕的写操作空间。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6,你可以一开始就把它这是成4G。

max_connections:如果你经常看到‘Too many connections'错误,是因为max_connections的值太低了。这非常常见因为应用程序没有正确的关闭数据库连接,你需要比默认的151连接数更大的值。max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。
InnoDB配置
从MySQL 5.5版本开始,InnoDB就是默认的存储引擎并且它比任何其他存储引擎的使用都要多得多。那也是为什么它需要小心配置的原因。

innodb_file_per_table:这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里(innodb_file_per_table = OFF) 或者为每张表的数据单独放在一个.ibd文件(innodb_file_per_table = ON)。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的,比如数据压缩。但是它不会带来任何性能收益。你不想让每张表一个文件的主要场景是:有非常多的表(比如10k+)。

MySQL 5.6中,这个属性默认值是ON,因此大部分情况下你什么都不需要做。对于之前的版本你必需在加载数据之前将这个属性设置为ON,因为它只对新创建的表有影响。

innodb_flush_log_at_trx_commit:默认值为1,表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的时候这个值是最合适的,比如在一个主节点上。但是对于磁盘(读写)速度较慢的系统,它会带来很巨大的开销,因为每次将改变flush到redo日志都需要额外的fsyncs。将它的值设置为2会导致不太可靠(reliable)因为提交的事务仅仅每秒才flush一次到redo日志,但对于一些场景是可以接受的,比如对于主节点的备份节点这个值是可以接受的。如果值为0速度就更快了,但在系统崩溃时可能丢失一些数据:只适用于备份节点。

innodb_flush_method: 这项配置决定了数据和日志写入硬盘的方式。一般来说,如果你有硬件RAID控制器,并且其独立缓存采用write-back机制,并有着电池断电保护,那么应该设置配置为O_DIRECT;否则,大多数情况下应将其设为fdatasync(默认值)。sysbench是一个可以帮助你决定这个选项的好工具。

innodb_log_buffer_size: 这项配置决定了为尚未执行的事务分配的缓存。其默认值(1MB)一般来说已经够用了,但是如果你的事务中包含有二进制大对象或者大文本字段的话,这点缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量,如果它不是0,增加innodb_log_buffer_size。
其他设置
query_cache_size: query cache(查询缓存)是一个众所周知的瓶颈,甚至在并发并不多的时候也是如此。 最佳选项是将其从一开始就停用,设置query_cache_size = 0(现在MySQL 5.6的默认值)并利用其他方法加速查询:优化索引、增加拷贝分散负载或者启用额外的缓存(比如memcache或redis)。如果你已经为你的应用启用了query cache并且还没有发现任何问题,query cache可能对你有用。这是如果你想停用它,那就得小心了。

log_bin:如果你想让数据库服务器充当主节点的备份节点,那么开启二进制日志是必须的。如果这么做了之后,还别忘了设置server_id为一个唯一的值。就算只有一个服务器,如果你想做基于时间点的数据恢复,这(开启二进制日志)也是很有用的:从你最近的备份中恢复(全量备份),并应用二进制日志中的修改(增量备份)。二进制日志一旦创建就将永久保存。所以如果你不想让磁盘空间耗尽,你可以用 PURGE BINARY LOGS 来清除旧文件,或者设置 expire_logs_days 来指定过多少天日志将被自动清除。

记录二进制日志不是没有开销的,所以如果你在一个非主节点的复制节点上不需要它的话,那么建议关闭这个选项。

skip_name_resolve:当客户端连接数据库服务器时,服务器会进行主机名解析,并且当DNS很慢时,建立连接也会很慢。因此建议在启动服务器时关闭skip_name_resolve选项而不进行DNS查找。唯一的局限是之后GRANT语句中只能使用IP地址了,因此在添加这项设置到一个已有系统中必须格外小心。

总结

当然还有其他的设置可以起作用,取决于你的负载或硬件:在慢内存和快磁盘、高并发和写密集型负载情况下,你将需要特殊的调整。然而这里的目标是使得你可以快速地获得一个稳健的MySQL配置,而不用花费太多时间在调整一些无关紧要的MySQL设置或读文档找出哪些设置对你来说很重要上。

时间: 2014-02-26

MySQL 4G内存服务器配置优化

公司网站访问量越来越大(日均超10万PV),MySQL自然成为瓶颈,关于 MySQL 的优化,最基本的是 MySQL 系统参数的优化. MySQL对于web架构性能的影响最大,也是关键的核心部分.MySQL的设置是否合理优化,直接影响到web的速度和承载量!同时,MySQL也是优化难度最大的一个部分,不但需要理解一些MySQL专业知识,同时还需要长时间的观察统计并且根据经验进行判断,然后设置合理的参数. 下面我们了解一下MySQL优化的一些基础,MySQL自身(my.cnf)的优化. 我们介绍一

MySQL5.6基本优化配置

随着 大量默认选项的改进, MySQL 5.6比以前版本需要调优的选项大为减少. 在本文中我将讲述需要优化的配置项. InnoDB设置 1.innodb_buffer_pool_size  -- 默认值为 128M. 这是最主要的优化选项,因为它指定 InnoDB 使用多少内存来加载数据和索引(data+indexes). 针对专用MySQL服务器,建议指定为物理内存的 50-80%这个范围. 例如,拥有64GB物理内存的机器,缓存池应该设置为50GB左右. 如果将该值设置得更大可能会存在风险,

MySQL性能优化之路---修改配置文件my.cnf

在Apache, PHP, MySQL的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分.对于Discuz!论坛程序也是如此,MySQL的设置是否合理优化,直接影响到论坛的速度和承载量!同时,MySQL也是优化难度最大的一个部分,不但需要理解一些MySQL专业知识,同时还需要长时间的观察统计并且根据经验进行判断,然后设置合理的参数. 下面我们了解一下MySQL优化的一些基础,MySQL的优化我分为两个部分,一是服务器物理硬件的优化,二是MySQL自身(my.cnf)的优化. 一.服务

Nginx 0.7.x + PHP 5.2.6(FastCGI)+ MySQL 5.1 在128M小内存VPS服务器上的配置优化第1/2页

对其用户和应用程序来讲,每一个VPS平台的运行和管理都与一台独立主机完全相同,因为每一个VPS均可独立进行重启并拥有自己的root访问权限.用户.IP地址.内存.过程.文件.应用程序.系统函数库以及配置文件.VPS服务器最重要的指标就是内存大小,多个VPS服务器可以共享一颗CPU,但不能共享同一块内存.内存越大,价格越贵. 下面,以我的博客所在的VPS为例,介绍在128M内存下对 Nginx 0.7.x + PHP 5.2.6(FastCGI)+ MySQL 5.1 的优化. 至于 Nginx

MySQL性能优化之max_connections配置参数浅析

MySQL的max_connections参数用来设置最大连接(用户)数.每个连接MySQL的用户均算作一个连接,max_connections的默认值为100.本文将讲解此参数的详细作用与性能影响. 与max_connections有关的特性 MySQL无论如何都会保留一个用于管理员(SUPER)登陆的连接,用于管理员连接数据库进行维护操作,即使当前连接数已经达到了max_connections.因此MySQL的实际最大可连接数为max_connections+1: 这个参数实际起作用的最大值

MySQL性能优化配置参数之thread_cache和table_cache详解

一.THREAD_CACHE MySQL里面为了提高客户端请求创建连接过程的性能,提供了一个连接池也就是 Thread_Cache池,将空闲的连接线程放在连接池中,而不是立即销毁.这样的好处就是,当又有一个新的请求的时候,mysql不会立即去创建连接 线程,而是先去Thread_Cache中去查找空闲的连接线程,如果存在则直接使用,不存在才创建新的连接线程. 有关Thread_Cache在MySQL有几个重要的参数,简单介绍如下: thread_cache_size Thread_Cache 中

MySQL配置文件my.cnf参数优化和中文详解

Mysql参数优化对于新手来讲,是比较难懂的东西,其实这个参数优化,是个很复杂的东西,对于不同的网站,及其在线量,访问量,帖子数量,网络情况,以及机器硬件配置都有关系,优化不可能一次性完成,需要不断的观察以及调试,才有可能得到最佳效果. 复制代码 代码如下: [client]port = 3306socket = /tmp/mysql.sock [mysqld]port = 3306socket = /tmp/mysql.sock basedir = /usr/local/mysqldatadi

Mysql5.5 InnoDB存储引擎配置和优化

环境为CentOS系统,1G内存,Mysql5.5.30.在/etc/my.cnf内添加: 复制代码 代码如下: skip-external-lockingskip-name-resolvemax_connections = 1024query_cache_size = 16Msort_buffer_size = 1Mtable_cache = 256innodb_buffer_pool_size = 128Minnodb_additional_mem_pool_size = 4Minnodb_

MySQL配置文件my.cnf中文详解附mysql性能优化方法分享

下面先说我的服务器的硬件以及论坛情况,CPU: 2颗四核Intel Xeon 2.00GHz内存: 4GB DDR硬盘: SCSI 146GB论坛:在线会员 一般在 5000 人左右 – 最高记录是 13264.下面,我们根据以上硬件配置结合一份已经做过一次优化的my.cnf进行分析说明:有些参数可能还得根据论坛的变化情况以及程序员的程序进行再调整.[mysqld]port = 3306serverid = 1socket = /tmp/mysql.sockskip-locking # 避免My

Android WebView使用方法详解 附js交互调用方法

目前很多Android app都内置了可以显示web页面的界面,会发现这个界面一般都是由一个叫做WebView的组件渲染出来的,学习该组件可以为你的app开发提升扩展性. 先说下WebView的一些优点: --可以直接显示和渲染web页面,直接显示网页 --webview可以直接用html文件(网络上或本地assets中)作布局 --和JavaScript交互调用 一.基本使用 首先layout中即为一个基本的简单控件: <WebView android:id="@+id/webView1

CentOS 6.6 源码编译安装MySQL 5.7.18教程详解

一.添加用户和组 1.添加mysql用户组 # groupadd mysql 2.添加mysql用户 # useradd -g mysql -s /bin/nologin mysql -M 二.查看系统中是否安装mysql,如果安装需要卸载 # rpm -qa | grep mysql mysql-libs-5.1.73-3.el6_5.x86_64 # rpm -e mysql-libs-5.1.73-3.el6_5.x86_64 --nodeps 三.安装所需依赖包 # yum -y ins

PHP定时备份MySQL与mysqldump语法参数详解

先来为大家介绍几个MySQL备份命令mysqldump常用操作实例: 1.mysqldump备份 只导出表结构 d:/PHP/xampp/mysql/bin/mysqldump -h127.0.0.1 -P3306 -uroot -p123456 snsgou_sns_test --no-data --default_character-set=utf8 > d:/Python/data/snsgou_sns_test_table.sql mysqldump只导出数据 d:/PHP/xampp/

redhat7.1 安装mysql 5.7.10步骤详解(图文详解)

在redhat下安装MySQL,步骤如下 Mysql目录安装位置:/usr/local/mysql 数据库保存位置:/data/mysql 日志保存位置:/data/log/mysql 下载安装包 http://downloads.mysql.com/archives/community/ 1. 获取mysql安装包,mysql-5.7.10-Linux-glibc2.5-x86_64.tar解压后目录如下. 2. 解压mysql-5.7.10-linux-glibc2.5-x86_64.tar

优化MyBatis配置文件中的配置详解

本文研究的主要是优化MyBatis配置文件中的配置的相关内容,具体介绍如下. 一.连接数据库的配置单独放在一个properties文件中 之前,我们是直接将数据库的连接配置信息写在了MyBatis的conf.xml文件中,如下: <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN&

mysql下载与安装过程详解

1:下载MySql 官网下载地址:https://dev.mysql.com/downloads/mysql/ 选择对应的下载文件.(我电脑是64位,所以这下载的是64位的下载文件) 2:安装MySql 打开下载文件解压到指定文件目录.(我这里解压目录为D:\mysql-5.7.21-winx64) 打开解压后的MySql文件在根目录下创建my.ini (mysql配置文件) my.ini文件内容如下: (建议直接复制粘贴下面文件) 这里需要将basedir 与 datadir 路径改成mysq

MySQL模式 Strict Mode知识点详解

I. Strict Mode阐述 根据 mysql5.0以上版本 strict mode (STRICT_TRANS_TABLES) 的限制: 1).不支持对not null字段插入null值 2).不支持对自增长字段插入''值,可插入null值 3).不支持 text 字段有默认值 看下面代码:(第一个字段为自增字段) Sql代码 $query="insert into demo values('','$firstname','$lastname','$sex')"; 上边代码只在非

Mybatis主配置文件的properties标签详解

我们配置MYSQL的连接池时可以直接写,也可以从外部导入配置文件. 1.直接在dataSource中写数据库基本信息 <dataSource type="POOLED"> <!-- 配置连接数据库的4个基本信息 --> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value=&

mysql数据存储过程参数实例详解

MySQL 存储过程参数有三种类型:in.out.inout.它们各有什么作用和特点呢? 一.MySQL 存储过程参数(in) MySQL 存储过程 "in" 参数:跟 C 语言的函数参数的值传递类似, MySQL 存储过程内部可能会修改此参数,但对 in 类型参数的修改,对调用者(caller)来说是不可见的(not visible). drop procedure if exists pr_param_in; create procedure pr_param_in ( in id