In the previous posts of this series, I presented how the Percona Support team approaches the analysis and troubleshooting of a MySQL server using a tried-and-tested method supported by specific tools found in the Percona Toolkit:
Inspecting MySQL Servers Part 1: The Percona Support Way
Inspecting MySQL Servers Part 2: Knowing the Server
Inspecting MySQL Servers Part 3: What MySQL?
Inspecting MySQL Servers Part 4: An Engine in …
[Read more]Some of you may use MySQL’s asynchronous replication feature called Semisynchronous Replication (aka semi-sync), and now with the MySQL Semi-Sync Summary Dashboard + Percona Monitoring and Management (PMM), you can see the most important metrics! Refer to the Install & Usage steps for deployment details (note you need Replication Set defined!).
What is Semisynchronous Replication
When enabled, Semisynchronous Replication instructs the Primary to wait until at least one replica has received and logged the event to the replica’s local relay log before completing the COMMIT on a transaction. This provides a higher level of data integrity because now it is known that the data exists in two places. This feature ensures a …
[Read more]Optimizing MySQL performance and troubleshooting MySQL problems are two of the most critical and challenging tasks for MySQL DBAs. The databases powering applications need to be able to handle changing traffic workloads while remaining responsive and stable in order to deliver an excellent user experience. Further, DBAs are also expected to find cost-efficient means of solving these issues.
In this webinar, we will demonstrate the advanced options of Percona Monitoring and Management V.2 that enable you to solve these challenges, which are built on free and open-source software. We will look at specific, common MySQL problems and review them.
Please join Peter Zaitsev on Wednesday, April 14th, 2021, at 11 am EDT for his webinar Optimize and Troubleshoot MySQL using Percona Monitoring and Management (PMM).
…
[Read more]MySQL Shell is the advanced MySQL client, which has many excellent features. In this blog, I am going to explain the MySQL shell commands “\show” and “\watch”. Both commands are very useful to monitor the MySQL process. It provides more insights into the foreground and background threads as well.
Overview
“\show” and “\watch” are the MySQL shell commands, which can be executed using the Javascript (JS), Python (Py), and SQL interfaces. Both commands are providing the same information, but the difference is you can refresh the results when using the command “\watch”. The refresh interval is two seconds.
Below are the available options you can use with the “\show” …
[Read more]Point-In-Time Recovery (PITR) for MySQL databases is an important feature that is essential and covers common use cases, like a recovery to the latest possible transaction or roll-back the database to a specific date before some bad query was executed. Percona Kubernetes Operator for Percona XtraDB Cluster (PXC) added support for PITR in version 1.7, and in this blog post we are going to look into the technical details and decisions we made to implement this feature.
Architecture Decisions Store Binary Logs on Object Storage
MySQL uses …
[Read more]Hi and thanks to all who attended my webinar on Tuesday, January 26th titled Using PMM to Identify & Troubleshoot Problematic MySQL Queries!
Like we do after all our webinars, we compile the list of questions that were answered verbally and also those that were posed yet remained unanswered since we ran out of time during the broadcast. Before we get to the questions, I wanted to make sure to include a link to the RED Method for MySQL Queries by Peter Zaitsev, Percona’s CEO:
https://grafana.com/grafana/dashboards/12470
Hi Michael, you suggested that table create and update times should be ignored. Surely these values come from information_schema.tables? Does that not reflect what I would see …
[Read more]Join us as Michael Coburn, Percona Product Manager, discusses two methods to identify and troubleshoot problematic MySQL queries using the RED Method and Percona Monitoring and Management (PMM) Query Analytics. He will also highlight specific Dashboards in PMM that visualize the rate, errors, and duration of MySQL events that may be impacting the stability and performance of your database instance.
Please join Michael Coburn, Product Manager, Percona, on Tuesday, January 26th, 2021 at 2:30 pm for his webinar “Using Percona Monitoring and Management to Identify and Troubleshoot Problematic MySQL Queries”.
If you can’t attend, …
[Read more]At work, I am in an ongoing discussion with a number of people on the Observability of Outliers. It started with the age-old question “How do I find slow queries in my application?” aka “What would I want from tooling to get that data and where should that tooling sit?”
As a developer, I just want to automatically identify and isolate slow queries!
Where I work, we do have SolarWinds Database Performance Monitor aka Vividcortex to find slow queries, so that helps. But that collects data at the database, which means you get to see slow queries, but maybe not application context.
There is also work done by a few developers which instead collects query strings, query execution times and query counts at the application. This has access to the call stack, so it can tell you which code generated the query that was slow.
It …
[Read more]So you’ve just added some more memory to your MySQL server; now what? If you’ve been around the MySQL block for a while, you know that nothing is automatically changed to take advantage of this new system RAM. Let’s have a look at a few parameters you would want to adjust.
InnoDB Parameters innodb_buffer_pool_size
The InnoDB buffer pool is “…the memory area that holds cached InnoDB data for both tables and indexes.” This parameter is probably the #1 tuning parameter in MySQL. If your buffer pool is too small, then InnoDB must spend extra CPU/Disk time, loading, and unloading pages in/out of memory. This is time better spent executing your queries.
The default size of this cache is 128MB; woefully small for any serious database. Increasing the size of this cache allows more frequently accessed pages to remain in memory for the fastest access. Obviously, you do not need a buffer pool which is larger than your …
[Read more]Understanding how MySQL uses memory is key to tuning it for optimal performance as well as troubleshooting cases of unexpected memory usage, i.e. when you have MySQL Server using a lot more than you would expect based on your configuration settings.
Early in MySQL history, understanding memory usage details was hard and included a lot of guesswork. Is it possible that some queries running require a large temporary table or allocated a lot of memory for stored user variables? Are any stored procedures taking an unexpectedly high amount of memory? All could be reasons for excessive MySQL memory usage, but you would not easily see if that is just the case.
All that changed with MySQL 5.7, which added memory instrumentation in Performance Schema, and with MySQL 8.0, this instrumentation is enabled by default, so you can get this data from pretty much any running instance.
If you’re looking for current memory …
[Read more]