Take the 2-minute tour ×

I have developed some mysql queries for my application, and created indexes and used EXPLAIN statements as well.

  • What types of testing methods we can use to check queries (performance testing, load testing, concurrency testing and others)

  • How to use those testing methods in your system, anything related to query testing is helpful to me.

Thanks in advance.

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

Test with sysbench , it is a great tool for simulating database traffic. Furthermore I would suggest getting familiar with the MySQL EXPLAIN statement as it helps to dissect a query and improve on slow areas.

There are quite a few articles on the internet that explain how to properly benchmark, here is just one of them http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/ .

Last but not least there is no substitute for testing with real data. Theoretically speaking certain queries should handle better than others, but the only sure way to know this is by testing your schema with actual data. There is a handly tool named generatedata that creates a lot of dummy data so that you can perform said tests.

In order to properly benchmark your queries you must ensure any cached queries and database information is wiped so that the result times are accurate and independent of one-another; you can do this by performing a RESET QUERY CACHE and FLUSH TABLES before running each query.

Additional information as requested: From experience the best way to handle concurrency is by using the MySQL SET TRANSACTION statement to properly isolate your queries. By using the InnoDB engine the database will perform row locking which is often sufficient for most applications. You can test this by performing equivalent tasks on the database but with separate transactions. Concurrency is a very broad topic in the database world and I would highly recommend further researching this topic.

share|improve this answer
thank you dennis, for ur great info. can i get some more clarity on this. –  lourdh Jan 2 '12 at 9:18
Please be sure to accept answers. I have made a few changes, if you would like me to elaborate further please be more specific. –  Mr. White Jan 2 '12 at 9:35
actually i have to do performance testing, load testing, concurrency testing on my SQL stmts, can you point me how to do these tests? and some good open source tools to do testing. –  lourdh Jan 2 '12 at 10:26
For performance testing use the explain statement as mentioned and record query execution times. Be sure to clear your cache in-between using my previous suggestions or the SQL SELECT SQL_NO_CACHE or disabling QueryCache for the test. In-order to perform load testing use the generatedata tool i recommended to fill your database and test with a large load. For concurrency I added additional information to my response. –  Mr. White Jan 2 '12 at 23:34
thank you dennis, for these info. it was really helpful –  lourdh Jan 3 '12 at 2:47
add comment

Your Answer


