文档章节

MySQL数据库服务器优化详细

Amamatthew
 Amamatthew
发布于 2014/08/20 16:57
字数 3323
阅读 20
收藏 1

最近在网上查了一些有关优化MySql的资料,并对照MySql手册对一些调优设置进行了细结,但是由于时间比较勿忙,没来得及对这些设置进行实验/测试,你可以把这些方法应用到实际中,得出具体结论。

调优方法大致如下:

MySql服务器的后台管理程序,要想使用客户端程序,该程序必须运行,因为客户端通过连接服务器来访问数据库。下面让我们以服务器的系统变量和状态变量为根据,优化我们的MySql数据库服务。在这之前,我们需要掌握以下方法:

查看MySql状态及变量的方法:

Mysql> show status ——显示状态信息(扩展show status like 'XXX')

Mysql> show variables ——显示系统变量(扩展show variables like 'XXX')

Mysql> show innodb status ——显示InnoDB存储引擎的状态

Shell> mysqladmin variables -u username -p password——显示系统变量

Shell> mysqladmin extended-status -u username -p password——显示状态信息

 

查看状态变量及帮助:

Shell> mysqld --verbose --help [|more #逐行显示]

 

首先,让我们看看有关请求连接的变量:

为了能适应更多数据库应用用户,MySql提供了连接(客户端)变量,以对不同性质的用户群体提供不同的解决方案,笔者就max_connections,back_log 做了一些细结,如下:

max_connections 是指MySql的最大连接数,如果服务器的并发连接请求量比较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越 多,介于MySql会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值。可以过'conn%'通配符查看当前状态的 连接数量,以定夺该值的大小。

back_log 是要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且 启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。如果期望在一个短时间内有很多连接, 你需要增加它。也就是说,如果MySql的连接数据达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈 的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。另外,这值(back_log)限于您的操作系统对到来的 TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制(可以检查你的OS文档找出这个变量的最大值),试图设定 back_log高于你的操作系统的限制将是无效的。

优化了MySql的连接后属性后,我们需要看看缓冲区变量:

使用MySql数据库存储大量数据(或使用复杂查询)时,我们应该考虑MySql的内存配置。如果配置MySQL服务器使用太少的内存会导致性 能不是最优的;如果配置了太多的内存则会导致崩溃,无法执行查询或者导致交换操作严重变慢。在现在的32位平台下,仍有可能把所有的地址空间都用完,因此 需要审视。

计算内存使用的秘诀公式就能相对地解决这一部分问题。不过,如今这个公式已经很复杂了,更重要的是,通过它计算得到的值只是“理论可能”并不是 真正消耗的值。事实上,有8GB内存的常规服务器经常能运行到最大的理论值(100GB甚至更高)。此外,你轻易不会使用到“超额因素”(它实际上依赖于 应用以及配置)。一些应用可能需要理论内存的10%而有些仅需1%。

那么,我们可以做什么呢?

 

来看看那些在启动时就需要分配并且总是存在的全局缓冲吧!

 

全局缓冲:

key_buffer_size, innodb_buffer_pool_size, innodb_additional_mem_pool_size,innodb_log_buffer_size, query_cache_size

 

注:如果你大量地使用MyISAM表,那么你也可以增加操作系统的缓存空间使得MySQL也能用得着。把这些也都加到操作系统和应用程序所需的 内存值之中,可能需要增加32MB甚至更多的内存给MySQL服务器代码以及各种不同的小静态缓冲。这些就是你需要考虑的在MySQL服务器启动时所需的 内存。其他剩下的内存用于连接。

 

key_buffer_size 决定索引处理的速度,尤其是索引读的速度。一般我们设为16M,通过检查状态值Key_read_requests和Key_reads,可以知道 key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用'key_read%'获得用来显示状态数 据)。key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。可 以使用检查状态值'created_tmp_disk_tables'得知详情。

 

