MySQL Stress testing issues?

Question

I had gone through MySQL High Performance book Chapter 2 for MySQL Bench marking and Profiling,I have some questions about benchmarking

1. How can we test an existing database and queries against it using sysbench tool?

2. Currently I am using mysqlslap for stress testing. Does it provide reliable and useful information?

We are creating a new application and could have up to 1 million users in a year. In order to configure MySQL Server for production we are load testing MySQL on local,I have populated tables with 100000 dummy records and trying to test with some queries.For now everything is on a local machine.

Here are the CPU and Memory usage results:

So What should i understand from this image ? Do i need to increase CPU as 100% is being used.

I am testing mysqlslap with my single query with 100 concurrent connection

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

What conclusions can I obtain from this result ?

Does my local MySQL set up is not able to handle 100 concurrent connections for a single query as it is taking huge time of 79 secs ?

If i execute this query in single thread it will take hardly .5 secs.

UPDATE 1

Result of show processlist

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

How can we test an existing database and queries against it using sysbench tool?

You can take a look into this post:

Stress test MySQL with queries captured with general log in MySQL

Currently I am using mysqlslap for stress testing. Does it provide reliable and useful information?

Use mysqlslap or sysbench(with default oltp tests) can be useful, in my opinion, in order to choose the right starting combination between HW architecture, MySQL version and MySQL configuration/initial tuning. To do this you have to collect and plot all results obtained(cpu, I/O, MySQL server info etc) changing these three parametres. I have worked with standard tests and "my" tests on the same HW combination and I obtained significant differences.

So, always in my opinion, the best way is:

• find your right HW, MySQL version and initial tuning using standard oltp

• test your workload using your tests (see first response) and works for the final/fine tunining then

UPDATE #1

So What should i understand from this image ? Do i need to increase CPU as 100% is being used. Does my local MySQL set up is not able to handle 100 concurrent connections for a single query as it is taking huge time of 79 secs ?

It seems that your bottleneck is the disk I/O not the CPU, because operations occurring during the sending data state tend to perform large amounts of disk access (reads). You can try to increase your innodb_buffer_pool_size to up to 80% of the machine physical memory size(on a dedicated server, on your local machine try increasing step by step), larger you set this value less disk I/O is needed to access data in tables