Comprimir, desfragmentar y optimizar la base de datos MariaDB / MySQL

En este artículo exploraremos algunos métodos de compresión y desfragmentación de tablas / bases de datos en MySQL / MariaDB, que le ayudarán a ahorrar espacio en un disco en el que se encuentra una base de datos.

Las bases de datos de grandes proyectos crecen enormemente con el tiempo y siempre surge la pregunta de qué hacer con ellas. Hay varias formas de solucionar el problema. Puede reducir la cantidad de datos en una base de datos eliminando información antigua, dividiendo una base de datos en otras más pequeñas, aumentando el tamaño del disco en un servidor o comprimiendo / reduciendo tablas.

Otro aspecto importante del funcionamiento de la base de datos es la necesidad de desfragmentar las tablas y las bases de datos de vez en cuando para mejorar su rendimiento.

Optimización y compresión de tablas InnoDB

Archivos ibdata1 e ib_log

La mayoría de los proyectos con InnoDB las mesas tienen un problema de grandes ibdata1 y ib_log archivos. En la mayoría de los casos, está relacionado con una configuración incorrecta de MySQL / MariaDB o con una arquitectura de base de datos. Toda la información de las tablas InnoDB se almacena en el archivo ibdata1, cuyo espacio no se recupera por sí mismo. Prefiero almacenar los datos de la tabla por separado. ibd * archivos. Para hacerlo, agregue la siguiente línea a my.cnf:

innodb_file_per_table

o

innodb_file_per_table=1

Si su servidor está configurado y tiene algunas bases de datos productivas con tablas InnoDB, haga lo siguiente:

1. Realice una copia de seguridad de todas las bases de datos en su servidor (excepto mysql y performance_schema). Puede obtener un volcado de base de datos usando este comando: # mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
2. Después de crear una copia de seguridad de la base de datos, detenga su servidor mysql / mariadb;
3. Cambie la configuración en my.cfg;
4. Borrar ibdata1 y ib_log archivos;
5. Inicie el demonio mysql / mariadb;
6. Restaurar todas las bases de datos desde la copia de seguridad: # mysql -u [username] –p[password] [database_name] < [dump_file.sql]

Después de hacerlo, todas las tablas InnoDB se almacenarán en archivos separados e ibdata1 dejará de crecer exponencialmente.

Compresión de tabla InnoDB

Puede comprimir tablas con texto / datos BLOB y ahorrar bastante espacio en disco.

Tengo una base de datos innodb_test que contiene tablas que potencialmente se pueden comprimir y, por lo tanto, puedo liberar algo de espacio en disco. Antes de hacer cualquier cosa, recomiendo hacer una copia de seguridad de todas las bases de datos. Conéctese a un servidor mysql:

# mysql -u root -p

Seleccione la base de datos que necesita en su consola mysql:

# use innodb_test;

Para mostrar la lista de tablas y sus tamaños, utilice la siguiente consulta:

SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in (MB)"
FROM information_schema.TABLES
WHERE table_schema = "innodb_test"
ORDER BY (data_length + index_length) DESC;

Donde innodb_test es el nombre de su base de datos.

Algunas tablas pueden estar comprimidas. Tomemos el b_crm_event_relations tabla como ejemplo. Ejecute esta consulta:

mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;

Después de ejecutarlo, puede ver que el tamaño de la tabla se ha reducido de 26 MB a 11 MB debido a la compresión.

Al comprimir las tablas, puede ahorrar mucho espacio en disco en su host. Sin embargo, cuando se trabaja con tablas comprimidas, la carga de la CPU aumenta. Utilice la compresión para tablas de base de datos si no tiene problemas con los recursos de la CPU, pero tiene un problema de espacio en disco.

Compresión de tablas MyISAM en MySQL / MariDB

Comprimir Myisam tablas, use una consulta especial en la consola del servidor en lugar de la consola mysql. Para comprimir una tabla, ejecute lo siguiente:

# myisampack -b /var/lib/mysql/test/modx_session

Donde / var / lib / mysql / test / modx_session es la ruta a su tabla. Desafortunadamente, no tenía una tabla grande y tuve que comprimir las pequeñas, pero el resultado aún se podía ver (el archivo se comprimió de 25 MB a 18 MB):

