目录
1. QPS&&TPS
1.1 数据库启动到现在的运行时间(秒)
1.2 查询量
1.3 status命令直接显示出QPS
1.4 每秒输出数据库状态(累加)
2. sysbench 测试工具
3. OLTP MySQL测试
3.1 普通参数
3.2 支持的lua脚本
3.3 脚本参数
3.4 测试数据准备
3.5 进行测试
3.6 测试结果解释
4. MySQL连接相关参数
4.1 什么是MySQL长连接和短连接
4.2 MySQL长短连接的实现原理
4.3 MySQL长连接和短连接的优缺点
4.4 MySQL长短连接的使用注意事项
5. MySQL线程相关参数
1. QPS&&TPS
QPS：query per second，数据库每秒执行的SQL数，包含insert、select、update、delete等。
TPS：transaction per second，数据库每秒执行的事务数
1.1 数据库启动到现在的运行时间(秒)
(root@localhost) [(none)]> show global status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime | 658 |
+---------------+-------+
1 row in set (0.00 sec)1.2 查询量
(root@localhost) [(none)]> show global status like 'Questions%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions | 11 |
+---------------+-------+
1 row in set (0.00 sec)查询量/运行时间=QPS
1.3 status命令直接显示出QPS
(root@localhost) [(none)]> status
--------------
mysql Ver 14.14 Distrib 5.7.44, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.44-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 14 min 28 sec
Threads: 2 Questions: 15 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 100 Queries per second avg: 0.017
--------------1.4 每秒输出数据库状态(累加)
[root@localhost ~]# mysqladmin extended-status -i 1 -uroot -p123456加上-r参数第一次显示总数，后面就显示差值
[root@localhost ~]# mysqladmin extended-status -i 1 -uroot -p123456 -r
.......
[root@localhost ~]# mysqladmin extended-status -i 1 -uroot -p123456 -r | grep -i questions
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Questions | 38 |
| Questions | 1 |
| Questions | 1 |
| Questions | 1 |
| Questions | 1 2. sysbench 测试工具
3. OLTP MySQL测试
3.1 普通参数
--threads=N 指定线程数 [1]
--events=N 事件总数的限制 [0]
--time=N 总执行时间(以秒为单位)[10]，0表示无限制
--warmup-time=N 预热时间，可以实现在 CPU/database/page/caches 预热之后再进行统计，这样得到的数据指标更准确 [0]
--forced-shutdown=STRING 强制关闭或禁用“off”之前，在—时间限制之后等待的秒数 [off]
--thread-stack-size=SIZE 每个线程的堆栈大小 [64K]
--thread-init-timeout=N 等待工作线程初始化的时间(以秒为单位) [30]
--rate=N 平均事务率。0表示不限制[0]
--report-interval=N 以秒为单位定期报告中间统计信息，0禁用间隔报告 [0]
--report-checkpoints=[LIST,...] 转储完整的统计信息，并在指定的时间点重置所有计数器。参数是一个逗号分隔的值列表，表示从测试开始到必须执行报表检查点所花费的时间(以秒为单位)。默认情况下，报表检查点是关闭的。 []
--debug[=on|off] 打印更多调试信息 [off]
--validate[=on|off] 在可能的情况下执行验证检查[off]
--help[=on|off] 打印帮助并退出 [off]
--version[=on|off] 打印版本并退出[off]
--config-file=FILENAME 包含命令行选项的文件
--luajit-cmd=STRING 执行LuaJIT控制命令。这个选项相当于“luajit -j”。有关更多信息，请参见LuaJIT文档
#一般数据库选项
General database options:
--db-driver=STRING 指定要使用的数据库驱动程序(“帮助”获取可用驱动程序列表) [mysql]
--db-ps-mode=STRING 语句使用模式 {auto, disable} [auto]
--db-debug[=on|off] 打印特定于数据库的调试信息 [off]
#数据库驱动
Compiled-in database drivers:
mysql - MySQL driver
mysql options:
--mysql-host=[LIST,...] MySQL服务器主机[localhost]
--mysql-port=[LIST,...] MySQL服务器端口 [3306]
--mysql-socket=[LIST,...] MySQL socket
--mysql-user=STRING MySQL 用户[sbtest]
--mysql-password=STRING MySQL 密码[]
--mysql-db=STRING MySQL 数据库名称 [sbtest]
--mysql-ssl=STRING SSL模式。这接受与MySQL客户机实用程序中的——ssl-mode选项相同的值。默认情况下禁用 [disabled]
--mysql-ssl-key=STRING 客户端私钥文件的路径名称
--mysql-ssl-ca=STRING CA文件的路径名
--mysql-ssl-cert=STRING 客户端公钥证书文件的路径名
--mysql-ssl-cipher=STRING 为SSL连接使用特定的密码 []
--mysql-compression[=on|off] 如果在客户端库中可用，则使用压缩 [off]
--mysql-debug[=on|off] 跟踪所有客户机库调用 [off]
--mysql-ignore-errors=[LIST,...] 要忽略的错误列表，或“全部” [1213,1020,1205]
--mysql-dry-run[=on|off] 试运行，假设所有MySQL客户端API调用都是成功的，而没有执行它们 [off] them [off]3.2 支持的lua脚本
选项 说明
oltp_read_only 只读测试
oltp_read_write 读写测试
oltp_insert 简单插入测试
bulk_insert 批量插入测试
oltp_delete delete删除测试
oltp_update_index 带索引的更新测试
oltp_update_non_index 不带索引的更新测试
oltp_point_select 等值查询测试
select_random_points 随机等值查询测试
select_random_ranges 随机范围查询测试 3.3 脚本参数
--auto_inc[=on|off] 使用 AUTO_INCREMENT 列作为主键（对于 MySQL），或者它在其他 DBMS 中的替代项。禁用时，使用客户端生成的ID [on]
--create_secondary[=on|off] 除了 PRIMARY KEY 创建二级索引 [on]
--create_table_options=STRING Extra CREATE TABLE options []
--delete_inserts=N 每个事务的 DELETE/INSERT 组合数 [1]
--distinct_ranges=N 每个事务的 SELECT DISTINCT 查询数 [1]
--index_updates=N 每个事务的 UPDATE 索引查询数 [1]
--mysql_storage_engine=STRING 如果使用 MySQL，指定存储引擎 [innodb]
--non_index_updates=N 每个事务的 UPDATE 非索引查询数 [1]
--order_ranges=N 每个事务的 SELECT ORDER BY 查询数 [1]
--pgsql_variant=STRING 使用 PostgreSQL 驱动程序运行时使用此 PostgreSQL 变体。当前唯一支持的变体是“redshift”。启用后，create_secondary 自动禁用，delete_inserts 设置为 0
--point_selects=N 每个事务的点 SELECT 查询数 [10]
--range_selects[=on|off] 启用/禁用所有范围 SELECT 查询 [on]
--range_size=N 范围 SELECT 查询的范围大小 [100]
--reconnect=N 每N个事件后重新连接，默认0表示不重连 [0]
--secondary[=on|off] 使用二级索引代替 PRIMARY KEY [off]
--simple_ranges=N 每个事务的简单范围 SELECT 查询数 [1]
--skip_trx[=on|off] 不要启动显式事务并在 AUTOCOMMIT 模式下执行所有查询 [off]
--sum_ranges=N 每个事务的 SELECT SUM() 查询数 [1]
--table_size=N 每个表的行数 [10000]
--tables=N 表的个数 [1]3.4 测试数据准备
4个线程在sbtest数据库下创建4张1000000行数据的表
sysbench --threads=4 --events=0 --time=30 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=4 --table-size=1000000 oltp_read_only prepare3.5 进行测试
# oltp_read_only只读测试
[root@localhost ~]# sysbench --threads=4 --events=0 --time=30 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=4 --table-size=1000000 oltp_read_only run
# oltp_read_write读写测试
[root@localhost ~]# sysbench --threads=4 --events=0 --time=30 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=4 --table-size=1000000 oltp_read_write run
# oltp_insert简单插入测试
[root@localhost ~]# sysbench --threads=4 --events=0 --time=30 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=4 --table-size=1000000 oltp_insert run
# bulk_insert批量插入测试
[root@localhost ~]# sysbench --threads=4 --events=0 --time=30 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=4 --table-size=1000000 bulk_insert run
# bulk_insert批量插入测试
[root@localhost ~]# sysbench --threads=4 --events=0 --time=30 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest bulk_insert run
#oltp_delete删除测试
[root@localhost sysbench]# sysbench --threads=4 --events=0 --time=30 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=4 --table-size=1000000 oltp_delete run
#oltp_update_index带索引的更新测试
[root@localhost sysbench]# sysbench --threads=4 --events=0 --time=30 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=4 --table-size=1000000 oltp_update_index run
#oltp_update_non_index不带索引的更新测试
[root@localhost sysbench]# sysbench --threads=4 --events=0 --time=30 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=4 --table-size=1000000 oltp_update_non_index run
#oltp_point_select等值查询测试
[root@localhost sysbench]# sysbench --threads=4 --events=0 --time=30 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=4 --table-size=1000000 oltp_point_select run
#select_random_points随机等值查询测试
[root@localhost sysbench]# sysbench --threads=4 --events=0 --time=30 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=4 --table-size=1000000 select_random_points run
#select_random_ranges随机范围查询测试
[root@localhost sysbench]# sysbench --threads=4 --events=0 --time=30 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=4 --table-size=1000000 select_random_ranges run3.6 测试结果解释
[root@localhost ~]# sysbench --threads=4 --events=0 --time=30 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=sbtest --tables=4 --table-size=1000000 oltp_read_write run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 4
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
#[1s],表示第1秒的压测统计报告
#thds:4，表示4个线程做压测
#tps：5.82，表示每秒执行了5.82个事务
#qps: 154.30，每秒可以执行4650.71个请求，(r/w/o: 113.54/25.23/15.53)表示在每秒中，分别是读、写、其他请求的个数，就是对QPS进行了拆解
#lat (ms,95%): 707.07，表示95%的请求的延迟都在707.07毫秒以下
#err/s: 0.00，每秒有0个请求是失败的
#reconn/s: 0.00，发生了0次网络重连
[ 1s ] thds: 4 tps: 5.82 qps: 154.30 (r/w/o: 113.54/25.23/15.53) lat (ms,95%): 707.07 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 4 tps: 8.19 qps: 169.95 (r/w/o: 122.86/30.71/16.38) lat (ms,95%): 831.46 err/s: 0.00 reconn/s: 0.00
......
SQL statistics:
queries performed:
read: 3640 # 在30s的压测期间执行了3640次的读请求
write: 1040 # 在30s的压测期间执行了1040次的写请求
other: 520 # 在压测期间执行了520次的其他请求
total: 5200 # 总请求
transactions: 260 (8.60 per sec.) # 一共执行了260个事务，每秒执行8.6个事务
queries: 5200 (172.03 per sec.) # 一共5200请求，每秒172.03个请求
ignored errors: 0 (0.00 per sec.) # 0个忽略错误
reconnects: 0 (0.00 per sec.) # 0个重连
#30.228秒执行了260个事务，每秒执行了8.6013个事务
Throughput:
events/s (eps): 8.6013
time elapsed: 30.2280s
total number of events: 260
Latency (ms):
min: 182.67 # 最小延迟的请求
avg: 463.30 # 平均延迟
max: 973.78 # 最大延迟
95th percentile: 759.88 # 95%的请求延迟都在759.8毫秒内
sum: 120458.37 # 总延迟
Threads fairness:
events (avg/stddev): 65.0000/0.71 #4个线程，每个线程平均执行的事务数的标准差
execution time (avg/stddev): 30.1146/0.09 #4个线程，每个线程平均执行时间的标准差在压测的时候可以查看系统的CPU、内存、磁盘IO、网络等情况
top - 12:37:35 up 5:26, 3 users, load average: 1.13, 0.62, 0.53
Tasks: 195 total, 1 running, 194 sleeping, 0 stopped, 0 zombie
%Cpu(s): 74.9 us, 18.4 sy, 0.0 ni, 2.0 id, 0.0 wa, 0.0 hi, 4.7 si, 0.0 st
KiB Mem : 2895292 total, 114672 free, 1370780 used, 1409840 buff/cache
KiB Swap: 3145724 total, 3145724 free, 0 used. 1319900 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1702 mysql 20 0 3096292 892172 14452 S 331.7 30.8 29:33.48 mysqld
20830 root 20 0 629244 7728 3048 S 61.4 0.3 0:04.99 sysbench
19938 root 20 0 162020 2336 1580 R 1.0 0.1 0:03.07 top
1188 root 20 0 574200 19480 6108 S 0.7 0.7 0:06.39 tuned
19 root 20 0 0 0 0 S 0.3 0.0 0:02.57 ksoftirqd/2
8027 root 20 0 163616 6116 4720 S 0.3 0.2 0:03.66 sshd
1 root 20 0 191268 4244 2620 S 0.0 0.1 0:21.40 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:00.11 kthreadd
4 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H
6 root 20 0 0 0 0 S 0.0 0.0 0:05.89 ksoftirqd/0
7 root rt 0 0 0 0 S 0.0 0.0 0:04.72 migration/0
8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh
9 root 20 0 0 0 0 S 0.0 0.0 0:13.59 rcu_sched
10 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 lru-add-drain
11 root rt 0 0 0 0 S 0.0 0.0 0:04.80 watchdog/0
12 root rt 0 0 0 0 S 0.0 0.0 0:01.77 watchdog/1
13 root rt 0 0 0 0 S 0.0 0.0 0:04.03 migration/1
14 root 20 0 0 0 0 S 0.0 0.0 0:01.69 ksoftirqd/1
16 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/1:0H上面mysqld使用的%CPU是331.7，意思是使用了3.317颗CPU，我的虚拟机总CPU为4颗逻辑CPU
4. MySQL连接相关参数
(root@localhost) [sbtest]> show global status like '%connect%';
+-----------------------------------------------+---------------------+
| Variable_name | Value |
+-----------------------------------------------+---------------------+
| Aborted_connects | 5 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 68 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 238 |
| Locked_connects | 0 |
| Max_used_connections | 152 |
| Max_used_connections_time | 2024-04-11 12:33:14 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 1 |
+-----------------------------------------------+---------------------+
16 rows in set (0.00 sec)
参数介绍：
Aborted_connects 尝试连接到MySQL服务器失败的次数
Connection_errors_accept 当一个客户端的连接错误数达到该值时,服务器会拒绝该客户端的后续连接
Connection_errors_internal 由于服务器中的内部错误（如无法启动新线程或内存不足）而拒绝的连接数
Connection_errors_max_connections 因为max_connections限制而连接失败的连接
Connection_errors_peer_address 当一个IP地址的连接错误数达到阈值时,会触发访问拒绝
Connection_errors_select 在侦听端口上调用select（）或poll（）期间发生的错误数。（此操作失败并不一定意味着客户端连接被拒绝。）
Connection_errors_tcpwrap libwrap库拒绝的连接数
Connections 表示MySQL从启动至今，成功建立连接的连接数，这个值是不断累加的
Locked_connects 尝试多少次连接后，锁住用户
Max_user_connections 某个用户并行占用的最大连接数
Max_user_connections_time 用户占用最大连接数的用户发生的时间点
Threads_connected 打开的连接数.
(root@localhost) [sbtest]> show variables like '%connect%';
+-----------------------------------------------+-----------------+
| Variable_name | Value |
+-----------------------------------------------+-----------------+
| character_set_connection | utf8 |
| collation_connection | utf8_general_ci |
| connect_timeout | 10 |
| disconnect_on_expired_password | ON |
| init_connect | |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_user_connections | 0 |
| performance_schema_session_connect_attrs_size | 512 |
+-----------------------------------------------+-----------------+
max_connect_errors 允许一个IP客户端端连接错误最大值，超过后在不刷新状态的情况下，禁止该客户端新连接
max_connections 实例最大连接数限制
max_user_connections 同一个用户，同时连接的最大连接数max_user_connections/max_connections = 85%
注意sysbench命令的threads参数和mysql的max_connections参数有关系，如果threads超过了max_connections就会报错FATAL: error 1040: Too many connections，调大max_connections参数即可，可以动态调整set global max_connections=256;
4.1 什么是MySQL长连接和短连接
MySQL连接分为长连接和短连接两种模式：
长连接：在长连接模式下，应用程序与MySQL数据库建立一次连接后，保持连接处于打开状态，直到显式关闭连接。这意味着应用程序可以多次使用相同的连接来执行多个查询或更新操作，而不需要每次都建立新的连接。
短连接：短连接模式下，应用程序执行完一个查询或更新操作后，立即关闭连接。下次需要再次操作数据库时，重新建立一个新的连接。
4.2 MySQL长短连接的实现原理
实现长连接和短连接的关键在于连接池的管理。连接池是一个维护数据库连接的池子，应用程序从池中获取连接，使用完后放回池中，而不是每次都建立新的连接。
- 长连接：在长连接模式下，应用程序从连接池中获取连接后，不主动关闭连接，而是将连接保持打开，等待下次使用。
- 短连接：在短连接模式下，应用程序获取连接后，在一次查询或更新操作后立即关闭连接，连接池会在连接关闭后将其释放。
4.3 MySQL长连接和短连接的优缺点
长连接的优点：
- 减少连接开销。
- 减轻数据库服务器负担。
- 可以维护事务状态。
长连接的缺点：
- 连接占用资源。
- 需要显式管理连接状态。
短连接的优点：
- 简单易用。
- 适用于短期任务。
短连接的缺点：
- 频繁连接开销。
- 无法维持事务状态。
4.4 MySQL长短连接的使用注意事项
长连接需要谨慎管理，确保在适当的时候关闭连接，避免资源泄漏。
确保连接池的配置合理，以满足应用程序的需求。
根据具体应用场景选择合适的连接模式。
5. MySQL线程相关参数
(root@localhost) [sbtest]> show global status like '%thread%';
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| Delayed_insert_threads | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 9 |
| Threads_connected | 1 |
| Threads_created | 152 |
| Threads_running | 2 |
+------------------------------------------+-------+
参数介绍：
Threads_cached 代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected 表示当前打开的连接数。
Threads_created 表示创建过的线程数。
Threads_running 是代表当前并发数，未休眠的线程。
(root@localhost) [sbtest]> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 9 |
+-------------------+-------+
thread_cache_size 当客户端断开连接时，如果缓存中的线程少于thread_cache_size，则会将客户端的线程放入缓存中，默认值为8 + (max_connections / 100)什么时候Threads_connected和Threads_running一样？有慢查询，阻塞
