De-optimizing MySQL

Question

Is there a simple command that will let me tweak a MySQL DB instance to run slower than normal?

I'm working on code that records and replays database interactions. One of the things it does is keep track of how long a given query/command takes to execute, and if it runs substantially slower during the replay, it throws a warning. Of course, If You Don't Test It, It Doesn't Work; I'm trying to come up with an automated test for this feature. Is there something I can do to my test DB that will screw up performance? All I need to do is reliably add 2+ milliseconds to any given query and I'm set.

Answer 1

If you just want to test long queries, do this: SELECT SLEEP(1);

It shouldn't matter what the query is itself if all you want to do is test if your duration detection works.

(I know this breaks the true "replay" aspect, but it should be trivial to add SLEEP(1) during "playback" to some select statements.)

EDIT:

A second idea, which you might like better: Create a lock on a table from another connection. Run the script. Wait a bit. Remove that lock. It won't involve messing with any of your playback queries.

Answer 2

Basic procedure like so:

1. begin transaction

2. do your sql stuff

3. sleep 2ms in perl or sql

4. commit

the key is the begin/commit part: it'll keep any locks you acquired, making things as slow as you want them.

Other test to consider:

1. begin transaction in two processes

2. do your sql stuff in first process

3. do your sql stuff in second process

4. do more sql stuff in each process

5. commit each process