Mysql常用配置参数整理

cat /etc/my.cnf

# 有关如何更改设置的建议，请参阅

# http://dev.mysql.com/doc /refman/5.6/en/server-configuration-defaults.html

# *** 不要编辑此文件。这是一个模板，在安装过程中将被复制到

# ** * 默认位置，并且如果您

# *** 升级到较新版本的 MySQL，该模板将被替换。

[mysqld]

# 删除前导 # 并设置为 MySQL 中最重要数据的 RAM 量

# 缓存。从 70% 开始专用服务器总 RAM 的 10%。

# innodb_buffer_pool_size = 128M

# 删除前导 # 以打开非常重要的数据完整性选项：日志记录

# 备份之间二进制日志的更改。

# log_bin

# 这些是常用设置，删除 # 并根据需要进行设置。

# basedir = .....

# datadir = .....

# port= .....

# server_id = .....

# socket = .....

# 删除前导 # 以设置主要对报告服务器有用的选项。

# 服务器默认设置对于事务处理速度更快和快速选择。

# 根据需要调整大小，尝试找到最佳值。

#innodb

user=mysql< /p>

innodb_buffer_pool_size=6G

innodb_log_file_size=4G

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit=2

innodb_file_per_table=1< /p>

innodb_file_io_threads=4

innodb_flush_method=O_DIRECT

innodb_io_capacity=2000

innodb_io_capacity_max=6000

innodb_lru_scan_depth=2000

innodb_thread_concurrency = 0

innodb_additional_mem_pool_size=16M

innodb_autoinc_lock_mode = 2

##################################################< /p>

# 二进制日志/复制

log-bin

sync_binlog=1

sync_relay_log=1

relay- log-info-repository=TABLE

master-info-repository=TABLE

expire_logs_days=7

binlog_format=ROW

transaction-隔离=已提交读

## ###################################### #########

#cache

tmp_table_size=512M

character-set-server=utf8

排序规则-server=utf8_general_ci

跳过-外部锁定

back_log=1024

key_buffer_size=1024M

thread_stack=256k

read_buffer_size=8M

thread_cache_size=64

query_cache_size=128M

max_heap_table_size=256M

query_cache_type=1

binlog_cache_size = 2M

table_open_cache=128

thread_cache=1024

thread_concurrency=8

wait_timeout=30

join_buffer_size = 1024M

sort_buffer_size = 8M

read_rnd_buffer_size = 8M

###### ########################################

#connect

max-connect-errors=100000

max-connections=1000

################## ##############################

explicit_defaults_for_timestamp=true

sql_mode= NO_ENGINE_SUBSTITUTION，STRICT_TRANS_TABLES

######################################## ## ########

# 二进制日志/复制（这里主要的复制功能是主从功能，提前配置好，主从配置后面会讲）

#二进制日志

log-bin

#保证最大程序上复制的InnoDB事务的持久性和一致性

sync_binlog =1

sync_relay_log=1

#启用这两项，可用于在崩溃时保证二进制和从服务器的安全

master-info-repository=TABLE

#设置清除日志时间

expire_logs_days=7

#行复制

binlog_format=ROW< /p>

#Mysql数据库事务隔离级别有四种类型（READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE）

transaction-isolation=READ-COMMITTED

#cache

#内存临时表最大大小

tmp_table_size=512M

character-set-server=utf8

collat​​ion-server= utf8_general_ci

#即跳过外部锁定

skip-external-locking

#MySQL可以临时存储的连接数（根据实际设置）

back_log=1024

#指定索引缓冲区的大小，仅适用于MyISAM表。写在这里也没关系

key_buffer_size= 1024M

#这条指令限制了每个数据库线程的栈大小

thread_stack=256k

#当一个查询连续扫描一个表时，MySQL会为其分配一块内存缓冲区

read_buffer_size=8M

#线程缓存

thread_cache_size=64

#查询缓存大小

query_cache_size=128M

#内部内存临时表的最大值，每个线程必须分配

max_heap_table_size=256M

#将查询结果放入查询缓存

query_cache_type =1

#表示事务过程中容纳二进制日志SQL语句缓存大小

binlog_cache_size = 2M

#同样是缓存表大小

table_open_cache=128

#缓存线程

thread_cache=1024

#建议设置为服务器CPU核心数的2倍

thread_concurrency=8

wait_timeout=30

#表间及表连接的缓冲区大小

join_buffer_size = 1024M

join_buffer_size = 1024M

# p>

# 是连接级参数。每个连接第一次需要使用这个缓冲区时，使用一次 性分配内存

sort_buffer_size=8M

#随机读取数据缓冲区使用内存

阅读_rnd_buffer_size = 8M

#connect

#是MySQL中与安全相关的计数器值，负责阻止过多失败的客户端，防止暴力破解密码

max-connect-errors=100000

