I had gone through MySQL High Performance book Chapter 2 for MySQL Bench marking and Profiling,I have some questions about benchmarking
-
How can we test an existing database and queries against it using sysbench tool?
-
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)
Best Answer
You can take a look into this post:
Stress test MySQL with queries captured with general log in MySQL
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
It seems that your bottleneck is the disk I/O not the CPU, because operations occurring during the
sending datastate tend to perform large amounts of disk access (reads). You can try to increase yourinnodb_buffer_pool_sizeto 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