Skip to main content

Percona Toolkit

Overview​

Percona Toolkit is a collection of command-line utilities for administering and troubleshooting MySQL-compatible databases (MySQL, Percona Server, and often MariaDB), covering tasks like query analysis, online schema changes, replication consistency checks, and operational diagnostics. (docs.percona.com)

Background and history​

Percona Toolkit grew out of real-world support and operations needs where ad-hoc scripts were common but risky and inconsistent. The toolkit provides standardized, documented tools for complex database tasks that are error-prone to do manually, especially under production pressure. (docs.percona.com)

Adoption and where it’s commonly used​

Percona Toolkit is widely used in production environments that run MySQL-compatible databases and need operational tooling for:

  • Performance triage (slow query analysis, process inspection)
  • Safer schema changes for large tables
  • Replication drift detection and correction
  • Diagnostics collection during incidents (docs.percona.com)

Maintained by​

Maintained by Percona and the Percona Toolkit open-source community. (GitHub)

Best when to use​

  • You operate MySQL/MariaDB at scale and need reliable “battle-tested” operational tools.
  • You regularly analyze slow queries and want repeatable reports.
  • You need online schema changes with controlled impact.
  • You need to verify replication consistency (and optionally repair drift).
  • You want standardized tooling instead of one-off scripts.

Not suitable when​

  • You need deep application-level tracing/APM rather than database-level tooling.
  • You cannot accept any production risk from operational tools (some toolkit commands can be disruptive if misused).
  • You rely exclusively on non-MySQL engines (PostgreSQL-only environments, etc.).
  • Your environment forbids the privileges required for safe use (TRIGGER, ALTER, PROCESS, replication privileges).

Compatibility notes​

  • Package availability and service integration vary by distribution and repository.
  • Some tools and behaviors are sensitive to database version and replication mode. Recent releases explicitly added support for newer MySQL versions (for example, MySQL 8.4 support in the 3.7.x era). (Percona Community)
  • TLS support and secure connection behavior can depend on both toolkit version and MySQL client library behavior; review release notes for your installed version. (Percona Community)

Concepts and how it works​

Percona Toolkit tools typically fall into these categories:

CategoryExamplesPrimary output
---
Observability and summariespt-summary, pt-mysql-summaryRead-only reports
Query and log analysispt-query-digest, pt-index-usageAggregated analytics
Replication verification/repairpt-table-checksum, pt-table-syncDrift reports and optional fixes
Online schema changespt-online-schema-changeControlled DDL workflow
Housekeeping and archivingpt-archiverPurge/archive results

Most tools connect using standard MySQL client options (host/user/password/socket) and either:

  • read from logs/files, or
  • query server metadata and performance tables, or
  • execute controlled SQL sequences.

Installation​

Debian/Ubuntu​

sudo apt update
sudo apt install percona-toolkit

RHEL/CentOS/Rocky/AlmaLinux/Fedora​

sudo dnf install percona-toolkit

Older systems may use:

sudo yum install percona-toolkit

Installing from Percona repositories​

When your distro repo doesn’t ship a suitable version, install via Percona repositories using percona-release, then install percona-toolkit with your package manager. Follow the Percona repository installation guidance for your OS. (docs.percona.com)

Verify installation​

pt-summary --version
pt-mysql-summary --version
pt-query-digest --version

List installed toolkit commands:

compgen -c | grep -E '^pt-' | sort

Connection and authentication​

Prefer secure credential handling​

Avoid passing passwords directly on the command line (they can appear in shell history and process listings).

Recommended patterns:

  • Use a MySQL option file (~/.my.cnf) with restricted permissions.
  • Use mysql_config_editor where supported by your MySQL client stack.
  • Use --ask-pass when available for interactive entry.

Example ~/.my.cnf:

[client]
user=dbadmin
password=REDACTED
host=127.0.0.1

Lock down permissions:

chmod 600 ~/.my.cnf

Common DSN patterns​

Many Percona Toolkit tools accept DSN-like arguments.

Examples:

  • Local socket: S=/var/run/mysqld/mysqld.sock
  • TCP: h=127.0.0.1,P=3306,u=dbadmin,p=REDACTED

