Mysq5.7参数配置优化与压力测试结果集

Mysq5.7参数配置优化与压力测试结果集

**本文中Mysql参数的解析均来自于ChatGPT**

环境描述：游戏云服务器、8核32G、CentOS7镜像

调整到的参数：

innodb_buffer_pool_size
innodb_flush_method
join_buffer_size
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
performance_schema
performance_schema_max_memory_classes
performance_schema_max_file_instances

压力测试使用到的命令行工具

sysbench

一、各个字段的ChatGPT解析

1.1、innodb_buffer_pool_size

innodb_buffer_pool_size

是 InnoDB 存储引擎中非常重要的一个参数，它用于设置 InnoDB 缓冲池的大小。InnoDB 缓冲池是 InnoDB 存储引擎用来缓存表数据和索引数据的内存区域，可以减少磁盘 I/O，提高查询性能。

在 MySQL 5.7 版本中，

innodb_buffer_pool_size

参数的默认值为 128MB。建议根据服务器内存大小来设置该参数的值，通常建议将其设置为物理内存的 50% ~ 80%。

需要注意的是，如果设置的值过大，可能会导致系统出现内存压力，影响整体性能。如果设置的值过小，缓冲池无法缓存足够的数据，会增加磁盘 I/O，降低查询性能。

当系统内存有限时，可以考虑将

innodb_buffer_pool_size

设置为内存的 50% 或更少，避免使用过多的内存导致系统出现内存不足问题。在具体设置值的过程中，需要进行实际的测试和调整，以达到最优性能。

可以通过以下 SQL 语句查询当前

innodb_buffer_pool_size

参数的设置值：

SHOW VARIABLES LIKE'innodb_buffer_pool_size';

可以通过以下 SQL 语句设置 innodb_buffer_pool_size 参数的值：

SETGLOBAL innodb_buffer_pool_size =<size>;

其中，**<size>** 表示需要设置的缓冲池大小，可以使用 KB、MB、GB 等单位。通过命令来设置可以立即生效，但重启后会失效。需要永久生效可以在配置文件中添加一行

innodb_buffer_pool_size=<size>

1.2、innodb_flush_method

innodb_flush_method

是MySQL InnoDB存储引擎用于刷新脏页的方法。具体来说，当数据被写入到InnoDB缓冲池中时，这些数据被称为脏页，因为它们还没有被写入磁盘。为了保持数据一致性，InnoDB需要定期将这些脏页刷新到磁盘上的数据文件中。

innodb_flush_method

参数决定了刷新脏页的方法，可以设置为以下选项之一：

• fsync：使用标准的fsync()系统调用刷新脏页。这是默认设置，适用于大多数系统。
• O_DSYNC：使用O_DSYNC标志打开数据文件，以确保脏页被刷新到磁盘。这个选项在某些系统上可以提高性能，但在其他系统上可能会导致性能下降。
• O_DIRECT：使用O_DIRECT标志打开数据文件，以将数据直接写入磁盘而不经过操作系统缓存。这个选项可以提高性能，但在某些系统上可能会受到一些限制。
• async_unbuffered：使用异步I/O来写入脏页，并将数据写入文件时不使用操作系统缓存。这个选项可以提高性能，但是在某些系统上可能会有稳定性问题。

需要注意的是，选择正确的 innodb_flush_method 对于性能和数据一致性都非常重要。如果使用了不适合系统的选项，可能会导致性能下降、数据损坏或其他问题。建议在使用新的选项之前，先在测试环境中进行测试。

1.3、join_buffer_size

join_buffer_size

是 MySQL 中用于执行连接操作时分配的内存缓冲区的大小。当需要对大表进行连接操作时，MySQL 需要分配额外的内存缓存区，将数据加载到缓存区中进行连接操作，这样可以避免频繁的 I/O 操作，从而提高连接操作的性能。

默认情况下，

join_buffer_size

的值为 262144 bytes (256 KB)，可以通过修改该参数来调整连接操作的性能。如果你的连接操作比较频繁，或者需要连接的表比较大，可以适当增大

join_buffer_size

的值，从而提高连接操作的性能。

需要注意的是，

join_buffer_size

