Standard Operational Inspections

Storage capacity is a critical focus during proactive inspections. While standard alerts might trigger at 80% utilization, inspections should flag instances exceeding 70%. This buffer ensures there is enough capacity to handle data growth over extended holidays, preventing emergency interventions during off-hours.

Process health involves verifying that the database daemon is active and responsive, ideally going beyond a simple ping to assess true operational readiness.

High availability readiness confirms the database is in a switchable state. For architectures using MHA, validation requires executing specific diagnostic scripts to ensure failover mechanisms are sound:

# MHA cluster validation commands
/usr/local/bin/mha_check_connectivity --config=/etc/mha/cluster_primary.ini
/usr/local/bin/mha_check_replication --config=/etc/mha/cluster_primary.ini
/usr/local/bin/mha_check_service --config=/etc/mha/cluster_primary.ini

Replication topology must be verified across all channels, including asynchronous, semi-synchronous, delayed, bidirectional, and cascading setups. Cross-data-center replication links and their own HA mechanisms also require validation.

Virtual IP (VIP) monitoring is necessary for instances relying on redundant network paths. A dropped VIP on a standby link might go unnoticed by the application, so automated probing tasks should be implemented to detect and alert on VIP failures.

Transitioning to Deep Inspections

Operational inspections focus heavily on basic availability. Deep inspections expand this scope to include reliability, performance, and user experience. The goal is to ensure the database is not just online, but also robust and performant.

Deep Inspection = Availability + Reliability + Performance + Analysis & Recommendations

Deep Availability Inspections

While operational checks focus on the instance, deep availability examines application-level risks. A prime example is auto-increment primary key exhaustion. Developers frequently define columns as INT (signed by default, max ~2.14 billion) or INT UNSIGNED (max ~4.29 billion). Choosing the wrong type, combined with ID gaps caused by transaction rollbacks, innodb_autoinc_lock_mode, and auto_increment_increment settings, can lead to rapid exhaustion. An INT UNSIGNED column might deplete twice as fast as expected if insert patterns waste allocated IDs.

When the auto-increment limits reached, the table becomes read-only. Modifying the column type to BIGINT SIGNED requires a blocking DDL operation. In one production scenario, a logging table ingesting up to 9 million rows daily exhausted its INT UNSIGNED primary key in under 9 months, resulting in a 6-hour outage to alter the table.

Deep Reliability Inspections

Availability (uptime) does not equal reliability (data correctness and zero loss). Deep reliability inspections must include Core Parameter Checks, which encompass three areas:

1. Compliance with baseline operational standards.
2. Parameter consistency between primary and replica nodes.
3. Consistency between runtime parameters and the configuration file (my.cnf).

Ensuring parameters meet baseline standards addresses historical drift, especially for instances not originally deployed by the current operations team. Critical data integrity parameters include:

# Core data integrity configurations
binlog_format = ROW
binlog_row_image = FULL
gtid_mode = ON
enforce_gtid_consistency = ON
innodb_doublewrite = ON
innodb_flush_log_at_trx_commit = 1
log_bin = binlog
master_info_repository = TABLE
sync_binlog = 1

Checking primary-replica parameter consistency prevents unexpected behavior following a failover, while also ensuring required differences (like server_id) exist.

Verifying runtime against configuration files is crucial. In MySQL 5.7 and earlier, SET GLOBAL does not persist to my.cnf. This two-step manual process invites human error. For instance, dynamically increasing innodb_buffer_pool_size to 128GB without persisting the change means a crash recovery will revert to the default 128MB, severely degrading performance. Another common risk is temporarily setting sync_binlog=0 and innodb_flush_log_at_trx_commit=0 on replicas to clear replication lag, then forgetting to revert them. If this replica is promoted, data loss becomes highly likely. Additionally, users with SUPER privileges might alter runtime parameters without server access, creating dangerous configuration drift.

Deep Performance Inspections

1. Missing Primary Keys: InnoDB tables must have a primary key, ideally a business-agnostic auto-increment integer. Lack of a primary key severely impacts performance and replication efficiency.

2. SQL and Index Optimization: Identify the Top 10 slowest queries and the Top 30 queries triggering full table scans. Queries with inefficient execution plans may perform adequately on small datasets but can cause sudden CPU spikes as data volume grows. Additionally, audit indexes for redundancy (e.g., idx_col1_col2 makes idx_col1 redundant), lack of usage, and low cardinality. Columns with extremely low distinct values (like gender) are poor index candidates; index discrimination should ideally approach 1.0.

3. Obsolete Storage Engines: MyISAM should be actively disabled in favor of InnoDB. Block its creation using configuration settings:

disabled_storage_engines=ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDB,MyISAM

Note: MySQL 5.7 still uses MyISAM for 10 internal metadata tables. This parameter must be removed before upgrading to version 8.0 and restored afterward.

4. Oversized Tables: Large tables (e.g., over 100GB) are catastrophic during DDL operations and resource-intensive during full scans. Evaluate whether such data belongs in MySQL or should be migrated to distributed systems like TiDB. Options include horizontal/vertical sharding or cold/hot data separation. While modern hardware can support instances up to 2TB and tables up to 100GB, actual limits should align with business performance requirements.

Analysis and Recommendations

The ultimate goal of performance inspections is to generate actionable data. Reports should highlight infrastructure and indexing issues with clear guidance, while complex SQL optimization should be delegated to the application developers who understand the business logic best.