MYSQL中my.cnf参数的示例分析

这篇文章主要介绍了MYSQL中my.cnf参数的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

mysql常用配置文件参数介绍和使用方法：

● max_conecctions：整个 MySQL 允许的最大连接数；
这个参数主要影响的是整个 MySQL 应用的并发处理能力，当系统中实际需要的连接量大于max_conecctions 的情况下，由于 MySQL 的设置限制，那么应用中必然会产生连接请求的等待，从而限制了相应的并发量。所以一般来说，只要 MySQL 主机性能允许，都是将该参数设置的尽可能大一点。一般来说 500 到 800 左右是一个比较合适的参考值

● max_user_connections：每个用户允许的最大连接数；上面的参数是限制了整个 MySQL 的连接数，而 max_user_connections 则是针对于单个用户的连接限制。在一般情况下我们可能都较少使用这个限制，只有在一些专门提供 MySQL 数据存储服务，或者是提供虚拟主机服务的应用中可能需要用到。除了限制的对象区别之外，其他方面和max_connections 一样。这个参数的设置完全依赖于应用程序的连接用户数，对于普通的应用来说，完全没有做太多的限制，可以尽量放开一些。

● net_buffer_length：网络包传输中，传输消息之前的 net buffer 初始化大小；这个参数主要可能影响的是网络传输的效率，由于该参数所设置的只是消息缓冲区的初始化大小，所以造成的影响主要是当我们的每次消息都很大的时候 MySQL 总是需要多次申请扩展该缓冲区大小。系统默认大小为 16KB，一般来说可以满足大多数场景，当然如果我们的查询都是非常小，每次网络传输量都很少，而且系统内存又比较紧缺的情况下，也可以适当将该值降低到8KB。

● max_allowed_packet：在网络传输中，一次传消息输量的最大值；这个参数与 net_buffer_length 相对应，只不过是 net buffer 的最大值。当我们的消息传输量大于 net_buffer_length 的设置时，MySQL 会自动增大 net buffer 的大小，直到缓冲区大小达到 max_allowed_packet 所设置的值。系统默认值为 1MB，最大值是 1GB，必须设定为 1024 的倍数，单位为字节。

● back_log：在 MySQL 的连接请求等待队列中允许存放的最大连接请求数。连接请求等待队列，实际上是指当某一时刻客户端的连接请求数量过大的时候，MySQL 主线程没办法及时给每一个新的连接请求分配（或者创建）连接线程的时候，还没有分配到连接线程的所有请求将存放在一个等待队列中，这个队列就是 MySQL 的连接请求队列。当我们的系统存在瞬时的大量连接请求的时候，则应该注意 back_log 参数的设置。系统默认值为 50，最大可以设置为 65535。当我们增大 back_log 的设置的时候，同时还需要主义 OS 级别对网络监听队列的限制，因为如果 OS 的网络监听设置小于 MySQL 的 back_log 设置的时候，我们加大“back_log”设置是没有意义的。

在 MySQL 中，为了尽可提高客户端请求创建连接这个过程的性能，实现了一个 Thread Cache 池，将空闲的连接线程存放在其中，而不是完成请求后就销毁。这样，当有新的连接请求的时候，MySQL 首先会检查 Thread Cache 池中是否存在空闲连接线程，如果存在则取出来直接使用，如果没有空闲连接线程，才创建新的连接线程。在 MySQL 中与连接线程相关的系统参数及状态变量说明如下：
● thread_cache_size：Thread Cache 池中应该存放的连接线程数。
当系统最初启动的时候，并不会马上就创建 thread_cache_size 所设置数目的连接线程存放在Thread Cache 池中，而是随着连接线程的创建及使用，慢慢的将用完的连接线程存入其中。当存放的连接线程达到 thread_cache_size 值之后，MySQL 就不会再续保存用完的连接线程了。如果我们的应用程序使用的短连接，Thread Cache 池的功效是最明显的。因为在短连接的数据库应用中，数据库连接的创建和销毁是非常频繁的，如果每次都需要让 MySQL 新建和销毁相应的连接线程，那么这个资源消耗实际上是非常大的，而当我们使用了 Thread Cache 之后，由于连接线程大部分都是在创建好了等待取用的状态，既不需要每次都重新创建，又不需要在使用完 之 后 销 毁 ， 所 以 可 以 节 省 下 大 量 的 系 统 资 源 。 所 以 在 短 连 接 的 应 用 系 统 中 ，thread_cache_size 的值应该设置的相对大一些，不应该小于应用系统对数据库的实际并发请求数。而如果我们使用的是长连接的时候，Thread Cache 的功效可能并没有使用短连接那样的大，但
也并不是完全没有价值。因为应用程序即使是使用了长连接，也很难保证他们所管理的所有连接都能处于很稳定的状态，仍然会有不少连接关闭和新建的操作出现。在有些并发量较高，应
用服务器数量较大的系统中，每分钟十來次的连接创建与关闭的操作是很常见的。而且如果应用服务器的连接池管理不是太好，容易产生连接池抖动的话，所产生的连接创建和销毁操作将
会更多。所以即使是在使用长连接的应用环境中，Thread Cache 机制的利用仍然是对性能大有帮助的。只不过在长连接的环境中我们不需要将 thread_cache_size 参数设置太大，一般来说
可能 50 到 100 之间应该就可以了。

