MySQL参数设置优化
发布时间：2022-03-25 15:53:42 所属栏目：编程 来源：互联网
导读：max_connections Variable Scope： Global Dynamic Variable： Yes Default： 151 （mysql5.5+） Meaning： 允许客户端同时连接的最大数 默认值以前是100,MySQL5.5+后151，但是默认值对大部分应用来说这都不够。通过观察Max_used_connections 状态变量随着时
|
max_connections Variable Scope： Global Dynamic Variable： Yes Default： 151 （mysql5.5+） Meaning： 允许客户端同时连接的最大数 默认值以前是100,MySQL5.5+后151，但是默认值对大部分应用来说这都不够。通过观察Max_used_connections 状态变量随着时间的变化。可以告诉你服务器连接是不是在某个时间点有个尖峰。如果这个值达到了max_connections，说明客户端至少被拒绝了一次。 建议值： 500+ （设置为你认为正常情况下有300或者更多连接，则可以设置为500或更多） thread_cache_size Variable Scope： Global Dynamic Variable： Yes Default： 0（mysql5.6.7-） Meaning： 有多少线程应该缓存重用 其默认值在mysql5.6.8+（autosized），根据如下公式得到：8 + (max_connections / 100)，其上限值为100.设置这个变量，可以通过观察服务器一段时间的活动，来计算一个有理有据的值。 观察Threads_connected状态变量并且找到它在一般情况下的最大值和最小值。例如：若Threads_connected状态从150变化到175，可以设置线程缓存为75。但是不用设置的非常大，因为保持大量 等待连接的空闲线程并没有什么真正的用处。也可以观察Threads_created状态随时间的变化。如果这个值很大或一直增长，这是另一个线索，告诉你可能需要调大thread_cache_size变量。 Threads_cached来查看有多少线程已经在缓存中了。 建议值：50-100 table_open_cache Variable Scope: Global Dynamic Variable: Yes Default: 400( mysql5.6.7- ) Meaning: 所有线程打开表的数量 从官方文档看出在MySQL5.6.8+开始默认值为2000，就能简单的判断出原来默认值是不够的。可以通过观察Opened_tables其值及其一段时间的变化来检查该变量。如果看到Opened_tables的值很大 并且又不经常执行FLUSH TABLES（执行其命令强制所有的表重新关闭且打开），那么可能你应该增加该变量的值。 建议值： 4096（有另一种说法：这个值从max_connections的10倍开始设置） open_files_limit Variable Scope： Global Dynamic Variable： No Default ： 400（mysql5.6.7-） Meaning： 缓存表定义的的数量（以.frm结尾的文件） 其默认值在mysql5.6.8+（autosized），根据如下公式得到：400 + (table_open_cache / 2) 其上限值为：2000。常可以把table_definition_cache 设置得足够高，以缓存所有的表定义。 除非有上万张表，否则这可能是最简单的方法。 建议值： 根据真正的数据库中表的数量（例如：数据库实例有1000张表，可以将其设置为1000+） back_log Variable Scope: Global Dynamic Variable: No Default: 50( mysql5.6.5- ) Meaning: 在很短时间内，可以有多少个请求链接在堆栈中等待被处理。 其默认值在mysql5.6.6+（autosized），根据如下公式得到： 50 + (max_connections / 5) 其上限值为：900。 如果每秒的连接数很多，可以将其值调大。其值和OS的TCP/IP链接有关联，和内核参数net.ipv4.tcp_max_syn_backlog的值相关，back_log的值不能大于其值。 建议值：2048 max_allowed_packet Variable Scope： Global Dynamic Variable： Yes Default： 100（mysql5.6.6+） Meaning： 最大的连接错误数 如果有时网络短暂抽风了，或者应用配置出现错误，或另有问题，如权限，在短暂的时间内不断地尝试链接，客户端可能被列入黑名单，然后将无法连接，知道再次刷新主机缓存（FLUSH HOSTS）。 这个选项的默认设置太小了，很容易导致问题。你也许希望增加这个值，实际上，如果知道服务器可以充分抵御蛮力攻击，可以把这个值设置的非常大，以有效地禁用主机黑名单。这个选项也就是 所谓的可以防止暴力破解。 建议值： 1000000 （其值为Percona 给出的建议值，但是应该确定其主机的已有抵御蛮力攻击的能力） skip_name_resolve Variable Scope： Global Dynamic Variable： No Default： OFF Meaning： DNS查找 这个选项禁用了另一个网络相关和鉴权认证的陷进：DNS查找。DNS是MySQL连接过程中的一个薄弱环节。当连接服务器时，它试图确定连接和使用的主机的主机名，作为身份验证凭据的一部分。 （就是说，你的凭据是用户名，主机名，以及密码，并不只是用户名和密码）但是验证主机来源，服务器需要执行DNS的正向和反向查找。要是DNS有问题就悲剧了，在某些时间点这是必然的事。 为了避免这种情况，我们强烈建议设置这个选项，在验证时关闭DNS查找，这样即快又安全。 建议值： ON Default： 0 Meaning： 控制MySQL怎么刷新二进制日志到磁盘 默认值为0，意味着MySQL并不刷新，有操作系统自己决定什么时候刷新缓存到持久化设备。如果这个值比0大，它指定了两次刷新到磁盘的动作之间间隔多少次二进制日志写操作 （如果autocommit被设置了，每个独立的语句都是一次写，否则就是一个事务一次写）。如果没有设置sync_binlog为1，那么崩溃以后可能导致二进制日志没有同步事务数据。这可以轻易地导致复制中断， 并且使得及时恢复变得不可能。无论如何，可以把这个值设置为1来获得安全的保障。这样就会要求MySQL把二进制日志和事务日志两个文件刷新到不同的位置。这可能需要磁盘寻道，相对来说是个很慢的操作。 建议值： 1 tmp_table_size和max_heap_table_size 这两个设置控制使用Memory引擎的内存临时表能使用多大的内存。如果隐式内存临时表的大小超过这两个设置的值，将会被转换为MyISAM表，所以它的大小可以继续增长。（隐式临时表是一种并非由自己创建， 而是服务器创建，用于保存执行行中的查询的中间结果的表）应该简单地把这两个变量设为同样的值。但是要谨防这个变量太大了，临时表最好呆在内存里，但是如果它们被撑得很大，实际上还是让它们使用 磁盘比较好，否则可能会让服务器内存溢出。假设查询语句没有创建庞大的临时表（通常可以通过合理的索引和查询设计来避免），那把这些变量设大一点，免得把内存临时表转换为磁盘临时表。这个过程可以 在SHOW PROCESSLIST中看到。使用临时表的情况可以通过状态变量Created_tmp_tables 和 Created_tmp_disk_tables 来监控。 建议值： 设置两个变量为同样的值（这个大小要根据自己的SQL查询级别及SQL语句的优化情况） query_cache_size Variable Scope： Global Dynamic Variable ： Yes Default： 0（mysql5.6.7-） Meaning: 缓存查询结果的内存大小 查询缓存使用的总内存空间，单位是字节。这个值必须是1024的整数倍，否则MySQL实际分配的数据会和你指定的略有不同。 建议值：<512MB Meaning： 设置使用连接查询缓存的大小 此选项可以提高没有使用索引的连接查询的性能。全局的建议不要设置太大，可以动态设置session级别的值。 建议值： 8MB read_rnd_buffer_size Variable Scope ： Global, Session Dynamic Variable ： Yes Default： 256kb Meaning： 读取排序行的缓存的大小 MySQL只会在有查询需要时才会为该缓存分配内存，并且只会分配需要的内存大小而不是全部指定的大小。如果增加其值，可以提高order by 的操作性能。 建议值： 16MB myisam_sort_buffer_size Variable Scope： Global, Session Dynamic Variable ： Yes Default： 8MB Meaning： 排序MyISAM的索引缓存的大小 当REPAIR TABLE或者创建索引，修改索引时操作（CREATE INDEX，ALTER TABLE）时，分配给用于其MyISAM索引排序的大小。 建议值: 8MB~256MB innodb_buffer_pool_size Variable Scope : Global Dynamic Variable: No Default： 128MB Meaning： InnoDB缓冲池的大小 如果大部分都是InnoDB表，InnoDB缓冲池或许比其他任何东西更需要内存。InnoDB缓冲池并不仅仅缓存索引：它还会缓存行的数据，自适应哈希索引，插入缓冲，锁，以及其他内部数据结构。 建议值：80%+ 物理内存 innodb_buffer_pool_instances Variable Scope ： Global Dynamic Variable ： No Default： 1（mysql5.6.5-） Meaning： InnoDB缓存池的实例个数 从InnoDB1.0.x版本开始，允许有多个缓冲池实例。每个页根据哈希值平均分配到不同的缓冲池实例中。这样做的好处是减少数据库内部资源竞争，增加数据库的并发处理能力。可以通过参数 innodb_buffer_pool_instances来配置。在MySQL5.6.6+其默认值为：（autosized），除了Window 32bit其值是根据innodb_buffer_pool_size的大小动态得到，其它默认值为8. 建议值：4+ （mysql5.5+） Variable Scope： Global Dynamic Variable： Yes Default： 1 Meaning： 控制日志缓冲刷新的频繁程度 日志缓冲必须刷新到持久化存储，以确保提交的事务完全被持久化了。如果和持久相比更在乎性能，可以修改 innodb_flush_log_at_trx_commit变量来控制日志缓冲刷新的频繁程度。可能的设置如下： 0 把日志缓冲写到日志文件，并且每秒刷新一次，但是事务提交时不做任何事情。 1 将日志缓冲写到日志文件，并且每次事务提交都刷新到持久化存储。这是默认的（并且是最安全的）设置，该设置 能保证不会丢失任何已经提交的事务，除非磁盘或者OS是’伪‘刷新。 2 每次提交时把日志缓冲写到日志文件，但是并不刷新。Innodb每秒做一次刷新。0与2最重要的不同是，如果MySQL 进程“挂了”，2不会丢失事务。 建议值： 1 innodb_io_capacity Variable Scope： Global Dynamic Variable： Yes Default： 200 InnoDB曾经在代码里写死了假设服务器运行在每秒100个I/O操作的单硬盘上。默认值很糟糕。现在可以告诉InnoDB服务器有多大的I/O能力。有时需要把这个值设置得相当高（像SSD这样极快的存储 设备上需要设置为上万）才能稳定地刷新脏页。 建议值： 根据server的I/O能力有关系 innodb_read_io_threads和innodb_write_io_threads 这些选项控制有多少后台线程可以被I/O操作使用。最近版本的MySQL里，默认值4个读线程和4个写线程，对大部分 服务器这都足够了，尤其是MySQL5.5里面可以用操作系统原生的异步I/O以后。 建议值：各为4（即默认值） Default： OFF（mysql5.6.5-） Meaning： 控制InnoDB表空间存储形式 其默认值在mysql5.6.6+后为ON。开启此选项后，关于InnoDB表的数据和索引单独存储在自己的表空间中（.ibd结尾的文件）。否则，存储在系统的表空间中（ibdata）。 建议值： ON （编辑：核心网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