innodb_buffer_pool_size 对于InnoDB表来说,作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和 索引。对于单独的MySQL数据库服务器,最大可以把该值设置成物理内存的80%。

 

innodb_additional_mem_pool_size 指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小。缺省值是1M。通常不用太大,只要够用就行,应该与表结构的复杂度有关系。如果不够用,MySQL会在错误日志中写入一条警告信息。

 

innodb_log_buffer_size 指定InnoDB用来存储日志数据的缓存大小,如果您的表操作中包含大量并发事务(或大规模事务),并且在事务提交前要求记录日志文件,请尽量调高此项值,以提高日志效率。

 

query_cache_size 是MySql的查询缓冲大小。(从4.0.1开始,MySQL提供了查询缓冲机制)使用查询缓冲,MySQL将SELECT语句和查询结果存放在缓冲区 中,今后对于同样的SELECT语句(区分大小写),将直接从缓冲区中读取结果。根据MySQL用户手册,使用查询缓冲最多可以达到238%的效率。通过 检查状态值’Qcache_%’,可以知道query_cache_size设置是否合理:如果Qcache_lowmem_prunes的值非常大,则 表明经常出现缓冲不够的情况,如果Qcache_hits的值也非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;如果Qcache_hits 的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲。此外,在SELECT语句中加入 SQL_NO_CACHE可以明确表示不使用查询缓冲。

 

除了全局缓冲,MySql还会为每个连接发放连接缓冲。

 

连接缓冲:

每个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K,甚至在线程空闲时,它们使用默认的线程堆栈,网络缓存等。事 务开始之后,则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗,然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表, 则需分配大约 read_buffer_size,sort_buffer_size,read_rnd_buffer_size,tmp_table_size 大小的内存空间。不过它们只是在需要的时候才分配,并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块。tmp_table_size 可能高达MySQL所能分配给这个操作的最大内存空间了。注意,这里需要考虑的不只有一点 —— 可能会分配多个同一种类型的缓存,例如用来处理子查询。一些特殊的查询的内存使用量可能更大——如果在MyISAM表上做成批的插入时需要分配 bulk_insert_buffer_size 大小的内存;执行 ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 命令时需要分配 myisam_sort_buffer_size 大小的内存。

 

read_buffer_size 是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。read_buffer_size变量 控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。

 

sort_buffer_size 是MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试增加sort_buffer_size变量的大小。

 

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

 

tmp_table_size是MySql的heap (堆积)表缓冲大小。所有联合在一个DML指令内完成,并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。具有大的 记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。如果某个内部heap(堆积)表大小超过tmp_table_size,MySQL可以根据需要 自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说,如果调高该 值,MySql同时将增加heap表的大小,可达到提高联接查询速度的效果。

 

当我们设置好了缓冲区大小之后,再来看看:

 

