How can artificially create a slow query in mysql?

Question

I'm giving a hands on presentation in a couple weeks. Part of this demo is for basic mysql trouble shooting including use of the slow query log. I've generated a database and installed our app but its a clean database and therefore difficult to generate enough problems.

I've tried the following to get queries in the slow query log:

Set slow query time to 1 second.

Deleted multiple indexes.

Stressed the system:

stress --cpu 100 --io 100 --vm 2 --vm-bytes 128M --timeout 1m

Scripted some basic webpage calls using wget.

None of this has generated slow queries. Is there another way of artificially stressing the database to generate problems? I don't have enough skills to write a complex Jmeter or other load generator. I'm hoping perhaps for something built into mysql or another linux trick beyond stress.

+1 For asking to get slow quries. If only it was this way around in real cases :D — red, Jun 30 '11 at 10:51

score 42 · Answer 1 · answered Jun 29 '11 at 23:21

42

Totally artificial but you can use the sleep() function:

select sleep(10);

In the log:

Time                 Id Command    Argument
# Time: 110629 16:19:13
# User@Host: mysql[mysql] @ localhost []
# Query_time: 10.000218  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1309389553;
select sleep(10);

answered Jun 29 '11 at 23:21

Mark Wagner

17,764
2
30
47

Thanks for the suggestion... It does get the log to grow, but I'd like to have them be legit queries for the app. This way in I can demo explain and other diagnostic tools. – Gray Race Jun 29 '11 at 23:32
:) Hmm...maybe a strategic `LOCK TABLES` will work. That may be too kludgy as well. – Mark Wagner Jun 29 '11 at 23:56
1

Usually select * from ((select * from database)x) and keep getting more and more recrusive does the trick for me. – alexyorke Jun 03 '14 at 15:49
Use `DO SLEEP(10);`if you don't want the result of SLEEP in your query. – Rasmus Friis Kjeldsen Aug 14 '17 at 08:37

score 9 · Accepted Answer · answered Jun 30 '11 at 14:23

9

Checkout mysqlslap. You can pass one of your webapp's queries with --query and specify concurrent clients with --concurrency.

answered Jun 30 '11 at 14:23

HTTP500

4,827
4
22
31

This worked best... using this and a combination of database de-tuning. – Gray Race Jul 01 '11 at 17:57

score 2 · Answer 3 · answered Jun 29 '11 at 23:16

2

Perhaps de-tuning the database might help? For example reducing the key_buffers size?

answered Jun 29 '11 at 23:16

agy

206
1
1

How can artificially create a slow query in mysql?

3 Answers3