[MySql]: controlli, ottimizzazione e deframmentazione

[aasmdaa]

Ciao a tutti,

avrei bisogno di alcune indicazioni (o un buon tutorial) sui controlli da effettuare su un database MySql in piedi da circa 4 anni per ottimizzarlo, capire se ci sono problemi sulle tabelle, se sia il caso di deframmentarle, ecc.
Insomma le operazioni di manutenzione e ottimizzazione classiche che si vanno a fare in questi casi.

Inoltre vorrei capire se facendo il dump del database, eliminandolo dal server e ricreandolo dal dump vado ad ottimizzare le tabelle togliendo almeno la frammentazione dato che vengono ricostruire da zero oppure non è così.

Grazie
ciao

[aasmdaa]

Accodo anche altra domanda: in alcune tabelle ci sono oltre 1 milione di records.

In casi come questi è il caso di dividere la tabella, ad esempio salvandola con

nome_tabella_old

e gestendone la lettura solo nel caso di necessità e creare una tabella nuova così da avere pochi records?

Questo porterebbe ad un notevole incremento di lettura e scrittura su tale tabella o sarebbe minima la differenza?

[franzauker2.0]

Originariamente inviato da aasmdaa
Ciao a tutti,

avrei bisogno di alcune indicazioni (o un buon tutorial) sui controlli da effettuare su un database MySql in piedi da circa 4 anni per ottimizzarlo, capire se ci sono problemi sulle tabelle, se sia il caso di deframmentarle, ecc.
Insomma le operazioni di manutenzione e ottimizzazione classiche che si vanno a fare in questi casi.

Bhè dovrei limitarmi alla faccina
Comunque dipende essenzialmente dall'engine, myisam o innodb, e se hai attivato i checksum sulle righe oppure no.
L'ottimizzazione si ottiene con mysqlcheck, con
--check
--auto-repair
--optimize
Ciò funziona con myisam, con innodb la tabella viene ricreata con un dump-restore occulto (vedi dopo)

Inoltre vorrei capire se facendo il dump del database, eliminandolo dal server e ricreandolo dal dump vado ad ottimizzare le tabelle togliendo almeno la frammentazione dato che vengono ricostruire da zero oppure non è così.

E' così, ma perdi due elementi.
Il "riscaldamento" (visto che si apre un mondo non lo apro, lascio fare a chi è più esperto) e NON ottieni indietro il tablespace innodb (il quale cresce sempre).

Originariamente inviato da aasmdaa
Accodo anche altra domanda: in alcune tabelle ci sono oltre 1 milione di records.

In casi come questi è il caso di dividere la tabella, ad esempio salvandola con

nome_tabella_old

e gestendone la lettura solo nel caso di necessità e creare una tabella nuova così da avere pochi records?

Questo porterebbe ad un notevole incremento di lettura e scrittura su tale tabella o sarebbe minima la differenza?

Si chiama shard(ing) o partizionamento orizzontale, può essere gestita a livello applicazione, a livello db (con trigger), a livello di motore (il partizionamento è stato incluso da mysql 5.1, poi... non dico cosa succede nelle altre versioni, visto che a qualcuno non interessa)

La risposta è ... qui bisognerebbe dire tante cose, che non dico ... dipende dalla funzione di scattering (sharding) la quale...

[aasmdaa]

L'engine è inno-db.

Mi sono studiato il comando mysqlcheck e l'ho utilizzato. Tutte le tabelle hanno dato esito OK con questo in finale:

User time 0.00, System time 0.00
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 467, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 120, Involuntary context switches 1

Che al momento non mi dice molto che andrò a cercare di capire.

Mi sono studiato che cos'è il tablespace innodb ma sul "riscaldamento" non ho trovato nulla. Cosa si intende od ha un nome tecnico così cerco di capire a cosa ti riferisci?

Per lo sharding avevo letto qualcosa ma devo ancora approfondire per capire da quando è necessario o consigliabile.

grazie

[franzauker2.0]

Per inno come detto ottimizzazione uguale a dump e restare il riscaldamento è il termine per il caricamento degli indici e la stima delle righe fatte da ottimizzatore un server riavviato è detto freddo ora ho cellulare domani magari scrivo dettagli

[franzauker2.0]

Bon qui ci sono parecchi temi diversi, i quali ovviamente non interessano a nessuno, ma pazienza.

1) riscaldamento del server, o warming up. Fondamentale, riguarda il caricamento nella query cache degli hash delle query eseguite, il reperimento dei dati e degli indici.
Innodb cacha non solo gli indici, ma anche i dati (oltre alle query), pertanto le prestazioni di un server "caldo" (riscaldato) dall'utilizzo da parte degli utenti sono di gran lunga migliori di quelli di un server "freddo", ovvero appena avviato.
Che è il caso di un optimize table (visto che come detto consiste in un dump+restore occulto nel caso di innodb).
In altri termini un server raggiunge (se ben configurato, cosa che non è minimamente scontata, anzi nella stragrande maggioranza dei casi è "mal" configurato, ma qui troverai tanti esperti che ti indirizzeranno in tal senso, ovvero "mal" ) le prestazioni massime un bel po' dopo il riavvio (anche qualche giorno).
Rammento, così tanto per dire, che la query cache mysql non è altro che l'hash della ... query medesima (binaria). Capisci bene che finquando non arrivano le query "più comuni" difficilmente la query cache entrerà in gioco durante la fase di pianificazione ed ottimizzazione

