MySQL服务器参数配置优化

淡淡的烟草味﹌ 2022-09-20 08:37 185阅读 0赞

（1）、innodb\_buffer\_pool\_size 的设置

innodb\_buffer\_pool\_size 定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和 MyISAM 存储引擎不同， MyISAM 的 key\_buffer\_size 只能缓存索引键，而 innodb\_buffer\_pool\_size 却可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。在一个以 InnoDB 为主的专用数据库服务器上，可以考虑把该参数设置为物理内存大小的 60%-70% ，在运行多个服务的服务器上，不宜过大，视情况5-20%即可。

SHOW GLOBAL STATUS LIKE 'Innodb\_buffer\_pool\_pages\_data';   
SHOW GLOBAL STATUS LIKE 'Innodb\_buffer\_pool\_pages\_total';  
SHOW GLOBAL STATUS LIKE 'Innodb\_page\_size';  
'Innodb\_buffer\_pool\_pages\_data' X 100 / 'Innodb\_buffer\_pool\_pages\_total'  
当结果 > 95% 则增加 innodb\_buffer\_pool\_size， 建议使用 ram total 75%  
建议值大小为： Innodb\_buffer\_pool\_pages\_data  \*  Innodb\_page\_size  \*  1.05 / (1024\*1024\*1024)

（2）、key\_buffer\_size的设置

key\_buffer\_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值\# created\_tmp\_disk\_tables得知详情。指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意：该参数值设置的过大反而会是服务器整体效率降低！如果你使它太大，系统将开始换页并且真的变慢了。

key\_buffer\_size被所有线程共享，它指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key\_read\_requests和Key\_reads，可以知道key\_buffer\_size设置是否合理。比例key\_reads / key\_read\_requests应该尽可能的低，至少是1:100，1:1000更好。key\_buffer\_size被所有线程共享，严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。

show status like "key\_%"

Key\_blocks\_unused       未用的key\_buffer缓存簇(blocks)数  
Key\_blocks\_used          已用的key\_buffer缓存簇数  
Key\_read\_requests        请求key\_buffer的次数  
Key\_reads                    key\_buffer中找不到,读取磁盘的次数

key\_buffer使用率 =  (Key\_blocks\_used/(Key\_blocks\_used+Key\_blocks\_unused))\*100%

key\_buffer未命中率 = Key\_reads/Key\_read\_requests\*100%

当MySQL执行某个利用了MyISAM数据表索引的语句的时候，它会使用键缓存来保持索引值。这种缓存减少了磁盘I/O：如果在缓存中找到了某个数据表需要的键值，就不需要再次从磁盘中读取。不幸的是，这种键缓存是有限的，并且在默认情况下，它是所有的MyISAM数据表共享使用的。如果在键缓存中没有找到键值并且键缓存是满的，争用将会导致：必须丢弃缓存中的某些值，为新值留出空间。如果下次需要那些已经被丢弃的值，就必须再次从磁盘上读取。  
如果你很倚重MyISAM数据表，那么把它的键保存在内存中效果会很好，但是缓存中的争用却会导致相反的效果。从同一张表或不同的表读取数据都可能引起争用。你可以通过把键缓存设置成足以保存某个特定数据表的全部索引，从而避免同一张数据表的争用，但是其它数据表的键仍然需要争用缓存空间。  
MySQL 4.1以上版本为这个问题提供了一种解决方案：它支持我们建立多个键缓存，并允许我们把某张数据表的索引指定并且预先装入某个缓存。如果你的数据表使用得很频繁，并且你有足够的内存，能够把它的索引载入缓存中，那么这种操作就是有用的。这种能力允许你同时避免同一张表和不同的表的争用：建立一个足够大的缓存，让它保存数据表的全部索引，并且指定该缓存专门用于那张数据表。在键被载入缓存之后，不在需要磁盘I/O操作。同时，键值永远不会被丢弃，对数据表的键的查看操作可以在内存中完成。  
下面的例子显示了如何为sampdb数据库的member数据表建立一个键缓存，该缓存的名称是member\_cache，大小为1MB。执行这些指令的时候，你必须有超级（SUPER）权限。

a、建立一个足够容纳数据表索引的独立的缓存：

SET GLOBAL member\_cache.key\_buffer\_size = 1024\*1024;

b、给数据表指定键缓存：

CACHE INDEX member IN member\_cache;

c、把数据表索引预先读入它的键缓存中：

LOAD INDEX INTO CACHE member;

如果你希望把其它的数据表载入同一个缓存中，或者为其它的数据表建立键缓存，上面的操作就足够了。

1) 键缓存池可以设置得很大，因为个人理解它比表缓存有更好的效率

2）因为是共享的键缓存池。存在的争用缓存空间的问题。解决的方法是为常用的重析键单独开键缓存池空间

>   
>