Mysql优化之参数调优

安的木偶游戏

已于 2024-04-30 15:53:51 修改

阅读量1.7k

点赞数 18

分类专栏： Mysql 文章标签： mysql 参数优化优化性能优化 mysql性能优化

于 2024-04-30 15:49:29 首次发布

本文链接：https://blog.csdn.net/qq42004/article/details/138348158

版权

Mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

前言

MySQL参数优化是针对数据库配置参数的调整和优化，以提高数据库系统的性能、稳定性和可用性。它和Mysql的应用环境例如项目的用户量在线情况、访问情况、存储资源量等以及服务硬件配置都有关系，优化也不可能一次性完成，需要不断的观察以及调试，才有可能得到最佳效果。它的优化主要是连接请求的变量和缓冲区变量。

连接请求的参数

Mysql连接请求的参数是指客户端连接到 MySQL 服务器时可以进行配置和调整的参数，这些参数可以影响连接的建立、使用和终止等方面的行为。

我这里是一台16核心4G的AnolisOS8.4的虚拟机，编译安装mysql:8.0.32

max_connections

Mysql中一个重要的参数，用于限制MySQL服务器同时允许的最大连接数。这个参数决定了Mysql服务器能够处理的并发连接数量。如果你服务器并发连接请求量比较大就调高此值，但是你的服务器要有相应的配置，因为Mysql会为每个连接提供连接缓冲区，就会开销越多的内存。show status like ‘connections’; 通配符查看当前状态的连接数量不管是否连接成功的连接数

查看一下服务和数据库状态：

mysql> show variables like 'max_connections'; 最大连接数
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.01 sec)

mysql> show status like 'max_used_connections'; 响应的连接数 
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 1     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'connections';  mysql的连接数不管是否连接成功的连接数
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 502   |
+---------------+-------+
1 row in set (0.00 sec)


[root@mysql ~]# free -h
              total        used        free      shared  buff/cache   available
Mem:          3.6Gi       1.2Gi       2.0Gi        16Mi       385Mi       2.1Gi
Swap:         2.0Gi          0B       2.0Gi

对mysql做500并发读写：

sysbench --threads=500 --time=120 \
         --mysql-host=192.168.0.103 --mysql-port=3306 \
         --mysql-user=root --mysql-password='123456' \
         /usr/local/sysbench/share/sysbench/oltp_read_write.lua \
         --tables=2 \
         --table_size=100000 \
         run

查看一下服务和数据库状态：

[root@mysql ~]# free -h  
              total        used        free      shared  buff/cache   available
Mem:          3.6Gi       1.9Gi       1.0Gi        16Mi       722Mi       1.5Gi
Swap:         2.0Gi          0B       2.0Gi

mysql> show status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 501   |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 4244  |
+---------------+-------+
1 row in set (0.00 sec)

max_connections的连接数设置理想值：max_used_connections/max_connections*100%≈85%。如果max_used_connections跟max_connections相同那么就是max_connections设置过低或者连接超过服务器负载上限了，如果低于10%则设置过大。压测和实际应用场景不一样，理解就可以了。在my.cnf的[mysqld]下修改最大连接数：

max_connections = 600

重启mysql服务

查看一下测试报告：

SQL statistics:
    queries performed:
        read:                            581742  总select数量
        write:                           166212  写入操作的查询数量，UPDATE、INSERT、DELETE 等写操作的
        other:                           83106
        total:                           831060
    transactions:                        41553  (342.26 per sec.)   总事务数(每秒事务数TPS)
    queries:                             831060 (6845.13 per sec.)  执行的查询数量（每秒执行量QPS）
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:                                       吞吐量
    events/s (eps):                      342.2565  每秒钟完成的事件数，
    time elapsed:                        121.4089s  经历时间
    total number of events:              41553      总的事务数

Latency (ms):
         min:                                  302.74  表示所有查询中最快的响应时间。
         avg:                                 1450.39  表示所有查询的平均响应时间。
         max:                                 8058.74  表示所有查询中最慢的响应时间。
         95th percentile:                     2778.39  表示 95% 的查询响应时间都在该值以下。
         sum:                             60267951.36  表示所有查询的累计响应时间。

