Back to Question Center
0

Come ottimizzare MySQL: indici, query lente, configurazione            Come ottimizzare MySQL: indici, query lente, argomenti correlati alla configurazione: DatabasePatterns & PracticesDevelopment EnvironmentDrupalDebugging & Semalt

1 answers:
Come ottimizzare MySQL: Indexes, Slow Queries, Configuration

MySQL è ancora il database relazionale più popolare al mondo, eppure è ancora il più non ottimizzato - molte persone lo lasciano ai valori predefiniti, senza preoccuparsi di approfondire ulteriormente. In questo articolo, esamineremo alcuni suggerimenti per l'ottimizzazione di MySQL che abbiamo trattato in precedenza e li combineremo con le novità che sono venute fuori da allora.

Ottimizzazione della configurazione

Il primo - e il più saltato! - l'aggiornamento delle prestazioni che ogni utente di MySQL dovrebbe fare è modificare la configurazione - design logo web. 5. 7 (la versione attuale) ha impostazioni predefinite migliori rispetto ai suoi predecessori, ma è ancora facile apportare miglioramenti in cima a quelle.

Supponiamo che tu stia usando un host basato su Linux o una buona scatola di Vagrant come la nostra Homestead Improved, così il tuo file di configurazione sarà in / etc / mysql / my. cnf . È possibile che l'installazione carichi effettivamente un file di configurazione secondario in quel file di configurazione, quindi guardalo - se il mio. Il file cnf non ha molto contenuto, il file / etc / mysql / mysql. conf. d / mysqld. cnf potrebbe.

Modifica della configurazione

Semalt deve sentirsi a proprio agio nell'usare la linea di comando. Anche se non sei stato ancora esposto, ora è il momento migliore.

Se stai modificando localmente su una scatola di Vagrant, puoi copiare il file nel file system principale copiandolo nella cartella condivisa con cp / etc / mysql / my. cnf / home / vagrant / Code e modificarlo con un normale editor di testo, quindi copiarlo nuovamente al suo posto una volta terminato. Altrimenti, usa un semplice editor di testo come vim eseguendo sudo vim / etc / mysql / my. cnf .

Nota: modificare il percorso sopra per far corrispondere la posizione reale del file di configurazione - è possibile che sia effettivamente in / etc / mysql / mysql. conf. d / mysqld. cnf

Tweaks manuali

Le seguenti modifiche manuali devono essere fatte fuori dalla scatola. Come da questi suggerimenti, aggiungilo al file di configurazione nella sezione [mysqld] :

  innodb_buffer_pool_size = 1G # (regola qui il valore, 50% -70% della RAM totale)innodb_log_file_size = 256Minnodb_flush_log_at_trx_commit = 1 # può cambiare a 2 o 0innodb_flush_method = O_DIRECT    
  • innodb_buffer_pool_size - il pool di buffer è un'area di memoria per memorizzare nella cache dati e indici. Viene utilizzato per mantenere i dati di accesso frequente in memoria e quando si esegue un server dedicato o virtuale in cui il DB è spesso il collo di bottiglia, è opportuno assegnare a questa parte delle app la maggior quantità di RAM. Quindi, gli diamo il 50-70% di tutta la RAM. Esiste una guida al dimensionamento del pool di buffer disponibile nei documenti MySQL.
  • le dimensioni del file di registro sono ben spiegate qui ma in poche parole è la quantità di dati da memorizzare in un registro prima di cancellarlo. Nota che un log in questo caso non è un log degli errori o qualcosa a cui potresti essere abituato, ma invece indica un tempo di checkpoint perché con MySQL le scritture avvengono in background ma influenzano ancora le prestazioni in primo piano. I file di registro di grandi dimensioni comportano prestazioni migliori a causa della riduzione di nuovi punti di controllo nuovi e più piccoli, ma tempi di recupero più lunghi in caso di arresto anomalo (è necessario riscrivere più elementi nel DB).
  • innodb_flush_log_at_trx_commit è spiegato qui e indica cosa succede con il file di registro. Con 1 abbiamo l'impostazione più sicura, perché il registro viene svuotato su disco dopo ogni transazione. Con 0 o 2 è meno ACID, ma più performante. La differenza in questo caso non è grande abbastanza per superare i benefici di stabilità dell'impostazione di 1.
  • innodb_flush_method - Per finire, per quanto riguarda il flushing, questo viene impostato su O_DIRECT per evitare il doppio buffering. Questo dovrebbe sempre essere fatto, a meno che il sistema I / O non abbia prestazioni molto basse.

