Geek Answers Handbook

How to check your mysql queries?

I developed several mysql queries for my application, created indexes, and used EXPLAIN statements.

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

  • How to use these testing methods on your system, everything related to testing queries is useful to me.

Thanks in advance.

+7
source share
1 answer

Test with sysbench , it is a great tool for modeling database traffic. In addition, I would suggest familiarizing yourself with the MySQL EXPLAIN statement, as it helps analyze the query and improve slow areas.

There are many articles on the Internet that explain how to test correctly, here is just one of them http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/ . p>

And last but not least, a place to test real data. Theoretically speaking, some requests should be handled better than others, but the only sure way to find out is to check your schema with actual data. There is a manual tool called generatedata that creates a lot of dummy data so you can perform the specified tests.

To properly verify your queries, you must ensure that all cached queries and database information are erased so that the result time is accurate and independent of one another; You can do this by doing RESET QUERY CACHE and FLUSH TABLES before running each query.

Further information on request: From experience, the best way to deal with concurrency is to use the SET TRANSACTION statement to properly isolate your requests. Using the InnoDB mechanism, the database will perform row locking, which is often sufficient for most applications. You can verify this by performing equivalent tasks in the database, but with separate transactions. concurrency is a very broad topic in the database world, and I would highly recommend continuing to explore this topic.

+7
source

More articles:


All Articles