Spick and Span

Tweaking Queries

An important PMM feature is the Query Analytics dashboard that shows how queries are executed and where they spend their time by helping you analyze database queries over time, optimize database performance, and find and remedy the source of problems. To get started, select the Query Analytics option in the main menu, which brings up all the database queries running across all deployed servers in the Overview panel (Figure 4). By default, the queries are sorted by database load. You can sort by other parameters to find the queries that run most frequently or that take the longest to complete.

Figure 4: Each filtering category displays the top five parameters. If more than five exist, click Show all beside the category name to see a complete list.

In addition to Query , you can also view the metrics for other object types such as Database , Schema , Client Host , and so on by selecting them from the pull-down menu. When you select an item in the Overview panel, it opens the Details panel at the bottom (Figure 5), which gives a breakdown of the selected item.

Figure 5: Switch to the Explain tab to view the output of the MySQL explain statement on the selected query.

To further help you make sense of and use the metrics for optimizing. database performance, the Query Analytics dashboard uses filters to drill down into the data. The filters are logically categorized for easier navigation and display the corresponding load to help you make more sense of the data.

For instance, the filters will point you to the nodes and schemas that are responsible for generating the maximum load. You can then continue drilling down to the particular query that's responsible for generating a majority of that load. Clicking on that query will bring up details to help you analyze the factors that make that particular query inefficient. Perhaps the query hasn't been written properly and is scanning more rows than it must.

Although the interface might sound cumbersome, it's actually pretty intuitive once you get the hang of it. If you work with databases, PMM is a valuable tool that will help you reign in inefficient queries and ensure you are making best use of the available hardware.

The Author

Mayank Sharma has been writing and reporting on open source software from all over the globe for almost two decades.