MySQL压力测试问题？

Question

我读过MySQL高性能书第二章关于MySQL台架标记和分析，我有一些关于基准测试的问题

1. 如何使用sysbench工具对现有数据库和查询进行测试？
2. 目前，我正在使用mysqlslap进行压力测试。它是否提供可靠和有用的信息？

我们正在创建一个新的应用程序，并可能在一年内拥有多达100万用户。为了将MySQL服务器配置为生产，我们在本地上加载测试MySQL，我已经用100000条虚拟记录填充了表，并且尝试用一些queries.For进行测试，现在所有东西都在本地机器上。

这里是CPU和内存使用的结果：

那么，我应该从这张图片中了解什么呢？我是否需要增加CPU，因为100%正在使用。

我正在用带有100个并发连接的单个查询测试mysqlslap。

mysqlslap -utest_user -p --create-schema=mydb --delimiter=";" --query=/home/abdul/Desktop/XYZ/DataSet/test_queries.sql --concurrency=100
Benchmark
    Average number of seconds to run all queries: 79.619 seconds
    Minimum number of seconds to run all queries: 79.619 seconds
    Maximum number of seconds to run all queries: 79.619 seconds
    Number of clients running queries: 100
    Average number of queries per client: 1

从这个结果我能得出什么结论？

我的本地MySQL设置无法处理单个查询的100个并发连接，因为这需要79秒的大量时间?

。

如果我在单个线程中执行此查询，则几乎不需要.5 secs。

更新1

显示处理列表的结果

mysql> show processlist;
+-----+-----------+------------------------------+-------+-------------+------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id  | User      | Host                         | db    | Command     | Time | State                                                                 | Info                                                                                                 |
+-----+-----------+------------------------------+-------+-------------+------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
|   9 | repl_user | administrator-55.local:57925 | NULL  | Binlog Dump | 7395 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL                                                                                                 |
|  62 | root      | localhost                    | world | Query       |    0 | NULL                                                                  | show processlist                                                                                     |
|  63 | root      | localhost                    | NULL  | Sleep       |   11 |                                                                       | NULL                                                                                                 |
|  64 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  65 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  66 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  67 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  68 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  69 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  70 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  71 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  72 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  73 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  74 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  75 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  76 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  77 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  78 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  79 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  80 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  81 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  82 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  83 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  84 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  85 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  86 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  87 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  88 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  89 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  90 | root      | localhost                    | mydb  | Query       |   11 | Writing to net                                                        | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  91 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  92 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  93 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  94 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  95 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  96 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  97 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  98 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
|  99 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 100 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 101 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 102 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 103 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 104 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 105 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 106 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 107 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 108 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 109 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 110 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 111 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 112 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 113 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 114 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 115 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 116 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 117 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 118 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 119 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 120 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 121 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 122 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 123 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 124 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 125 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 126 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 127 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 128 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 129 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 130 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 131 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 132 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 133 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 134 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 135 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 136 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 137 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 138 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 139 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 140 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 141 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 142 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 143 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 144 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 145 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 146 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 147 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 148 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 149 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 150 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 151 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 152 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 153 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 154 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 155 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 156 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 157 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 158 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 159 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 160 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 161 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 162 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
| 163 | root      | localhost                    | mydb  | Query       |   11 | Sending data                                                          | SELECT t1.profile_login_name, t2.name_id FROM user_profile as t1 inner join user_name as t2 on t1.us |
+-----+-----------+------------------------------+-------+-------------+------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
103 rows in set (0.00 sec)

Answer 1

如何使用sysbench工具对现有数据库和查询进行测试？

你可以看看这篇文章：

使用通用日志在MySQL中捕获的查询对MySQL进行压力测试

目前，我正在使用mysqlslap进行压力测试。它是否提供可靠和有用的信息？

在我看来，使用mysqlslap或sysbench(使用默认oltp测试)对于在HW体系结构、MySQL版本和MySQL配置/初始调优之间选择正确的启动组合可能很有用。要做到这一点，您必须收集并绘制所获得的所有结果(cpu、I/O、MySQL服务器信息等)，以更改这三个参数。我在相同的HW组合中使用了标准测试和“我的”测试，并获得了显著的差异。

所以，在我看来，最好的方法是：

• 使用标准oltp查找正确的HW、MySQL版本和初始调优。
• 使用测试测试(请参阅第一个响应)测试工作负载，然后进行最后的/精细的调优。

更新#1

那么，我应该从这张图片中了解什么呢？我是否需要增加CPU，因为100%正在使用。本地MySQL设置是否无法处理单个查询的100个并发连接，因为它花费了79秒的大量时间？

您的瓶颈似乎是磁盘I/O，而不是CPU，因为在sending data状态下发生的操作往往会执行大量的磁盘访问(读)。您可以尝试将innodb_buffer_pool_size提高到机器物理内存大小的80% (在专用服务器上，在本地计算机上尝试逐步增加)，设置此值越大，访问表中的数据所需的磁盘I/O就越少。