Ottimizzare le prestazioni e gestire i database nel server flessibile del Database di Azure per MySQL usando sys_schema

SI APPLICA A:Database di Azure per MySQL - Server singolo Database di Azure per MySQL - Server flessibile

Importante

Il server singolo del Database di Azure per MySQL è in fase di ritiro. È consigliabile eseguire l'aggiornamento al server flessibile del Database di Azure per MySQL. Per altre informazioni sulla migrazione a Database di Azure per MySQL - Server flessibile, vedere Cosa succede a Database di Azure per MySQL - Server singolo?

Il database performance_schema di MySQL, introdotto per la prima volta in MySQL 5.5, rende disponibile la strumentazione per molte risorse del server cruciali, come l'allocazione della memoria, i programmi archiviati, il blocco dei metadati e così via. Il database performance_schema, tuttavia, contiene più di 80 tabelle e per ottenere le informazioni necessarie spesso è necessario unire le tabelle di performance_schema alle tabelle di information_schema. Basandosi sia su performance_schema che su information_schema, il sys_schema offre una potente raccolta di visualizzazioni descrittive in un database di sola lettura ed è completamente abilitato in Database di Azure per MySQL Server flessibile versione 5.7.

Il database sys_schema include 52 viste, ognuna con uno dei prefissi seguenti:

• Host_summary o IO: latenze correlate alle operazioni di I/O.
• InnoDB: stato e blocchi del buffer InnoDB.
• Memory: utilizzo della memoria da parte di host e utenti.
• Schema: informazioni correlate allo schema, come incremento automatico, indici e così via.
• Statement: informazioni sulle istruzioni SQL; può trattarsi di istruzioni che causano una scansione di tabella completa o una durata prolungata delle query.
• User: risorse utilizzate e raggruppate in base agli utenti. Ad esempio I/O su file, connessioni e memoria.
• Wait: eventi di attesa raggruppati in base a host o utente.

Vengono di seguito presentati alcuni modelli di uso comune di sys_schema. Per iniziare, i modelli di utilizzo verranno raggruppati in due categorie: ottimizzazione delle prestazioni e manutenzione del database.

Ottimizzazione delle prestazioni

sys.user_summary_by_file_io

Le operazioni di I/O sono le più dispendiose nel database. È possibile scoprire la latenza media di I/O tramite una query sulla vista sys.user_summary_by_file_io. Con il valore predefinito di 125 GB di spazio di archiviazione sottoposto a provisioning, la latenza di I/O è di circa 15 secondi.

Poiché Database di Azure per MySQL server flessibile ridimensiona le operazioni di I/O rispetto all'archiviazione, dopo aver aumentato lo spazio di archiviazione di cui è stato effettuato il provisioning a 1 TB, la latenza di I/O riduce a 571 ms.

sys.schema_tables_with_full_table_scans

Anche con un'attenta pianificazione, molte query possono comunque comportare scansioni di tabella complete. Per altre informazioni sui tipi di indici e su come ottimizzarli, è possibile fare riferimento a questo articolo: Come risolvere i problemi relativi alle prestazioni delle query. Le scansioni di tabella complete comportano un elevato utilizzo di risorse e influiscono negativamente sulle prestazioni del database. Il modo più rapido per individuare le tabelle con scansione di tabella completa consiste nell'eseguire una query sulla vista sys.schema_tables_with_full_table_scans.

sys.user_summary_by_statement_type

Per risolvere i problemi di prestazioni del database, potrebbe essere utile identificare gli eventi che si verificano all'interno del database e la vista sys.user_summary_by_statement_type può essere perfetta a questo scopo.

In questo esempio, Database di Azure per MySQL server flessibile ha trascorso 53 minuti per scaricare il log delle query lente 44579 volte. Si tratta di molto tempo e di molte operazioni di I/O. È possibile ridurre questa attività disabilitando il log query lente o riducendo la frequenza di registrazione nel log query lente nel portale di Azure.

Manutenzione del database

sys.innodb_buffer_stats_by_table

[IMPORTANTE!]

L'esecuzione di query su questa vista può compromettere le prestazioni. Si consiglia di eseguire questa risoluzione dei problemi durante gli orari di ufficio di minore attività.

Il pool di buffer InnoDB risiede in memoria e rappresenta il principale meccanismo di cache tra il sistema di gestione di database e l'archiviazione. Le dimensioni del pool di buffer InnoDB sono associate al livello delle prestazioni e non possono essere modificate, se non scegliendo uno SKU di prodotto diverso. Come nel caso della memoria nel sistema operativo, viene effettuato lo swapping delle pagine meno recenti per fare spazio a dati più nuovi. Per scoprire quali tabelle utilizzano la maggior parte della memoria del pool di buffer InnoDB, è possibile eseguire una query sulla vista sys.innodb_buffer_stats_by_table.

Nel grafico precedente è evidente che, escludendo le tabelle e le visualizzazioni di sistema, ogni tabella nel database mysqldatabase033, che ospita uno dei siti WordPress, occupa 16 kB, ovvero 1 pagina, di dati nella memoria.

Sys.schema_unused_indexes & sys.schema_redundant_indexes

Gli indici sono strumenti validi per migliorare le prestazioni di lettura, ma comportano costi aggiuntivi per inserimenti e archiviazione. Sys.schema_unused_indexes e sys.schema_redundant_indexes forniscono informazioni dettagliate sugli indici inutilizzati o duplicati.

Conclusione

In sintesi, il database sys_schema è un valido strumento sia per l'ottimizzazione delle prestazioni che per la manutenzione del database. Assicurarsi di sfruttare questa funzionalità nell'istanza del server flessibile Database di Azure per MySQL.

Passaggi successivi

• Per trovare le risposte dei peer alle domande più pressanti o per pubblicare una nuova domanda o risposta, visitare Stack Overflow.