的值过大会占用过多的内存资源，因此需要根据实际情况来进行调整。另外，如果在执行连接操作时，MySQL 发现没有足够的内存可以用于分配

join_buffer_size

的内存缓冲区，会导致连接操作失败，这时需要调整 join_buffer_size 的值或增加系统内存等措施来解决该问题。

1.4、sort_buffer_size

sort_buffer_size

是MySQL中用于排序操作的缓冲区大小。当排序数据时，MySQL会将数据读入这个缓冲区，然后在内存中执行排序操作。如果数据量很大，但是

sort_buffer_size

太小，MySQL可能会在磁盘上进行排序操作，从而降低查询性能。因此，调整sort_buffer_size的大小可以对查询性能产生影响。

默认情况下，

sort_buffer_size

的值为262144字节（256KB）。可以通过

SET

语句或在

my.cnf

配置文件中设置该值。但是，需要注意的是，sort_buffer_size的值不能超过可用内存的大小。

值得注意的是，如果要对大型数据集进行排序，则

sort_buffer_size

的大小可能需要设置为非常大的值，甚至需要使用磁盘临时文件进行排序。在这种情况下，可以通过调整

max_length_for_sort_data

参数来控制可以在内存中排序的数据大小，以及通过增加

tmpdir

配置参数来增加磁盘临时文件的容量。

1.5、read_buffer_size

read_buffer_size

是一个用于控制单个连接（session）读取数据时的缓冲区大小的参数。它指定了客户端读取数据时使用的缓冲区大小，它的默认值是 131072 （即 128 KB）。

在单次查询中，当 MySQL 从磁盘中读取大量数据时，通过增加

read_buffer_size

的值来提高查询的效率。这是因为读取数据时使用缓冲区可以减少 I/O 操作的次数。在大多数情况下，增加

read_buffer_size

的值可以提高查询的性能。但是，如果设置的值过大，会占用过多的内存，导致系统出现内存压力，因此需要根据实际情况进行调整。

需要注意的是，

read_buffer_size

只适用于读取数据时的缓冲区，而不适用于写入数据时的缓冲区。写入数据时的缓冲区大小由

innodb_write_io_threads

和

innodb_write_io_threads

参数控制。

1.6、read_rnd_buffer_size

read_rnd_buffer_size

是 MySQL 中一个用来控制随机读取操作所使用缓冲区大小的参数。它指定了 MySQL 在执行随机读取操作（例如排序、GROUP BY 或 DISTINCT）时所使用的缓冲区的大小。

当 MySQL 需要进行随机读取操作时，会使用

read_rnd_buffer_size

参数所指定大小的缓冲区来存储读取到的数据，以减少硬盘的 I/O 操作。如果该参数设置得太小，MySQL 将不得不多次从磁盘中读取数据，导致性能下降。

通常情况下，建议将

read_rnd_buffer_size

设置为

sort_buffer_size

的一半，因为它们都是用来处理随机访问的缓冲区。在大多数情况下，设置为 256K 就足够了。如果查询中包含了大量的 GROUP BY、DISTINCT 或 ORDER BY 等操作，那么可以适当增加该参数的大小，以提高性能。

1.7、performance_schema

performance_schema

是 MySQL 数据库提供的一种用于性能监控的机制，其目的是为了方便开发人员和 DBA（数据库管理员）在 MySQL 中进行性能分析和调优。通过收集 MySQL 服务器的性能数据，分析这些数据，开发人员和 DBA 可以更好地了解服务器的行为，以便诊断性能问题并改进服务器的性能。

performance_schema

是 MySQL 5.5 及以上版本提供的一个插件，通过收集和记录 MySQL 服务器的状态信息，提供了一组视图和表，用于查看数据库的性能指标。这些视图和表可以用于收集各种性能数据，包括 CPU 使用率、I/O 操作、锁和等待事件、执行计划、SQL 语句等等。

performance_schema 主要包含以下几个方面的内容：

• 状态变量和系统变量： 提供了有关 MySQL 服务器状态和配置的信息。
• 事件计数器： 包含关于各种事件发生的计数器。
• 事件等待和锁定信息： 包含有关等待事件和锁定的详细信息。
• 线程和进程状态： 提供了有关 MySQL 线程和进程状态的信息。
• 用户与帐户： 提供了有关用户和帐户的信息。
• 语句分析： 提供了有关语句执行时间、执行计划和锁定的信息。