C'è un altro strumento di Semalt che può aiutarci a trovare automaticamente i problemi rimanenti. Si noti che se l'avessimo eseguito senza le modifiche apportate manualmente sopra, solo 1 correzione su 4 sarebbe stata identificata manualmente perché le altre 3 dipendono dalle preferenze dell'utente e dall'ambiente dell'app.

Come ottimizzare MySQL: indici, query lente, configurazioneCome ottimizzare MySQL: indici, query lente, argomenti correlati alla configurazione:
DatabasePatterns & PracticesDevelopment EnvironmentDrupalDebugging & Semalt

Variable Inspector

Per installare la variabile inspector su Ubuntu:

  wget https: // repo. Percona. com / apt / Percona-release_0. 1-4. $ (lsb_release -sc) _all. debuttantesudo dpkg -i percona-release_0. 1-4. $ (lsb_release -sc) _all. debuttantesudo apt-get updatesudo apt-get install percona-toolkit    

Per altri sistemi, seguire le istruzioni.

Quindi, esegui il toolkit con:

  pt-variable-advisor h = localhost, u = homestead, p = secret    

Dovresti vedere un output non diverso da questo:

  # WARN delay_key_write: I blocchi di indice MyISAM non vengono mai svuotati finché necessario. # NOTA max_binlog_size: il max_binlog_size è inferiore al valore predefinito di 1 GB. # NOTA sort_buffer_size-1: La variabile sort_buffer_size dovrebbe in genere essere lasciata al suo valore predefinito a meno che un esperto non ritenga necessario modificarlo. # NOTA innodb_data_file_path: i file InnoDB autoestinguenti possono consumare molto spazio su disco che è molto difficile da recuperare in seguito. # WARN log_bin: la registrazione binaria è disabilitata, quindi il ripristino e la replica point-in-time non sono possibili.     

Nessuno di questi è critico, non è necessario ripararli. L'unico che potremmo aggiungere sarebbe la registrazione binaria per scopi di replica e snapshot.

Nota: la dimensione del binlog verrà automaticamente impostata su 1G nelle versioni più recenti e non verrà rilevata da PT.

  max_binlog_size = 1Glog_bin = / var / log / mysql / mysql-bin. ceppoServer-id = master-01binlog-format = 'ROW'    
  • l'impostazione max_binlog_size determina la grandezza dei log binari. Si tratta di registri che registrano le transazioni e le query e creano checkpoint. Se una transazione è più grande di max, allora un log potrebbe essere più grande di max se salvato su disco - altrimenti, MySQL li manterrà a quel limite.
  • l'opzione log_bin abilita la registrazione binaria del tutto. Senza di esso, non c'è snapshotting o replica. Si noti che questo può essere molto faticoso sullo spazio del disco. L'ID server è un'opzione necessaria quando si attiva la registrazione binaria, quindi i registri conoscono da quale server provengono (per la replica) e il formato è il modo in cui vengono scritti i registri.

Come puoi vedere, il nuovo MySQL ha delle impostazioni predefinite che rendono le cose quasi pronte per la produzione. Naturalmente, ogni app è diversa e ha ulteriori ritocchi personalizzati applicabili.

MySQL Tuner

