Q&A on Webinar “Using PMM to Identify and Troubleshoot Problematic MySQL Queries”

Problematic MySQL QueriesHi 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

RED Method for MySQL Queries

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 if I do ls -l in datadir?

Yes, I did make this suggestion, but after further research, I ought to qualify my response. TLDR; you will only see useful information in the CREATE_TIME field.

As per the MySQL Manual for SHOW TABLE STATUS which defines the fields CREATE_TIME, UPDATE_TIME, and CHECK_TIME, you will find that only CREATE_TIME for InnoDB tables provides accurate information for when the table was originally created.  You will see either NULL or a recent-ish timestamp value for UPDATE_TIME, but this cannot be trusted as features such as InnoDB Change Buffering will skew this value, and thus the timestamp will not necessarily reflect when the SQL write happened, but only when the delayed write to the ibd file occurred.  Further, if you have your table stored in the system tablespace (like the example below) you will continue to see NULL for the UPDATE_TIME.

To your point about ls -l on the datadir, or the stat command: you cannot rely on this information at any level of accuracy.  Since ls -l is equivalent to the Modify field of the output of stat, we’ll use this  command to show the behaviour once you create the table, and what it reports after you restart mysqld on your datadir.  So let’s see this in action via an example.

Before restarting you’ll notice that Access time is equivalent to what Percona Server for MySQL reports for CREATE_TIME:

However after you restart mysqld, you will no longer be able to tell the create time as MySQL will have updated the Access time on disk, and now the values don’t have very much material relevance as to the access patterns on the table.

Can I use Percona Monitoring and Management (PMM) with an external bare-metal server of Clickhouse?

PMM leverages an instance of Clickhouse inside the docker container (or your AMI, or your OVF destination) for storage of MySQL query data.  At this time we are shipping PMM as an appliance and therefore we don’t provide instructions on how to connect Query Analytics to an external instance of Clickhouse.

If the question is about “can I monitor Clickhouse database metrics using PMM” the answer is Yes absolutely you can!  In fact, PMM will work with any of the Prometheus Exporters and the way to enable this is via the feature we call External Services – take a look at our Documentation for the correct syntax to use!  Usage of External Services will get you pretty metrics, whereas Grafana (which is what we use in PMM to provide the visuals) already contains a native Clickhouse datasource which you can use to run SQL queries from within PMM against Clickhouse.  Simply define the datasource and you’re done!

All PMM2 features are compatible with MySQL 8?

The latest release of PMM 2.14 (January 28th, 2021) supports MySQL 8 and Percona Server for MySQL 8.  PMM now supports not only traditional asynchronous replication but also MySQL InnoDB Group Replication, and of course Percona’s own Percona XtraDB Cluster (PXC) write-set replication (aka wsrep via Galera).  When using Query Analytics with Percona Server for MySQL or PXC, you’ll also benefit from the Extended Slow Log Format, which provides for a very detailed view of activity at the InnoDB storage engine level:

PMM Query Analytics Detail screen

I added several dbs to PMM, however the QAN shows only few and not all. What could be an issue? How do I approach you for Percona support on such things?

There could be a few things going on here that you’ll want to review from Percona’s Documentation:

  1. Do you have a user provisioned with appropriate access permissions in MySQL?
  2. If sourcing from PERFORMANCE_SCHEMA, is P_S actually enabled & properly configured?
  3. Is long_query_time and other slow log settings properly configured to write events?

Slow Log Configuration

These are the recommended settings for the slow log on Percona Server for MySQL. I prefer the slow log vs P_S because you get the InnoDB storage engine information along with other extended query properties (which are not available in upstream MySQL, nor in RDS, or via PERFORMANCE_SCHEMA):

User Permissions

You’ll want to use this permissions for the PMM user:

PERFORMANCE_SCHEMA

Using PERFORMANCE_SCHEMA is less detailed but comes with the benefit that you’re writing and reading from an in-memory only object, so you’re saving IOPS to disk. Further if you’re in AWS or other DBaaS you generally don’t get raw access to the on-disk slow log, so PERFORMANCE_SCHEMA can be your only option.

PERFORMANCE_SCHEMA turned on

By default, the latest versions of Percona Server for MySQL and Community MySQL ship with PERFORMANCE_SCHEMA enabled by default, but sometimes users disable it.  If you find it is disabled, a restart of mysqld is required in order to enable.

You want to make sure your my.cnf includes:

You can check via a running MySQL instance by executing:

PERFORMANCE_SCHEMA configuration

You’ll need to make sure you enable the following consumers so that mysqld writes events to the relevant P_S tables:

Are there any specific limitations when using PMM for monitoring AWS Aurora?

The most significant limitation is that you cannot access the Slow Log and thus must configure for PERFORMANCE_SCHEMA as the query datasource.  See the previous section on how to configure PERFORMANCE_SCHEMA as needed for PMM Query Analytics.

One great feature of PMM is our native support for AWS Aurora. We have a specific dashboard for those Aurora-only features:

PMM MySQL Amazon Aurora Details dashboard

Thanks for attending!

If you attended (or watched the video), please share via comments any takeaways or further questions you may have!   And let me know if you enjoyed my jokes 🙂

Share this post

Leave a Reply