Analisi e Ottimizzazione della Memoria

Uno degli aspetti più importanti per l’amministrazione di un database MySQL è sicuramente il controllo delle sue performance e dello spazio che occupa su disco. In questa lezione ci occuperemo di come effettuare un’attenta analisi dello spazio occupato dai dati delle nostre tabelle MySQL e di come intervenire per abilitare la compressione nella creazione di nuove tabelle.

Prima di addentrarci nell’argomento, però, è doveroso fornire alcune nozioni basilari per permettere al lettore di seguire al meglio la guida.

Cos'è l'Information Schema?

Possiamo pensare all’Information Schema come ad un master database col compito di mantenere i dettagli di tutti gli altri database MySQL presenti sul nostro server. Il database INFORMATION_SCHEMA permette in poche parole l’accesso ai metadati dei vari database. Per rendere più chiaro questo concetto, possiamo pensare ai metadati come ai nomi dei database del server, i nomi delle tabelle, i nomi dei vari tipi di dati delle colonne o dei privilegi di accesso dei database. Per semplicità, diciamo quindi che l’Information Schema raccoglie in una sorta di catalogo tutte le informazioni di basso livello dei vari database del server.
Omettiamo di proposito l’esplicazione della struttura di INFORMATION_SCHEMA perchè sarebbe inutile ai fini della nostra analisi.

Occupazione di memoria di database e tabelle

Per verificare il contenuto dell'Information Schema del nostro server MySQL, possiamo eseguire una query SQL che facciamo riferimento al sopra citato database INFORMATION_SCHEMA:

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables
ORDER BY table_schema, table_name;

Il risultato sarà simile al seguente, diversificandosi ovviamente in base ai database del server su cui è eseguita la query precedente.

TABLE_SCHEMA    | TABLE_NAME
--------------- | ------------
bilotec         | #sql-16a_1ed
bilotec         | autori
bilotec         | categoria
bilotec         | cliente
bilotec         | corsi
db              | menu

Come possiamo vedere da questo esempio, all’interno di INFORMATION_SCHEMA sono presenti tutte le tabelle dei nostri database (nel nostro caso i database sono due: bilotec e db). Supponendo di volere analizzare il contenuto di una tabella, per capire quanto spazio stia occupando su disco e verificare che InnoDB la stia effettivamente comprimendo, possiamo adoperare il comando sotto riportato:

SELECT
    table_name AS 'Table',
    data_length + index_length 'Size (Bytes)',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
FROM information_schema.TABLES
WHERE table_schema = "[yourDB]"
    AND table_name = "[yourTABLE]";

Nello specifico, supponendo di voler analizzare la tabella cliente del database bilotec, modifichiamo la query nel seguente modo:

SELECT
    table_name AS 'Table',
    data_length + index_length 'Size (Bytes)',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
FROM information_schema.TABLES
WHERE table_schema = "bilotec"
    AND table_name = "cliente";

Verrà restituito il risultato seguente:

Table   | Size (Bytes)  | Size (MB)
------- | ------------- | ---------
cliente | 2024          | 0.00193

Per semplicità di utilizzo e di analisi, possiamo direttamente controllare le dimensioni di tutte le tabelle dei database presenti nel server come segue:

SELECT
     table_schema as `Database`,
     table_name AS `Table`,
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;

Occupazione di memoria di uno specifico database

Per controllare lo spazio usato da uno specifico database, possiamo invece usare il comando SQL:

SELECT *
FROM information_schema.TABLES
WHERE table_schema='bilotec'

Ciò restituirà un risultato simile al seguente:

TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: comp
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Compressed
TABLE_ROWS: 40660
AVG_ROW_LENGTH: 4168
DATA_LENGTH: 169480192
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 1572864
AUTO_INCREMENT: NULL
CREATE_TIME: 2014-10-10 08:33:22
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
CHECKSUM: NULL
CREATE_OPTIONS: row_format=COMPRESSED KEY_BLOCK_SIZE=4
TABLE_COMMENT:
1 row in set (0.00 sec)

Da questo risultato notiamo subito che lo storage engine è InnoDB (ENGINE: InnoDB), che effettua una compressione (ROW_FORMAT: Compressed) sui nostri dati, i quali hanno una dimensione pari a DATA_LENGTH: 169480192; più precisamente, la dimensione media di ogni riga è specificata da AVG_ROW_LENGTH: 4168.

Questa semplice query può quindi tornare molto utile per verificare se InnoDB abbia effettuato o meno una compressione sui dati, al fine di preservare lo spazio a disposizione del server.

Abilitare la compressione per le nuove tabelle

Grazie all’uso di comandi SQL è possibile creare delle tabelle in cui i dati sono salvati in forma compressa. La compressione dei dati migliora sia la performance del database che la sua scalabilità. Inoltre, il fatto che il server debba scambiare dati di dimensioni minori con il disco rigido, ha l’effetto di migliorare di molto i tempi di trasferimento.

In genere, InnoDB comprime automaticamente tutte quelle tabelle che possiedono un numero cospicuo di colonne con stringhe, o che possiedono dati che spesso vengono sia letti che scritti. Tuttavia, non possiamo mai essere certi a priori su quali tabelle saranno effettivamente compresse. Per forzare questa funzionalità, ed assicurarci che tutti i dati di una tabella vengano compressi, possiamo specificare questa necessità in fase di creazione della tabella. Vediamo come:

CREATE TABLE name
 (column1 INT PRIMARY KEY)
 ENGINE=InnoDB
 ROW_FORMAT=COMPRESSED
 KEY_BLOCK_SIZE=4;

Nella query precedente, ROW_FORMAT=COMPRESSED indica la volontà di comprimere i dati della tabella, mentre KEY_BLOCK_SIZE=4 specifica di volere una compressione applicati su blocchi da 4KB.