mysql常用参数

最新推荐文章于 2022-03-20 12:33:21 发布

cuansiye8661

最新推荐文章于 2022-03-20 12:33:21 发布

阅读量137

点赞数

expire_logs_days

设置二进制日志的保留时间 expire_logs_days=n,   “n”就是保留最近“几天”的日志信息，之前的就都删掉,
   set global expire_logs_days=30; 每次产生新的mysql_bin或者重启mysql的时候生效；
    在压力比较大的mysql上或生产环境，我们不应该启动这个参数(my.cnf中不显式设置该参数或设置expire_logs_days=0)：mysql每天产生十几个或更多的binlog文件，启用这个参数后，一次清理这么多文件，必定会导致磁盘io被占满，mysql出现抖动或hang住。因此建议自己编写脚本，每次purge完一个binlog后，sleep几秒

lower_case_table_names

1: 不区分表明的大小写； 0：区分表明的大小写；
注意：改这个值得时候要注意，如果这个值之前为0 ，要改成1，需要将之前有大写的表明更改成小写。否则服务重启后，这些有大写的表明将无法识别

have_query_cache

表示当前版本的MYSQL是否支持Query Cache

query_cache_size

MYSQL的查询缓存用于缓存select查询结果，并在下次接收到同样的查询请求时，不再执行实际查询处理而直接返回结果，有这样的查询缓存能提高查询的速度，使查询性能得到优化，前提条件是你有大量的相同或相似的查询，而很少改变表里的数据，否则没有必要使用此功能。可以通过Qcache_lowmem_prunes变量的值来检查是否当前的值满足你目前系统的负载。注意：如果你查询的表更新比较频繁，而且很少有相同的查询，最好不要使用查询缓存。

thread_cache_size

这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。(?>表示要调整的值)   根据物理内存设置规则如下：
    1G ―> 8
    2G ―> 16
    3G ―> 32
    >3G ―> 64

table_open_cache

table_cache 参数设置表高速缓存的数目。每个连接进来，都会至少打开一个表缓存。因此， table_cache 的大小应与 max_connections 的设置有关。
例如，对于 200 个并行运行的连接，应该让表的缓存至少有 200 × N ，这里 N 是应用可以执行的查询的一个联接中表的最大数量。此外，还需要为临时表和文件保留一些额外的文件描述符。
缓存机制
当 Mysql 访问一个表时，如果该表在缓存中已经被打开，则可以直接访问缓存；如果还没有被缓存，但是在 Mysql 表缓冲区中还有空间，那么这个表就被打开并放入表缓冲区；
如果表缓存满了，则会按照一定的规则将当前未用的表释放，或者临时扩大表缓存来存放，使用表缓存的好处是可以更快速地访问表中的内容。
参数调优
一般来说，可以通过查看数据库运行峰值时间的状态值 Open_tables 和 Opened_tables ，判断是否需要增加 table_cache 的值（其中 open_tables 是当前打开的表的数量， Opened_tables 则是已经打开的表的数量）。
即如果open_tables接近table_cache的时候，并且Opened_tables这个值在逐步增加，那就要考虑增加这个值的大小了。还有就是Table_locks_waited比较高的时候，也需要增加table_cache。
mysql> show global status like 'open%_tables';
如果Open_tables的值已经接近table_cache的值，且Opened_tables还在不断变大，则说明mysql正在将缓存的表释放以容纳新的表，此时可能需要加大table_cache的值。对于大多数情况，
比较适合的值：
Open_tables / Opened_tables >= 0.85
Open_tables / table_cache <= 0.95
如果对此参数的把握不是很准，VPS管理百科给出一个很保守的设置建议：把MySQL数据库放在生产环境中试运行一段时间，然后把参数的值调整得比Opened_tables的数值大一些，并且保证在比较高负载的极端条件下依然比Opened_tables略大。
在mysql默认安装情况下，table_cache的值在2G内存以下的机器中的值默认时256到 512，如果机器有4G内存,则默认这个值是2048，但这决意味着机器内存越大，这个值应该越大，
因为table_cache加大后，使得mysql对 SQL响应的速度更快了，不可避免的会产生更多的死锁（dead lock），这样反而使得数据库整个一套操作慢了下来，严重影响性能。
所以平时维护中还是要根据库的实际情况去作出判断，找到最适合你维护的库的 table_cache值。
清空缓存执行
mysql >flush tables;
命令将会清空当前所有缓存的表。

innodb_additional_mem_pool_size

这个参数我们平时调整的可能不是太多，很多人都使用了默认值，可能很多人都不是太熟悉这个参数的作用。
innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

这个参数大小是否足够还是比较容易知道的，因为当过小的时候，MySQL 会记录 Warning 信息到数据库的 error log 中，这时候你就知道该调整这个参数大小了。

tmp_table_size

mysql 的配置文件中，tmp_table_size 的默认大小是 32M。
如果一张临时表超出该大小，MySQL产生一个 The table tbl_name is full 形式的错误，
如果你做很多高级 GROUP BY 查询，增加 tmp_table_size 值。
这是 mysql 官方关于此选项的解释：
tmp_table_size
This variable determines the maximum size for a temporary table in memory.
If the table becomes too large, a MYISAM table is created on disk.
Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory.
Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped.
Be aware that memory is also allocated per-thread.
An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect.
The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory.