Os enseñaremos a configurar innodb_buffer_pool_size correctamente y así optimizar nuestros recursos disponibles. Pero primero necesitamos saber qué es y para qué sirve.
Uno de los mayores quebraderos de cabeza de todo sysadmin es optimizar tus recursos al máximo para que rindan de la forma más óptima posible con los mínimos recursos necesarios. Veremos la configuración de innodb_buffer_pool_size. Porque sobredimensionar algo es malo al mismo tiempo que quedarnos cortos también lo es.
Como vemos en esta alerta, zabbix me estaba avisando de que en uno de mis servidores webs, la utilización del buffer de MySQL era menor del 50% durante 5 minutos. Pero qué es el Buffer pool y para qué se utiliza?
En esencia el Buffer pool se encarga de guardar en memoria nuestros datos de una tabla concreta y sus índices en caché, para que, si en algún momento se vuelven a requerir esos datos, cargarlos desde la memoria en lugar del disco, como ya sabéis, un disco es mucho más lento que una memoría RAM. Es una forma que tiene de optimizar InnoDB. Os dejo más información en la página oficial de MySQL aquí respecto a configurar innodb_buffer_pool_size.
sobredimensionar algo es malo al mismo tiempo que quedarnos cortos también lo es.
En mi caso zabbix me estaba advirtiendo que mi innodb_buffer_pool_size tenía asignada mucha más memoria RAM de la que requería. ¿Pero cuánta más? El chivato de zabbix me advertía de que solamente estaba consumiendo un 19% del pool. Pero como puedo saber cuánto necesita?. En esencia nos podemos guiar por el tamaño máximo de nuestra mayor tabla en MySQL, aquí viene su explicación.
Debéis saber que el valor que definimos en innodb_buffer_pool_size siempre debe ser igual o un multiplo de innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. Si el valor definido no es multiplo, innodb_buffer_pool_size se ajustará automáticamente por un valor que si lo sea. Algo que también hay que saber es que siempre que nuestro innodb_buffer_pool_size sea menor de 1GB, nuestro innodb_buffer_pool_instances será 1, así como el valor por defecto de innodb_buffer_pool_size son 128MB. Os dejo más información aquí.
Después de saber la teoría, vamos a la práctica y a la alerta que se trataba en cuestión en la imagen anterior.
Buscamos cuál es nuestra tabla de base de datos que más ocupa
MariaDB [(none)]> SELECT
-> table_schema AS 'DB Name',
-> ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS 'DB Size in MB'
-> FROM
-> information_schema.tables
-> GROUP BY
-> table_schema;
+-----------------------+---------------+
| DB Name | DB Size in MB |
+-----------------------+---------------+
| codinginfraestructure | 2.1 |
| foro_vanll_sysadm | 2.0 |
| information_schema | 0.2 |
| maps | 0.0 |
| mysql | 1.5 |
| nextcloud | 2.3 |
| ofertoapp | 0.1 |
| performance_schema | 0.0 |
| wpdb | 16.6 |
+-----------------------+---------------+
9 rows in set (0.009 sec)Como vemos, en mi caso es la base de datos wpdb que ocupa 16.6MB, el siguiente paso es buscar la tabla que más ocupa en esa base de datos.
MariaDB [(none)]> SELECT
-> TABLE_NAME AS `Table`,
-> ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
-> FROM
-> information_schema.TABLES
-> WHERE
-> TABLE_SCHEMA = "wpdb"
-> ORDER BY
-> (DATA_LENGTH + INDEX_LENGTH)
-> DESC;
+------------------------------+-----------+
| Table | Size (MB) |
+------------------------------+-----------+
| wp_posts | 6 |
..............................Por consiguiente, la tabla wp_posts ocupa 6MB.
Esto quiere decir que para configurar innodb_buffer_pool_size debería ser 12MB o 24MB ?
Realmente es una orientación que debemos tomar, ciertamente si definimos un pool de 12MB o 24MB funcionaría perfectamente y no tendríamos problema alguno.
¿Y qué hay de la regla del 80%?
A priori, esta regla nos indica que deberíamos asignar un 80% de nuestra memoria física a nuestro buffer pool, Quiere decir que si tenemos 1TB de RAM deberíamos asignar 250GB? Como habréis deducido no tiene ningún sentido. Esta regla es un punto de partida inicial pero servidores pequeños pero una vez se van dimensionando dependerá de muchos factores, tales como si el servidor de base de datos es dedicado o no. Así como el uso que se le da a la base de datos. En mi caso modifiqué el valor a 64MB y pasé a usar un 50% del buffer pool. Os dejo también una entrada de Jay Jenssen, IT Director de percona que aporta su visión a configurar innodb_buffer_pool_size correctamente.
También os dejo la siguiente entrada para realizar un script de backup de una base de datos simple en linux, hasta la próxima!
