Gli index inutilizzati richiedono controlli su ogni nodo replica del database
Gli index inutilizzati nel database relazionale open source usato da molte applicazioni web non possono essere valutati in sicurezza basandosi soltanto sul server primary quando le replica gestiscono parte del workload di lettura. Un index, una struttura dati che aiuta il server di database a trovare righe senza esaminare un'intera tabella, può non ricevere alcuna lettura sul primary pur rimanendo essenziale per query di reportistica, dashboard o elaborazioni batch eseguite altrove. Gli index non necessari comportano comunque dei costi: il server di database deve aggiornarli durante le operazioni INSERT e UPDATE, consumano memoria del buffer pool, aumentano le dimensioni dei backup e possono prolungare le modifiche allo schema.
Dalla versione 5.7, il sys schema include `sys.schema_unused_indexes`, una view che mostra gli index per i quali non sono state registrate letture dall'avvio del server. Le sue evidenze provengono da Performance Schema, il sistema interno del server di database per la raccolta di misurazioni operative. Questi contatori sono locali a ciascun server. Non mostrano che una replica dedicata alla reportistica utilizza un index per le attività di fine mese, che un reader Aurora serve richieste di dashboard o che un load balancer invia particolari query a un membro di un Galera o Percona XtraDB Cluster. La rimozione di un simile index tramite DDL replicato, cioè i comandi che modificano le strutture del database, potrebbe trasformare una query veloce su una replica in un full table scan che esamina ogni riga.
I riavvii creano un altro punto cieco, perché i contatori tornano a zero. Un server aggiornato martedì potrebbe non avere ancora osservato un processo batch settimanale o un report trimestrale. L'uptime è quindi importante, ma nessun numero universale di giorni dimostra che un index sia ridondante. Il periodo di osservazione deve includere gli eventi aziendali e i job pianificati che rendono rappresentativo il workload.
Percona ha sviluppato lo script Python open source `check_unused_indexes` per combinare le evidenze nell'intera replication topology, cioè l'insieme connesso di primary, replica e peer del cluster che conservano dati correlati. Partendo da un inventario e da un primary designato, si connette a ogni server elencato e legge `SHOW REPLICA STATUS`, oppure `SHOW SLAVE STATUS` nelle versioni precedenti, insieme a `@@wsrep_cluster_name`, ad `information_schema.replica_host_status` di Aurora, dove disponibile, e alla variabile di stato `Uptime`. Segue quindi la replica downstream, comprese le configurazioni di replica concatenata e circolare, e aggiunge i peer di Galera o Percona XtraDB Cluster. I reader Aurora vengono associati tramite gli ID server o una convenzione per gli alias.
Le voci dell'inventario non correlate al primary scelto vengono escluse, consentendo a un solo file di descrivere un'infrastruttura più ampia. La corrispondenza degli host viene eseguita come testo semplice: se `Source_Host` contiene `db-primary.internal`, l'inventario deve usare quel nome host anziché un indirizzo IP. Un inventario può contenere una voce `alias=host:port` per riga; quando la porta viene omessa, viene usata la 3306. I server possono anche essere forniti direttamente tramite `--primary` e `--replicas`.
Dopo aver determinato la topology, lo script interroga `sys.schema_unused_indexes` su ogni nodo incluso. Ignora sempre `sys`, il system schema del server di database, `performance_schema` e `information_schema`. Esclude inoltre un index quando la sua prima colonna partecipa a una foreign key, una regola che collega valori tra tabelle, perché il server di database richiede index di supporto per questi vincoli. Gli operatori possono restringere il controllo con `--include-schema` e `--include-table` oppure aggiungere esclusioni con `--ignore-schema`.
Il passaggio finale calcola l'intersezione dei risultati, conservando soltanto gli index segnalati come non letti su ogni nodo controllato. Nell'esempio di Percona, il primary ha segnalato 12 candidati, una replica ne ha segnalati otto e un'altra cinque. I rispettivi uptime erano di 42,1 giorni, 42,1 giorni e 14,0 giorni. Soltanto due index, `app_db.orders.idx_legacy_status` e `app_db.users.idx_created_at`, risultavano inutilizzati su tutti e tre i server. Gli altri 10 candidati rilevati sul primary servivano letture da qualche altra parte. L'opzione `-qs` o `--quiet-summary` stampa soltanto i comandi generati, utilizzabili in un ticket o in una richiesta di modifica.
Lo script adotta deliberatamente un approccio prudente in caso di evidenze mancanti. L'impossibilità di raggiungere il primary durante la discovery è un errore fatale. Le voci dell'inventario non raggiungibili producono avvisi che ne indicano il nome, perché lo script non può esaminarne lo stato della replica né sapere se appartengano alla topology. Se il primary scompare durante la raccolta degli index, l'elaborazione si arresta immediatamente. Se in questa fase non risponde una replica o un peer del cluster già individuato, lo script termina, a meno che non venga specificato `--force`. I risultati forzati sono esplicitamente limitati ai nodi che hanno risposto e non devono essere usati soltanto per aggirare un problema temporaneo di rete.
I nodi con un uptime inferiore al minimo generano un avviso dopo il controllo. La soglia predefinita è di sette giorni e può essere configurata con `-u`; impostandola su zero, l'avviso viene disattivato. L'opzione di test `--pause-seconds` inserisce un ritardo tra la discovery e la raccolta, consentendo di arrestare deliberatamente una replica e verificare che il programma termini con il codice 1. Percona consiglia di testare questo percorso di errore prima di affidarsi allo script in produzione.
L'installazione richiede Python 3.6 o una versione successiva e la libreria client Python per il server di database. Per impostazione predefinita, le credenziali vengono lette da `~/.my.cnf` oppure da un altro file di opzioni del database selezionato con `-c`. Lo stesso account read-only deve esistere su ogni nodo e deve disporre dell'autorizzazione per leggere `sys.schema_unused_indexes` e `information_schema` e per eseguire `SHOW REPLICA STATUS`. Lo script non modifica nulla. Il suo progetto, costituito da un singolo file, è pubblicato con licenza Apache 2.0.
I comandi generati sono proposte, non azioni automatizzate. Per impostazione predefinita, l'output per la versione 8.0 rende ogni candidato un invisible index. L'optimizer, il componente del server di database che pianifica le query, smette di selezionare un invisible index, ma il database continua a mantenerlo durante le scritture. La modifica della visibilità è un'operazione sui metadati e può essere annullata in pochi secondi, mentre eliminare e successivamente ricostruire un index di grandi dimensioni può richiedere ore. `--action drop` stampa i comandi di rimozione, mentre `--action both` stampa sia i comandi relativi alla visibilità sia quelli di rimozione.
Un rollout prudente prevede innanzitutto la revisione di ogni candidato, quindi rende invisibili gli index selezionati sul primary, affinché la modifica venga replicata nell'intera topology. Gli operatori devono osservare almeno un ciclo aziendale completo, comprese le elaborazioni batch settimanali e le attività di fine mese, monitorando al contempo le metriche delle query e lo slow query log. Se le prestazioni peggiorano, `ALTER INDEX ... VISIBLE` ripristina l'accesso da parte dell'optimizer. La versione 8.0 consente inoltre a una singola sessione di impostare `use_invisible_indexes=on` in `optimizer_switch`, anche tramite `SET_VAR`, per verificare se una determinata query sceglierebbe l'index nascosto senza esporlo all'applicazione.
Restano alcuni limiti importanti. Vengono controllati soltanto i server elencati nell'inventario. Performance Schema deve essere abilitato. `sys.schema_unused_indexes` omette le primary key, ma può segnalare index UNIQUE che, pur non ricevendo letture, potrebbero applicare regole aziendali; i candidati devono quindi essere verificati con `SHOW CREATE TABLE`. Il raggruppamento Aurora dipende dagli ID server o da alias come `aurora-reader-1`.