2) ottimizzazione & innodb.
Qui l'argomento è relativo al meccanismo di memorizzazione di questo engine, che è a pagine, con ordinamento per la chiave primaria.
La quale può esistere oppure no, se non esiste... ne viene fatta una occulta, autoincrementante, quindi esiste sempre.
In altri termini se la chiave primaria è saggiamente predisposta, e ci sono interrogazioni di tipo range sulla chiave, dopo l'ottimizzazione queste richiederanno il reperimento di meno pagine, e in ordine sequenziale, in quanto i dati sono proprio lì, appiccicati alla chiave primaria.
Più o meno proprio come accade deframmentando un hard disk magnetico.
Nel caso di un supporto fisico SSD, ovviamente, l'ordine è irrilevante, e l'ottimizzazione meno significativa.
C'è solo un piccolo miglioramento dovuto al (possibile) reperimento di meno pagine dovute all'overlap, ma è proprio robina da fanatici

3) ottimizzazione & effetti collaterali negativi.
Visto che, notoriamente, non mi intendo di queste cose, segnalo un possibile effetto collaterale negativo, ovvero l'addensamento proprio delle pagine dopo il riordinamento (siamo sempre nell'ipotesi di disco magnetico, e stock di dati così grande\sistema così mal configurato da non riuscire a cacharlo interamente).
In questo caso, paradossalmente, eventuali query che vadano ad intervenire (in scrittura) su un certo intervallo piccolo, non fanno altro che "martellare" le medesime pagine.
Nella mia (poca) esperienza è raro che ciò accada, o meglio era più frequente quando si aveva poca RAM e gli accessi fisici molto più frequenti di oggi.
Avere un server con 64GB non è un grosso problema, si iniziano ad averli sopra i 128GB (molto interessanti i fujitsu per inciso sotto questo profilo)

4) indici a copertura.
Un possibile ulteriore elemento da considerare, nel caso dell'ottimizzazione, è l'utilizzo (eventuale) di indici a copertura, tecnica usata su mysql per tenere direttamente il valore delle righe non nell'area dati, ma in quella indice (nelle relative foglie).
Siccome, come tutti sanno, gli indici secondari innodb mantengono (sono giustapposti) sempre alla chiave primaria, ecco che in questo caso, paradossalmente, si può avere un ordinamento sub-ottimale delle pagine (rispetto a come sono state inserite) proprio per il ri-ordinamento sulla base della chiave primaria "occulta", piuttosto dei componenti dell'indice di copertura.
Anche in questo caso parliamo di situazioni tipicamente non bimbominkieske.

5) partizionamento / sharding.
Come detto si può fare a livello applicativo o mediante il partizionamento di mysql il quale avviene, brutalmente, mediante la creazione di una o più tabelle fisicamente distinte (in realtà ciò accade nel caso di innodb_per_file, altrimenti sono zone del tablespace) che "magicamente" vengono individuate mediante "qualcosa"
Il "qualcosa" è la funzione di partizionamento, tipicamente anche qui un semplice range sulla chiave primaria (ma non necessariamente).
Che so "suddividi i record per anno di inserimento"

6) in questo caso ci sono vari approcci, ovvero equi (una sorta di scattering vero e proprio), o non-equi.
Quelli equi vanno bene nel caso in cui si voglia privilegiare l'accesso a sezioni distinte e grosso modo omogenee, ovvero dividere lo stock dei record in modo "equo".
Ci sono pregi e difetti: il difetto principale riguarda l'uso di dataset così grandi (anche partizionati) da non risiedere in RAM.
In questo caso può aver senso, sempre se uno tira a indovinare dopo aver letto topolino o lurkato qualche forum, adottare una suddivisione non-equa tra stock dei dati (poco usati) e quelli invece di frequente accesso.
Se ci riferiamo, ad esempio, a dati contabili, capisci bene che può aver senso suddividere in pochi (anche due) porzioni: dati 2013 e "resto-del-mondo".
In questo modo i dati 2013 sono piccoli e, possibilmente, cachabili, mentre il resto del mondo se ne va a 'ffanculo (sono poco usati)

7) tecnica di sharding senza indici.
Per carichi davvero grandi, ma proprio grandi, anche l'indice può diventare così grande da non poter risiedere nella RAM.
Questo è male, mooolto male, perchè in pratica perdi l'utilità di avere indici tout court, e nello stesso tempo paghi il costo di aggiornarli.
In questa situazione estrema si usa tipicamente solo shard, senza indici (!).
Ovvero memorizzi i dati in tabelle separate (mediante un partizionamento che segua la logica delle query, ammesso che esista, e che quindi agevoli il pruning) e vai di full scan in ogni caso.
Questo perchè - di nuovo - innodb cacha anche i dati, non solo gli indici, insomma "pregi il signore" che durante la fase di riscaldamento la query cache venga caricata delle interrogazioni più comuni e che le pagine dati più usate vengano in qualche modo tenute in RAM.
Mentre gli indici, come detto, sarebbero negativi e non positivi.
Francamente si tratta di un'evoluzione pensata a suo tempo per...
non lo scrivo, come al solito chi se ne frega?

[aasmdaa]

Bè che dire... chiaro e preciso (da pillola!).
Mi sembra di aver capito tutto (sicuri?), ora proseguo con alcuni studi in merito e nel week end faccio alcune ottimizzazioni su tale database.

Intanto grazie.