How to Visualize Data that Really Matters to Business with Grafana and MySQL

So you have a Grafana dashboard that shows failures at 0.01% and that latency is down throughout the company.

But rather than get a pat on the back, “your boss’s boss is saying cut the crap or stop the mumbo jumbo. What does it really mean for our business?” said Peter Zaitsev, CEO of Percona, which offers solutions such as support, management services, consultant training, and custom engineering for MySQL, MariaDB, MongoDB, Postgres and other open source databases.

During GrafanaCon 2019 in L.A., Zaitsev showed attendees how to “visualize what actually matters to business” using Percona’s monitoring and management product, which integrates Grafana to analyze database performance and provide a solution for database monitoring.

Using Grafana and MySQL, Percona has built custom dashboards to showcase metrics such as how many orders are made in a minute or how much money was earned in 24 hours based on the same information that clients already have in their databases. “That is something we have done for a number of our customers and a lot of them love it,” said Zaitsev.

In addition to walking through live demos of two data sets – an e-commerce example and one that shows historic airline data for the United States – Zaitsev outlined three tricks for optimal setup on MySQL before getting started.

Use read-only user for additional safety.

“Bad things can happen due to mistakes or ill intent,” warned Zaitsev.

Set max_execution_time variable to avoid extensively long queries.

As users play with different variables and expand an interval out not to one day, but as far as 10 years, the queries can become very slow. When users realize it’s going to take hours to run the query, they may move on to a different page; however, the query continues to run.

“That means it’s very easy for you to go ahead and completely saturate your server with hundreds of bad queries,” said Zaitsev.

As a solution Zaitsev advised using the max_execution_time option, which is available in MySQL 5.7 or later. This feature allows users to set a time interval after which the system will automatically terminate ongoing queries.

Consider using MySQL replicas for queries.

“If you’re using replication to view some heavy queries, run those from the dedicated slave,” said Zaitsev. “Because, especially if there are complicated dashboards, all those panels run queries in parallel, and if you have 10 to 20 heavy queries running at the same time, they can impact the real-time part of your application.”

Watch all of Zaitsev’s live demos below:

For more from GrafanaCon 2019, check out all the talks on YouTube.