While the standard Percona Monitoring Plugins provide a robust baseline, they often omit critical database performance indicators such as Queries Per Second (QPS), Transactions Per Second (TPS), and Input/Output Operations Per Second (IOPS). To maintain comprehensive observability, the monitoring configuration must be extended to include these metrics. The following implementation details the configuration of TPS monitoring, which requires capturing transaction commit and rollback counts alongside server uptime.

Verifying MySQL Status Variables

Prior to modifying the monitoring scripts, verify the specific status variables within the MySQL environment to ensure accurate naming and data types.

SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';
SHOW GLOBAL STATUS LIKE 'Uptime';

Modifying the PHP Collection Script

The core data collection logic resides in ss_get_mysql_stats.php. This script uses a key-value map to cache status variables. To incorporate the required TPS components, append the following entries to the $keys array. New internal codes (e.g., 'x9', 'xa', 'xb') are assigned to avoid conflicts with existing definitions.

// File: /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php

$keys = array(
    // ... existing keys ...
    
    // Custom TPS component metrics
    'Txn_Commit_Count'     => 'x9',
    'Txn_Rollback_Count'   => 'xa',
    'Server_Uptime_Seconds'=> 'xb',
);

Configuring Zabbix Agent Parameters

The Zabbix agent requires explicit instructions to fetch these new values. Update the userparameter_percona_mysql.conf file by adding the following UserParameter directives. These directives link the Zabbix item keys to the wrapper script and the corresponding internal codes defined in the PHP script.

# File: /usr/local/zabbix/etc/zabbix_agentd.conf.d/userparameter_percona_mysql.conf

UserParameter=db.percona.commit,/usr/local/bin/get_mysql_stats_wrapper.sh x9
UserParameter=db.percona.rollback,/usr/local/bin/get_mysql_stats_wrapper.sh xa
UserParameter=db.percona.uptime,/usr/local/bin/get_mysql_stats_wrapper.sh xb

Validation and Service Restart

After saving the configuration changes, validate the script output manually to ensure the agent can retrieve the data correctly. Subsequently, restart the Zabbix agent service to apply the new parameters.

# Test the wrapper script
/usr/local/bin/get_mysql_stats_wrapper.sh x9
/usr/local/bin/get_mysql_stats_wrapper.sh xa
/usr/local/bin/get_mysql_stats_wrapper.sh xb

# Restart the agent
systemctl restart zabbix-agent
systemctl status zabbix-agent

Zabbix Frontend Configuration

Navigate to the Zabbix web interface to create the monitoring items. Create three separate items of type Zabbix agent for the Commit, Rollback, and Uptime metrics using the keys defined in the agent configuration (e.g., db.percona.commit).

Preprocessing Setup:
Since Com_commit and Com_rollback are cumulative counters, they must be converted to rates to be useful for performance analysis. For each of these items, add a Change per second preprocessing step. The Uptime item can be monitored as a raw numeric value, though it is not strictly required for the TPS calculation if using rate-based counters.

Creating the Calculated TPS Metric

The final TPS metric is derived by summing the per-second rates of commits and rollbacks. Create a new item with the type Calculated.

• Name: MySQL TPS
• Type: Calculated
• Formula: last("db.percona.commit") + last("db.percona.rollback")