mysql 开发进阶篇系列 18 MySQL Server(innodb_buffer_pool_size)

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

1.3.2 Innodb_additional_mem_pool_size

Innodb_additional_mem_pool_size用于存放innodb的字典信息和其他一些内部结构所需要的内存空间。Innodb表越多,需要的空间就越大,系统默认为1M。

一个常规的几百个innodb表的mysql,如果不是每个表都是有上百个字段的话,20M内存就足够,设置超过实际所需要的内存并没有太大的意义,只是浪费内存而已。

 

(6)、sort_buffer:
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order
by或group by操作。默认数值是2097144(2m),我把它改为 16777208 (16m)。

  由于SHOW
VARIABLES下的参数都是静态值。当mysql重启时,上面的缓存设置将失效。

1.缓存参数

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

索引块是缓冲的并且被所有的线程共享。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
语句为索引分配了一个键高速缓冲,预加载可以将索引块放入该缓存,否则,索引块将被加载到默认的键高速缓冲。
 

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

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

(7)、table_cache:
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是64,我把它改为512。

    图片 1

1.3 innodb缓存参数优化

innodb的缓存参数主要是两个方面,一类是数据索引(innodb_buffer_pool)结构,另一类是日志(Innodb_log_buffer)。

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。

        
这个参数定义了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 。

  重启后还是134217728.如下图
  图片 2

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

(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%的开销,主要用在每个缓存页帧的描述、adaptive
hash等数据结构,如果不是安全关闭,启动时还要恢复的话,还要另开大约12%的内存用于恢复,两者相加就有差不多21%的开销。

Innodb_buffer_pool_pages_total 

缓存池页总数目。
共占用了16382 页 。单位page

Innodb_buffer_pool_pages_free

缓存池剩余的页数目。
在16382 页中有2000页没有使用。 单位page

Innodb_buffer_pool_pages_data

缓存池中包含数据的页的数目,包括脏页。
14273个页含有数据。单位page

Innodb_buffer_pool_read_requests

innodb进行逻辑读的数量。
529670886次请求读。单位次数

Innodb_buffer_pool_reads

进行逻辑读取时无法从缓冲池中获取而执行单页读取的次数。
941147次是物理I/0读取。单位次数

Innodb_buffer_pool_write_requests

写入 InnoDB 缓冲池的次数。
48606702次请求写入。单位次数

Innodb_buffer_pool_read_ahead_rnd

记录进行随机读的时候产生的预读次数。
0次

Innodb_buffer_pool_read_ahead

预读到innodb buffer pool里次数。
1465370次。  单位page

Innodb_buffer_pool_read_ahead_evicted

预读的页数,但是没有被读取就从缓冲池中被替换的页的数量,一般用来判断预读的效率。 0次

Innodb_buffer_pool_wait_free

数据要写入buffer pool的时候,需要等待空闲页的次数。是指缓存池里没有干净页的时候读取或创建页,要先等待页被刷新。
2927次。 单位次数。

Innodb_buffer_pool_pages_dirty

buffer pool缓存池中脏页的数目。
0次。单位是page

Innodb_buffer_pool_pages_flushed

buffer pool缓存池中刷新页请求的数目。
15437744次。单位page

Innodb_buffer_pool_pages_misc

buffer pool缓存池中当前页已经被用作管理用途或hash index而不能用作为普通数据页的数目。
109次。单位page

Innodb_buffer_pool_pages_old

在旧区域存放着多少个页。
5249次。单位page

Innodb_buffer_pool_pages_made_young

移动到新区域的有多少个页。
353059次。单位page

Innodb_buffer_pool_pages_made_not_young

没有移动到新区域的有多少个页。
31725809次。单位page

1.2 myisam缓存参数优化

影响myisam存储引擎效率的缓存参数是key_buffer_size(索引缓存大小)。用来缓存myisam表的索引。32为平台不要超过2G,64位平台不要超过4G,一般设置为可用内存的30%-40%,请至少保留16~32M的大小,以适应给予磁盘临时表所需。

对于key_buffer_size的设置可以通过三个指标来计算,第一个是索引的总大小,第二个是系统可用物理内存,第三个是系统当前的key
cache命中率。

Key_size=key_number*(key_length+4)/0.67

Max_key_buffer_size<系统可用物理内存 – 线程使用的内存(Thread_usage)

Thread_usage=max_connections*(sort_buffer_size+join_buffer_size+read_buffer_size+read_rnd_buffer_size+thread_stack)

通过以下三个比率数据,就可以知道key cache设置是否合理:

Key_buffer使用率=(1-key_blocks_used/(key_blocks_used+key_blocks_unused))*100%

Key_buffer_read_hitratio=(1-key_reads/key_read_requests)*100%

Key_buffer_write_hitratio=(1-key_writes/key_write_requests)*100%

一般来说key_buffer使用率应该在99%以上,key_buffer_read_hitratio也应该尽可能地高

(10)、wait_timeout:
服务器在关闭它之前在一个连接上等待行动的秒数。
默认数值是28800,我把它改为7200。
 
(11)、innodb_thread_concurrency:
你的服务器CPU有几个就设置为几,默认为8。
 
(12)、query_cache_size  与 query_cache_limit
QueryCache 之后所带来的负面影响:
a) Query 语句的hash 运算以及hash 查找资源消耗。当我们使用Query Cache
之后,每条SELECT
类型的Query 在到达MySQL 之后,都需要进行一个hash
运算然后查找是否存在该Query 的
Cache,虽然这个hash 运算的算法可能已经非常高效了,hash
查找的过程也已经足够的优化
了,对于一条Query
来说消耗的资源确实是非常非常的少,但是当我们每秒都有上千甚至几千
条Query 的时候,我们就不能对产生的CPU 的消耗完全忽视了。
b) Query Cache 的失效问题。如果我们的表变更比较频繁,则会造成Query Cache
的失效率非常
高。这里的表变更不仅仅指表中数据的变更,还包括结构或者索引等的任何变更。也就是说我
们每次缓存到Query Cache 中的Cache
数据可能在刚存入后很快就会因为表中的数据被改变而被
清除,然后新的相同Query 进来之后无法使用到之前的Cache。
c) Query Cache 中缓存的是Result Set
,而不是数据页,也就是说,存在同一条记录被Cache 多
次的可能性存在。从而造成内存资源的过渡消耗。当然,可能有人会说我们可以限定Query
Cache 的大小啊。是的,我们确实可以限定Query Cache
的大小,但是这样,Query Cache 就很
容易造成因为内存不足而被换出,造成命中率的下降。
 