Threads fairness:
    events (avg/stddev):           83.1060/3.57  表示平均每个线程处理的事件数为83.1060 标准差为3.57。标准差越小，表示线程间事件处理的分布越集中，公平性越好。
    execution time (avg/stddev):   120.5359/0.36  表示每个线程的平均执行时间约为 120.5359 秒 ，表示执行时间的标准差，用于衡量执行时间的稳定性。较小的标准差表示执行时间变化较小。

back_log

back_log是Mysql中的一个参数，用于设置连接请求队列长度。当Mysql服务器同时收到多个连接请求时，如果当前连接数已经达到了max_connections设置的最大连接数限制，而此时又有新的连接请求到来，Mysql就会将这些连接请求放入连接请求队列中等待处理。它就指定了服务器处理请求连接时排队等待的数目。如果连接请求队列已满，新的连接请求到来，Mysql就会拒绝这些新的连接请求，并返回相应的错误信息给客户端。它帮助Mysql服务器处理并发连接请求时，防止因为瞬间并发请求过多而导致新的连接被拒绝或连接延迟增加的情况发生。建议将back_log参数设置为max_connections参数的两倍或三倍。

查看一下服务器的进程列表：

mysql> show full processlist;
+-----+-----------------+---------------------+--------+---------+------+----------------------------+--------------------------------------------------------+
| Id  | User            | Host                | db     | Command | Time | State                      | Info                                                   |
+-----+-----------------+---------------------+--------+---------+------+----------------------------+--------------------------------------------------------+
|   5 | event_scheduler | localhost           | NULL   | Daemon  |    0 | waiting for handler commit | NULL                                                   |
|  89 | root            | localhost           | NULL   | Query   |    0 | init                       | show full processlist                                  |
| 139 | root            | 192.168.0.108:32938 | sbtest | Sleep   |    0 |                            | NULL                                                   |
| 140 | root            | 192.168.0.108:32940 | sbtest | Execute |    0 | waiting for handler commit | COMMIT                                                 |
| 141 | root            | 192.168.0.108:32942 | sbtest | Sleep   |    0 |                            | NULL                                                   |
| 142 | root            | 192.168.0.108:32944 | sbtest | Sleep   |    0 |                            | NULL                                                   |
| 143 | root            | 192.168.0.108:32948 | sbtest | Sleep   |    0 |                            | NULL                                                   |
| 144 | root            | 192.168.0.108:32946 | sbtest | Sleep   |    0 |                            | NULL                                                   |
.............

如果发现"unauthenticated user"的待连接进程，并且在服务器的日志中没有明显的错误信息。就要加大back_log 的值了或加大max_connections的值。但是back_log的调整增加了请求队列的长度导致连接请求在队列中等待的时间变长，可能会导致客户端连接请求的响应时间变长，从而影响到查询的执行时间。

查看back_log设置:

mysql> show variables like 'back_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log      | 600   |
+---------------+-------+
1 row in set (0.01 sec)

修改my.cnf文件，在[mysqld]添加：

back_log = 1200

mysql> show variables like 'back_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log      | 1200  |
+---------------+-------+
1 row in set (0.00 sec)

执行一次并发600的测试：

