tool for custom sql load testing

Question

I would like to run some tests to compare our mysql application in several configurations. But I don't want to use something like sysbench or oltp tests, because we have some heavy stored-procedures logic. So... I want to test against our procedures.

Is there any test application/framework which we can use to run custom queries (as option - in parallel) and see statistics? Something like Siege for web? What I already found usually use their own generated database schemas and scenarios.

I could ask my developers to creates some custom java interface and use Siege for it, bot don't want to add overhead or affect numbers.

Regards, Igor.

Answer 1

Jmeter can do database load testing using JDBC: http://jakarta.apache.org/jmeter/usermanual/build-db-test-plan.html

I haven't used the jdbc functionality before, so I don't know if it will handle your complex queries.

Answer 2

You can follow the general principles of most unit test engines to build a simple mySQL unit testing engine.

We build something like this for MS SQL in the past

The idea was the following

1) We write unit testing procedures and give them the same name prefixes like "test_". In MS SQL we also used extended properties as meta information to describe some specific parameters of out tests (the same like in NUnit one can use different test attributes)

2) We write a procedure/script that opens a cursor, selects from catalog all procedure names that starts "test_" and execute each of them in EXEC (@procedure_name) statement. If test fails, test procedure should raise an error.

3) The results of test execution are stored in a table.

4) In order to run test over the same data and get predictable results, we have a sample test database that we backuped once and restored from backup before each tests execution

Answer 3

If you would like a tool that now comes packaged with MySQL 5.1, you could do worse than try MySQL Slap (aka mysqlslap). An example straight from the documentation:

Supply your own create and query SQL statements, with 50 clients querying and 200 selects for each:

mysqlslap --delimiter=";" \  
--create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" \  
--query="SELECT * FROM a" --concurrency=50 --iterations=200

This supplies the option to run the tests in parallel - although the implementation of parallelism using multiple clients may not be exactly to your liking. Here is a brief description (again, verbatim):

mysqlslap runs in three stages:

1. Create schema, table, and optionally any stored programs or data you want to using for the test. This stage uses a single client connection.

2. Run the load test. This stage can use many client connections.

3. Clean up (disconnect, drop table if specified). This stage uses a single client connection.

If this is not exactly to your taste, you could either use a cron script to trigger MySQL Slap at a specific time for many users, or for many identically configured/spec'd machines, with identical network paths to server etc (the latter is very important, as benchmarking should eliminate any possible discrepancies to prevent obscured conclusions).

If (as is likely) you are running a previous MySQL version, this link provides useful information on how to compile the 5.1 source for 5.0; further backporting is probably not possible.

Here is a basic walkthrough.

Answer 4

I like to:

1. Write some PHP/Python/Perl script to access the database and perform the test and print the result in XML format
2. Use a HTTP benchmarking tool (httperf or similar) to slog the database and save the resulting outputs
3. Parse for failures, repeat.

Answer 5

I ended up using SQLIO to stress the disks, and then wrote some custom queries to generate quite a bit of data and then do various calculations on large tables and simulate a heavily loaded, but poorly optimized database. I then ramped up the number of concurrent connections until it died.