As part of the release of Percona Monitoring and Management, we set up an extensive online Percona Monitoring and Management demo at pmmdemo.percona.com. The demo is pretty self-explanatory and easy to explore, but you will probably find it a lot more interesting with some explanation. This post provides some guidance.
The PMM demo has a wide variety of MySQL and MongoDB servers set up. Besides its use as a public demo, it shows how things look with different software versions (what works and what does not). Different versions have different quirks and information available.
If you go to System Overview you will see the dropdown of different system names:
For “MySQL,” we’re using fairly self-explanatory names:
- mysql57r is MySQL 5.7 (which is set up as a replica)
- mdb101 is MariaDB 10.1
- pmm-server is the local server running the PMM server component
- pxc56-1 is Percona XtraDB Cluster (node 1)
The sd-XXXXX entries are MongoDB nodes that we haven’t renamed yet.
To keep things interesting (for me at least), all the MySQL nodes are running different workloads. The workloads are available in the GitHub repository if you want to play with PMM on your host and need workloads to generate graphs.
Percona Server for MySQL 5.7 (ps57) currently has the most complex workload, touching InnoDB, MyISAM and TokuDB storage engines. It also has a special workload that shows how the Query Cache stalls with periodic updates, etc. This populates all the graphs, but also it might make them very crowded for viewing (see the TokuDB Dashboard):
There is a mix of workloads set up for all the storage engines to illustrate the variety that typically exists in real systems: sysbench is running and regularly injecting a small number of transactions to generate a light load, plus a periodic heavy reporting query kicks the box. I have also added a heavy spike of updates once an hour to trigger slave lag. It is very interesting to see how this all plays out if you know what to look for.
For example, we can see that five minutes of a very heavy update load causes a replication lag of up to 15 minutes (see the Replication Dashboard):
Or watch balancing the buffer pool content changes get populated with mostly dirty pages during a short spike of heavy updates (InnoDB Dashboard):
You might notice that the host and interval are constantly reset when switching between dashboards. Grafana was designed this way to keep dashboards independent. A better way to navigate is to use Menu in the top right corner to switch dashboards. It preserves selected hosts and time interval settings. This can be a very helpful tool to drill-down into the details for specific period:
Our demo workload is not as advanced for MongoDB. But you can still get a very useful MongoDB cluster overview:
Or drill down into the performance of specific replica sets or individual nodes:
Another part of the PMM demo you should consider exploring is the Query Analytics component. This component shows you the queries that are causing the most load on the system (together with the load that corresponds to the query), how frequent the query occurs (QPS) and the query response time details:
This view helps you understand which queries might need optimization. Typically, it is a good idea to look at queries generating a lot of load, or queries that have response times that don’t always meet your application response time requirements. The 95 percentile and Max Latency scores show these points, and are visible when you mouse over the latency bar:
Things get really interesting when you click on a query that is a candidate for optimization. The dashboard shows everything you need to know to optimize the query:
You can see a number of per-query metrics:
- Amount of created IO
- Number of sent and examined rows
- How has it changed over time
You also can see overall values. For example, this query spent 73% of its execution time waiting on disk IO, which means it would benefit by either getting a faster disk or more memory.
The EXPLAIN output (as well as CREATE TABLE for the table in question) and information about the table size and index cardinality is typically all the information you need whenever there are some missing indexes or queries that need optimization some other way:
Finally, if you need to know more about hardware (or virtual instances) and server configurations, you can click on the server information icon in the left corner. The settings icon shows the information about the system and MySQL instance (as pt-summary and pt-mysql-summary would provide it):
Much of this information is available as graphs, but it is often more informative to see it this way.
There is much more to explore in the PMM demo, but this blog post is getting way too long! I’ll write about it at another time.
Most software offering a comparable level of features is commercially licensed or deployed as subscription-only, cloud-based software. Percona Monitoring and Management (PMM) software is 100% free and open source.
Take a turn with the demo. Then download it and explore it in your environment!