#连接数

max-connections=1000

#启用查询缓存

explicit_defaults_for_timestamp=true

#mysql服务器可以工作在不同的模式下，并针对不同的客户端以不同的方式应用这些模式

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

下面列出了对性能优化的影响较大的主要变量主要分为连接请求变量和缓冲区变量。

1. 连接请求变量：

1) max_connections

MySQL最大连接数。增加该值会增加 mysqld 所需的文件描述符的数量。如果服务器有大量并发连接r请求时，建议增大该值以增加并行连接数。当然，这是基于机器能够支持的，因为如果连接数较多，MySQL会为每个连接提供一个连接。缓冲区会消耗较多的内存，因此必须适当调整该值，不能盲目增大设置值。

如果该值太小，经常会出现ERROR 1040: Too much Connections。您可以使用“conn%”通配符检查当前连接数以确定该值的大小。

显示“max_connections”最大连接数等变量

显示“max_used_connections”响应连接数等状态

如下：

mysql > 显示“max_connections”等变量；

+—————————–+——-+

|变量名 |值 |

+— ——————–+——-+

|最大连接数 | 256：|

+—————————–+——-+

mysql> 显示类似“max%connections”的状态；

+ ————————–+——-+

|变量名 |值 |

+—————————--+——-+

|最大使用连接数 | 256|

+————————————-+ ——-+

max_used_connections / max_connections * 100%（理想值 ≈ 85%）

如果max_used_connections与max_connections相同，则说明max_connections设置得太低或者超出了服务器负载限制，低如果小于10%则说明设置太大。

2)  back_log

MySQL 可以临时存储的连接数。当 MySQL 主线程在短时间内收到大量连接请求时，这会起作用。如果MySQL的连接数据达到max_connections，新的请求就会被存入栈中等待某个连接释放资源。堆栈的数量是back_log。如果等待连接数超过back_log，则不会授予连接资源。

back_log值表示在MySQL暂时停止应答之前的短时间内，堆栈中可以存储多少个请求发出新的请求。只有当您期望在短时间内有很多连接时，您才需要增加它，换句话说，该值是传入 TCP/IP 连接的侦听队列的大小。

当观察你的主机进程列表（mysql> show full processlist）时，你发现大量的264084 |未经身份验证的用户 | xxx.xxx.xxx.xxx |空|连接 |空|登录 |要连接的进程为 NULL。 ，需要增加back_log的值。

默认值为50，可调整为128。系统设置范围为小于512的整数。

3）interactive_timeout

数量交互式连接在被服务器关闭之前等待操作的秒数。交互式客户端被定义为使用 mysql_real_connect() 的 CLIENT_INTERACTIVE 选项的客户端。

默认值为28800，可调整为7200。

2. 缓冲区变量

全局缓冲区ffer:

4) Key_buffer_size

key_buffer_size指定索引缓冲区的大小，它决定了索引处理的速度，尤其是索引读取的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。 key_reads / key_read_requests 的比率应尽可能低，至少为 1:100，1:1000 更好（上述状态值可以使用 SHOW STATUS LIKE ‘key_read%’ 获得）。

key_buffer_size仅适用于MyISAM表。即使您不使用 MyISAM 表，但内部临时磁盘表是 MyISAM 表，也可以使用此值。您可以检查状态值created_tmp_disk_tables以了解详细信息。

例如：

mysql>显示'key_buffer_size'等变量；

+——————-+————+

|变量名 |值 |

+—————————+————+

|密钥缓冲区大小 | 536870912 |

+ ———— ———-+————+

key_buffer_size 为 512MB。我们来看看美国key_buffer_size的ge：

mysql>显示全局状态，如'key_read%';

+—————————+————-+

+————- ————+————-+

| Key_read_requests| 27813678764 |

| Key_reads：| 6798830 |

+————————— +————-+

总共有 27813678764 个索引读请求，6798830 个请求在内存中未找到，索引是直接从硬盘读取的。计算索引未命中缓存的概率：

key_cache_miss_rate = Key_reads / Key_read_requests * 100%，最好设置在1/1000左右

默认配置值为8388600（8M） ，主机内存为4GB，可调值为268435456（256MB）。

5) query_cache_size

使用查询缓冲，MySQL 将查询结果存储在缓冲区中。以后，对于同一个 SELECT 语句（区分大小写），将直接从缓冲区读取结果。

通过检查状态值Qcache_*，可以知道query_cache_size设置是否合理（上述状态值可以使用 SHOW STATUS LIKE ‘Qcache%’ 获得）。如果Qcache_lowmem_prunes的值很大，说明经常出现缓冲不足的情况。如果Qcache_hits的值也很大，说明查询缓冲区使用非常频繁，需要增加缓冲区大小。如果Qcache_hits的值不大，说明你的查询重复率很低。在这种情况下，使用查询缓冲会影响效率，因此可以考虑不使用查询缓冲。另外，在SELECT语句中添加SQL_NO_CACHE可以明确表示不使用查询缓冲区。

