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 | …

Percona Monitoring and Management (PMM) has been on the road for a while now, and it brings exciting new features and improvements. For those who are not familiar with the tool, PMM allows deep insight into the performance of applications and databases. The most crucial highlight: it is 100% open-source. Also, the source code is present on GitHub.

Back to new features and improvements. One of them, that customers were requesting, was the support for LDAP. This feature finally arrived with PMM version 2 thanks to the new version of Grafana, and I intend to demonstrate in …

Monitoring your MySQL database performance in real-time helps you immediately identify problems and other factors that could be causing issues now or in the future. It’s also a good way to determine which components of the database can be enhanced or optimized to increase your efficiency and performance. This is usually done through monitoring software and tools either built-in to the database management software or installed from third-party providers.

Prometheus is an open-source software application used for event monitoring and alerting. It can be used along with a visualization tool like Grafana to easily create and edit dashboards, query, visualize, alert on, and understand your metrics. ScaleGrid provides full admin access to your MySQL deployments – this makes it …

Debezium is providing out of the box CDC solution from various databases. In my last blog post, I have published how to configure the Debezium MySQL connector. This is the next part of that post. Once we deployed the debezium, to we need some kind of monitoring to keep track of whats happening in the debezium connector. Luckily Debezium has its own metrics that are already integrated with the connectors. We just need to capture them using the JMX exporter agent. Here I have written how to monitor Debezium MySQL connector with Prometheus and Grafana. But the dashboard is having the basic metrics only. You can build your own dashboard for more detailed monitoring.

Reference: List of Debezium monitoring metrics

Install JMX exporter in …