Ottimizzare le prestazioni e gestire i database in Database di Azure per MySQL - Server flessibile usando il sys_schema

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

Importante

Database di Azure per MySQL server singolo si trova nel percorso di ritiro. È consigliabile eseguire l'aggiornamento a Database di Azure per MySQL server flessibile. Per altre informazioni sulla migrazione a Database di Azure per MySQL server flessibile, vedere Che cosa accade a Database di Azure per MySQL server singolo?

Il performance_schema MySQL, disponibile per la prima volta in MySQL 5.5, fornisce la strumentazione per molte risorse server vitali, ad esempio l'allocazione della memoria, i programmi archiviati, il blocco dei metadati e così via. Tuttavia, il performance_schema contiene più di 80 tabelle e il recupero delle informazioni necessarie spesso richiede il join di tabelle all'interno del performance_schema e le tabelle del 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, verranno raggruppati i modelli di utilizzo 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 l'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, vedere 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 impiegato 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 delle query lente o riducendo la frequenza di accesso lento delle query al 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 di prestazioni e non possono essere modificate a meno che non venga scelto 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.

Nell'immagine precedente è evidente che, oltre alle tabelle e alle viste di sistema, ogni tabella nel database mysqldatabase033, che ospita uno dei siti WordPress, occupa 16 KB o 1 pagina, di dati in memoria.

Sys.schema_unused_indexes e 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 risposte peer alle domande più interessate o pubblicare una nuova domanda/risposta, visitare Stack Overflow.