Capturing and Analyzing Millions of Queries without Any Overhead

Wednesday, 30 August 2017 - 3:40pm4:10pm

Karthik Appigatla, Sr. Database Engineers, LinkedIn, and Basavaiah Thambara, LinkedIn

Abstract: 

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.

Open Access Media

USENIX is committed to Open Access to the research presented at our events. Papers and proceedings are freely available to everyone once the event begins. Any video, audio, and/or slides that are posted after the event are also free and open to everyone. Support USENIX and our commitment to Open Access.

Presentation Audio

BibTeX
@conference {205490,
author = {Karthik Appigatla and Basavaiah Thambara},
title = {Capturing and Analyzing Millions of Queries without Any Overhead},
year = {2017},
address = {Dublin},
publisher = {{USENIX} Association},
}