This document outlines the strategic direction and future development plans for MySQLTuner-perl. Our mission is to provide the most stable, portable, and reliable performance tuning advisor for MySQL-compatible databases.
To ensure consistency and high-density development, the following roles are defined for roadmap orchestration:
- Owner: Jean-Marie Renouard (@jmrenouard) - Ultimate authority on the project, constitution, and core mission.
- Product Manager: Antigravity (AI Agent) - Responsible for backlog management, specification design, and execution tracking of the roadmap items.
- Release Manager: Antigravity (AI Agent) - Responsible for technical validation, testing orchestration, and unified release cycle execution.
- Production Stability & Safety: All recommendations must be verified and safe for production.
- SQL Modeling & Schema Design: Beyond operational tuning, provide deep insights into database architecture.
- Zero-Dependency Portability: Maintain single-file architecture with core-only dependencies.
- Modern Ecosystem Support: Seamless integration with Containers (Docker/K8s) and Cloud providers.
- Metadata-Driven CLI Options: Refactored option parsing to centralize defaults, validation, and documentation.
- Enhanced SQL Modeling: Expanded diagnostic checks for Foreign Key type mismatches, missing indexes, and schema sanitization.
- Structured Error Log Ingestion: Supported
performance_schema.error_logfor diagnostic ingestion (MySQL 8.0+). - Refined Reporting: Improved data richness in the "Modeling Analysis" tab.
| Item | Status |
|---|---|
| System Call Optimization | [x] Replaced awk, grep, hostname, uname, sysctl with native Perl. |
| Native /proc Parsing | [x] Implemented native parsing for cpuinfo, meminfo, swappiness. |
| Index Audit 2.0 | [x] Integrated performance_schema for redundant/unused index detection. |
| Observability Log Ingestion | [x] Support for syslog, journald, and performance_schema.error_log. |
| Transactional Contention | [x] Detect isolation levels and long-running transactions. |
| Buffer Pool Advisory | [x] More granular analysis of InnoDB Redo Log Capacity based on RAM/Writes. |
- Infrastructure-Aware Tuning: Detect storage types (NVMe/SSD) and hardware architectures (ARM64/Graviton).
- MySQL 9.x Full Compatibility: Support for removed variables and
mysql_native_passwordelimination. - Authentication Plugin Auditing: Detect insecure plugins (SHA-1 based
mysql_native_password) and recommend migration paths (caching_sha2_password,ed25519). - Sysbench Metrics Integration: Automated baseline capture and performance comparison within the report.
- Multi-Cloud Autodiscovery: Automated detection of RDS, GCP, and Azure specific performance flags and optimizations.
- Query Anti-Pattern Detection: Use
performance_schemato identify non-SARGable queries andSELECT *abuse. - [/] Modular Reporting Engine: (In Progress) Refactor Jinja2 templates for dynamic section injection.
- [/] Historical Trend Analysis: (Experimental) Allow the script to ingest previous run data to identify performance regressions.
- [/] Smart Migration LTS Advisor:
- Automated pre-upgrade risk reports (variable removal, deprecation notices).
- Compatibility audit for SQL modes, character sets, and version-specific engine changes.
- Weighted Health Score:
- Unified KPI (0-100) aggregating findings from Security, Performance, and Resilience.
- Comparative scoring against previous runs or established industry baselines.
- [/] Predictive Capacity Planning:
- Data growth forecasting based on binlog throughput and table statistics.
- Memory headroom analysis for traffic peak forecasting.
- [/] Cluster & Replication Intelligence:
- Root cause analysis for replication lag (IO/SQL thread contention).
- GTID consistency checks and multi-source replication tuning.
- [/] Security Hardening 2.0:
- Version-based CVE exposure detection (community-fed database).
- Advanced encryption-at-rest (TDE) and SSL/TLS cipher suite validation.
- [/] Guided Auto-Fix Engine:
- Interactive mode to simulate configuration changes.
- Generation of ready-to-use
SET GLOBALormy.cnfsnippets.
- InnoDB Internals 3.0:
- [/] I/O Pressure & Flushing Advisor: Combined analysis of
innodb_io_capacity,Innodb_buffer_pool_wait_free, and adaptive flushing metrics to prevent I/O stalls. - Read-Ahead Efficiency Audit: Measure
Innodb_buffer_pool_read_ahead_evictedvsInnodb_buffer_pool_read_aheadto optimizeinnodb_read_ahead_threshold. - Deadlock & Contention Analytics: Historic deadlock tracking via
performance_schemawith specific table-level contention reports. - Modern Storage Alignment: Deep audit of
innodb_doublewrite_pagesalignment (128 for MySQL 8.4+),innodb_use_fdatasyncfor syscall reduction, andinnodb_flush_method.
- [/] I/O Pressure & Flushing Advisor: Combined analysis of
- Resource Isolation & Multi-Tenancy:
- NUMA-Aware Memory Allocation: Verification of
innodb_numa_interleaveand system memory controller balance. - Temp & Undo Lifecycle Manager: Proactive advisory for MariaDB temporary tablespace online truncation (
innodb_truncate_temporary_tablespace_now) and MySQL undo health.
- NUMA-Aware Memory Allocation: Verification of
- Adaptive Intelligence:
- Read-Ahead & Change Buffer Optimization: Dynamic recommendation to disable legacy features (
innodb_change_buffering,innodb_adaptive_hash_index) based on workload patterns. - Purge Lag Prevention: Automated detected of purge lag (
Innodb_history_list_length) and recommendation forinnodb_purge_threadsscaling.
- Read-Ahead & Change Buffer Optimization: Dynamic recommendation to disable legacy features (
- Distributed Consistency & Performance:
- Group Replication Health Audit: Detailed analysis of
MEMBER_STATE,MEMBER_ROLE, andMEMBER_VERSIONviaperformance_schema.replication_group_members. - Advanced Flow Control Tuning: Precise monitoring of Certification (
COUNT_TRANSACTIONS_IN_QUEUE) and Applier (COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE) queues. - Certification Conflict Analytics: Quantitative detection of transaction local rollbacks (> 5% threshold) for Multi-Primary conflict troubleshooting.
- Group Replication Health Audit: Detailed analysis of
- Cluster Resilience & Topology Optimization:
- Inter-Node Latency Impact: Analysis of how network performance affects the group consensus and triggers write throttling.
- Communication Message Cache: Verification of
group_replication_message_cache_sizeagainst system RAM to prevent OOM during network partitions. - Auto-Recovery Channel Tuning: Optimization of incremental state transfers (IST) vs SST during member re-joining.
- HA Ecosystem & Proxy Support:
- MySQL Router Awareness: (Experimental) Detection of Router-mediated connections via
performance_schema.threadsmetadata. - Quorum Integrity Framework: Alignment check for
unreachable_majority_timeoutand partition handling configurations. - MTR (Multi-Threaded Replication) Scaling: Dynamic advisory for
slave_parallel_workersbased on cluster apply lag.
- MySQL Router Awareness: (Experimental) Detection of Router-mediated connections via
- Data Consistency & GTID Integrity:
- GTID Gap Analysis: Detection of non-contiguous global transaction identifiers and missing transactions across the replication chain.
- Consistency Enforcement Audit: Verification of
enforce_gtid_consistency,gtid_mode=ON, andbinlog_format=ROWfor all nodes.
- Throughput & Parallelism Optimization:
- Parallel Applier (MTR) Tuning: Advanced monitoring of worker thread saturation and busy-wait distribution.
- Dependency Tracking Analysis: Verification of dependency tracking type (
COMMIT_ORDERvsWRITESETin MySQL) andslave_parallel_mode(MariaDB).
- Network & Durability Enhancements:
- Binary Log Compression Audit: Monitoring efficiency and CPU impact of
binlog_transaction_compression(MySQL 8.0.20+). - Binlog Cache Deep-Dive: Analysis of
Binlog_cache_disk_useratio to detect large transactions causing disk stalls. - Semi-Sync Safety Check: Dynamic analysis of semi-synchronous wait points (
AFTER_SYNCvsAFTER_COMMIT) and fallback triggers. - Multi-Source Channel Monitoring: Full observability for multi-master and multi-channel replication topologies.
- Binary Log Compression Audit: Monitoring efficiency and CPU impact of
- Synchronous Efficiency & Streaming:
- Streaming Replication Audit: Observability for large transaction fragments (
wsrep_streaming_log_writes) and their I/O footprint (MariaDB 10.4+). - Gcache Lifecycle Optimization: Advanced sizing advisory for
gcache.sizevs write load to maximize IST success.
- Streaming Replication Audit: Observability for large transaction fragments (
- Conflict & Performance Diagnostics:
- Certification Failure Deep-Dive: Quantitative analysis of brute-force aborts (
wsrep_local_bf_aborts) and certification conflicts. - Cluster-Wide Flow Control Mapping: Identification of "bottleneck nodes" (Victim vs Culprit) using
wsrep_flow_control_sentmetrics. - Write-Set Dependency Analysis: Optimization of
wsrep_slave_threadsbased onwsrep_cert_deps_distancetracking.
- Certification Failure Deep-Dive: Quantitative analysis of brute-force aborts (
- Stability & Scalability Safeguards:
- Network Jitter Detection: Monitoring of group communication latency (
wsrep_evs_repl_latencystatistics) and its impact on consistency. - PXC Strict Mode Verification: Consistency checks for Percona XtraDB Cluster specific security and performance enforcements.
- Network Jitter Detection: Monitoring of group communication latency (
- Storage Engine Protection:
- [/] InnoDB Page Integrity Audit: Verification of
innodb_checksum_algorithmstrength (full_crc32for MariaDB 10.5+,CRC32for MySQL) and ensuringinnodb_checksumsis active. - Redo Log Safety Check: Monitoring of
innodb_log_checksumsto prevent undetected recovery from corrupted logs. - Doublewrite Consistency: Alignment check between doublewrite buffer activity and storage atomic write capabilities.
- [/] InnoDB Page Integrity Audit: Verification of
- Replication Pipeline Validation:
- Binlog Event Integrity: Verification of
binlog_checksum(CRC32) across the topology and alignment with storage algorithms. - End-to-End Verification Audit: Analysis of
source_verify_checksumandreplica_sql_verify_checksumsettings. - Relay Log Hardening: Verification of checksum validation before transaction application on replicas.
- Binlog Event Integrity: Verification of
- Query Performance Profiling:
- Wait Event Fingerprinting: Aggregation of
performance_schemawait events to identify the primary database bottleneck (CPU, disk, lock, network). - Workload Characterization: Automated classification of the database as Read-Heavy, Write-Heavy, or Mixed based on I/O ratios.
- Metadata & Object Lifecycle:
- Table Churn & Fragmentation Advisor: Identification of tables with frequent DML that require periodic
OPTIMIZE TABLE. - Auto-Increment Exhaustion Audit: Monitoring of large tables for potential auto-increment overflow (especially 32-bit integers).
- Logging & Lock Instrumentation:
- Deadlock Logging Audit: Verification of
innodb_print_all_deadlocksandinnodb_status_outputsettings. - Lock Monitor Insights: Advisory for enabling
innodb_status_output_locksduring active contention troubleshooting. - Log Hygiene & Rotation: Verification of log rotation policies and verbosity settings (
log_error_verbosity/log_findings).
- Deadlock Logging Audit: Verification of
- Proactive Error Log Tracer:
- Semantic Error Detection: Automated parsing for OOM (Out of Memory) patterns, semaphore waits, and filesystem bottlenecks.
- Corruption & Recovery Guard: Early detection of "crashed" tables or InnoDB checksum failures in the logs.
- Resource Limit Correlation: Mapping of "too many open files" errors to
open_files_limitand OS-level table cache settings.
- Correlation Engine (Experimental):
- Temporal Event Linking: Logic to link error log timestamps with Performance Schema wait events or high CPU load detected during execution.
- Unified Health Dashboard:
- Sectional Health Scoring: Implementation of a 0-100 KPI for each major diagnostic area (Storage Engine, Security, Replication, SQL Modeling).
- Critical Findings Executive Summary: Automated prioritization of the top 3 items per section with color-coded badges (🔴 Critical, 🟡 Finding, 🟢 Optimal).
- Efficiency & Resource Mapping:
- Throughput Efficiency Index: Real-time ratio analysis of logical work (Queries/sec) vs physical resource consumption (
Innodb_buffer_pool_read_requests). - Resource Saturation Heatmap: Visual representation of proximity to system limits (CPU/MEM/IO/Connections).
- Throughput Efficiency Index: Real-time ratio analysis of logical work (Queries/sec) vs physical resource consumption (
- Comparative Insights:
- Historical Performance Deltas: Sectional trend analysis identifying areas of performance regression or improvement based on previous run data.
- Export Performance Safeguards:
- Default Row Limit: Implementation of a 50,000 rows default limit for all
dumpdirexports to prevent database slowdowns. - Configurable Quotas: Addition of
--dump-limitoption to allow user-defined row overrides.
- Default Row Limit: Implementation of a 50,000 rows default limit for all
- Metadata & Durability:
- Manifest Generation: Automated generation of
manifest.json/metadata.txtfor better traceability of offline diagnostic snapshots. - I/O Latency Monitoring: Real-time tracking of export duration per object with notices for slow disk subsystems.
- Manifest Generation: Automated generation of
- Compression & Efficiency:
- On-the-fly Compression: Support for compressed
.gzexports to minimize disk footprint in container/limited-storage environments.
- On-the-fly Compression: Support for compressed
We welcome community feedback on this roadmap. If you have specific feature requests or want to contribute to a specific phase, please open an issue on our GitHub repository.