通过使用

performance_schema

，开发人员和 DBA 可以监控服务器性能，了解服务器上哪些操作是瓶颈，以便优化数据库的性能，提高数据库的响应速度。

1.8、performance_schema_max_memory_classes

performance_schema_max_memory_classes

是一个 MySQL 参数，用于设置在

performance_schema

中可用的最大内存类别数。

performance_schema

是一种用于监视 MySQL 数据库系统性能的工具，可以提供有关数据库运行状况的详细信息。

内存类别是用于存储性能架构监视器实例的内存池，其中包括各种表、统计信息和其他信息。如果您的 MySQL 实例正在处理大量活动连接并启用了

performance_schema

，那么可能需要增加

performance_schema_max_memory_classes

的值以容纳更多的实例。

默认情况下，MySQL 5.7 中的

performance_schema_max_memory_classes

参数设置为 300。该参数的最小值为 100，最大值为 10,000。适当的值取决于您的 MySQL 实例的特定需求和资源。如果您的

performance_schema

实例超出了

performance_schema_max_memory_classes 

的限制，您可能会看到错误消息，提示您增加该参数的值。

1.9、performance_schema_max_file_instances

performance_schema_max_file_instances

是一个 MySQL 配置参数，它用于控制 Performance Schema 文件实例的最大数量。

在 Performance Schema 中，每个事件和计数器都会产生一个文件实例。这些实例是在运行时动态创建的，并且需要占用一定的内存和文件句柄。为了防止 Performance Schema 运行时使用过多的内存和文件句柄，MySQL 提供了

performance_schema_max_file_instances

这个参数来限制 Performance Schema 文件实例的最大数量。如果超出了这个限制，MySQL 会自动删除一些旧的文件实例，以便腾出空间供新的实例使用。

需要注意的是，

performance_schema_max_file_instances

参数只控制 Performance Schema 文件实例的数量，而不控制 Performance Schema 内存的使用量。如果需要限制 Performance Schema 的内存使用量，可以使用

performance_schema_max_memory_classes

参数来控制 Performance Schema 可以使用的内存类别的数量。

二、查询了解本机的配置

2.1、查询缓存命中率

mysql>showstatuslike'innodb_buffer_pool_read%';+---------------------------------------+-------------+| Variable_name                         |Value|+---------------------------------------+-------------+| Innodb_buffer_pool_read_ahead_rnd     |0|| Innodb_buffer_pool_read_ahead         |2230922|| Innodb_buffer_pool_read_ahead_evicted |21401|| Innodb_buffer_pool_read_requests      |28175251399|| Innodb_buffer_pool_reads              |67660375|+---------------------------------------+-------------+

Innodb_buffer_pool_read_requests

表示从InnoDB缓冲池中读取数据页的请求数量。如果该值非常高，表示大多数请求都从内存中得到了满足，因此缓冲池的效率很高。

Innodb_buffer_pool_reads

表示InnoDB从磁盘读取数据页的次数。如果该值较高，表示缓冲池的大小不足以满足大多数请求，因此需要频繁地从磁盘读取数据。这会导致较长的等待时间和性能问题，因此需要增加缓冲池的大小来减少从磁盘读取数据页的次数。

可以通过以下的公式来判断
percent>=99%，则表示当前的buffer pool满足当前的需求。否则需要考虑增加 innodb_buffer_pool_size的值。
percent = innodb_buffer_pool_read_requests / (innodb_buffer_pool_reads + innodb_buffer_pool_read_requests) * 100%

2.2、缓存数据页占比

mysql>showstatuslike'innodb_buffer_pool_pages%';+----------------------------------+----------+| Variable_name                    |Value|+----------------------------------+----------+| Innodb_buffer_pool_pages_data    |257168|| Innodb_buffer_pool_pages_dirty   |0|| Innodb_buffer_pool_pages_flushed |20911515|| Innodb_buffer_pool_pages_free    |4102|| Innodb_buffer_pool_pages_misc    |858|| Innodb_buffer_pool_pages_total   |262128|+----------------------------------+----------+

