Vai a

Suggerimenti per l'ottimizzazione delle prestazioni per MySQL

L'ottimizzazione delle prestazioni è un aspetto critico della gestione di qualsiasi database. L'ottimizzazione delle prestazioni può essere eseguita in ogni fase della gestione del database, a partire dalla scelta dei componenti hardware e software per l'hosting dei server di database alla configurazione di schemi e modelli dei dati. Questo documento tratta i suggerimenti per l'ottimizzazione delle prestazioni dei database MySQL nel cloud, in particolare Cloud SQL per MySQL, comprese le best practice per la creazione di istanze di nuovi database e l'ottimizzazione di quelli esistenti.

Considerazioni relative all'hardware

Le configurazioni hardware sono un fattore importante per le prestazioni del database. Prima di definire le configurazioni dell'hardware, è importante conoscere bene il numero di utenti attivi simultanei di un'applicazione, le dimensioni del database e degli indici e la latenza prevista dell'applicazione o servizio. Di seguito sono riportate alcune importanti considerazioni relative all'hardware:

Unità di elaborazione centrale (CPU)

La potenza di elaborazione è uno dei fattori più importanti in un sistema di database con prestazioni elevate. Il numero di connessioni/utenti/thread simultanei determina il numero di core richiesti per elaborare le richieste inviate al database. La CPU allocata al database deve essere in grado di gestire i carichi di lavoro normali e di picco (estremi) affinché le prestazioni delle applicazioni raggiungano livelli ottimali.

Nel caso di Cloud SQL, l'offerta MySQL completamente gestita di Google Cloud, la CPU viene allocata sotto forma di CPU virtuale (vCPU). Il numero di vCPU allocate a un database determina in sostanza la quantità di memoria e la velocità effettiva di rete per un'istanza di database in quanto ogni vCPU dispone di una quantità massima di memoria allocata e anche la velocità effettiva di rete varia in base al numero di vCPU. Cloud SQL offre flessibilità per scalare il numero di vCPU per la tua istanza, per soddisfare facilmente i requisiti di memoria e di velocità effettiva di rete della tua applicazione.

Memoria

Un aspetto importante per determinare la quantità di memoria da allocare per un database è assicurarsi che le dimensioni del pool del buffer siano sufficienti per contenere il working set. Il working set corrisponde ai dati che attivamente utilizzati in qualsiasi momento dal database. La memoria allocata deve essere sufficiente per contenere il working set o i dati a cui si accede di frequente, che di solito sono costituiti da dati di database, indici, buffer di sessione, cache del dizionario e tabelle hash. Un modo per verificare se è stata allocata abbastanza memoria è controllare lo stato delle letture da disco nel database. Idealmente, le letture dal disco dovrebbero essere inferiori o molto ridotte in condizioni di carico di lavoro normali.

In caso di allocazione di memoria insufficiente per l'istanza, l'istanza potrebbe presentare problemi di tipo "Out of Memory", che causeranno il riavvio e l'istanza del database o tempo di inattività dell'applicazione. 

Archiviazione

L'archiviazione dei database è un altro componente che svolge un ruolo importante nell'ottimizzazione delle prestazioni. Cloud SQL offre 2 tipi di archiviazione

  • SSD (predefinito) 
  • HDD

Un'SSD offre prestazioni e velocità effettiva molto migliori rispetto all'HDD. Per questo motivo è consigliabile scegliere sempre SSD per prestazioni migliori, in particolare per i carichi di lavoro di produzione. 

Le operazioni di input/output di lettura e scrittura al secondo (IOP) allocate all'istanza dipendono dalla quantità di spazio di archiviazione allocato durante la creazione dell'istanza. Maggiore è la dimensione del disco, maggiori sono le IOP in lettura e scrittura. Ti consigliamo quindi di creare istanze con dimensioni dei dati più elevate per migliorare le prestazioni delle IOP. Il seguente screenshot della console Google Cloud mostra il riepilogo delle risorse (inclusa la capacità massima) allocate all'istanza di database al momento della creazione, aiutando gli utenti a confermare e comprendere esattamente come verrà configurato il loro database dopo la creazione dell'istanza.

