从这篇开始,讲innodb存储引擎中,对于几个重要的服务器参数配置。这些参数以innodb_xx
开头。

mysql的各种参数有300余种,可以将其分为两类:一是缓存参数,二是个性化参数。对缓存参数的配置在一定程度内对mysql性能的影响是显著的。同时各种个性化参数的设置,可以使mysql表现出不同的性状。

mysql 优化调试命令  
1、mysqld –verbose –help
这个命令生成所有mysqld选项和可配置变量的列表
2、通过连接它并执行这个命令,可以看到实际上使用的变量的值:
mysql> SHOW VARIABLES;
还可以通过下面的语句看到运行服务器的统计和状态指标:
mysql>SHOW STATUS;
使用mysqladmin还可以获得系统变量和状态信息:
shell> mysqladmin variables
shell> mysqladmin extended-status
shell> mysqladmin flush-table
命令可以立即关闭所有不使用的表并将所有使用中的表标记为已经关闭,这样可以有效释放大多数使用中的内存。FLUSH
TABLE在关闭所有表之前不返回结果。
 
swap -s检查可用交换区
 
mysql内存计算公式
 
mysql used mem = key_buffer_size + query_cache_size +
tmp_table_size

  1. innodb_buffer_pool_size的设置

1.缓存参数

目前常用的存储引擎有两种,一是myisam,另一种是innodb。关于这两种存储引擎的异同这里就不做过多的介绍。使用存储引擎的不同,对参数的优化也会不一样。但有一些缓存参数是跨存储引擎的,就是无论使用何种存储引擎,它都会发挥其作用。下面将按三类对其进行详细的介绍。

  • 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 中输入如下命令,可自动计算自己的当前配置最大的内存消耗
     
    SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
    SHOW VARIABLES LIKE ‘innodb_additional_mem_pool_size’;
    SHOW VARIABLES LIKE ‘innodb_log_buffer_size’;
    SHOW VARIABLES LIKE ‘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_bytes AS MAX_MEMORY_GB;
     
     
    mysql关键参数设置
    Mysqld 数据库的参数设置有两种类型,
     
    一种是全局参数,影响服务器的全局操作;
    另一种是会话级参数,只影响当前的客户端连接的相关操作。
     
    服务器启动时,所有全局参数都初始化为默认值。可以在初始化文件或命令行中指定的选项来更改这些默认值。服务器启动后,通过连接服务器并执行
    SET GLOBAL var_name
    语句可以更改动态全局参数。要想更改全局参数,必须具有 SUPER
    权限。全局参数的修改只对新的连接生效,已有的客户端连接并不会生效。
     
    服务器还可以为每个客户端连接维护会话级参数,客户端连接时使用相应全局参数的当前值对客户端会话参数进行初始化。客户可以通过
    SET SESSION var_name
    语句来更改动态会话参数。设置会话级参数不需要特殊权限,但每个客户端可以只更改自己的会话级参数,不能更改其它客户的会话级参数。
    不指定设置的参数类型时,默认设置的是会话级参数。
     

        
这个参数定义了innodb存储引擎的表数据和索引数据的最大内存缓冲区大小,和myisam不同,myisam的key_buffer_size只缓存索引键,而innodb_buffer_pool_size是同时为数据块和索引块做缓存的。这个特性与oracle是一样的,这个值设得越高,访问表中数据需要的磁盘i/o就越少(物理I/O)。在一个专用的数据库服务器上,可以设置这个参数达机器物理内存大小的50–80%。考虑点:在单独给
MySQL
使用的主机里,内存分配还包括系统使用,线程独享,myisam缓存等。还有允许的并发连接数。还有建议不要把它设置得太大,因为对物理内存的竞争可能在操作系统上导致内存调度。

1.1 跨引擎缓存参数优化

这类缓存参数是针对查询的优化,优化方向是sql、表、日志、线程对象的缓存优化。具体说明如下:

(1)、max_connections:
允许的同时客户的数量。增加该值增加 mysqld
要求的文件描述符的数量。这个数字应该增加,否则,你将经常看到 too many
connections 错误。 默认数值是100,我把它改为1024 。

-- innodb缓存区大小(kb)
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

1.1.1 query cache

query cache将已经执行过的sql和结果集放在缓存区中,如果再有同样的select语句(区分大小写),将直接从缓存区中读取,这样能大大提高query语句的查询效率,但是,如果要query的表经常被更新,则会导致在cache中的sql失效,这时使用query cache不但不能提高效率反而会使数据库的性能变得更差。所以使用query cache时需要对这点留意。

使用query cache时,需要先将query_cache_type设置为ON(打开查询缓存)。同时需要对要缓存的结果集的大小进行限制。query_cache_limit=1M(最大结果集为1M),query_cache_min_res_unit=1K(结果集最小为1K),不在这个范围内的结果集将不会被缓存。query cahce的大小以字节为单位,须为1024的整数倍,建议为系统内存的1/8,不超过256M。通过查看query的global status来查看设置是否合理:

Qcache_free_blocks:目前还处于空闲状态的 Query Cache 中内存 Block 数目

Qcache_free_memory:目前还处于空闲状态的 Query Cache 内存总量

Qcache_hits:Query Cache 命中次数

Qcache_inserts:向 Query Cache 中插入新的 Query Cache 的次数,也就是没有命中的次数

Qcache_lowmem_prunes:当 Query
Cache 内存容量不够,需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数

Qcache_not_cached:没有被
Cache 的 SQL 数,包括无法被 Cache 的 SQL 以及由于

Qcache_queries_in_cache:目前在
Query Cache 中的 SQL 数量

Qcache_total_blocks:Query Cache 中总的 Block 数量

命中率: [Qcache_hits /( Qcache_hits+ Qcache_inserts)]*100%

如果命中率高,且Qcache_free_memory大则说明query_cache_size设置过大

如果命中率低,且Qcache_lowmem_prunes大则说明query_cache_size设置过小

 

(2)、record_buffer:
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,你可能想要增加该值。默认数值是131072(128k),我把它改为16773120
(16m)

图片 1  SELECT
268435456/1024.0/2014.0=130M。

1.1.2 table cache

为了解决打开表描述文件符太过频繁的问题,mysql在系统中实现了一个table cache机制,用来cache打开的所有表文件的描述符。通过这样的方式来减少因为频繁打开关闭文件描述符所带来的资源消耗。

table cache的设置与mysql设置的最大连接数成正比,其比例值等于一个connection打开多少表,计算方式如下:

table_cache=max_connection*N

使用flush table来关闭所有文件描述符,通过查看table open状态来查看参数设置是否合理,合理的设置应该如下:

open_tables/opened_tables>=0.85

open_tables/table_cache<=0.95

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

查看内存大小:
[root@xuegod64 ~]# cat /proc/meminfo

1.1.3 join buffer(线程独有)

当join查询的类型是all,index,range或者index_merge的时候就会使用到join buffer。实际上参与join的每个表都会用到join buffer,所以一条sql会用到至少两个join buffer。join buffer在5.1.23的版本前的最大值是4G,但之后,除了windows外,其它64位的平台可以超出4G的限制。系统默认为128k,但是如果join语句多,建议为1M,内存充足则可以增加到2M。