● thread_stack：每个连接线程被创建的时候，MySQL 给他分配的内存大小。
当 MySQL 创建一个新的连接线程的时候，是需要给他分配一定大小的内存堆栈空间，以便存放客户端的请求 Query 以及自身的各种状态和处理信息。不过一般来说如果不是对 MySQL 的连接线
程处理机制十分熟悉的话，不应该轻易调整该参数的大小，使用系统的默认值（192KB）基本上可以所有的普通应用环境。如果该值设置太小，会影响 MySQL 连接线程能够处理客户端请求的Query 内容的大小，以及用户创建的 Procedures 和 Functions 等

计算出系统新建连接连接的 Thread
Cache 命中率，也就是通过 Thread Cache 池中取得连接线程的次数与系统接收的总连接次数的比率，如下：
Threads_Cache_Hit = (Connections - Threads_created) / Connections * 100%我们可以通过上面的这个运算公式计算一下上面环境中的 Thread Cache 命中率：Thread_Cache_Hit= (127 - 12) / 127 * 100% = 90.55%一般来说，当系统稳定运行一段时间之后，我们的 Thread Cache 命中率应该保持在 90%左右甚至更高的比率才算正常。可以看出上面环境中的 Thread Cache 命中比率基本还算是正常的。Table Cache 相关的优化，我们先来看一下 MySQL 打开表的相关机制。由于多线程的实现机制，为了尽可能的提高性能，在MySQL 中每个线程都是独立的打开自己需要的表的文件描述符，而不是通过共享已经打开的表的文件描述符的机制来实现。当然，针对于不同的存储引擎可能有不同的处理方式。如 MyISAM 表，每一个客户端线程打开任何一个 MyISAM 表的数据文件都需要打开一个文件描述符，但如果是索引文件，则可以多个线程共享同一个索引文件的描述符。对于 Innodb 的存储引擎，如果我们使用的是共享表空间来存储数据，那
么我们需要打开的文件描述符就比较少，而如果我们使用的是独享表空间方式来存储数据，则同样，由于存储表数据的数据文件较多，则同样会打开很多的表文件描述符。除了数据库的实际表或者索引打开以外，临时文件同样也需要使用文件描述符，同样会占用系统中 open_files_limit 的设置限额。为了解决打开表文件描述符太过频繁的问题，MySQL 在系统中实现了一个 Table Cache 的机制，和前面介绍的 Thread Cache 机制有点类似，主要就是 Cache 打开的所有表文件的描述符，当有新的请求的时候不需要再重新打开，使用结束的时候也不用立即关闭。通过这样的方式来减少因为频繁打开关闭文件描述符所带来的资源消耗。我们先看一看 Table Cache 相关的系统参数及状态变量。在 MySQL 中我们通过 table_cache（从 MySQL5.1.3 开始改为table_open_cache），来设置系统中为我们 Cache 的打开表文件描述符的数量。通过 MySQL 官方手册中的介绍，我们设置 table_cache 大小的时候应该通过 max_connections 参数计算得来，公式如下：
table_cache = max_connections * N；其中 N 代表单个 Query 语句中所包含的最多 Table 的数量。但是我个人理解这样的计算其实并不是太准确，分析如下：
首先，max_connections 是系统同时可以接受的最大连接数，但是这些连接并不一定都是 active 状态的，也就是说可能里面有不少连接都是处于 Sleep 状态。而处于 Sleep 状态的连接是不可能打开任何Table 的。其次，这个 N 为执行 Query 中包含最多的 Table 的 Query 所包含的 Table 的个数也并不是太合适，因为我们不能忽略索引文件的打开。虽然索引文件在各个连接线程之间是可以共享打开的连接描述符的，但总还是需要的。而且，如果我 Query 中的每个表的访问都是通过现通过索引定位检索的，甚至可能还是通过多个索引，那么该 Query 的执行所需要打开的文件描述符就更多了，可能是 N 的两倍甚至三倍。最后，这个计算的公式只能计算出我们同一时刻需要打开的描述符的最大数量，而 table_cache 的设置也不一定非得根据这个极限值来设定，因为 table_cache 所设定的只是 Cache 打开的描述符的数量的大小，而不是最多能够打开的量的大小。