SQL statistics:
    queries performed:
        read:                            618744 
        write:                           176784
        other:                           88392
        total:                           883920
    transactions:                        44196  (362.89 per sec.) 总事务数(每秒事务数)，处理能力稍微提升
    queries:                             883920 (7257.82 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      362.8910
    time elapsed:                        121.7887s
    total number of events:              44196

Latency (ms):
         min:                                  497.47  
         avg:                                 1502.24  表示所有查询的平均响应时间。
         max:                                 7658.21
         95th percentile:                     2493.86
         sum:                             66393088.43

Threads fairness:
    events (avg/stddev):           80.3564/2.59  
    execution time (avg/stddev):   120.7147/0.42

wait_timeout和interactive_timeout

wait_timeout：用于控制非交互式客户端连接的超时时间，关闭一个非交互的连接之前所要等待的秒数。默认值为28800秒。这个值如果设置太小，连接关闭的很快，从而使一些持久的连接不起作用。如果设置太大，容易造成连接打开时间过长，在show processlist时会看到很多sleep状态的连接从而造成too many connections。一般希望wait_timeout尽可能地低。
interactive_timeout：用于控制交互式客户端连接的超时时间，关闭一个交互的连接之前所要等待的秒数。默认值为28800秒。比如我们在终端上进入mysql管理，使用的是交互的连接，如果没有操作时间超过了interactive_time设置的时间就会自动断开。

查看一下初始值：

mysql> show variables like 'interactive_timeout';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| interactive_timeout        | 28800 |
| mysqlx_interactive_timeout | 28800 |
+----------------------------+-------+
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

在/etc/my.cnf的[mysqld]下修改：

wait_timeout=100 
interactive_timeout=100


mysql> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 100   |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 100   |
+---------------+-------+
1 row in set (0.00 sec)

执行并发600的测试分别设置wait_timeout=100、200、300、500：

wait_timeout=100的第一次测试结果：

SQL statistics:
    queries performed:
        read:                            763700
        write:                           218200
        other:                           109100
        total:                           1091000
    transactions:                        54550  (450.81 per sec.)  

    queries:                             1091000 (9016.19 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      450.8097
    time elapsed:                        121.0045s
    total number of events:              54550

Latency (ms):
         min:                                  127.76
         avg:                                 1324.17 表示所有查询的平均响应时间。
         max:                                12507.84
         95th percentile:                     2082.91
         sum:                             72233660.88

Threads fairness:
    events (avg/stddev):           90.9167/4.81
    execution time (avg/stddev):   120.3894/0.24

缓冲区变量

缓冲区变量是指Mysql中用于控制和优化数据库性能的一组参数，它们用于调整和管理数据库系统中的内存缓冲区大小，从而提高数据库的性能和响应速度。这些变量包括了用于不同存储引擎和不同功能的缓冲区。通过调整这些缓冲区变量的大小，可以根据数据库系统的实际情况和需求来优化数据库性能，提高查询速度和响应性能。

全局缓冲

key_buffer_size参数指定索引缓冲区的大小针对是Mylsam引擎，它决定索引处理的速度，尤其是索引读的速度。但是如果的你的数据库主要使用的是InnoDB存储引擎，但在某些情况下Mysql也会使用Mylsam引擎来创建临时表，这个值也需要按需配置。当然你也可以使用"default_tmp_storage_engine=引擎名;" 在mysql配置文件中固定临时表的引擎。

show status like 'key_read%';

检查状态值Key_read_requests和Key_reads，判断key_buffer_size设置是否合理。key_reads/key_read_requests应该尽可能的低，至少是1:100，1:1000更好。

mysql> show status like 'key_read%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Key_read_requests | 586154 | 自服务器启动以来，所有索引读取的请求数量。
| Key_reads         | 6      | 自服务器启动以来，从磁盘读取索引块的次数。
+-------------------+--------+
2 rows in set (0.00 sec)

有586154个请求其中只有6个是从硬盘读取的。索引未命中缓存的概率计算key_cache_miss_rate=Key_reads/Key_read_requests*100% 。

mysql> SHOW STATUS LIKE 'created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
+-------------------------+-------+
1 row in set (0.00 sec)

测试的时候没有创建临时表，全部在内存中。修改key_buffer_size参数在my.cnf的[mysqld]下例如：

key_buffer_size = 512M

此处不再修改。

innodb_buffer_pool_size是Innodb的全局缓冲池存储表和索引的数据，最大可以把该值设置成物理内存的80%。查看一下：

mysql> show status like 'innodb_buffer_pool_read%';
+---------------------------------------+-----------+
| Variable_name                         | Value     |
+---------------------------------------+-----------+
| Innodb_buffer_pool_read_ahead_rnd     | 0         |
| Innodb_buffer_pool_read_ahead         | 0         |
| Innodb_buffer_pool_read_ahead_evicted | 0         |
| Innodb_buffer_pool_read_requests      | 501744653 | 从InnoDB缓冲池读取的请求数。
| Innodb_buffer_pool_reads              | 4603      | 从磁盘读取到InnoDB缓冲池的页数。
+---------------------------------------+-----------+
5 rows in set (0.01 sec)

命中率=(Innodb_buffer_pool_read_requests-Innodb_buffer_pool_read_requests)/Innodb_buffer_pool_read_requests×100%。修改参数my.cnf文件中[mysqld]：

innodb_buffer_pool_size=2048M

查询缓存

InnoDB引擎的设计不包含查询缓存,在Mylsam引擎查询缓存的大小由参数query_cache_size控制。它定义了Mysql服务器用于存储查询缓存的内存大小。较大的query_cache_size值可以提高查询缓存的效率，但也会增加内存使用量。通常，如果查询缓存命中率低，可以尝试增加query_cache_size的值来提高性能。但在某些情况下，关闭查询缓存可能会更好，特别是在高并发环境中。

SELECT语句（区分大小写），将直接从缓冲区中读取结果。两个SQL语句，只要相差哪怕是一个字符（例如大小写不一样；多一个空格等），那么这两个SQL将使用不同的一个缓存。通过检查状态值’Qcache%'可以知道query_cache_size设置是否合理。

Qcache_free_blocks：这是缓存中相邻内存块的数量。较大的值表示缓存中存在许多碎片。执行flush query cache会对缓存中的碎片进行整理，获得更多的空闲块。可以用flush query cache清空free blocks。
Qcache_free_memory：Query Cache中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多了。
Qcache_hits：表示命中缓存的次数。可以通过该值来验证我们的查询缓存的效果。较高的值表明缓存效果良好命中率高。
Qcache_inserts：表示多少次未命中然后插入，这意味着新来的SQL请求在缓存中未找到，执行查询处理后将结果放入查询缓存中。值越高表示查询缓存应用的越少,效果也就不理想。
Qcache_lowmem_prunes：表示因为内存不足而被清除出查询缓存的查询数量。通过"Qcache_lowmem_prunes"和"Qcache_free_memory"相互结合，能够更清楚的了解到我们系统中Query Cache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重或者内存很少。
Qcache_not_cached：表示不适合进行缓存的查询的数量。这通常是因为这些查询不是 SELECT 语句，或者包含了不适合缓存的内容，比如包含了动态生成的内容或者不稳定的函数如：NOW()。
Qcache_queries_in_cache：表示当前查询缓存中缓存的查询数量。
Qcache_total_blocks：当前Query Cache中的block数量。

由于query_cache_size在mysql8.0中已经被废弃,但是有些项目可能还在用mysql5.7。使用mysql5.7看一下。

mysql> show variables like '%query_cache%';
	+------------------------------+----------+
	| Variable_name                | Value    |
	+------------------------------+----------+
	| have_query_cache             | YES      |
	| query_cache_limit            | 1048576  | 超过此大小的查询将不缓存
	| query_cache_min_res_unit     | 4096     | 缓存块的最小大小，默认4KB设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费
	| query_cache_size             | 33554432 | 查询缓存大小。最小单位1024byte，按倍数设置
	| query_cache_type             | OFF      | 缓存类型，决定缓存什么样的查询
	| query_cache_wlock_invalidate | OFF      | 表示是否在写锁定表时使缓存失效。
	+------------------------------+----------+ 
	6 rows in set (0.01 sec)


mysql> SHOW STATUS LIKE 'Qcache%';
	+-------------------------+----------+
	| Variable_name           | Value    |
	+-------------------------+----------+
	| Qcache_free_blocks      | 1        |
	| Qcache_free_memory      | 33537320 |
	| Qcache_hits             | 0        |
	| Qcache_inserts          | 0        |
	| Qcache_lowmem_prunes    | 0        |
	| Qcache_not_cached       | 6        |
	| Qcache_queries_in_cache | 0        |
	| Qcache_total_blocks     | 1        |
	+-------------------------+----------+
	8 rows in set (0.00 sec)

query_cache_type参数0(OFF)：关闭,没启用缓存虽然have_query_cache是YES；1(ON)缓存所有结果，除非select语句使用SQL_NO_CACHE禁用查询缓存；2(DEMAND)：只缓存select语句中通过SQL_CACHE指定需要缓存的查询。

修改my.cnf配置文件添加配置重启服务器：

query_cache_size=256M 
query_cache_type=1


mysql> show variables like '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 268435456 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.00 sec)

查询缓存碎片率：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_hits +Qcache_inserts)*100%

Query Cache的限制：所有子查询中的外部查询SQL不能被Cache，在Procedure，Function以及Trigger中的Query不能被Cache；包含诸如 NOW()、RAND() 等每次执行可能得到不同结果的函数的查询语句也不能被缓存。

其他优化

max_connect_errors

max_connect_errors：是一个Mysql中与安全有关的计数器值。这个参数表示允许的连接错误次数。如果某个客户端尝试连接的次数超过这个值Mysql将禁止其连接。这有助于防止恶意连接或连接攻击。直到Mysql服务器重启或通过flush hosts命令清空此host的相关信息。max_connect_errors的值与性能并无太大关系。修改/etc/my.cnf文件，在[mysqld]下面添加如下内容 max_connect_errors=20 重启后show variables like 'max_connect_errors’查看。

mysql> show variables like '%connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
+--------------------+-------+
1 row in set (0.01 sec)

优化链接数my.cnf文件修改[mysqld]：

max_connect_errors = 20

重启生效：

mysql> show variables like '%connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 20    |
+--------------------+-------+
1 row in set (0.00 sec)

sort_buffer_size

sort_buffer_size：给需要进行排序的Mysql分配该大小的一个内存每个连接独享。增加这值加速ORDER BY或GROUP BY操作。较大的值可以提高排序性能，但也会增加内存占用。 Sort_Buffer_Size是一个connection级参数(连接级参数是指对于每个连接到Mysql服务器的客户端连接可以设置的参数),在每个connection（session）第一次需要使用这个buffer的时候一次性分配设置的内存。 Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。假如有1000个连接将会消耗1000*sort_buffer_size(2M)≈2G内存。

mysql> show variables like 'sort_buffer_size';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| sort_buffer_size | 8388608 |
+------------------+---------+
1 row in set (0.00 sec)

修改my.cnf文件在[mysqld]:

sort_buffer_size = 10M

重启生效：

mysql> show variables like 'sort_buffer_size';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| sort_buffer_size | 10485760 |
+------------------+----------+
1 row in set (0.00 sec)

max_allowed_packet

Mysql根据配置会限制Mysql服务接受的单个语句或查询的数据包大小。有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败。官方建议最大值是1GB，必须设置1024的倍数。

mysql> show variables like 'max_allowed%';
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
1 row in set (0.01 sec)

在my.cnf文件的[mysqld]下设置此处设置的是1G：

max_allowed_packet = 1G

join_buffer_size

用于表间关联缓存的大小，和sort_buffer_size一样该参数对应的分配内存也是每个连接独享。较大的值可能会导致内存占用增加，但可以提高连接性能。默认值是256k。

mysql> show variables like 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

在my.cnf文件的[mysqld]下设置此处是默认设置：

join_buffer_size = 256K

thread_cache_size

服务器线程缓存即允许在内存中缓存的线程数量,增加这个值可以减少创建新线程的开销,特别是在有高并发连接请求的情况下可以提高系统的响应速度。这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求那么请求将从缓存中读取。如果缓存中是空的或者新的请求那么这个线程将被重新创建，如果有很多新的线程增加这个值可以改善系统性能。通过比较Connections和Threads_created状态的变量,可以看到这个变量的作用。

设置规则：确定每个线程的内存消耗,如果每个线程大约需要1MB的内存,你有4GB的可用内存那么你大约可以配置4000个线程。但是这个值可以能受服务器环境和访问情况不同，所有要在设置前测试评估一下。

mysql> show global status like 'Threads_created';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_created | 1     |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 6000  |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 1024  |
+-------------------+-------+
1 row in set (0.01 sec)

在my.cnf文件的[mysqld]下设置此处是1G：

thread_cache_size = 1024

Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。如果线程池中的线程数量超过这个值,多余的线程将会被销毁。
Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created :代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。

查看一下：

mysql> show global status like 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 600   |
| Threads_connected | 1     |
| Threads_created   | 601   |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.00 sec)