与查询缓冲相关的参数包括query_cache_type、query_cache_limit、query_cache_min_res_unit。

query_cache_type指定是否使用查询缓冲，可以设置为0、1或2。该变量是SESSION级别的变量。

query_cache_limit指定单个q可以使用的缓冲区大小默认为1M。

query_cache_min_res_unit是4.1版本后引入的。它指定分配缓冲区空间的最小单位。默认为 4K。检查状态值Qcache_free_blocks。如果该值很大，说明缓冲区中的碎片较多，说明查询结果比较少。在这种情况下，需要减少query_cache_min_res_unit。

例如：

mysql>显示全局状态，如'qcache%';

+————————————-+—— ————–+

|变量名 |值 |

+————————————-+——————–+

| Qcache_free_blocks | Qcache_free_blocks | 22756 |

| Qcache_free_内存 | 76764704 |

| Qcache_hits | 213028692 |

| Qcache_插入| 208894227 |

| Qcache_lowmem_prunes | Qcache_lowmem_prunes | 4010916 |

| Qcache_not_cached | 13385031 |

| Qcache_queries_in_cache | Qcache_queries_in_cache | 43560 |

| |

+—— ————————-+——————–+

mysql> 显示类似 'query_cache%' 的变量；

+———————— ————–+————–+

|变量名 |

+———————————–+————– +

|查询缓存限制 | 2097152 |

| query_cache_min_res_unit | 4096 |

|查询缓存大小 | 203423744 |

|查询缓存类型 |开 |查询缓存wlock_invalidate | OFF |

+———— ———————————–+——————+

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100 %

如果查询缓存碎片率超过20%，可以使用FLUSH QUERY CACHE对缓存进行碎片整理，或者如果你的查询都是小数据量，可以尝试减小query_cache_min_res_unit。

查询缓存利用率=（query_cache_size – Qcache_free_memory）/query_cache_size * 100%

如果查询缓存利用率低于25%，则说明query_cache_size设置过大，可以适当减少；如果查询缓存利用率在 80% 以上且 Qcache_lowmem_prunes > 50，则意味着 query_cache_size 可能有点小，或者可能存在太多碎片。

查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hit * 100%

示例服务器查询缓存碎片率=20.46%，查询缓存利用率=62.26%，查询缓存命中率=1.94%，命中率很差，可能写操作比较频繁，并且可能存在一些碎片。

每个连接的缓冲区

6) record_buffer_size

每个执行顺序扫描表的线程分配一个该大小的缓冲区。如果您进行大量顺序扫描，您可能需要增加该值。

默认值为131072（128K），可改为16773120（16M）

7) read_rnd_buffer_size

随机读缓冲区大小。当以任何顺序（例如，按排序顺序）读取行时，都会分配随机读取缓冲区。当执行排序查询时，MySQL会首先扫描缓冲区，以避免磁盘搜索，提高查询速度。如果需要对大量数据进行排序，可以适当增大该值。然而，MySQL 会为每个客户端分配这个缓冲区空间因此应尽量适当设置该值，以避免内存开销过大。

一般可以设置为16M

8) sort_buffer_size

每个需要排序的线程都分配一个这个大小的缓冲区。增加此值可加快 ORDER BY 或 GROUP BY 操作的速度。

默认值为2097144（2M），可修改为16777208（16M）。

9) join_buffer_size

可用于联合查询操作的缓冲区大小

record_buffer_size、read_rnd_buffer_size、sort_buffer_size 、join_buffer_size是每个线程独占的，也就是说，如果有100个线程连接，则占用16M*100

10) table_cache

的大小表缓存。每当MySQL访问一个表时，如果表缓冲区中有空间，就会打开该表并将其放入其中，从而可以更快地访问表内容。 通过检查Open_tables和<的状态值/strong>Opened_tables高峰时，您可以决定是否需要增加table_cache的值。 如果发现open_tables等于table_cache，并且open_tables不断增长，那么就需要增加table_cache的值（上面的状态值可以使用SHOW STATUS LIKE ‘Open%tables’获取）。注意table_cache不能盲目设置太大的值。如果设置过高，可能会导致文件描述符不足，导致性能不稳定或连接失败。

对于1G​​内存的机器，建议值为128-256。内存中对于4GB左右的服务器，该参数可以设置为256M或384M。

11) max_heap_table_size

用户可以创建的内存表（内存表）的大小。该值用于计算内存表的最大行大小。该变量支持动态更改，即设置@max_heap_table_size=#

该变量和tmp_table_size一起设置r 限制内部存储器表的大小。如果内部堆（堆栈）表的大小超过 tmp_table_size，MySQL 可以根据需要自动将内存堆表更改为基于硬盘的 MyISAM 表。