table_cache 所有线程打开的表的数目,增大该值可以增加mysqld需要的文件描述符的数量。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并 放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值’Open_tables’和’Opened_tables’,可以决定是否需要增加 table_cache的值。如果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增 加table_cache的值了(上述状态值可以使用’Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果 设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。

 

做了以上方面的调优设置之后,MySql应该基本能满足您需求(当然是建立在调优设置适当的情况下),我们还应该了解并注意:

 

只有简单查询OLTP(联机事务处理)应用的内存消耗经常是使用默认缓冲的每个线程小于1MB,除非需要使用复杂的查询否则无需增加每个线程的 缓冲大小。使用1MB的缓冲来对10行记录进行排序和用16MB的缓冲基本是一样快的(实际上16MB可能会更慢,不过这是其他方面的事了)。

 

找出MySQL服务器内存消耗的峰值。这很容易就能计算出操作系统所需的内存、文件缓存以及其他应用。在32位环境下,还需要考虑到32位的限 制,限制 “mysqld” 的值大约为2.5G(实际上还要考虑到很多其他因素)。现在运行 “ps aux” 命令来查看 “VSZ” 的值(MySQL 进程分配的虚拟内存)。监视着内存变化的值,就能知道是需要增加或减少当前的内存值了。

 

最后来看看调优设置方法:

 

安装好MySql后,配制文件应该在 ./share/mysql ("./"即MySql安装目录) 目录中,配制文件有几个,有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf。win环境下即存在于MySql安装目录中的.ini文件。不同的流量的网站和不同配制的服务器环境,当然需要有不同的配制文 件了。

一般的情况下,my-medium.cnf这个配制文件就能满足我们的大多需要;一般我们会把配置文件拷贝到 /etc/my.cnf ,win环境下则拷备到 my.ini 下即可,只需要修改这个配置文件就可以了。

 


本文转载自:http://www.2cto.com/database/201304/204960.html

Amamatthew
粉丝 67
博文 1050
码字总数 76443
作品 0
厦门
后端工程师
私信 提问
Mysql:优化分析(值得深思)

Mysql优化分析 Mysql作为现在的主流关系型数据库,在优化上值得深思啊! 不能只停留在如何使用Mysql,而不去思考如何优化的问题啊。 在谈到Mysql优化方面,Mysql在这方面有专门的文档说明这个...

王木东
2018/07/13
0
0
LAMP平台部署及企业应用

LAMP平台的构成组件**linux操作系统、Apache网站服务器、Mysql数据库服务器、php网页编程语言安装软件包时通过--with-apxs2、--with-mysql配置选项分别可指定httpd、mysql、的相关路径要使h...

李佳良
2018/01/04
0
0
Twitter 将其使用的 MySQL 开源

新闻来源:Twitter官方博客 MySQL 是被广泛使用的一个数据库,但是 Web 开发人员会在大规模访问时的数据库扩展以及性能方面遇到问题。这也是近年来 NoSQL 数据库开始出现并逐渐繁荣的一个原因...

红薯
2012/04/10
2.1K
12
MySQL企业监控系统--MySQL MTOP

MySQLMTOP 是一个由Python+PHP开发的开源MySQL企业监控系统。系统由Python实现多进程数据采集和告警,PHP实现WEB展示和管理。MySQL服务器无需安装任何Agent,只需在监控WEB界面配置相关数据库...

andrew_ru
2014/01/02
38.4K
10
mySQL (关系型数据库管理系统)

MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Da...

愉快的鱼儿
2017/05/19
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Netty整合Protobuffer

现在我们都知道,rpc的三要素:IO模型,线程模型,然后就是数据交互模型,即我们说的序列化和反序列化,现在我们来看一下压缩比率最大的二进制序列化方式——Protobuffer,而且该方式是可以跨...

算法之名
23分钟前
13
0
如何用C++实现栈

栈的定义 栈(stack)又名堆栈,它是一种运算受限的线性表。限定仅在表尾进行插入和删除操作的线性表。这一端被称为栈顶,相对地,把另一端称为栈底。向一个栈插入新元素又称作进栈、入栈或压...

BWH_Steven
41分钟前
4
0
编程作业20190210900169

1编写一个程序,提示用户输入名和姓,然后以“名,姓”的格式打印出来。 #include <stdio.h>#include <stdlib.h> int main(){ char firstName[20]; char lastName[20]; print......

1李嘉焘1
53分钟前
12
0
补码的优点及原理分析

只讨论整数 1.计算机内部为什么没有减法器? 减法运算本身其实就是加法,如x - y即x +(-y),所以只需要将负数成功表示出来并可以参加加法运算,那加法器就可同时实现“+”和“-”的运算。这...

清自以敬
今天
76
0
Docker 可视化管理 portainer

官网安装指南: https://portainer.readthedocs.io/en/latest/deployment.html docker-compose.yml 位置,下载地址:https://downloads.portainer.io/docker-compose.yml...

Moks角木
今天
9
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部