InnoDB的几个变量

innodb_flush_log_at_trx_commit

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点取值分别为0、1、2三个。

0：表示当事务提交时，做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次。
1：则在每秒钟或是每次事物的提交都会引起日志文件写入flush磁盘的操作，确保了事务的ACID。
2：每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作。

该值对插入数据的速度影响非常大。因此Mysql手册也建议尽量将插入操作合并成一个事务这样可以大幅提高速度，注重性能允许丢失最近部分事务的危险的前提下可以把该值设为0或2，如果对数据的持久性要求很高可以选择设置为1。

innodb_thread_concurrency

innodb_thread_concurrency：此参数用来设置innodb线程的并发数量，默认值为0表示不限制，若要设置则与服务器的CPU核数相同或是cpu的核数的2倍，建议用默认设置。

innodb_log_buffer_size

innodb_log_buffer_size：此参数确定缓存日志文件所用的内存大小以M为单位。缓冲区更大能提高性能。增大该值可以提高写入性能，但也会增加内存的消耗。建议将innodb_log_buffer_size 设置为较小的值，以便控制内存使用量。

innodb_log_file_size

innodb_log_file_size ：此参数确定数据日志文件的大小以M为单位，增大该值可以减少事务日志文件的切换频率提高写入性能。

innodb_log_files_in_group

