24

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.

Improve this question
1
  • 2
    +1 For asking to get slow quries. If only it was this way around in real cases :D Commented Jun 30, 2011 at 10:51

3 Answers 3

Reset to default
10

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

Improve this answer
1
  • This worked best... using this and a combination of database de-tuning. Commented Jul 1, 2011 at 17:57
44

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);
Improve this answer
5
  • 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. Commented Jun 29, 2011 at 23:32
  • :) Hmm...maybe a strategic LOCK TABLES will work. That may be too kludgy as well. Commented 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. Commented Jun 3, 2014 at 15:49
  • Use DO SLEEP(10);if you don't want the result of SLEEP in your query. Commented Aug 14, 2017 at 8:37
  • Good enough to test the configuration in Amazon RDS to make sure slow log setting is working correctly :) Commented Sep 8 at 17:11
2

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

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.