mysql deliberate slowdown

Question

I have an Java application that connects to database. In the production environment, the dataset is very large, so the application very slow and I want to simulate (the slowness) in development environment. Is there a way, how to slowdown mysql, so the response time is bigger?
I know, that I could enlarge my test dataset, but the proccessing of the large dataset would eat processor cycles, I'm rather searching for something that would do "cheap" sleeps in MySQL.

Answer 1

You can copy MySQL database files to (slow, old) usb key and set MySQL's setting datadir to point to usb. I copied MySQL data directory to usb key and set datadir variable to usb.

#Path to the database root
#datadir="C:/Program Files (x86)/MySQL/MySQL Server 5.0/Data/"
datadir = "E:/data"

I suppose it's important to set innodb_flush_log_at_trx_commit to 1.

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=1

This way each update, delete, insert statement results I/O flush.

Answer 2

To add to the SLEEP() function comment, here's an easy way to integrate a sleep into any SQL query when you can't run it as separate statement:

LEFT JOIN (SELECT SLEEP(30)) as `sleep` ON 1=1

Answer 3

MySQL has a SLEEP(duration) miscellaneous function, where duration is the number of seconds.

source: http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_sleep