MySQL内存及虚拟内存优化设置

mysql优化调试命令

1、mysqld--verbose--help

这个命令生成所有mysqld选项和可配置变量的列表

2、通过连接它并执行这个命令，可以看到实际上使用的变量的值：

mysql>showvariables;

还可以通过下面的语句看到运行服务器的统计和状态指标：

mysql>showstatus；

使用mysqladmin还可以获得系统变量和状态信息：

shell>mysqladminvariables

shell>mysqladminextended-status

shell>mysqladminflush-table命令可以立即关闭所有不使用的表并将所有使用中的表标记为已经关闭，这样可以有效释放大多数使用中的内存。flushtable在关闭所有表之前不返回结果。

swap-s检查可用交换区

mysql内存计算公式

mysqlusedmem=key_buffer_size+query_cache_size+tmp_table_size+innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+max_connections*(read_buffer_size+read_rnd_buffer_size+sort_buffer_size+join_buffer_size+binlog_cache_size+thread_stack)

在mysql中输入如下命令，可自动计算自己的当前配置最大的内存消耗

showvariableslike'innodb_buffer_pool_size';showvariableslike'innodb_additional_mem_pool_size';showvariableslike'innodb_log_buffer_size';showvariableslike'thread_stack';set@kilo_bytes=1024;set@mega_bytes=@kilo_bytes*1024;set@giga_bytes=@mega_bytes*1024;set@innodb_buffer_pool_size=2*@giga_bytes;set@innodb_additional_mem_pool_size=16*@mega_bytes;set@innodb_log_buffer_size=8*@mega_bytes;set@thread_stack=192*@kilo_bytes;select(@@key_buffer_size+@@query_cache_size+@@tmp_table_size+@innodb_buffer_pool_size+@innodb_additional_mem_pool_size+@innodb_log_buffer_size+@@max_connections*(@@read_buffer_size+@@read_rnd_buffer_size+@@sort_buffer_size+@@join_buffer_size+@@binlog_cache_size+@thread_stack))/@giga_bytesasmax_memory_gb;

mysqld数据库的参数设置有两种类型，

一种是全局参数，影响服务器的全局操作；

另一种是会话级参数，只影响当前的客户端连接的相关操作。

服务器启动时，所有全局参数都初始化为默认值。可以在初始化文件或命令行中指定的选项来更改这些默认值。服务器启动后，通过连接服务器并执行setglobalvar_name语句可以更改动态全局参数。要想更改全局参数，必须具有super权限。全局参数的修改只对新的连接生效，已有的客户端连接并不会生效。

服务器还可以为每个客户端连接维护会话级参数，客户端连接时使用相应全局参数的当前值对客户端会话参数进行初始化。客户可以通过setsessionvar_name语句来更改动态会话参数。设置会话级参数不需要特殊权限，但每个客户端可以只更改自己的会话级参数，不能更改其它客户的会话级参数。

不指定设置的参数类型时，默认设置的是会话级参数。

(1)、max_connections：允许的同时客户的数量。增加该值增加mysqld要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到toomanyconnections错误。默认数值是100，我把它改为1024。(2)、record_buffer：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128k)，我把它改为16773120(16m)(3)、key_buffer_size：

为了最小化磁盘的i/o，myisam存储引擎的表使用键高速缓存来缓存索引，这个键高速缓存的大小则通过key-buffer-size参数来设置。如果应用系统中使用的表以myisam存储引擎为主，则应该适当增加该参数的值，以便尽可能的缓存索引，提高访问的速度。

索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是8388600(8m)，我的mysql主机有2gb内存，所以我把它改为402649088(400mb)。

默认情况下，所有的索引都使用相同的键高速缓存，当访问的索引不在缓存中时，使用lru（leastrecentlyused最近最少使用）算法来替换缓存中最近最少使用的索引块。为了进一步避免对键高速缓存的争用，从mysql5.1开始，可以设置多个键高速缓存，并为不同的索引键指定使用的键高速缓存。下面的例子演示如何修改高速键缓存的值，如何设置多个键高速缓存，以及如何为不同的索引指定不同的缓存：