QueryCache 的正确使用:
虽然Query Cache
的使用会存在一些负面影响,但是我们也应该相信其存在是必定有一定价值。我
们完全不用因为Query Cache 的上面三个负面影响就完全失去对Query Cache
的信心。只要我们理解了
Query Cache 的实现原理,那么我们就完全可以通过一定的手段在使用Query
Cache 的时候扬长避短,重
发发挥其优势,并有效的避开其劣势。
首先,我们需要根据Query Cache 失效机制来判断哪些表适合使用Query
哪些表不适合。由于Query
Cache 的失效主要是因为Query 所依赖的Table 的数据发生了变化,造成Query
的Result Set 可能已经
有所改变而造成相关的Query Cache
全部失效,那么我们就应该避免在查询变化频繁的Table 的Query 上
使用,而应该在那些查询变化频率较小的Table 的Query 上面使用。MySQL
中针对Query Cache 有两个专
用的SQL Hint(提示):SQL_NO_CACHE
和SQL_CACHE,分别代表强制不使用Query Cache 和强制使用
Query Cache。我们完全可以利用这两个SQL Hint,让MySQL 知道我们希望哪些SQL
使用Query Cache 而
哪些SQL 就不要使用了。这样不仅可以让变化频繁Table 的Query 浪费Query
Cache 的内存,同时还可以
减少Query Cache 的检测量。
其次,对于那些变化非常小,大部分时候都是静态的数据,我们可以添加SQL_CACHE
的SQL Hint,
强制MySQL 使用Query Cache,从而提高该表的查询性能。
最后,有些SQL 的Result Set 很大,如果使用Query Cache 很容易造成Cache
内存的不足,或者将
之前一些老的Cache 冲刷出去。对于这一类Query
我们有两种方法可以解决,一是使用SQL_NO_CACHE 参
数来强制他不使用Query Cache 而每次都直接从实际数据中去查找,
另一种方法是通过设定
“query_cache_limit”参数值来控制Query Cache 中所Cache 的最大Result Set
,系统默认为
1M(1048576)。当某个Query 的Result Set
大于“query_cache_limit”所设定的值的时候,Query
Cache 是不会Cache 这个Query 的。

相关文章

发表评论

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

*
*
Website