SREcon17 Europe/Middle East/Africa

This talk is about a new way of monitoring and analyzing millions of queries with no overhead.

Optimizing queries is the most important aspect of scaling database servers. Before we can optimize, we need to identify the problematic queries. We have slow-query log in MySQL where we can set a threshold and all the queries crossing threshold will be logged in a file and later can be used for analysis. Other way is to use performance_schema database inside MySQL which gives various metrics of queries.

The problem is that enabling the slow query log will incur a 25-35% overhead on the database, since we have to have to write to a file. Additionally, since only queries exceeding the threshold will be logged, we won't have any data about queries below that threshold. Meanwhile, enabling performance_schema incurs a 10-20% overhead, and is complex to understand.

To minimize overhead and effectively measure all queries, we have built a query analyzer which incurs less than 3% CPU overhead and no overhead on any other resources.