Even though Percona Monitoring and Management 2 (PMM) comes with a lot of dashboards and metrics out of the box, sometimes we need to extend the default metrics by running custom MySQL queries.

For example, suppose you want to have information about cached indexes from Innodb tables from innodb_cached_indexes table. That metric is not being captured by any default dashboard, but it is possible to extend PMM and make it capture the result of custom queries.

Getting Started With Custom Queries

Custom queries can be added to mysqld_exporter by adding them to the appropriate config file in /usr/local/percona/pmm2/collectors/custom-queries/mysql. There are three subdirectories inside it: high-resolution, low-resolution, and medium-resolution. PMM allows …

Percona engineers often need to analyze and review complex SQL database queries. Good formatting can make these SQL queries much easier to understand and work with. Without clear formatting, queries can become confusing and hard to debug.

Online query formatting services provide one set of solutions. Examples are Code Beautify, FreeFormatter, and sqlformat.org. However, many users are not comfortable sharing their queries with third-party services, especially if your SQL code contains confidential information.

This article examines alternatives to online tools for SQL query formatting tools that have been successfully used by Percona engineers. These solutions come in different types:

1. Plug-ins to your code editor or …

A couple of weeks ago, one of our customers reached us asking about the WARNING messages in their MySQL error log. After a while, there were a few more requests from some other customers asking whether to worry about these messages or not. In this post, I am going to write about the condition at which this WARNING message is written into the log and will explain some of the fundamentals behind the scene.

Look at the warningmber message which appears in the MySQL error log. It says it’s difficult to find a free block in the buffer pool and searched through the pool in a loop for 336 times. This is something weird to imagine; why would it have to go in a loop so many times? Let’s try to understand this.

[Warning] InnoDB: Difficult to find free blocks in the buffer pool (336 search iterations)! 0 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version …

While there is no magic bullet for MySQL 5.7 database tuning, there are a few areas that can be focused on upfront that can dramatically improve the performance of your MySQL installation. While much information has been published on this topic over the years, I wanted to break down some of the more critical settings that anyone can implement – with no guesswork required.

Depending on the version of MySQL you are running, some of the default values used in this post may differ from your install, but the premise is still largely the same. We will focus on MySQL 5.7 for the purposes of this article.

Initial MySQL performance tuning can be broken down to the following categories:

• Tuning for your hardware
• Tuning for best performance/best practices
• Tuning for your workload

MySQL 5.7 Database Tuning Tuning For Your Hardware

Depending on the hardware …

In my Webinar on Using Percona Monitoring and Management (PMM) for MySQL Troubleshooting, I showed how to use direct queries to ClickHouse for advanced query analysis tasks. In the followup Webinar Q&A, I promised to describe it in more detail and share some queries, so here it goes.

PMM uses ClickHouse to store query performance data which gives us great performance and a very high compression ratio. ClickHouse stores data in column-store format so it handles denormalized data very well. As a result, all query performance data is stored in one simple “metrics” table:

Tweet

I have over the last few years been fortunate to have two books published through Apress, Pro MySQL NDB Cluster which I wrote together with Mikiya Okuno and MySQL Connector/Python Revealed. With the release of MySQL 8 around a year ago, I started to think of how many changes there has been in the last few MySQL versions. Since MySQL 5.6 was released as GA in early 2013, some of the major features related to performance tuning includes the Performance Schema which was greatly changed in 5.6, histograms, EXPLAIN ANALYZE, hash …

As a MySQL DBA, the databases powering your applications need to handle changing traffic workloads while remaining responsive and stable so you can deliver an excellent user experience. Optimizing MySQL performance and troubleshooting MySQL problems cost-efficiently are some of the most critical and challenging tasks for MySQL DBAs. In this presentation, we will look at specific, common MySQL problems and demonstrate how Percona Monitoring and Management (PMM), built on free and open-source software, enables you to solve these challenges.

Please join Percona CEO Peter Zaitsev on Wednesday, March 18, 2020, at 1:30 pm EDT for his webinar “Optimize and Troubleshoot MySQL Using Percona Monitoring and Management”.

…

There are a lot of things I love about Prometheus; its data model is fantastic for monitoring applications and PromQL language is often more expressive than SQL for data retrieval needs you have in the observability space. One thing, though, I hate about Prometheus with a deep passion is the behavior of its rate() and similar functions, deeply rooted in the Prometheus computational model, which I was told by the development team is not likely to change.

So What’s the Problem, and Why is it Such a Big Deal?

First – the problem.  rate() functions give you the rate of change of the time series for the Interval supplied, so rate(mysql_global_status_questions[10s]) will basically give us the average number of MySQL questions over the last 10seconds. Everything is great so far.

But what if the resolution of this time series is lower than 10 seconds, for example, if we take …

In a MySQL master-slave high availability (HA) setup, it is important to continuously monitor the health of the master and slave servers so you can detect potential issues and take corrective actions. In this blog post, we explain some basic health checks you can do on your MySQL master and slave nodes to ensure your setup is healthy. The monitoring program or script must alert the high availability framework in case any of the health checks fails, enabling the high availability framework to take corrective actions in order to ensure service availability.

MySQL Master Server Health Checks

We recommended that your MySQL master monitoring program or scripts runs at frequent intervals. Assuming that the monitoring script is running on the same server as your …

If you’re using MySQL’s Performance Schema you may use “query digests” as IDs to identify specific query patterns in the events_statements_summary_by_digest Performance Schema Table.

You might assume these hashes are stable between different versions, so, for example, when upgrading from MySQL 5.7 to MySQL 8, you can compare the query response time and other execution details for the same hashes as part of your upgrade process and have confidence that queries run the same (or better) after the upgrade.   Unfortunately, you can’t.

For some reason, the hashing algorithm is different between MySQL 5.6,  MySQL 5.7, and MySQL 8 even for the most trivial queries:

MySQL 5.6

mysql> select digest,digest_text  from events_statements_summary_by_digest where digest_text='commit';
+----------------------------------+-------------+
| digest                           | digest_text | …