# du -sh modx_session.MYD

25M modx_session.MYD

# myisampack -b /var/lib/mysql/test/modx_session

Compressing /var/lib/mysql/test/modx_session.MYD: (4933 records)
- Calculating statistics
- Compressing file
29.84%
Remember to run myisamchk -rq on compressed tables

# du -sh modx_session.MYD

18M modx_session.MYD

Usé el -B introduzca el comando. Cuando lo agrega, se realiza una copia de seguridad de una tabla antes de la compresión y se marca con la etiqueta VIEJA:

# ls -la modx_session.OLD

-rw-r----- 1 mysql mysql 25550000 Dec 17 15:20 modx_session.OLD

# du -sh modx_session.OLD

25M modx_session.OLD

Optimización de tablas y bases de datos en MySQL y MariaDB

Para optimizar tablas y bases de datos, se recomienda desfragmentarlas. Asegúrese de que haya tablas en la base de datos que requieran desfragmentación.

Abra la consola MySQL, seleccione una base de datos y ejecute esta consulta:

select table_name, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 50 order by data_free_mb;

Por lo tanto, mostrará todas las tablas con al menos 50 MB de espacio no utilizado:

+-------------------------------+----------------+--------------+
| TABLE_NAME | data_length_mb | data_free_mb |
+-------------------------------+----------------+--------------+
| b_disk_deleted_log_v2 | 402 | 64 |
| b_crm_timeline_bind | 827 | 150 |
| b_disk_object_path | 980 | 72 |

data_length_mb - tamaño total de una mesa

data_free_mb - espacio no utilizado en una mesa

Estas son las tablas que podemos desfragmentar. Compruebe cuánto espacio ocupan en el disco:

# ls -lh /var/lib/mysql/innodb_test/ | grep b_

-rw-r----- 1 mysql mysql 402M Oct 17 12:12 b_disk_deleted_log_v2.MYD
-rw-r----- 1 mysql mysql 828M Oct 17 13:23 b_crm_timeline_bind.MYD
-rw-r----- 1 mysql mysql 981M Oct 17 11:54 b_disk_object_path.MYD

Para optimizar estas tablas, ejecute el siguiente comando en la consola mysql:

# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;

Después de una desfragmentación exitosa, verá un resultado como este:

+-------------------------------+----------------+--------------+
| TABLE_NAME | data_length_mb | data_free_mb |
+-------------------------------+----------------+--------------+
| b_disk_deleted_log_v2 | 74 | 0 |
| b_crm_timeline_bind | 115 | 0 |
| b_disk_object_path | 201 | 0 |

Como puede ver, data_free_mb ahora es igual a 0 y el tamaño de la tabla se ha reducido significativamente (3 - 4 veces).

También puede ejecutar la desfragmentación usando mysqlcheck en la consola de su servidor:

# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file

Dónde innodb_test es tu base de datos
Y b_workflow_file es el nombre de la mesa

Para optimizar todas las tablas en una base de datos, ejecute este comando en la consola de su servidor:
# mysqlcheck -o innodb_test -u root -p

Donde innodb_test es un nombre de base de datos

O ejecute la optimización de todas las bases de datos en el servidor:

# mysqlcheck -o --all-databases -u root -p

Si comprueba el tamaño de la base de datos antes y después de la optimización, verá que el tamaño total se ha reducido:

# du -sh

2.5G

# mysqlcheck -o innodb_test -u root -p

innodb_test.b_admin_notify
note : Table does not support optimize, doing recreate + analyze instead
status : OK
innodb_test.b_admin_notify_lang
note : Table does not support optimize, doing recreate + analyze instead
status : OK
innodb_test.b_adv_banner
note : Table does not support optimize, doing recreate + analyze instead
status : OK

# du -sh

1.7G

Por lo tanto, para ahorrar espacio en su servidor, puede optimizar y comprimir sus tablas y bases de datos MySQL / MariDB de vez en cuando. Recuerde hacer una copia de seguridad de una base de datos antes de realizar cualquier trabajo de optimización.