Shattered Silicon Monitoring (SSM) is a fork of Percona Monitoring and Management (PMM) v1. It contains additional features and fixes not present in PMM.

MariaDB and MySQL monitoring are a critical part of any production MySQL or MariaDB deployment. It provides the only reliable way to tune and optimize MySQL an MariaDB configuration.

Like PMM, SSM has the server docker image, and a client package. The server image consists of a minimal EL8 userspace (EL7 in PMM), Prometheus, Grafana, and a few other supporting packages. The client package consists of a selection of modified Prometheus data exporters for Linux, MySQL/MariaDB, PostgreSQL, ProxySQL and MongoDB.

Our MySQL support specialists are huge fans of PMM, particularly PMM v1, but there have been a few bugs we found annoying, and a few features that we really wished it had (and they aren’t in PMM v2, either). So we forked PMM v1 and created SSM.

Additional Features in SSM Compared to PMM

As of the initial SSM release 8.6.1.17.5.1, compared to current PMM v1 release 1.17.5

  • RDS instance monitoring supports Query Analytics data harvesting via the slow log instead of being limited to performance_schema.

This is critically important for any project where the code base makes extensive use of prepared statements (and using prepared statements and passing bound parameters is good for performance, especially if you are re-using those prepared statements multiple times) because prepared statements don’t show up in performance_schema.

We also augmented the performance_schema query harvester to try to fetch prepared statement data, but this only persists until the prepared statement is deallocated, either explicitly or by the session disconnecting. That means that the only reliable way to get the full representative data set is to fetch the data using the slow log – and we have added that feature.

  • Node and service removal directly via server inventory page.

Nodes and individual services associated with each node can now be individually deleted from the inventory page. Part of this feature exists in PMM, but only for nodes added via the server side as remote nodes.

SSM feature extends to so that all registered nodes and services are individually removable via the server side directly. The main reason for this is that in some environments it is common that servers will get built, registered for monitoring and then torn down without being deregistered and purged via client-side commands. This is particularly problematic in cases where a server fails and is unrecoverable and has to be replaced. The data will eventually expire, but the old server registration will stick around in PMM forever and keep polling the server that no longer exists. This feature allows any registered server or service to be manually deregistered and purged from the server side via the web interface.

Original PMM v1 mysqld_exporter had no visibility of some of the telemetry data with MariaDB 10.5+ because it is presented slightly differently starting with MariaDB 10.5. SSM brings updated exporters that bring visibility of this telemetry data in line with what is available for older MariaDB releases.

Bug Fixes not Present in PMM

  • Disk performance data was not present when monitoring servers running kernels newer than 4.18.x. The problem was caused by a change in /proc/diskstats format introduced in 4.18 to allow for tracking block discards for SSDs, and the original node_exporter couldn’t understand the new format.
  • Disk performance data was not present when monitoring RDS servers. The problem was caused by a labelling change in RDS monitoring data payload.
  • Database host uptime was not listed for RDS instances, though that data is available from cloudwatch.

Quick Start

This should be very familiar to anyone who has used PMM v1 before. SSM, being a fork of PMM v1, behaves very similarly.

SSM for RDS

Full detailed documentation is available here:

https://github.com/shatteredsilicon/ssm-doc/blob/1.x/docs/amazon-rds.md

Create the SSM user in MySQL:

CREATE USER 'ssm'@'%' IDENTIFIED BY 'ssm_mysql_pass';
GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD ON . TO 'ssm'@'%';
GRANT SELECT ON performance_schema.* TO 'ssm'@'%';

Configure Parameter Group:

innodb_monitor_enable=all
performance_schema=ON
log_output=FILE
slow_query_log=ON
long_query_time=0
log_slow_verbosity=query_plan,explain # MariaDB/Percona only
log_slow_admin_statements=ON
log_slow_slave_statements=ON
userstat = 1 # MariaDB / Percona only

Create the IAM:

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "Stmt1664804420",
"Effect": "Allow",
"Action": [
"rds:DescribeDBInstances",
"rds:DescribeDBParameters",
"rds:DescribeDBLogFiles",
"rds:DownloadDBLogFilePortion",
"cloudwatch:GetMetricStatistics",
"cloudwatch:ListMetrics"
],
"Resource": [
"" ] }, { "Sid": "Stmt1664804472", "Effect": "Allow", "Action": [ "logs:DescribeLogStreams", "logs:GetLogEvents", "logs:FilterLogEvents" ], "Resource": [ "arn:aws:logs:::log-group:RDSOSMetrics:"
]
}
]
}

Make sure Enhanced Monitoring is enabled for your RDS instance.

Start the SSM server on a dedicated EC2 instance (don’t use a t-class for monitoring more than one database):

#!/bin/bash

docker pull shatteredsilicon/ssm-server:latest

docker create \
-v /opt/prometheus/data \
-v /opt/consul-data \
-v /var/lib/mysql \
-v /var/lib/grafana \
--name ssm-data \
shatteredsilicon/ssm-server:latest /bin/true

docker run -d \
-p 80:80 -p 443:443\
--volumes-from ssm-data \
--name ssm \
--restart always \
-e SERVER_USER=ssm \
-e SERVER_PASSWORD=ssm_web_auth_password \
shatteredsilicon/ssm-server:latest

Add the database instance into monitoring:

  • Point your web browser at the IP address of the server where you installed SSM server
  • Go to the SSM menu in the top right
  • Select _SSM Add Instance
  • Add an Amazon RDS MySQL or Aurora MySQL Instance
  • Enter the key/secret for the IAM you created earlier
  • Once the RDS nodes are discovered, click on the blue toggle switch on the right next to the node you want to add
  • Add the MySQL username and password you created with the GRANT statements above
  • Click connect

At this point you should be good to go.

SSM for standalone MySQL / MariaDB instances

Full detailed documentation is available here:

https://github.com/shatteredsilicon/ssm-doc/blob/1.x/docs/conf-mysql.md

You will want a slightly different set of GRANT statements compared to RDS:

CREATE USER 'ssm'@'localhost' IDENTIFIED BY 'ssm_mysql_pass';
GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON . TO 'ssm'@'localhost';
GRANT SELECT ON performance_schema.* TO 'ssm'@'localhost';

Add the same configuration changes into my.cnf as for the RDS parameter group further up on this page.

Download the ssm-client package from here (we have aarch64 and x86-64 packages, they should work on EL7+ distributions):

https://dl.shatteredsilicon.net/ssm/8/RPMS/

Install the ssm-client:

yum localinstall ssm-client-*.rpm
ssm-admin config --client-name short_local_host_name --server server_ip_address --server-user=ssm --server-password=ssm_web_auth_password
ssm-admin add mysql --user ssm --password ssm_mysql_pass

Verify that everything is working:

ssm-admin list
ssm-admin check-network

At this point you should have performance and query telemetry showing up in your SSM instance.

Happy monitoring. :-)

If you find a bug, you can file an issue here:

https://github.com/shatteredsilicon/ssm-submodules/issues