显示当前的参数大小，为16m：

mysql>showvariableslike'key_buffer_size';

+-----------------+-------+

|variable_name|value|

+-----------------+-------+

|key_buffer_size|16384|

+-----------------+-------+

1rowinset(0.00sec)

修改参数值到200m：

mysql>setglobalkey_buffer_size=204800;

queryok,0rowsaffected(0.00sec)

mysql>showvariableslike'key_buffer_size';

+-----------------+--------+

|variable_name|value|

+-----------------+--------+

|key_buffer_size|204800|

+-----------------+--------+

1rowinset(0.00sec)

上面介绍的是默认的键缓存，下面介绍如何设置多个键缓存：

设置hot_cache的键缓存100m，cold_cache的键缓存100m，另外还有200m的默认的键缓存。如果索引不指定键缓存，则会放在默认的键缓存中。

mysql>setglobalhot_cache.key_buffer_size=102400;

queryok,0rowsaffected(0.00sec)

mysql>setglobalcold_cache.key_buffer_size=102400;

queryok,0rowsaffected(0.01sec)

mysql>showvariableslike'key_buffer_size';

+-----------------+--------+

|variable_name|value|

+-----------------+--------+

|key_buffer_size|204800|

+-----------------+--------+

1rowinset(0.00sec)

如果要显示设置的多键缓存的值，可以使用：

mysql>select@@global.hot_cache.key_buffer_size;

+------------------------------------+

|@@global.hot_cache.key_buffer_size|

+------------------------------------+

|102400|

+------------------------------------+

1rowinset(0.03sec)

mysql>select@@global.cold_cache.key_buffer_size;

+-------------------------------------+

|@@global.cold_cache.key_buffer_size|

+-------------------------------------+

|102400|

+-------------------------------------+

1rowinset(0.00sec)

指定不同的索引使用不同的键缓存：

mysql>cacheindextest1inhot_cache;

+------------+--------------------+----------+----------+

+------------+--------------------+----------+----------+

+------------+--------------------+----------+----------+

1rowinset(0.00sec)

mysql>cacheindextest2inhot_cache;

+------------+--------------------+----------+----------+

+------------+--------------------+----------+----------+

+------------+--------------------+----------+----------+

1rowinset(0.00sec)

通常在数据库刚刚启动的时候，需要等待数据库热起来，也就是等待数据被缓存到缓存区中，这段时间数据库会因为buffer的命中率低而导致应用的访问效率不高。使用键高速缓存的时候，可以通过命令将索引预加载到缓存区中，大大缩短了数据库预热的时间。具体的操作方式是：

mysql>loadindexintocachetest1,test2ignoreleaves;

+------------+--------------+----------+----------+

+------------+--------------+----------+----------+

+------------+--------------+----------+----------+

2rowsinset(3.89sec)

如果已经使用cacheindex语句为索引分配了一个键高速缓冲，预加载可以将索引块放入该缓存，否则，索引块将被加载到默认的键高速缓冲。

4)、back_log：要求mysql能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的tcp/ip连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。当你观察你的主机进程列表，发现大量264084|unauthenticateduser|xxx.xxx.xxx.xxx|null|connect|null|login|null的待连接进程时，就要加大back_log的值了。默认数值是50，我把它改为500。(5)、interactive_timeout：服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对mysql_real_connect()使用client_interactive选项的客户。默认数值是28800，我把它改为7200。(6)、sort_buffer：每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速orderby或groupby操作。默认数值是2097144(2m)，我把它改为16777208(16m)。(7)、table_cache：为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是64，我把它改为512。(8)、thread_cache_size：可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较connections和threads_created状态的变量，可以看到这个变量的作用。我把它设置为80。(9)mysql的搜索功能用mysql进行搜索，目的是能不分大小写，又能用中文进行搜索只需起动mysqld时指定--default-character-set=gb2312(10)、wait_timeout：服务器在关闭它之前在一个连接上等待行动的秒数。默认数值是28800，我把它改为7200。

(11)、innodb_thread_concurrency：你的服务器cpu有几个就设置为几,默认为8。