join_buffer_size ：当我们的 Join 是 ALL ， index ， rang 或者 index_merge 的时候使用的Buffer；实际上这种 Join 被称为 Full Join。实际上参与 Join 的每一个表都需要一个 Join Buffer，所以在Join 出现的时候，至少是两个。Join Buffer 的设置在 MySQL 5.1.23 版本之前最大为 4GB，但是从5.1.23 版本开始，在除了 Windows 之外的 64 位的平台上可以超出 4BG 的限制。系统默认是 128KB。

● sort_buffer_size：系统中对数据进行排序的时候使用的 Buffer；Sort Buffer 同样是针对单个 Thread 的，所以当多个 Thread 同时进行排序的时候，系统中就会出现多个 Sort Buffer。一般我们可以通过增大 Sort Buffer 的大小来提高 ORDER BY 或者是 GROUP BY的处理性能。系统默认大小为 2MB，最大限制和 Join Buffer 一样，在 MySQL 5.1.23 版本之前最大为 4GB，从 5.1.23 版本开始，在除了 Windows 之外的 64 位的平台上可以超出 4GB 的限制。如果应用系统中很少有 Join 语句出现，则可以不用太在乎 join_buffer_size 参数的大小设置，但是如果 Join 语句不是很少的话，个人建议可以适当增大 join_buffer_size 的设置到 1MB 左右，如果内存充足甚至可以设置为 2MB。对于 sort_buffer_size 参数来说，一般设置为 2MB 到 4MB 之间可以满足大多数
应用的需求。当然，如果应用系统中的排序都比较大，内存充足且并发量不是特别的大的时候，也可以继续增大 sort_buffer_size 的设置。在这两个 Buffer 设置的时候，最需要注意的就是不要忘记是每个Thread 都会创建自己独立的 Buffer，而不是整个系统共享的 Buffer，不要因为设置过大而造成系统内存不足。


innodb_thread_concurrendy：此参数为innodb为保障服务正常运行的限流操作，设置为0表示由innodb自己控制；一般建议设置为服务器CPU的核数（不含超线程），过大会导致服务hang死等不可用情况


innodb_io_capacity：每秒后台进程处理IO操作的数据页上线，一般可设置为存储总IO能力的75%，一般IO性能比较好的情况下此参数建议配置成1000


innodb_buffer_pool_instance：在innodb_buffer_pool中划分实例，每个实例下都包含flush、LRU、free列表，一般大内存建议配置多个innodb_buffer_pool_instance


innodb_max_dirty_pages_pct：innodb从innodb_buffer_pool中刷新脏页到磁盘的比例，设置太高对IO影响较大，此参数与innodb_io_capacity结合使用，IO性能较好情况下可以设置为75%


innodb_flush_method：设置为O_DIRECT时，直接刷新内存数据到磁盘避免raid设备上的缓存


innodb_file_per_table：设置为1，每个表单独一个数据文件，这样可以放在其他磁盘做软连接，提升IO性能；另一方面可以降低共享表空间的IO竞争，避免ibdata1过大


innodb_flush_log_at_trx_commit：设置为0：每秒将log buffur中的内容刷新到磁盘；设置为1：每次事务提交前将log buffur中内容刷新到磁盘；设置为2：将log buffur中内容写入事务日志，但由于操作系统等缓存可能存在，不一定会刷新到磁盘


sync_binlog：刷新binlog的数目，非核心系统设置为1000，表示当累积1000条binlog记录时才会刷新到磁盘；核心系统可以设置为1，保证主备服务器数据同步；双1模式：即innodb_flush_log_at_trx_commit和sync_binlog都设置为1，这样主备的数据时一致的，不会丢失数据

感谢你能够认真阅读完这篇文章，希望小编分享的“MYSQL中my.cnf参数的示例分析”这篇文章对大家有帮助，同时也希望大家多多支持亿速云，关注亿速云行业资讯频道，更多相关知识等着你来学习!