Use TLS where required by policy; confirm your toolkit version and MySQL client support for TLS options in your environment. (Percona Community)

High-impact safety rules​

Production risk

Some Percona Toolkit commands can lock tables, create triggers, generate heavy load, or modify data. Before using write-capable tools in production:

  • Verify backups and restore procedures
  • Test on a staging copy of production data
  • Start with read-only/dry-run modes
  • Set explicit load limits and throttles
  • Schedule changes during a maintenance window when possible (docs.percona.com)
Replication considerations

Tools that check or change data (checksums, sync, schema changes) must be evaluated against your replication topology, filters, and failover tooling. Validate on a replica first when possible.

Common commands and daily workflows​

Baseline server and MySQL health (read-only)​

System summary​

sudo pt-summary

MySQL configuration and status summary​

sudo pt-mysql-summary

Use these reports to capture a “known-good baseline” after changes (upgrades, config tuning, cache adjustments).

Slow query analysis with pt-query-digest​

pt-query-digest aggregates queries from logs and provides ranked summaries by total time, count, and other dimensions.

Analyze a slow query log​

sudo pt-query-digest /var/log/mysql/slow.log > /tmp/slow-report.txt

Review top offenders quickly​

sed -n '1,200p' /tmp/slow-report.txt

Operational notes:

  • Run off-peak when logs are large.
  • For ongoing operations, rotate slow logs regularly and digest a bounded time window.

Duplicate and redundant index checks (read-only)​

pt-duplicate-key-checker finds redundant or duplicate indexes that waste memory and slow writes.

pt-duplicate-key-checker --host 127.0.0.1 --user dbadmin --password 'REDACTED'
Index changes are write operations

Use this tool for reporting only; implement index changes through controlled migrations (and consider online schema change workflows for large tables).

Online schema changes with pt-online-schema-change​

pt-online-schema-change performs DDL by creating a shadow table, copying rows in chunks, applying triggers to capture changes, and swapping tables at the end. (Debian Manpages)

Use dry-run first

Always run --dry-run before --execute, and ensure you understand required privileges and trigger behavior. Large tables and busy write workloads increase risk. (docs.percona.com)

  1. Confirm table size and write activity (read-only).
  2. Run --dry-run.
  3. Run with strict throttles and load limits.
  4. Monitor replication lag and application latency.
  5. Keep a rollback plan.

Example: add an index with a controlled run​

Dry run:

pt-online-schema-change \
  --alter "ADD INDEX idx_meta_key (meta_key)" \
  --dry-run \
  --max-load Threads_running=25 \
  --critical-load Threads_running=60 \
  --chunk-time 0.5 \
  D=wordpress,t=wp_postmeta,h=127.0.0.1,u=dbadmin,p=REDACTED

Execute:

pt-online-schema-change \
  --alter "ADD INDEX idx_meta_key (meta_key)" \
  --execute \
  --max-load Threads_running=25 \
  --critical-load Threads_running=60 \
  --chunk-time 0.5 \
  D=wordpress,t=wp_postmeta,h=127.0.0.1,u=dbadmin,p=REDACTED

Operational guidance:

  • Prefer running on a replica first (same schema/data shape).
  • Ensure the table has a suitable key for chunking; tables without a good key can be problematic.
  • Avoid running multiple online schema changes concurrently on the same instance.

Replication consistency: pt-table-checksum and pt-table-sync​

pt-table-checksum (detect drift)​

This tool checks whether replicas match the primary by computing chunked checksums.

Replication and load risk

pt-table-checksum can generate significant load. Run during low traffic, apply throttles, and validate that your replication filters and topology are compatible before execution.

Example (typical primary execution):

pt-table-checksum \
  --databases wordpress \
  --max-load Threads_running=25 \
  --critical-load Threads_running=60 \
  --chunk-time 0.5 \
  h=127.0.0.1,u=dbadmin,p=REDACTED

pt-table-sync (repair drift)​

Use only after you fully understand the drift source and you have verified backups.

Safer approach:

  • Generate and review SQL to be applied (print-only), then apply through your controlled change process.

Example (print changes):