Innodb_buffer_pool_pages_data

表示当前InnoDB缓冲池中已经被使用的页面数量

Innodb_buffer_pool_pages_total

表示InnoDB缓冲池中所有页面的总数。

它们可以用来计算当前InnoDB缓冲池的使用率，使用公式为
percent = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
percent 这个值越接近100%，表示InnoDB缓冲池的使用率越高，也就意味着缓冲池越充分，系统的性能表现可能更好。
小于95%的话则需要考虑增加 innodb_buffer_pool_size的值。

2.3、查询performance_schema使用的内存

mysql>select event_name, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_global_by_event_name orderby SUM_NUMBER_OF_BYTES_ALLOC desclimit10;+----------------------------------------------------------------------+---------------------------+| event_name                                                           | SUM_NUMBER_OF_BYTES_ALLOC |+----------------------------------------------------------------------+---------------------------+| memory/performance_schema/file_instances                             |5954600960|| memory/performance_schema/table_handles                              |161546240|| memory/performance_schema/table_io_waits_summary_by_index_usage      |54788096|| memory/performance_schema/table_shares                               |54525952|| memory/performance_schema/rwlock_instances                           |16384000|| memory/performance_schema/events_statements_history_long             |14320000|| memory/performance_schema/table_lock_waits_summary_by_table          |14090240|| memory/performance_schema/events_statements_history_long.tokens      |10240000|| memory/performance_schema/events_statements_summary_by_digest.tokens |10240000|| memory/performance_schema/events_statements_history_long.sqltext     |10240000|+----------------------------------------------------------------------+---------------------------+

可以看到我这里的

file_instances

已经占用了将近6G的内存，这是因为数据库与区服是在同一服务器上的，数据库保存游戏的日志信息会打开大量的磁盘文件，这就会使得

performance_schema/file_instances

表记录了大量的数据（当时看到有将近千万条数据）

这里如何查询到内存占用都不高的话也不需要调整关于performance_schema的三个参数

• performance_schema
• performance_schema_max_memory_classes
• performance_schema_max_file_instances

三、各参数调整后的压测数据

3.1、安装压测工具

sysbench 安装：等下载完成之后，在 /usr/share/sysbench/ 目录下会有一些软件自带等lua测试脚本文件，提供系统的压测脚本。

[root@huawei-sh1 ~]# yum install -y sysbench

3.1.1、测试CPU性能

#2个线程寻找20000以内的素数
sysbench --test=cpu --cpu-max-prime=20000 --num-threads=2 run

3.1.2、测试IO性能

sysbench --test=fileio --file-total-size=20G prepare
sysbench --test=fileio --file-total-size=20G --file-test-mode=rndrw run
sysbench --test=fileio --file-total-size=20G cleanup

3.2、mysql压测

准备：创建测试库 test_db，同时创建好对应的测试账号test_user，密码也是test_user，该用户有访问test_db的权限

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare

参数说明：

--db-driver=mysql                         基于mysql驱动连接mysql数据库，如oracle或sqlserver，那就是其他数据库驱动
--time=300                                连续访问300秒
--threads=10                            用10个线程模拟并发访问
--report-interval=1                        每隔1秒输出一下压测情况
--mysql-host=127.0.0.1                    测试的机器地址
--mysql-port=3306                        数据库的端口
--mysql-user=test_user                    连接的用户
--mysql-password=test_user                连接的密码
--mysql-db=test_db                        测试的库
--tables=20                                构建20个测试表
--table_size=1000000                    每个表100万行数据
oltp_read_write                            执行oltp数据库的读写测试
--db-ps-mode=disable                    禁止ps模式

测试数据库的综合读写TPS，用oltp_read_write模式（命令最后是run而非prepare，即运行压测）：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable run

测试数据库的只读性能，用oltp_read_only模式（命令中oltp_read_write已变为oltp_read_only）：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_only --db-ps-mode=disable run

测试数据库的删除性能，用oltp_delete模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_delete --db-ps-mode=disable run

测试数据库的更新索引字段的性能，用oltp_update_index模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_index --db-ps-mode=disable run

测试数据库的更新非索引字段的性能，用oltp_update_non_index模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_update_non_index --db-ps-mode=disable run

