mysql set 常用参数_mysql常用参数调优方法总结

问题描述：服务器报错Out of memory: Kill process (mysqld)内存溢出

Out of memory 问题，这通常是因为某时刻应用程序大量请求内存导致系统内存不足造成的，这通常会触发 Linux 内核里的 Out of Memory (OOM) killer，OOM killer 会杀掉某个进程以腾出内存留给系统用，不致于让系统立刻崩溃。

在整体的系统运行过程中，数据库服务器 MySQL 的压力是最大的，不仅占用很多的内存和 cpu 资源，而且占用着大部分的磁盘 io 资源。

一、优化mysql缓存—-

mysql>show variables like '%query_cache%';

mysql本身是有对sql语句缓存的机制的，合理设置我们的mysql缓存可以降低数据库的io资源。

#query_cache_type= 查询缓存的方式(默认是 ON)

query_cache_size 如果你希望禁用查询缓存，设置 query_cache_size=0。禁用了查询缓存，将没有明显的开销，

query_cache_limit 不缓存大于这个值的结果。(缺省为 1M)

找到/etc/mysql/my.cnf 配置进行修改(注意备份)：首先我把query_cache_size这个值改为了0.禁用了缓存

二、再提一下key_buffer_size

mysql>show variables like '%key_buffer_size%';

key_buffer_size是对MyISAM表性能影响最大的一个参数

key_buffer_size表示索引缓冲区的大小，严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。

可以检查状态值Key_read_requests和Key_reads，即可知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好，如果这个数值过大，就应该调高key_buffer_size的数值

mysql>show global status like '%key_read%';

对于1G内存的机器，如果不使用MyISAM表，推荐值是16M(8-64M)

重启mysql:  service mysqld start

启动服务，测试。没有再报错。

三、MySQL调优之innodb_buffer_pool_size大小设置

相关查看命令

sql> show global variables like 'innodb_buffer_pool_size';

sql> show global status like 'Innodb_buffer_pool_pages_data';

sql> show global status like 'Innodb_page_size';

或

sql> use mysql;

sql> select @@innodb_buffer_pool_size;

....

MariaDB [(none)]> show global variables like 'innodb_buffer_pool_size';

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

| Variable_name           | Value     |

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

| innodb_buffer_pool_size | 268435456 |

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

1 row in set (0.00 sec)

MariaDB [(none)]> show global status like 'Innodb_buffer_pool_pages_data';

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

| Variable_name                 | Value |

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

| Innodb_buffer_pool_pages_data | 6082  |

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

1 row in set (0.00 sec)

MariaDB [(none)]> show global status like 'Innodb_buffer_pool_pages_total';

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

| Variable_name                  | Value |

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

| Innodb_buffer_pool_pages_total | 16383 |

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

1 row in set (0.00 sec)

MariaDB [(none)]> show global status like 'Innodb_page_size';

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

| Variable_name    | Value |

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

| Innodb_page_size | 16384 |

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

1 row in set (0.00 sec)

MariaDB [(none)]>

官方对这个几个参数的解释：

Innodb_buffer_pool_pages_data

The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and

clean pages.

Innodb_buffer_pool_pages_total

The total size of the InnoDB buffer pool, in pages.

Innodb_page_size

InnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to be

easily converted to bytes

调优参考计算方法：

val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%

val > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的75%

val < 95% 则考虑减小 innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

设置命令：set global innodb_buffer_pool_size = 2097152; //缓冲池字节大小，单位kb，如果不设置，默认为128M

设置要根据自己的实际情况来设置，如果设置的值不在合理的范围内，并不是设置越大越好，可能设置的数值太大体现不出优化效果，反而造成系统的swap空间被占用，导致操作系统变慢，降低sql查询性能。

修改配置文件的调整方法，修改my.cnf配置：

innodb_buffer_pool_size = 2147483648  #设置2G

innodb_buffer_pool_size = 2G  #设置2G

innodb_buffer_pool_size = 500M  #设置500M

MySQL5.7及以后版本，改参数时动态的，修改后，无需重启MySQL，但是低版本，静态的，修改后，需要重启MySQL。