La console Google Cloud mostra il riepilogo delle risorse (inclusa la capacità massima) allocate all'istanza di database al momento della creazione
Cloud SQL offre inoltre una funzionalità di autorizzazione dell'aumento automatico dello spazio di archiviazione che, se abilitato, aggiunge automaticamente ulteriore capacità di archiviazione se lo spazio di archiviazione allocato scende al di sotto di una soglia specificata.

Regione

Uno dei modi per ridurre la latenza di rete è scegliere la regione dell'istanza più vicina all'applicazione o al servizio. Cloud SQL per MySQL è disponibile in tutte le regioni di Google Cloud, consentendo agli utenti di creare più facilmente un'istanza di un database il più vicino possibile agli utenti finali.

Scalabilità elastica

Cloud SQL offre un modo semplice per fare lo scale up o lo scale down delle risorse (CPU, memoria o archiviazione) assegnate a un'istanza di database. Questo può essere utile per carichi di lavoro con requisiti di risorse variabili. Ad esempio, gli utenti possono aumentare (fare lo scale up) le risorse durante un periodo in cui i requisiti del carico di lavoro aumentano e quindi fare lo scale down quando la situazione del carico di lavoro di picco è terminata.

Configurazioni MySQL

Questa sezione contiene le best practice per le configurazioni dei database MySQL al fine di migliorare le prestazioni.

Versione

Scegli la versione più recente di MySQL quando crei un nuovo database. Le versioni più recenti offrono correzioni di bug e ottimizzazioni per prestazioni migliori rispetto alle versioni precedenti. Cloud SQL fornisce la versione più recente di MySQL disponibile sul mercato e la rende la versione predefinita durante la creazione di un nuovo database. Maggiori dettagli sulle versioni di MySQL supportate su Cloud SQL.

Dimensioni del pool del buffer InnoDB

Per le istanze MySQL, InnoDB è l'unico motore di archiviazione supportato. Dimensioni del pool del buffer Innodb è il primo parametro che un utente deve definire per ottenere prestazioni ottimali. Il pool del buffer è l'area di memoria allocata per l'archiviazione delle cache delle tabelle e degli indici, dei dati modificati prima dello flush e di altre strutture interne come l'AHI (Adaptive Hash Index). 