测试数据库的插入性能，用oltp_insert模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_insert --db-ps-mode=disable run

测试数据库的写入性能，用oltp_write_only模式：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_write_only --db-ps-mode=disable run

sysbench工具会根据你的指令构造出各种SQL语句去更新或查询20张测试表的数据，同时监测数据库的压测性能指标，最后完成压测后，可执行下面cleanup命令，清理数据。

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup

测试事务性能

sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --mysql-db=test --oltp-table-size=3000 --oltp-table-name=t1 --mysql-socket=/var/lib/mysql/mysql.sock prepare

测试事务实例

sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --mysql-db=test --oltp-table-size=3000 --oltp-table-name=t1 --mysql-socket=/var/lib/mysql/mysql.sock run

3.3、结果分析：按照上面命令，每隔1秒都会输出一次压测报告，此时他每隔一秒会输出类似下面的东西：

[ 60s ] thds: 10 tps: 1395.99 qps: 27919.66(r/w/o: 19543.99/5583.86/2791.35) lat (ms, 95%): 17.63 err/s: 0.00 reconn/s: 0.00

• thds: 10，这个意思就是有10个线程在压测
• tps: 1395.99 ，这个意思就是每秒执行了1395.99 个事务
• qps: 27919.66 ，这个意思就是每秒可以执行27919.66 个请求
• (r/w/o: 19543.99/5583.86/2791.35)，这个意思就是说，在每秒27919.66 个请求中，有19543.99个读请求，5583.86个写请求，2791.35个其他的请求，就是对QPS进行了拆解
• lat (ms, 95%): 17.63 ，这个意思就是说，95%的请求的延迟都在17.63 毫秒以下
• err/s: 0.00 reconn/s: 0.00，这两个的意思就是说，每秒有0个请求是失败的，发生了0次网络重连

这个压测结果会根据每个人的机器的性能不同有很大差距，要是机器性能特别高，可开很多的并发线程去压测，如100个线程，此时可能会发现数据库每秒的TPS有几千个，如果机器性能很低，可能压测出TPS才二三十个，QPS才几百个，这都有可能。

完成压测后，会显示一个总的压测报告，如下：

SQL statistics:
    queries performed:
        read: 2345210     // 这就是说在300s的压测期间执行了148万多次的读请求
        write: 670060     // 这是说在压测期间执行了29万多次的写请求
        other: 335030     // 这是说在压测期间执行了30万多次的其他请求
        total: 3350300     // 这是说一共执行了210万多次的请求
    transactions: 167515(1395.6 per sec. )     // 这是说一共执行了10万多个事务，每秒执行350多个事务
    queries: 3350300(27919.26 per sec. )    // 这是说一共执行了210万多次的请求，每秒执行7000+请求
    ignored errors: 0(0.00 per sec.)
    reconnects: 0(0.00 per sec.)
    
// 下面就是说，一共执行了300s的压测，执行了10万+的事务
General staticstics: 
    total time: 120.0052s
    total number of events: 167515
Latency (ms):
    min: 3.19 // 请求中延迟最小的是3.19ms
    avg: 7.16 // 所有请求平均延迟是7.16ms
    max: 690.35 // 延迟最大的请求是690.35ms
    95th percentile: 17.63 // 95%的请求延迟都在21.33ms以内

四、最后是个人的压测数据集合

根据上面的表格可以看出，参数一和参数五的区别也是不小的，每秒可以多大约7000次的请求，以及每秒能多执行将近400件事务。所以说缓存对Mysql的性能影响还是很大的，大家如果担心内存溢出的话可以使用下面的查询语句判断出最顶峰时使用的内存。

SELECT((@@key_buffer_size+@@innodb_buffer_pool_size+@@innodb_log_buffer_size)/1024/1024)+((@@read_rnd_buffer_size+@@read_buffer_size+@@myisam_sort_buffer_size+@@sort_buffer_size+@@join_buffer_size)/1024/1024*@@max_connections);

这三个参数我的调整后如下，不过这个还是得根据个人机器的使用情况进行调整

performance_schema=NO
performance_schema_max_memory_classes=200
performance_schema_max_file_instances=200000

调整后file_instances表占用的内存大概在140M这样