CODEWITHSUNDEEP
mysqltestingload-testing
lourdh
lourdh

Reputation: 459

how to test your mysql queries?

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

Thanks in advance.

Upvotes: 7

Views: 9759

Answers (1)

Dennis
Dennis

Reputation: 4017

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.

Upvotes: 8

Related Questions