innodb_log_files_in_group：提高并发写入性能。Mysql可以以循环方式将日志文件写到多个文件。推荐设置为3。

read_buffer_size

read_buffer_size：Mysql为每个客户端连接读取缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和sort_buffer_size一样该参数对应的分配内存也是每个连接独享。

read_rnd_buffer_size

read_rnd_buffer_size： Mysql客户端连接随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时Mysql会首先扫描一遍该缓冲以避免磁盘搜索提高查询速度。如果需要排序大量数据可适当调高该值。但Mysql会为每个客户连接发放该缓冲空间所以应尽量适当设置该值，以避免内存开销过大。

bulk_insert_buffer_size

bulk_insert_buffer_size：批量插入数据缓存大小，可以有效提高插入效率默认为8M。

binary log

log-bin=/usr/local/mysql/data/mysql-bin：启用二进制日志，用于记录数据库中的更改操作，以实现数据备份和复制。
binlog_cache_size：为每个session分配的内存，在事务过程中用来存储二进制日志的缓存,提高记录bin-log的效率。没有什么大事务，数据库操作（数据库中执行数据的增加（Insert）、修改（Update）、删除（Delete）等操作）也不是很频繁的情况下可以设置小一点。如果事务大而且多，数据库操作也频繁则可以适当的调大一点。前者建议是1M，后者建议是2–4M 。
max_binlog_cache_size：表示的是binlog 能够使用的最大cache内存大小。
max_binlog_size：指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin否则硬盘扛不住而且建议定期做删除
expire_logs_days：定义了mysql清除过期日志的时间。二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。

log_queries_not_using_indexes

log_queries_not_using_indexes：开启这个选项真实地记录了返回所有行的查询。记录未使用索引的查询的日志功能，便于识别潜在的性能问题。

安的木偶游戏

关注

18
点赞
踩
10

收藏

觉得还不错? 一键收藏
1
评论
Mysql优化之参数调优

MySQL参数优化是针对数据库配置参数的调整和优化，以提高数据库系统的性能、稳定性和可用性。它和Mysql的应用环境例如项目的用户量在线情况、访问情况、存储资源量等以及服务硬件配置都有关系，优化也不可能一次性完成，需要不断的观察以及调试，才有可能得到最佳效果。它的优化主要是连接请求的变量和缓冲区变量。
复制链接

扫一扫