12) tmp_table_size

通过设置tmp_table_size选项来增加临时表的大小，例如高级GROUP BY操作生成的临时表。如果增大这个值，MySQL会同时增大堆表的大小，这样可以达到提高join查询速度的效果。 建议尽可能优化查询，保证查询过程中生成的临时表在内存中，避免临时表过大。结果生成基于硬盘的 MyISAM 表。

mysql> 显示全局状态，如“created_tmp%”；

+———————————–+————+

|变量名：|值：|

+————————————-+————+

|创建_tmp_disk_tables | 21197 |

| | 58、

| Created_tmp_tables：| 1771587 |

+————————————–+————–+

每次创建临时表，Created_tmp_tables都会增加。如果临时表的大小超过tmp_table_size，则会在磁盘上创建临时表。 Created_tmp_disk_tables 也增加。 Created_tmp_files表示MySQL服务创建的临时文件的数量。理想的配置是：

Created_tmp_disk_tables/Createn_tmp_tables * 100% <= 25%。比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% = 1.20%，应该还不错

默认是16M，调整到64-256最优，线程独占，也有可能没有足够的内存且I/O被阻塞

13) thread_cache_size

其中保存的可以重用的线程数量。如果有，则从缓存中取出新线程，如果断开连接时有空间，则将客户端的线程放入缓存中。如果有很多ne对于线程，该变量值可用于提高性能。

通过对比Connections和Threads_created状态下的变量，可以看出这个变量的作用。

默认值为110，可调整为80。

14）thread_concurrency

建议设置为CPU核心数的2倍服务器的。例如，对于双核CPU，thread_concurrency的值应为4；对于2个双核CPU，thread_concurrency的值应为8。默认为8

15) wait_timeout

指定请求的最大连接时间。对于内存4GB左右的服务器，可以设置为5-10。

3. 配置InnoDB的几个变量

innodb_buffer_pool_size

对于InnoDB表来说，innodb_buffer_pool_size的作用相当于key_buffer_size 对于 MyISAM 表的作用。 InnoDB使用该参数指定的内存大小来缓冲数据和索引。为了对于独立的 MySQL 数据库服务器，该值最大可以设置为物理内存的 80%。

根据MySQL手册，对于2G内存的机器，建议值为1G（50%）。

innodb_flush_log_at_trx_commit

主要控制innodb将日志缓冲区中的数据写入日志文件并刷新磁盘的时间点。值分别为0和1。 , 2 三。 0，表示该方提交事务时，不进行日志写入操作，而是每秒将日志缓冲区中的数据写入日志文件并刷新一次磁盘； 1、那么每秒或者每次事务提交都会引起日志文件写入和刷新磁盘的操作，保证事务的ACID；设置为2，每次事务提交都会引起写入日志文件的动作，但每秒完成一次刷新磁盘操作。

实际测试发现这个值对o影响很大n 插入数据的速度。当设置为2时，插入10000条记录只需要2秒。当设置为0时，只需要1秒。当设置为1时，只需要1秒。需要 229 秒。因此，MySQL手册也建议将插入操作尽可能合并到一个事务中，这样可以大大提高速度。

根据MySQL手册，该值可以设置为0或2，同时允许丢失一些最近事务的风险。

innodb_log_buffer_size

日志缓存大小一般为1-8M，默认为1M。对于较大的事务，可以增加缓存大小。

可设置为4M或8M。

innodb_additional_mem_pool_size

该参数指定InnoDB用于存储数据字典和其他内部数据结构的内存池大小。默认值为 1M。一般不需要太大，够用即可。应该和compl有关表结构的存在性。如果不够，MySQL会在错误日志中写入一条警告消息。

根据MySQL手册，对于2G内存的机器，建议值为20M，可以适当增加。

innodb_thread_concurrency=8

建议设置为2*(NumCPUs+NumDisks)，默认一般为8

对比MySQL 5.6 性能相比之前的 GA 版本有了显着提升，但是默认的缓存设置对于小型站点来说不太合理。通过修改my.ini文件中的performance_schema_max_table_instances参数，可以有效降低内存占用。

以下是5.6的默认设置

performance_schema_max_table_instances 12500

table_definition_cache 1400

table_open_cache 2000

可以调整为 , 或较小的点。

pperformance_schema_max_table_instances=600

table_definition_cache=400

table_open_cache=256

performance_schema_max_table_instances

检测的表对象的最大数量检测到的检测的表对象的最大数量最大数量。

table_definition_cache
可以存储在定义缓存中的表定义（来自 .frm 文件）的数量。如果使用大量表，可以创建较大的表定义缓存以加快表的打开速度。与普通表缓存不同，表定义缓存占用的空间更少，并且不使用文件描述符。最小值和默认值都是400。
缓存frm文件