(12)、query_cache_size与query_cache_limit

querycache之后所带来的负面影响：a)query语句的hash运算以及hash查找资源消耗。当我们使用querycache之后，每条select类型的query在到达mysql之后，都需要进行一个hash运算然后查找是否存在该query的cache，虽然这个hash运算的算法可能已经非常高效了，hash查找的过程也已经足够的优化了，对于一条query来说消耗的资源确实是非常非常的少，但是当我们每秒都有上千甚至几千条query的时候，我们就不能对产生的cpu的消耗完全忽视了。b)querycache的失效问题。如果我们的表变更比较频繁，则会造成querycache的失效率非常高。这里的表变更不仅仅指表中数据的变更，还包括结构或者索引等的任何变更。也就是说我们每次缓存到querycache中的cache数据可能在刚存入后很快就会因为表中的数据被改变而被清除，然后新的相同query进来之后无法使用到之前的cache。c)querycache中缓存的是resultset，而不是数据页，也就是说，存在同一条记录被cache多次的可能性存在。从而造成内存资源的过渡消耗。当然，可能有人会说我们可以限定querycache的大小啊。是的，我们确实可以限定querycache的大小，但是这样，querycache就很容易造成因为内存不足而被换出，造成命中率的下降。

querycache的正确使用：

虽然querycache的使用会存在一些负面影响，但是我们也应该相信其存在是必定有一定价值。我们完全不用因为querycache的上面三个负面影响就完全失去对querycache的信心。只要我们理解了querycache的实现原理，那么我们就完全可以通过一定的手段在使用querycache的时候扬长避短，重发发挥其优势，并有效的避开其劣势。首先，我们需要根据querycache失效机制来判断哪些表适合使用query哪些表不适合。由于querycache的失效主要是因为query所依赖的table的数据发生了变化，造成query的resultset可能已经有所改变而造成相关的querycache全部失效，那么我们就应该避免在查询变化频繁的table的query上使用，而应该在那些查询变化频率较小的table的query上面使用。mysql中针对querycache有两个专用的sqlhint（提示）：sql_no_cache和sql_cache，分别代表强制不使用querycache和强制使用querycache。我们完全可以利用这两个sqlhint，让mysql知道我们希望哪些sql使用querycache而哪些sql就不要使用了。这样不仅可以让变化频繁table的query浪费querycache的内存，同时还可以减少querycache的检测量。其次，对于那些变化非常小，大部分时候都是静态的数据，我们可以添加sql_cache的sqlhint，强制mysql使用querycache，从而提高该表的查询性能。最后，有些sql的resultset很大，如果使用querycache很容易造成cache内存的不足，或者将之前一些老的cache冲刷出去。对于这一类query我们有两种方法可以解决，一是使用sql_no_cache参数来强制他不使用querycache而每次都直接从实际数据中去查找，另一种方法是通过设定“query_cache_limit”参数值来控制querycache中所cache的最大resultset，系统默认为1m（1048576）。当某个query的resultset大于“query_cache_limit”所设定的值的时候，querycache是不会cache这个query的。(13)、innodb_buffer_pool_size

innodb_buffer_pool_size定义了innodb存储引擎的表数据和索引数据的最大内存缓冲区大小。和myisam存储引擎不同，myisam的key_buffer_size只能缓存索引键，而innodb_buffer_pool_size却可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少innodb类型的表的磁盘i/o。在一个以innodb为主的专用数据库服务器上，可以考虑把该参数设置为物理内存大小的60%-80%

innodb占用的内存，除innodb_buffer_pool_size用于存储页面缓存数据外，另外正常情况下还有大约8%的开销，主要用在每个缓存页帧的描述、adaptivehash等数据结构，如果不是安全关闭，启动时还要恢复的话，还要另开大约12%的内存用于恢复，两者相加就有差不多21%的开销。这样，12g的innodb_buffer_pool_size，最多的时候innodb就可能占用到14.5g(12gx21%)的内存，再加上操作系统用的几百m，近千个线程堆栈，就差不多16g了。

max_queries_per_hour用来限制用户每小时运行的查询数量：