Cloud SQL definisce un valore predefinito pari a circa il 72% della memoria dell'istanza da allocare per il pool del buffer InnoDB, in base alle dimensioni dell'istanza (i valori predefiniti variano in base alle dimensioni dell'istanza). Ulteriori dettagli sulle impostazioni del pool del buffer su istanze di diverse dimensioni. Cloud SQL offre la flessibilità di modificare le dimensioni del pool del buffer in base alle esigenze delle tue applicazioni utilizzando i flag di database. 

Il pool del buffer deve avere dimensioni tali che sia disponibile una memoria libera sufficiente nell'istanza per il buffer di sessione, la cache del dizionario, le tabelle performance_schema (se abilitate) oltre al pool del buffer InnoDB.

Gli utenti possono controllare le letture del disco effettuate dall'istanza per identificare la quantità di dati letti dai dischi rispetto alle letture soddisfatte dal pool del buffer. Se ci sono più letture dal disco, l'aumento della dimensione del pool del buffer e della memoria dell'istanza migliorerebbe le prestazioni delle query di lettura.

Dimensioni file di log di ripetizione/InnoDB

Il file di log InnoDB o il log di ripetizione contengono la registrazione delle modifiche apportate ai dati delle tabelle. Le dimensioni del file di log di InnoDB definiscono le dimensioni del singolo file di log di ripetizione. 

Per i carichi di lavoro che richiedono elevati volumi di scrittura, dimensioni dei log di ripetizione più elevate consentono più spazio per le scritture senza che sia necessario eseguire flush di checkpoint frequenti e salvataggi dell'I/O del disco, migliorando così le prestazioni di scrittura. Le dimensioni totali del log di ripetizione, che possono essere calcolate come (innodb_log_file_size * innodb_log_files_in_group), dovrebbero essere sufficienti per gestire almeno 1-2 ore di scrittura dei dati durante i periodi con intense attività di accesso al database.

Cloud SQL definisce un valore predefinito di 512 MB. Cloud SQL offre inoltre la flessibilità di aumentare le dimensioni del file di log InnoDB utilizzando i flag di database. 

NOTA: l'aumento del valore delle dimensioni del file di log di InnoDB comporta l'aumento dei tempi di ripristino dagli arresti anomali.

durabilità

Il flag innodb_flush_log_at_trx_commit controlla la frequenza con cui viene eseguito il flush dei dati di log sul disco e se viene eseguito o meno il flush per ogni commit di transazione

Le prestazioni di scrittura sulle repliche di lettura possono essere aumentate modificando i valori di innodb_flush_log_at_trx_commit in 0 o 2.

Cloud SQL non supporta la modifica dell'impostazione di durabilità su CloudSQL principale. Tuttavia, Cloud SQL consente di modificare il flag sulle repliche di lettura. La riduzione della durabilità delle repliche di lettura migliora le prestazioni di scrittura sulle repliche. Questo aiuta a risolvere i problemi di ritardi del processo di replica nelle repliche. Scopri di più su innodb_flush_log_at_trx_commit.

Dimensione del buffer di log InnoDB

La dimensione del buffer del log InnoDB corrisponde alla quantità del buffer che InnoDB utilizza per scrivere nel file di log (log di ripetizione). 

Se le transazioni (inserimenti, aggiornamenti o eliminazioni) nel database sono di grandi dimensioni e il buffer utilizzato è superiore a 16 MB, InnoDB deve eseguire l'I/O del disco prima di eseguire il commit della transazione, il che influisce sulle prestazioni. Per evitare l'I/O del disco, aumenta il valore di innodb_log_buffer_size.

Cloud SQL definisce un valore predefinito di 16 MB per le dimensioni del buffer di log InnoDB. La variabile di stato MySQL innodb_log_waits mostra il numero di volte in cui le dimensioni di innodb_log_buffer_size erano insufficienti e che InnoDB ha dovuto attendere l'esecuzione del flush prima di eseguire il commit della transazione. Se il valore di innodb_log_waits è maggiore di 0 e aumenta, aumenta il valore di innodb_log_buffer_size utilizzando flag di database per prestazioni migliori. Il valore di innodb_log_buffer_size e innodb_log_waits può essere identificato eseguendo le query seguenti nella shell MySQL (interfaccia a riga di comando). Queste query mostrano il valore delle variabili di stato e delle variabili globali in MySQL.

SHOW GLOBAL VARIABLES LIKE 'innodb_log_buffer_size';

SHOW GLOBAL STATUS LIKE 'innodb_log_waits';

Capacità IO InnoDB

La capacità di IO InnoDB definisce il numero di operazioni di input/output disponibili per le attività in background (come il flush delle pagine dal pool del buffer e l'unione dei dati dal buffer di modifica).  

Il valore predefinito di Cloud SQL per innodb_io_capacity è 5000 e 10.000 per innodb_io_capacity_max

Questa impostazione predefinita funziona al meglio per la maggior parte dei carichi di lavoro, ma se il carico di lavoro richiede elevati volumi di scrittura o le modifiche non applicate sull'istanza sono elevate e se sull'istanza è disponibile un numero di operazioni di input/output sufficiente, valuta la possibilità di aumentare innodb_io_capacity e innodb_io_capacity_max. Il valore delle modifiche applicate può essere trovato utilizzando la seguente query nella shell MySQL:

mysql -e 'show engine InnoDB status \G;' | grep Ibuf

Buffer di sessione

I buffer di sessione sono la memoria allocata per le singole sessioni. Se la tua applicazione o le tue query includono molti inserimenti, aggiornamenti, ordinamenti, join e necessita di buffer più elevati, la definizione di valori elevati dei buffer durante l'esecuzione della query in una determinata sessione evita l'overhead delle prestazioni. Gli utenti possono impedire un'allocazione eccessiva di buffer a livello globale, che aumenta i valori per tutte le connessioni e, a sua volta, aumenta l'utilizzo totale della memoria dell'istanza. La modifica del valore predefinito per i seguenti buffer aiuta a migliorare le prestazioni delle query. Questi valori possono essere modificati utilizzando i flag del database.

sort_buffer_size,

join_buffer_size,

tmp_table_size,

max_heap_table_size.

Tieni presente che si tratta di valori di buffer per sessione, l'aumento dei limiti potrebbe interessare tutte le connessioni e, alla fine, potrebbe comportare un aumento dell'utilizzo complessivo della memoria.

Table_open_cache e Table_Definition_cache

Se sono presenti troppe tabelle (oltre le migliaia) nell'istanza di database (in database singoli o multipli), aumenta i valori di table_open_cache e table_Definition_cache per migliorare la velocità di apertura delle tabelle.

Table_Definition_cache accelera l'apertura delle tabelle e contiene una sola voce per tabella. La cache della definizione delle tabelle occupa meno spazio e non utilizza descrittori di file. Se il numero di istanze di tabelle nella cache degli oggetti dizionario supera il limite definito in table_definition_cache, un meccanismo LRU inizia a contrassegnare le istanze delle tabelle per la rimozione dalla cache degli oggetti dizionario per fare spazio alla definizione della nuova tabella. Questo processo viene eseguito ogni volta che si apre una nuova tabella. Vengono chiusi solo gli spazi delle tabelle non attivi. Questo processo di rimozione rallenterebbe l'apertura delle tabelle.

Table_open_cache definisce il numero di tabelle aperte per tutti i thread. Per verificare se devi aumentare la cache delle tabelle, controlla la variabile di stato Opened_tables. Se il valore di Opened_tables è elevato e non utilizzi spesso FLUSH TABLES, valuta la possibilità di aumentare il valore della variabile table_open_cache.

Table_open_cache e table_definition_cache possono essere impostate sul numero effettivo di tabelle nell'istanza. Scopri di più sul motore per suggerimenti per i numeri elevati di tabelle aperte in Cloud SQL.

Nota: Cloud SQL offre flessibilità per modificare questi valori.

Suggerimenti per gli schemi

Definire sempre le chiavi primarie

La definizione di chiavi primarie nelle tabelle consente di organizzare fisicamente i dati in modo da agevolare ricerca, recupero e ordinamento dei record più rapidi e, di conseguenza, di migliorare le prestazioni.

Le chiavi primarie che utilizzano preferibilmente valori interi con incremento automatico sono ideali per i sistemi OLTP.

L'assenza di chiavi primarie è anche uno dei motivi principali dei ritardi nel processo di replica o del suo rallentamento negli scenari di replica basati su righe.

Creare indici

La creazione di indici consente un recupero più rapido dei dati e, di conseguenza, migliora le prestazioni delle query di lettura. Crea indici per le colonne utilizzate nelle clausole WHERE, ORDER BY e GROUP BY delle query. 

NOTA: troppi indici non utilizzati potrebbero anche ostacolare le prestazioni del database.

Best practice per l'ottimizzazione delle prestazioni

Eseguire benchmark

Esegui test delle prestazioni o benchmark per scoprire se la configurazione è ottimale o può essere migliorata ulteriormente regolando le configurazioni per hardware, database MySQL o progettazione dello schema. Modifica un parametro alla volta ed esamina i risultati rispetto al benchmark per determinare eventuali miglioramenti.

Pool di connessioni

Il pool di connessioni è una tecnica per creare e gestire un pool di connessioni pronte per essere utilizzate da qualsiasi processo che ne abbia bisogno. Il pool di connessioni può aumentare notevolmente le prestazioni dell'applicazione, riducendo al contempo l'utilizzo complessivo delle risorse. Esamina i dettagli su come gestire le connessioni dall'applicazione, inclusi il conteggio delle connessioni e il timeout.

Distribuire il carico di lavoro di lettura alle repliche di lettura

Puoi utilizzare le repliche di lettura (più repliche in tutta la zona) per scaricare il carico di lavoro di lettura dall'istanza principale. Questo riduce il sovraccarico o il carico sull'istanza principale e, a sua volta, migliora le prestazioni dell'istanza principale. Sono inoltre disponibili altre risorse per le query di lettura sulla replica di lettura.  

ProxySQL, un proxy MySQL open source ad alte prestazioni in grado di eseguire il routing delle query di database, può essere utilizzato per scalare orizzontalmente il database Cloud SQL per MySQL.

Evitare query a lunga esecuzione

È noto che le query che vengono eseguite per diversi minuti oppure ore causano un peggioramento delle prestazioni. 

  • I log di annullamento vengono utilizzati per archiviare la versione precedente delle righe modificate per eseguire il rollback della transazione e anche per fornire la lettura coerente (snapshot di dati) in una transazione. Questi log di annullamento vengono memorizzati sotto forma di elenchi collegati con versioni recenti che fanno riferimento a versioni precedenti, che rimandano ulteriormente a versioni precedenti e così via. Le transazioni a lunga esecuzione tendono a ritardare l'eliminazione definitiva dei log di annullamento e pertanto aumentano l'elenco dei log di annullamento. InnoDB deve attraversare l'elevato volume di log di annullamento e il lungo collegato, il che riduce le prestazioni. 
  • Le query a lunga esecuzione consumano inoltre risorse (come memoria, buffer, blocchi), che non vengono liberate a lungo e influiscono sulle altre query a causa della mancanza di risorse.

Evitare transazioni di grandi dimensioni

Troppe modifiche ai record (aggiornamento, eliminazione, inserimento) in una singola transazione riserveranno risorse (blocchi, buffer) per troppi record. Questo potrebbe causare un overflow dei buffer dei log comportando I/O su disco. Le query rimanenti dovranno attendere il rilascio delle risorse o dei blocchi. Ciò comporta l'inserimento di una quantità eccessiva di dati nel pool del buffer, il che impedisce un suo ulteriore utilizzo. Il rollback di transazioni di queste dimensioni riduce anche le prestazioni del database. Per ovviare a questo problema, il consiglio è quello di suddividere le transazioni di grandi dimensioni in transazioni più piccole e a esecuzione più rapida.

Ottimizzare le query

Ottimizza sempre le query per ottenere risultati ottimali, ovvero meno risorse e un'esecuzione più rapida. Esamina i suggerimenti per l'ottimizzazione delle query MySQL.

Strumenti per l'ottimizzazione delle prestazioni

Monitoraggio

Cloud SQL offre dashboard predefinite per diversi prodotti Google Cloud, tra cui una dashboard di monitoraggio Cloud SQL predefinita. Gli utenti possono utilizzare questa dashboard per monitorare l'integrità generale delle istanze principali e di replica. Gli utenti possono anche creare le proprie dashboard personalizzate per mostrare le metriche di loro interesse. Utilizzando queste dashboard e metriche, è possibile identificare e risolvere i vari colli di bottiglia delle prestazioni, come un elevato utilizzo di CPU o di memoria, utilizzando i consigli elencati in precedenza. Anche gli avvisi possono essere configurati in base a queste metriche.

Flag relativo alle query lente

Il flag relativo alle query lente può essere abilitato sull'istanza Cloud SQL per MySQL per identificare le query la cui esecuzione richiede più tempo di quello specificato in long_query_time. Queste query lente possono essere ulteriormente analizzate e ottimizzate per migliorare le prestazioni. Scopri come abilitare e controllare le query lente per le istanze Cloud SQL.

Schema delle prestazioni

Lo schema delle prestazioni fornisce un monitoraggio di basso livello dell'istanza MySQL. Lo schema delle prestazioni può essere abilitato su un'istanza Cloud SQL per MySQL con memoria > 15 Gb. I report Sys schema forniscono vari report per identificare i colli di bottiglia, le attese, gli indici mancanti, l'utilizzo della memoria e così via.

Insight sulle query

Query Insights è una funzionalità nativa di Cloud SQL in cui le query possono essere profilate e analizzate per migliorarne le prestazioni. Query Insights supporta il monitoraggio intuitivo e fornisce informazioni diagnostiche che non si limitano al rilevamento per identificare la causa principale dei problemi di prestazioni. 

Suggerimenti per le prestazioni

Il motore per suggerimenti per un numero elevato di tabelle Cloud SQL è anche una funzionalità nativa di Cloud SQL che fornisce agli utenti di Cloud SQL suggerimenti relativi al miglioramento delle prestazioni dei database esistenti, consigli per definire la configurazione al fine di migliorare le prestazioni e ridurre i costi delle istanze. Per ulteriori dettagli, consulta i motori per suggerimenti di Cloud SQL.

Google Cloud offre un database MySQL gestito che è stato creato per soddisfare le tue esigenze aziendali, dal ritiro del data center on-premise all'esecuzione di applicazioni SaaS, fino alla migrazione dei sistemi aziendali principali.