pt-table-sync \
  --print \
  --databases wordpress \
  h=127.0.0.1,u=dbadmin,p=REDACTED

Archiving/purging with pt-archiver​

pt-archiver can delete or archive rows matching a condition in small batches.

Data loss risk

pt-archiver can permanently delete data. Always:

  • Start with --dry-run
  • Use --limit and --commit-each
  • Verify the --where clause on a SELECT first
  • Test on a staging copy

Example (dry run with a safe SELECT check first):

# Verify what will match
mysql -e "SELECT COUNT(*) FROM wordpress.wp_actionscheduler_logs WHERE log_date_gmt < NOW() - INTERVAL 30 DAY;"

# Dry run archive/delete plan
pt-archiver \
  --source h=127.0.0.1,u=dbadmin,p=REDACTED,D=wordpress,t=wp_actionscheduler_logs \
  --where "log_date_gmt < NOW() - INTERVAL 30 DAY" \
  --dry-run \
  --limit 1000 \
  --commit-each

Practical use cases for WordPress operations​

1) After a plugin update, site slows down​

  1. Confirm resource pressure (CPU, I/O, memory) using system tools.
  2. Digest slow logs to identify top queries:
sudo pt-query-digest /var/log/mysql/slow.log | sed -n '1,200p'

  1. If a specific query pattern dominates:

    • Check indexing
    • Check plugin behavior
    • Implement caching or reduce query frequency

2) Database bloat in logs/transients tables​

  • Identify large tables and growth patterns with SQL (read-only).
  • Use pt-archiver to purge in small batches with a dry run first.

3) Add indexes without long locks​

  • Use pt-online-schema-change with strict throttles.
  • Validate on a replica first.
  • Monitor replication and application error rates during execution.

Troubleshooting​

Toolkit can’t connect to MySQL​

Common causes:

  • Wrong socket/host/port
  • Missing privileges
  • TLS requirement mismatch

Safe checks:

mysql --host=127.0.0.1 --user=dbadmin -p -e "SELECT 1;"

Then re-run the toolkit command with matching connection parameters.

pt-online-schema-change fails early​

Common causes:

  • Missing privileges (TRIGGER/ALTER/CREATE)
  • Table design issues that prevent safe chunking
  • Triggers already present or incompatible trigger policies

Operational fixes:

  • Verify grants (read-only):
pt-show-grants --host 127.0.0.1 --user dbadmin --password 'REDACTED'
  • Re-run with --dry-run and review output.
  • Consider alternative online DDL mechanisms supported by your MySQL version if appropriate.

pt-table-checksum impacts performance​

Mitigations:

  • Lower chunk time and enforce load thresholds
  • Run during off-peak hours
  • Limit to specific databases/tables

Security notes​

  • Use least-privilege accounts for each tool:

    • Read-only tools should use read-only grants.
    • Write-capable tools should use scoped, time-limited privileges.

  • Avoid passwords in command history and process lists; prefer option files or interactive entry.

  • Restrict network access to MySQL (private networks, local sockets, security groups).

  • Ensure encrypted connections where required by policy; validate toolkit/client TLS behavior for your installed version. (Percona Community)

Quick reference​

Common tools by task​

TaskToolSafety level
--
System baseline reportpt-summaryRead-only
MySQL baseline reportpt-mysql-summaryRead-only
Slow query rankingpt-query-digestRead-only
Find redundant indexespt-duplicate-key-checkerRead-only
Online schema changept-online-schema-changeHigh risk
Replication drift detectionpt-table-checksumMedium/high load
Replication drift repairpt-table-syncData-changing risk
Batch purge/archivept-archiverData loss risk

Safe-first checklist for high-risk tools​

  • Capture a baseline with pt-summary and pt-mysql-summary
  • Confirm backups and restore path
  • Run dry-run/print-only mode first
  • Set throttles (--max-load, --critical-load, chunk controls)
  • Monitor replication lag and application latency
  • Keep rollback and emergency stop procedures ready
Placeholder

This page is ready for daily use; expand with your environment-specific defaults (standard DSN, standard thresholds, and approved maintenance windows) as part of your runbook.