mysql>grantallondbname。*todb@localhostidentifiedby“123456”withmax_connections_per_hour5;

（db用户在dbname的数据库上控制用户每小时打开新连接的数量为5个）

max_user_connections限制有多少用户连接mysql服务器：

mysql>grantallondbname。*todb@localhostidentifiedby“123456”withmax_user_connections2;

（db用户在dbname的数据库账户一次可以同时连接的最大连接数为2个）

max_updates_per_hour用来限制用户每小时的修改数据库数据的数量：

mysql>grantallondbname。*todb@localhostidentifiedby“123456”withmax_updates_per_hour5;

（db用户在dbname的数据库上控制用户每小时修改更新数据库的次数为5次）

max_user_connections用来限制用户每小时的修改数据库数据的数量：

mysql>grantallondbname。*todb@localhostidentifiedby“123456”

withmax_queries_per_hour20;指mysql单个用户的最大连接数

（db用户在dbname的数据库上控制用户每小时的连接数为20个）

调优举例

针对my.cnf文件进行优化：

[mysqld]

skip-locking（取消文件系统的外部锁）

skip-name-resolve（不进行域名反解析,注意由此带来的权限/授权问题）key_buffer_size=256m（分配给myisam索引缓存的内存总数）对于内存在4gb左右的服务器该参数可设置为256m或384m。注意：该参数值设置的过大反而会是服务器整体效率降低！max_allowed_packet=4m（允许最大的包大小）thread_stack=256k（每个线程的大小）table_cache=128k（缓存可重用的线程数）back_log=384（临时停止响应新请求前在短时间内可以堆起多少请求，如果你需要在短时间内允许大量连接，可以增加该数值）sort_buffer_size=2m(分配给每个线程中处理排序)read_buffer_size=2m（读取的索引缓冲区大小）join_buffer_size=2m（分配给每个线程中处理扫描表连接及索引的内存）myisam_sort_buffer_size=64m（myisam引擎排序缓冲区的大小）table_cache=512（缓存数据表的数量，避免重复打开表的开销）thread_cache_size=64（缓存可重用线程数，见笑创建新线程的开销）query_cache_size=64m（控制分配给查询缓存的内存总量）tmp_table_size=256m(指定mysql缓存的内存大小)max_connections=768（最大连接数）指mysql整个的最大连接数max_connect_errors=10000(最大连接错误数据)wait_timeout=10（超时时间，可以避免攻击）thread_concurrency=8（根据cpu数量来设置）skip-bdb禁用不必要的引擎skip-networking（关闭mysqltcp/ip连接方式）log-slow-queries=/var/log/mysqlslowqueries.loglong_query_time=4（设定慢查询的时间）skip-host-cache(提高mysql速度的)open_files_limit=4096(打开文件数）interactive_timeout=10(服务器在关闭它前在一个交互连接上等待行动的秒数)max_user_connections=500（最大用户连接数）

key_buffer_size默认为218调到128最佳

query_cache_size

tmp_table_size默认为16m调到64-256最挂innodb_thread_concurrency=8你的服务器cpu有几个就设置为几,默认为8

table_cache=1024物理内存越大,设置就越大.默认为2402,调到512-1024最佳innodb_additional_mem_pool_size=8m默认为2minnodb_flush_log_at_trx_commit=0等到innodb_log_buffer_size列队满后再统一储存,默认为1innodb_log_buffer_size=4m默认为1mread_buffer_size=4m默认为64kread_rnd_buffer_size随机读缓存区默认为256ksort_buffer_size=32m默认为256kmax_connections=1024默认为1210

thread_cache_size=120默认为60

性能测试

1、mysql自带测试工具

shell>perl-mcpan-eshell

cpan>installdbi

cpan>installdbd::mysql

shell>cdsql-bench

shell>perlrun-all-tests--server=server_name

server_name是一个支持的服务器。要获得所有选项和支持的服务器，调用命令：

shell>perlrun-all-tests--help

2、mysqlreport

http://hackmysql.com/mysqlreport

参考文档

http://dev.mysql.com/doc/refman/5.1/zh/optimization.html

http://hackmysql.com/tools