索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),到你能负担得起那样多。如果你使它太大,系统将开始换页并且真的变慢了。默认数值是8388600(8m),我的mysql主机有2gb内存,所以我把它改为
402649088(400mb)。
默认情况下,所有的索引都使用相同的键高速缓存,当访问的索引不在缓存中时,使用
LRU ( Least Recently Used
最近最少使用)算法来替换缓存中最近最少使用的索引块。为了进一步避免对键高速缓存的争用,从
MySQL5.1
开始,可以设置多个键高速缓存,并为不同的索引键指定使用的键高速缓存。下面的例子演示如何修改高速键缓存的值,如何设置多个键高速缓存,以及如何为不同的索引指定不同的缓存:
显示当前的参数大小,为16M:
mysql> show variables like ‘key_buffer_size’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| key_buffer_size | 16384 |
+—————–+——-+
1 row in set (0.00 sec)
修改参数值到200M:
mysql> set global key_buffer_size=204800;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘key_buffer_size’;
+—————–+——–+
| Variable_name | Value |
+—————–+——–+
| key_buffer_size | 204800 |
+—————–+——–+
1 row in set (0.00 sec)
上面介绍的是默认的键缓存,下面介绍如何设置多个键缓存:
设置 hot_cache 的键缓存 100M , cold_cache 的键缓存 100M ,另外还有
200M 的默认的键缓存。如果索引不指定键缓存,则会放在默认的键缓存中。
mysql> set global hot_cache.key_buffer_size=102400;
Query OK, 0 rows affected (0.00 sec)
mysql> set global cold_cache.key_buffer_size= 1024 00;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like ‘key_buffer_size’;
+—————–+——–+
| Variable_name | Value |
+—————–+——–+
| key_buffer_size | 204800 |
+—————–+——–+
1 row in set (0.00 sec)
如果要显示设置的多键缓存的值,可以使用:
mysql> SELECT @@global.hot_cache.key_buffer_size;
+————————————+
| @@global.hot_cache.key_buffer_size |
+————————————+
| 102400 |
+————————————+
1 row in set (0.03 sec)
mysql> SELECT @@global.cold_cache.key_buffer_size;
+————————————-+
| @@global.cold_cache.key_buffer_size |
+————————————-+
| 102400 |
+————————————-+
1 row in set (0.00 sec)
指定不同的索引使用不同的键缓存:
mysql> CACHE INDEX test1 in hot_cache;
+————+——————–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————+——————–+———-+———-+
| test .test1 | assign_to_keycache | status | OK |
+————+——————–+———-+———-+
1 row in set (0.00 sec)
mysql> CACHE INDEX test2 in hot_cache;
+————+——————–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————+——————–+———-+———-+
| test .test2 | assign_to_keycache | status | OK |
+————+——————–+———-+———-+
1 row in set (0.00 sec)
通常在数据库刚刚启动的时候,需要等待数据库热起来,也就是等待数据被缓存到缓存区中,这段时间数据库会因为
buffer
的命中率低而导致应用的访问效率不高。使用键高速缓存的时候,可以通过命令将索引预加载到缓存区中,大大缩短了数据库预热的时间。具体的操作方式是:
mysql> LOAD INDEX INTO CACHE test1,test2 IGNORE LEAVES;
+————+————–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+————+————–+———-+———-+
| test .test1 | preload_keys | status | OK |
| test .test2 | preload_keys | status | OK |
+————+————–+———-+———-+
2 rows in set (3.89 sec)
如果已经使用 CACHE INDEX
语句为索引分配了一个键高速缓冲,预加载可以将索引块放入该缓存,否则,索引块将被加载到默认的键高速缓冲。
 

    图片 2

1.1.4 sort buffer(线程独有)

当sql需要进行排序操作时会用到sort buffer。通过增大sort buffer的大小可以提高order by或group by的处理性能。系统默认为2M,最大限制和join
buffer一样,一般设置在2mb~4mb之间可以满足大多数应用的需求。

4)、back_log:
要求 mysql
能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。
back_log
值指出在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的tcp/ip连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。
当你观察你的主机进程列表,发现大量 264084 | unauthenticated user |
xxx.xxx.xxx.xxx | null | connect | null | login | null
的待连接进程时,就要加大 back_log 的值了。默认数值是50,我把它改为500。

发表评论

电子邮件地址不会被公开。 必填项已用*标注