MySQL
e' un diffuso database relazionale Open Source
che fornisce un'ampia serie di funzionalita'.
In questa pagina analizziamo la funzionalita'
di compressione dei dati,
che fornisce vantaggi su alcune tipologie di dati.
La compressione permette un significativo risparmio di spazio
al costo di un maggior consumo della CPU.
Spesso pero' anche le prestazioni finali sono comunque migliori
perche' si ottengono vantaggi sulle operazioni di I/O.
In questo documento cerchiamo di vedere, da punto di vista pratico, l'utilizzo della data compression. Le ultime versioni di MySQL permettono la compressione dati sia a livello di tabella che di pagina: entrambe le tecniche sono descritte.
Nel seguito sono riportate alcune informazioni di interesse organizzate in paragrafi specifici: Introduzione, Table compression, Page compression, Suggerimenti per le applicazioni, Varie ed eventuali, ...
Il contenuto di questo documento e' tecnico. E' opportuna una conoscenza del database MySQL e dell'Engine InnoDB.
La compressione permette un significativo risparmio di spazio
al costo di un maggior consumo della CPU.
Spesso pero' le prestazioni finali sono comunque migliori
perche' si ottengono vantaggi sulle operazioni di I/O.
Anche se la compressione agisce su diversi elementi
i datatype su cui e' piu' efficace sono ovviamente:
VARCHAR, VARBINARY, BLOB e TEXT.
Ci sono pero' anche alcune controindicazioni nell'utilizzo
della compressione!
Se i dati sono pochi, se sono modificati spesso,
se non sono utilizzate colonne di tipo testuale o blob, ...
l'utilizzo della compressione non porta a significativi
risparmi di spazio e rallenta la base dati.
La compressione va quindi utilizzata solo nei casi in
cui si hanno grandi quantita' di dati di tipo testuale
acceduti principalmente in lettura.
Con MySQL si possono utilizzare due differenti modalita' di compressione dei dati:
Nel seguito verranno descritte in dettaglio... continuate a leggere!
La table compression comprime tutti i dati di una tabella ed
i suoi indici ottenendo un significativo risparmio di spazio.
MySQL utilizza la famosa libreria zlib per la compressione dei dati.
La table compression e' disponibile per le tabelle InnoDB
configurate con file-per-table
utilizzando il file format Barracuda.
La table compression e' disponibile dalla versione 5.1,
migliorata notevolmente nelle
versioni successive e,
nelle versioni piu' recente, tutti i prerequisiti sono gia' presenti per default.
Creare una tabella compressa e' semplice:
CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED;
Per rimuovere la compressione e' sufficiente:
ALTER TABLE t1 ROW_FORMAT=DYNAMIC; OPTIMIZE TABLE t1;
E' possibile specificare un'ulteriore clausola KEY_BLOCK_SIZE il cui valore di default (8 per indicare 8K) e' tuttavia adatto alla maggioranza dei casi.
L'analisi sull'efficacia della compressione si effettua su due diverse dimensioni: lo spazio ed il tempo!
Per lo spazio la verifica e' semplice: basta confrontare la dimensione dei file .ibd tra tabelle non compresse e compresse.
Per valutare l'impatto sulla CPU sono disponibili diverse tabelle dell'INFORMATION_SCHEMA: INNODB_CMP, INNODB_CMPMEM, INNODB_CMP_PER_INDEX.
Poiche' per le tabelle
compresse e' necessaria una doppia immagine
delle pagine in memoria,
quando si utilizza la compressione e' opportuno
incrementare il parametro INNODB_BUFFER_POOL_SIZE.
Se i dati lo consentono si puo' anche ridurre maggiormente
la dimensione della pagina compressa: KEY_BLOCK_SIZE.
Tuttavia i valori piu' efficaci sono tipicamente 8 (che e' gia' il default)
o 4.
La page compression che comprime a livello di pagina e' utilizzabile sui sistemi operativi che supportano gli sparse file. In pratica la pagina viene compressa lasciandola vuota al fondo e, se lo spazio libero e' sufficiente al sistema operativo per liberare uno o piu' blocchi di disco si ottiene un risparmio di spazio.
La page compression e' disponibile dalla versione 5.7 di MySQL su InnoDB e sulle tabelle organizzate come file-per-table (che e' il default in 5.7). La Page Compression viene chiamata anche transparent page compression.
Creare una tabella con la page compression e' semplice:
CREATE TABLE t1 (c1 INT PRIMARY KEY) COMPRESSION="zlib";
Altrettanto semplice e' modificare una tabella esistente:
ALTER TABLE t1 COMPRESSION="zlib"; OPTIMIZE TABLE t1; ALTER TABLE t1 COMPRESSION="None"; OPTIMIZE TABLE t1;
Nel caso della Page Compression la valutazione del risparmio
in termini di spazio controllando
la dimensione dei file .ibd puo' ingannare...
il comando ls -l riporta la dimensione apparente del file
mentre il comando du -h riporta la dimensione reale.
La compressione dei dati e' utile... quando si hanno molti dati!
Vediamo in questo paragrafo qualche suggerimento applicativo
utile per caricare grandi quantita' di dati.
Quando si fanno caricamenti massivi
ovviamente si lavora a batch e si bufferizza tutto il possibile.
Dal punto di vista applicativo ci sono ulteriori suggerimenti:
evitare i COMMIT frequenti;
non effettuare controlli sui dati
(eg. controllo delle foreign key),
utilizzare le istruzioni di INSERT con la sintassi per record multipli,
inserire i dati ordinati per primary key.
Il disegno del DB e' molto importante ma le regole da seguire sono
semplici: mettere solo i dati e creare solo gli indici che servono.
Nel caso della compressione queste indicazioni sono ancora piu'
importanti che in un normale disegno di database.
E' quindi fondamentale normalizzare i dati e, per quanto riguarda gli
indici, valutarne l'eventuale successiva creazione.
I parametri di tuning consigliati sono: innodb_flush_log_at_trx_commit=2, innodb_log_file_size=grande (eg 4GB), innodb_buffer_pool_size adeguato e niente log_bin. Sui benchmark massivi tipicamente si effettuano tuning piu' spinti (eg. disabilitare il performance schema) ma non e' il caso in produzione...
Ma cos'e' meglio? Table compression, Page compression o non comprimere affatto?
Dipende... su dati normali la percentuale di compressione e' del 50%
con entrambe le tecniche.
L'uso di CPU e' tipicamente inferiore con la transparent compression
ma non tutti i sistemi operativi la supportano.
Quindi e' necessario seguire il consiglio di San Tommaso: provate!
Titolo: MySQL Compression
Livello: Esperto
Data:
14 Febbraio 2017
Versione: 1.0.0 - 14 Febbraio 2017
Autore: mail [AT] meo.bogliolo.name