In order to optimise the performance of their MySQL instances, LinkedIn has created a Query Analyzer tool in order to analyze and tune expensive queries. It runs on the network layer, stores everything within a centralized server, and provides a UI for users to analyze metrics.
LinkedIn runs a multitenant MySQL architecture. This is mainly due to having over 500 applications which are dependant on it, meaning that single tenancy would cause extremely high resource costs. The tradeoff to this approach means that queries from one application can negatively detriment the performance for another, which is why it is important for them to be able to understand and optimise them as much as possible.
The query analyzer is split into three components:
- Agent: Runs on the MySQL nodes and collects metrics about running queries and their performance.
- Centralized Server: Where all the data about queries is stored for reporting.
- UI: Sits on top of the server and provides a user-friendly means to analyze the data.
Karthik Apigatla, senior database engineer at LinkedIn, highlights its benefits as:
… allowing our database engineers to identify problematic queries at a single glance, to compare a week-over-week overlay of query activity, and to troubleshoot database slowdowns quickly and efficiently.
Originally, out of the box solutions such as MySQL Performance Schema and slow query log were considered, but they came with too much of a performance penalty when introduced. Unlike those tools, the Query Analyzer runs on the network layer, minimising any kind of performance hit to the instances.
The agent captures raw query packets and reconstructs the query using the MySQL protocols, and then calculates response times based on a time diff between when packets first enter the port and when they are first produced by the database response.
The UI provides a tabular view of distinct queries for hosts, allowing the user to filter by periods of time. Individual queries can also be selected, providing graphs and other useful metrics such as load and average time.
The tool also provides a query load metric which is calculated as ‘execution time * number of executions’. By taking this and converting it into a ratio, it can be compared against other queries to see which ones took the largest percentage of load. For example, even if a query takes a few milliseconds, the metric would still correctly report if it is running too many times, thus taking up the majority of the load.
LinkedIn has not laid out a specific timeline, but intends to open source the tool in the near future.
This article is shared by www.script.biz | A leading resource of inspired clone scripts. It offers hundreds of popular scripts that are used by thousands of small and medium enterprises.