Home / server / Questions / 285547
Accepted
Gray Race
Asked: 2011-06-30 15:07:46 +0800 CST

How can artificially create a slow query in mysql?

  • 772

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.

mysql stress-testing

3 Answers

  1. 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);
    • 42
  2. Best Answer

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

    • 9

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

    • 2