MariaDB/MySQL: configurare il buffer pool di InnoDB

Buffer pool di InnoDB: memoria e prestazioni innodb_buffer_pool_size è sicuramente la più importante fra le variabili di configurazione di MariaDB e MySQL: la cache delle tabelle InnoDB deve essere molto grande per raggiungere prestazioni ottimali. Se superiamo la memoria disponibile InnoDB userà l'area di swap del disco, e le prestazioni caleranno drasticamente. Se poi lo swap non è sufficiente, MariaDB crasherà. E' anche possibile che Linux termini il processo di MySQL perché richiede troppa memoria. Quanta memoria assegnare? Bisogna evitare di assegnare troppa memoria al buffer pool, ma qual è il valore esatto? La documentazione suggerisce di assegnare al buffer pool l'80% della memoria disponibile. Nella realtà spesso questo non è possibile: ad esempio perché per ogni connessione utente vengono allocati dei buffer, che in ambienti ad alta concorrenza possono occupare molto spazio. Il suggerimento migliore che si trova in rete è: il buffer pool dovrebbe essere grande quanto il working set - cioè l'insieme di dati che viene letto e scritto frequentemente. Come fare: stored procedure Stabilire le dimensioni del working set non è facile. Per semplificare questo compito abbiamo scritto una piccola stored procedure in SQL. Ecco che cosa fa: Legge la tabella INNODB_BUFFER_PAGE, nel database information_schema, un certo numero di volte. Ad ogni osservazione, inserisce gli id delle pagine in una tabella temporanea. Utilizziamo come id le informazioni relative alla loro provenienza (file fisico, blocco). Fornisce statistiche sulle pagine trovate, e su quante volte sono state osservate. Il totale indica le dimensioni ottimali del buffer pool. Il numero di pagine osservate una sola volta indica approssimativamente le dimensioni ottimali per la old list: la parte del buffer pool che contiene le pagine lette meno frequentemente. La variabile che controlla le dimensioni della old list è innodb_old_blocks_pct. Nel chiamare la procedura, si passano due parametri: Il numero di osservazioni; L'intervallo tra le osservazioni, in secondi. Esempio: MariaDB [_]> CALL show_working_set_size(10, 1); +-------+-------------+---------+ \| FOUND \| page_number \| bytes \| +-------+-------------+---------+ \| 1 \| 2 \| 32768 \| \| 2 \| 9 \| 147456 \| \| 4 \| 5 \| 81920 \| \| 5 \| 41 \| 671744 \| \| 6 \| 21 \| 344064 \| \| 7 \| 28 \| 458752 \| \| 9 \| 85 \| 1392640 \| \| 10 \| 128 \| 2097152 \| \| NULL \| 319 \| 5226496 \| +-------+-------------+---------+ Codice della procedura: CREATE PROCEDURE show_working_set_size( IN p_observations_num INT UNSIGNED , IN p_observation_interval INT UNSIGNED ) MODIFIES SQL DATA COMMENT 'Show InnoDB Buffer Pool working set size' BEGIN CREATE OR REPLACE TEMPORARY TABLE innodb_used_pages ( BLOCK_ID INT UNSIGNED NOT NULL , POOL_ID INT UNSIGNED NOT NULL , FOUND INT UNSIGNED NOT NULL COMMENT 'how much times the page was found in buffer' , PRIMARY KEY (POOL_ID, BLOCK_ID) ) ENGINE = MEMORY ; WHILE p_observations_num > 0 DO INSERT IGNORE INTO innodb_used_pages SELECT POOL_ID, BLOCK_ID, 1 AS FOUND FROM information_schema.INNODB_BUFFER_PAGE WHERE PAGE_STATE <> 'NOT_USED' ON DUPLICATE KEY UPDATE FOUND := FOUND + 1; DO SLEEP(p_observation_interval); SET p_observations_num = p_observations_num - 1; END WHILE; SELECT FOUND , COUNT(*) AS page_number , COUNT(*) * @@innodb_page_size AS bytes FROM innodb_used_pages GROUP BY FOUND WITH ROLLUP; DROP TEMPORARY TABLE innodb_used_pages; END