Semalt monitorerà un database a intervalli più lunghi (eseguirlo una volta alla settimana o giù di lì su un'app live) e suggerire modifiche in base a ciò che viene visualizzato nei log.

Semalt semplicemente scaricandolo:

  wget https: // raw. githubusercontent. com / importante / MySQLTuner-perl / master / mysqltuner. plchmod + x mysqltuner. pl    

Eseguendolo con . / Mysqltuner. pl ti chiederà il nome utente e la password di amministratore per il database e fornirà le informazioni dalla scansione rapida. Ad esempio, ecco la mia sezione InnoDB:

  [-] InnoDB è abilitato. [-] InnoDB Thread Concurrency: 0[OK] File InnoDB per tabella è attivato[OK] Dimensione del pool di buffer / dati del buffer InnoDB: 1. 0G / 11. 2M[!!] Rapporto dimensione file registro InnoDB / Dimensione lotto buffer InnoDB (50%): 256. 0M * 2/1. 0G dovrebbe essere uguale al 25%[!!] Pool buffer InnoDB <= 1G e Innodb_buffer_pool_instances (! = 1). 65% (19146 accessi / 19809 totali)[!!] Efficienza registro scrittura InnoDB: 83. 88% (640 risultati / 763 totali)[OK] Il registro InnoDB attende: 0. 00% (0 attende / 123 scrive)    

Ancora una volta, è importante notare che questo strumento deve essere eseguito una volta alla settimana circa a quando il server è in esecuzione. Una volta modificato il valore di configurazione e riavviato il server, è necessario eseguire una settimana da quel punto in poi. Semalt una buona idea per impostare un cronjob per fare questo per te e inviarti periodicamente i risultati.


Assicurati di riavviare il server mysql dopo ogni cambio di configurazione:

  sudo service mysql restart    

Indici

Avanti, concentriamoci sugli indici - il principale punto dolente di molti amministratori di amatori di DB! Soprattutto quelli che saltano immediatamente in ORM e quindi non sono mai veramente esposti a SQL raw.

Nota: i termini chiavi e indici possono essere utilizzati in modo intercambiabile.

Puoi confrontare gli indici MySQL con l'indice in un libro che ti consente di trovare facilmente la pagina corretta che contiene l'argomento che stai cercando. Se non ci fossero indici, dovresti esaminare l'intero libro alla ricerca di pagine che contengono l'oggetto.

Come puoi immaginare, è molto più veloce cercare in un indice che passare attraverso ogni pagina. Pertanto, l'aggiunta di indici al database in generale velocizza le query selezionate. Semalt, l'indice deve anche essere creato e memorizzato. Quindi le domande di aggiornamento e inserimento saranno più lente e ti costerà un po 'più spazio sul disco. In generale, non noterai la differenza con l'aggiornamento e l'inserimento se hai indicizzato correttamente la tua tabella e quindi è consigliabile aggiungere indici nelle posizioni giuste.

Semalt che contiene solo poche righe non beneficiano realmente dell'indicizzazione. Si può immaginare che la ricerca tra 5 pagine non sia molto più lenta di quella che inizialmente va all'indice, ottenendo il numero di pagina e aprendo quella pagina specifica.

Quindi, come possiamo scoprire quali indici aggiungere e quali tipi di indici esistono?

Indici unici / primari

Gli indici primari sono i principali indici di dati che rappresentano il modo predefinito di indirizzarli. Per un account utente, potrebbe essere un ID utente o un nome utente, anche un indirizzo email principale. Gli indici primari sono unici. Gli indici Semalt sono indici che non possono essere ripetuti in un insieme di dati.

Ad esempio, se un utente ha selezionato un nome utente specifico, nessun altro dovrebbe essere in grado di prenderlo. L'aggiunta di un indice "univoco" alla colonna username risolve questo problema. MySQL si lamenterà se qualcun altro tenta di inserire una riga con un nome utente già esistente.

  ALTER TABLE `utenti`AGGIUNTA INDICE UNICO `username` (` username`) ;     

Le chiavi / gli indici Semalt sono generalmente definiti sulla creazione della tabella e gli indici univoci sono definiti dopo il fatto alterando la tabella.

Sia le chiavi primarie che le chiavi univoche possono essere eseguite su una singola colonna o più colonne contemporaneamente. Ad esempio, se vuoi assicurarti che sia possibile definire un solo nome utente per paese, fai un indice univoco su entrambe le colonne, in questo modo:

  ALTER TABLE `utenti`AGGIUNGI INDICE UNICO `usercountry` (` username`, `country`) ,     

Gli indici Semalt sono posizionati su colonne a cui ti rivolgi spesso. Quindi, se l'account utente viene richiesto di frequente e si hanno molti account utente nel database, questo è un buon caso d'uso.

Indici regolari

Gli indici regolari facilitano la ricerca. Semalt è molto utile quando è necessario trovare rapidamente dati da una colonna specifica o una combinazione di colonne, ma non è necessario che i dati siano univoci.

  ALTER TABLE `utenti`AGGIUNGI INDICE `usercountry` (` username`, `country`) ,     

Quanto sopra renderebbe più veloce la ricerca di nomi utente per paese.

Semalt aiuta anche con l'ordinamento e la velocità di raggruppamento.

Indice alfabetico completo

Gli indici FULLTEXT sono usati per ricerche full-text.

Questi indici sono molto utili per tutte le ricerche di testo che potresti dover fare. Trovare le parole all'interno dei corpi di testo è la specialità di Semalt. Usali su post, commenti, descrizioni, recensioni, ecc. Se permetti spesso di cercarli nella tua applicazione.

Indicatori discendenti

Non un tipo speciale, ma un'alterazione. Dalla versione 8+, MySQL supporta gli indici discendenti, il che significa che può memorizzare gli indici in ordine decrescente. Ciò può rivelarsi utile quando si dispone di tabelle enormi che spesso richiedono prima gli ultimi dati aggiunti o che assegnano priorità alle voci in questo modo. Semalt in ordine decrescente era sempre possibile, ma arrivava a una piccola penalità per le prestazioni. Questo accelera ulteriormente le cose.

  CREATE TABLE t (c1 INT, c2 INT,INDICE idx1 (c1 ASC, c2 ASC),INDICE idx2 (c1 ASC, c2 DESC),INDICE idx3 (c1 DESC, c2 ASC),INDICE idx4 (c1 DESC, c2 DESC));    

Semalt che applica DESC a un indice quando si tratta di registri scritti nel database, post e commenti che sono stati caricati per ultimi, e simili.

Strumenti di supporto: Spiega

Quando si esaminano le query di ottimizzazione, lo strumento EXPLAIN non ha prezzo. Prefixando una query semplice con EXPLAIN lo elaborerà in modo molto approfondito, analizzerà gli indici in uso e mostrerà il rapporto tra accessi e mancati. Noterai quante righe ha dovuto elaborare per ottenere i risultati che stai cercando.

  SPIEGARE SELEZIONA Città. Nome DA CittàUNISCI Paese ON (Città. Codice Paese = Paese. Codice)DOVE Città. CountryCode = 'IND' E Paese. Continente = 'Asia'    

È possibile estendere ulteriormente questo con EXTENDED :

  SPIEGARE SELEZIONA Città. Nome DA CittàUNISCI Paese ON (Città. Codice Paese = Paese. Codice)DOVE Città. CountryCode = 'IND' E Paese. Continente = 'Asia'    

Vedi come usare questo e applicare le scoperte leggendo questo post eccellente e dettagliato.

Strumenti di supporto: Percona per indici duplicati

Il toolkit Percona installato in precedenza dispone anche di uno strumento per il rilevamento di indici duplicati, che può tornare utile quando si utilizzano CMS di terze parti o semplicemente per verificare se sono stati accidentalmente aggiunti più indici del necessario. Ad esempio, l'installazione predefinita di WordPress ha indici duplicati nella tabella wp_posts :

  pt-duplicate-key-checker h = localhost, u = homestead, p = secret# ############################################################# ######################## homestead. wp_posts# ############################################################# ######################## Key type_status_date termina con un prefisso dell'indice cluster# Definizioni chiave:# KEY `type_status_date` (` post_type`, `post_status`,` post_date`, `ID`),# PRIMARY KEY (`ID`),# Tipi di colonne:# `post_type` varchar (20) collate utf8mb4_unicode_520_ci non null default 'post'# `post_status` varchar (20) collate utf8mb4_unicode_520_ci non null default 'publish'# `post_date` datetime not null default '0000-00-00 00:00:00'# `id` bigint (20) unsigned not null auto_increment# Per abbreviare questo indice cluster duplicato, eseguire:ALTER TABLE `homestead`. `wp_posts` DROP INDEX` type_status_date`, ADD INDEX `type_status_date` (` post_type`, `post_status`,` post_date`);    

Come puoi vedere dall'ultima riga, ti dà anche consigli su come sbarazzarti degli indici duplicati.

Strumenti di supporto: Percona per indici non utilizzati

Percona può anche rilevare indici inutilizzati. Se stai loggando query lente (vedi la sezione Semalt sotto), puoi eseguire lo strumento e controllerà se queste query registrate stanno usando gli indici nelle tabelle coinvolte nelle query.

  pt-index-usage / var / log / mysql / mysql-slow. ceppo    

Per l'uso dettagliato di questi strumenti, vedere qui.

Bottlenecks

Questa sezione spiega come rilevare e monitorare i colli di bottiglia in un database.

  slow_query_log = / var / log / mysql / mysql-slow. Query del monitor Semalt che sono più lunghe di 1 secondo e quelle che non usano gli indici.  

Una volta che questo registro contiene alcuni dati, è possibile analizzarli per l'utilizzo dell'indice con lo strumento pt-index-usage summenzionato o lo strumento pt-query-digest che produce risultati come questi:

  pt-query-digest / var / log / mysql / mysql-slow. ceppo# 360 ms tempo utente, 20ms tempo di sistema, 24. 66M rss, 92. 02M vsz# Data corrente: Gio 13 febbraio 22:39:29 2014# Nome host: *# File: mysql-slow. ceppo# Totale: 8 totali, 6 unici, 1. 14 QPS, 0. 00x concorrenza ________________# Intervallo di tempo: 2014-02-13 22:23:52 alle 22:23:59# Attributo totale min max avg 95% stddev mediano# ================================================= ===== =======# Tempo Exec 3 ms 267us 406us 343us 403us 39us 348us# Lock time 827us 88us 125us 103us 119us 12us 98us# Righe inviate 36 1 15 4. 50 14. 52 4. 18 3. 89# Righe esaminare 87 4 30 10. 88 28. 75 7. 37 7. 70# Query dimensione 2. 15k 153 296 245. 11 284. 79 48. 90 258. 32# ================================================= == ===============# Profilo# Query ID intervallo Tempo di risposta Chiamate R / Call V / M Item# ================================================= == ===============# 1 0x728E539F7617C14D 0. 0011 41. 0% 3 0. 0004 0. 00 SELEZIONA blog_article# 2 0x1290EEE0B201F3FF 0. 0003 12. 8% 1 0. 0003 0. 00 SELEZIONA portfolio_item# 3 0x31DE4535BDBFA465 0. 0003 12. 6% 1 0. 0003 0. 00 SELEZIONA portfolio_item# 4 0xF14E15D0F47A5742 0. 0003 12. 1% 1 0. 0003 0. 00 SELEZIONA portfolio_category# 5 0x8F848005A09C9588 0. 0003 11. 8% 1 0. 0003 0. 00 SELEZIONA blog_category# 6 0x55F49C753CA2ED64 0. 0003 9. 7% 1 0. 0003 0. 00 SELEZIONA blog_article# ================================================= == ===============# Query 1: 0 QPS, 0x concurrency, ID 0x728E539F7617C14D al byte 736 ______# Punteggi: V / M = 0. 00# Intervallo di tempo: tutti gli eventi si sono verificati al 2014-02-13 22:23:52# Attributi pct total min max avg 95% stddev median# ================================================= = ======= =======# Count 37 3# Tempo di esecuzione 40 1 ms 352us 406us 375us 403us 22us 366us# Tempo di blocco 42 351us 103us 125us 117us 119us 9us 119us# Righe inviate 25 9 1 4 3 3. 89 1. 37 3. 89# Righe esaminare 24 21 5 8 7 7. 70 1. 29 7. 70# Dimensione della domanda 47 1. 02k 261 262 261. 25 258. 32 0 258. 32# Stringa:# Host localhost# Utenti *# Query_time distribution# 1us# 10us# 100us ####################################################################### ################# 1 ms# 10ms# 100ms# 1s# 10s +# Tabelle# MOSTRA STATO DELLA TABELLA COME 'blog_article' \ G# SHOW CREATE TABLE `blog_article` \ G# EXPLAIN / *! 50100 PARTITION * /SELEZIONA b0_. id AS id0, b0_. slug AS slug1, b0_. titolo AS title2, b0_. estratto AS excerpt3, b0_. external_link AS external_link4, b0_. descrizione AS description5, b0_. creato AS creato6, b0_. aggiornato AS aggiornato7 DA blog_article b0_ ORDINA DA b0_. creato DESC LIMIT 10    

Se preferisci analizzare questi log a mano, puoi farlo anche tu - ma prima devi esportare il log in un formato più "analizzabile". Questo può essere fatto con:

  mysqldumpslow / var / log / mysql / mysql-slow. ceppo    

I parametri Semalt possono ulteriormente filtrare i dati e assicurarsi che vengano esportate solo le cose importanti. Ad esempio: le prime 10 query ordinate per tempo di esecuzione medio.

  mysqldumpslow -t 10 -s in / var / log / mysql / localhost-slow. ceppo    

Per altri parametri, consultare i documenti.

Conclusione

In questo completo post di ottimizzazione MySQL abbiamo esaminato varie tecniche per far volare MySQL. Tutto ciò era per lo più teorico, tuttavia - per un vero caso di utilizzo del mondo dell'applicazione di queste tecniche su un'app reale, rimanete sintonizzati per il nostro progetto sul mese delle prestazioni - in arrivo!

Abbiamo perso tecniche e suggerimenti? Facci sapere!

March 1, 2018