23

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
  • 2
    +1 For asking to get slow quries. If only it was this way around in real cases :D
    – red
    Jun 30, 2011 at 10:51

3 Answers 3

9

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

1
  • This worked best... using this and a combination of database de-tuning.
    – Gray Race
    Jul 1, 2011 at 17:57
43

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);
4
  • 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, 2011 at 23:32
  • :) Hmm...maybe a strategic LOCK TABLES will work. That may be too kludgy as well. Jun 29, 2011 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 3, 2014 at 15:49
  • Use DO SLEEP(10);if you don't want the result of SLEEP in your query. Aug 14, 2017 at 8:37
2

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .