Nicht verwendete indexes erfordern Prüfungen auf jedem replica node der Datenbank
Nicht verwendete indexes in der relationalen Open-Source-Datenbank, die von vielen Webanwendungen genutzt wird, können nicht sicher allein anhand des primary servers beurteilt werden, wenn replicas einen Teil der Lese-Workload übernehmen. Ein index, also eine Datenstruktur, mit deren Hilfe der Datenbankserver Zeilen finden kann, ohne eine gesamte Tabelle zu untersuchen, kann auf dem primary keine Lesezugriffe verzeichnen und dennoch für Reporting-, Dashboard- oder Batch-queries an anderer Stelle unverzichtbar sein. Unnötige indexes verursachen weiterhin Kosten: Der Datenbankserver muss sie bei INSERT- und UPDATE-Operationen aktualisieren, sie belegen Speicher im buffer pool, vergrößern Backups und können schema changes verlängern.
Seit Version 5.7 enthält das sys schema `sys.schema_unused_indexes`, eine view, die indexes anzeigt, für die seit dem Start des servers keine Lesezugriffe aufgezeichnet wurden. Die zugrunde liegenden Daten stammen aus Performance Schema, der internen Einrichtung des Datenbankservers zur Erfassung von Betriebsmetriken. Diese counters gelten jeweils nur lokal auf einem server. Sie zeigen nicht, dass eine Reporting-replica einen index für Monatsabschlussarbeiten verwendet, ein Aurora reader Dashboard-Anfragen bedient oder ein load balancer bestimmte queries an ein einzelnes Mitglied eines Galera oder Percona XtraDB Cluster sendet. Wird ein solcher index durch repliziertes DDL entfernt, also durch Befehle, die Datenbankstrukturen ändern, könnte eine schnelle query auf einer replica zu einem full table scan werden, der jede Zeile untersucht.
Neustarts erzeugen eine weitere Beobachtungslücke, weil die counters auf null zurückgesetzt werden. Ein server, der am Dienstag gepatcht wurde, hat möglicherweise noch keinen wöchentlichen Batch-Prozess oder Quartalsbericht beobachtet. Die Uptime ist daher wichtig, doch keine universelle Anzahl von Tagen beweist, dass ein index überflüssig ist. Der Beobachtungszeitraum muss die geschäftlichen Ereignisse und geplanten Jobs umfassen, die für eine repräsentative Workload sorgen.
Percona hat das Open-Source-Python-Skript `check_unused_indexes` entwickelt, um Nachweise über eine replication topology hinweg zusammenzuführen, also über den verbundenen primary, die replicas und die Cluster-Peers, die zusammengehörige Daten vorhalten. Anhand eines inventory und eines festgelegten primary stellt es eine Verbindung zu jedem aufgeführten server her und liest `SHOW REPLICA STATUS` beziehungsweise bei älteren Versionen `SHOW SLAVE STATUS` sowie `@@wsrep_cluster_name`, Auroras `information_schema.replica_host_status`, sofern verfügbar, und die Statusvariable `Uptime`. Anschließend folgt es der replication downstream, einschließlich verketteter und zirkulärer replication, und fügt Peers aus Galera oder Percona XtraDB Cluster hinzu. Aurora readers werden anhand von server IDs oder einer Alias-Konvention zugeordnet.
inventory-Einträge, die nicht mit dem ausgewählten primary zusammenhängen, werden ausgeschlossen, sodass eine Datei eine größere server-Flotte beschreiben kann. Der Host-Abgleich erfolgt als einfacher Text: Wenn `Source_Host` den Wert `db-primary.internal` enthält, muss das inventory diesen hostname anstelle einer IP-Adresse verwenden. Ein inventory kann pro Zeile einen Eintrag im Format `alias=host:port` enthalten; wird der port weggelassen, wird 3306 verwendet. Server können außerdem direkt mit `--primary` und `--replicas` angegeben werden.
Nach Ermittlung der replication topology fragt das Skript `sys.schema_unused_indexes` auf jedem einbezogenen node ab. Es ignoriert immer `sys`, das system schema des Datenbankservers, sowie `performance_schema` und `information_schema`. Außerdem schließt es einen index aus, wenn dessen erste Spalte an einem foreign key beteiligt ist, also an einer Regel, die Werte zwischen Tabellen verbindet, weil der Datenbankserver unterstützende indexes für diese constraints benötigt. Administratoren können die Prüfung mit `--include-schema` und `--include-table` eingrenzen oder mit `--ignore-schema` weitere Ausschlüsse hinzufügen.
Im letzten Schritt bildet das Skript die Schnittmenge der Ergebnisse und behält nur indexes bei, die auf jedem geprüften node als ungelesen gemeldet wurden. Im Beispiel von Percona meldete der primary 12 Kandidaten, eine replica acht und eine weitere fünf. Ihre Uptime betrug 42,1 Tage, 42,1 Tage und 14,0 Tage. Nur zwei indexes, `app_db.orders.idx_legacy_status` und `app_db.users.idx_created_at`, erschienen auf allen drei servern als ungenutzt. Die anderen zehn Kandidaten auf dem primary bedienten an anderer Stelle Lesezugriffe. Die Option `-qs` beziehungsweise `--quiet-summary` gibt nur die erzeugten Befehle zur Verwendung in einem Ticket oder Änderungsantrag aus.
Das Skript ist bei fehlenden Nachweisen bewusst konservativ. Kann der primary während der Ermittlung nicht erreicht werden, ist dies ein schwerwiegender Fehler. Nicht erreichbare inventory-Einträge erzeugen namentliche Warnungen, weil das Skript ihren replication status nicht prüfen und nicht feststellen kann, ob sie zur replication topology gehören. Verschwindet der primary während der Erfassung der indexes, wird die Verarbeitung sofort beendet. Fällt in dieser Phase eine ermittelte replica oder ein Cluster-Peer aus, beendet sich das Skript, sofern nicht `--force` angegeben wurde. Erzwungene Ergebnisse sind ausdrücklich auf die nodes beschränkt, die geantwortet haben, und sollten nicht lediglich dazu verwendet werden, ein vorübergehendes Netzwerkproblem zu umgehen.
Nodes, deren Uptime unter dem Mindestwert liegt, erzeugen nach der Prüfung eine Warnung. Der Standardgrenzwert beträgt sieben Tage und kann mit `-u` konfiguriert werden; der Wert null deaktiviert die Warnung. Die Testoption `--pause-seconds` fügt zwischen Ermittlung und Erfassung eine Verzögerung ein. Dadurch ist es möglich, eine replica absichtlich anzuhalten und zu bestätigen, dass das Programm mit dem Exit-Code 1 beendet wird. Percona empfiehlt, diesen Fehlerpfad zu testen, bevor das Skript in der Produktion eingesetzt wird.
Die Installation erfordert Python 3.6 oder neuer sowie die Python-Clientbibliothek für den Datenbankserver. Zugangsdaten werden standardmäßig aus `~/.my.cnf` oder aus einer anderen, mit `-c` ausgewählten Datenbank-options file gelesen. Auf jedem node sollte dasselbe read-only account vorhanden sein. Es benötigt die Berechtigung, `sys.schema_unused_indexes` und `information_schema` zu lesen sowie `SHOW REPLICA STATUS` auszuführen. Das Skript selbst nimmt keine Änderungen vor. Sein aus einer einzigen Datei bestehendes Projekt wird unter der Apache-2.0-Lizenz veröffentlicht.
Die erzeugten Befehle sind Vorschläge und keine automatisierten Aktionen. Standardmäßig macht die Ausgabe für Version 8.0 jeden Kandidaten zu einem invisible index. Der optimizer, die Komponente des Datenbankservers für die query-Planung, wählt einen invisible index nicht mehr aus, doch die Datenbank pflegt ihn weiterhin bei Schreibvorgängen. Das Ändern der Sichtbarkeit ist eine metadata operation und kann innerhalb von Sekunden rückgängig gemacht werden, während das Entfernen und spätere Neuerstellen eines großen index Stunden dauern kann. `--action drop` gibt Befehle zum Entfernen aus, während `--action both` sowohl die Befehle zum Ändern der Sichtbarkeit als auch die Befehle zum Entfernen ausgibt.
Bei einer vorsichtigen Einführung wird zunächst jeder Kandidat geprüft. Anschließend werden ausgewählte indexes auf dem primary unsichtbar gemacht, damit die Änderung in der gesamten replication topology repliziert wird. Administratoren sollten mindestens einen vollständigen Geschäftszyklus einschließlich wöchentlicher Batch-Prozesse und Monatsabschluss-Jobs beobachten und dabei query-Metriken sowie das slow query log überwachen. Verschlechtert sich die Performance, stellt `ALTER INDEX ... VISIBLE` den Zugriff durch den optimizer wieder her. Version 8.0 ermöglicht es außerdem, für eine einzelne session `use_invisible_indexes=on` in `optimizer_switch` festzulegen, auch über `SET_VAR`. So lässt sich testen, ob eine bestimmte query den verborgenen index auswählen würde, ohne ihn für die Anwendung verfügbar zu machen.
Wichtige Einschränkungen bleiben bestehen. Es werden nur server geprüft, die im inventory aufgeführt sind. Performance Schema muss aktiviert sein. `sys.schema_unused_indexes` lässt primary keys aus, kann jedoch UNIQUE indexes melden, die trotz fehlender Lesezugriffe Geschäftsregeln durchsetzen. Kandidaten sollten daher mit `SHOW CREATE TABLE` geprüft werden. Die Gruppierung in Aurora hängt von server IDs oder Aliasen wie `aurora-reader-1` ab.