utilizados son que predeterminado más motores motor los federated enable cual configurar cambiar bdb almacenamiento mysql innodb

predeterminado - que son los motores de almacenamiento en mysql



Cómo: limpiar un motor de almacenamiento InnoDB mysql? (3)

¿Es posible limpiar un motor de almacenamiento mysql innodb para que no esté almacenando datos de tablas eliminadas?

¿O tengo que reconstruir una nueva base de datos cada vez?

Aquí hay una respuesta más completa con respecto a InnoDB. Es un proceso un poco largo, pero puede valer la pena el esfuerzo.

Tenga en cuenta que /var/lib/mysql/ibdata1 es el archivo más ocupado en la infraestructura de InnoDB. Normalmente alberga seis tipos de información:

  • Datos de tabla
  • Índices de tabla
  • Datos de MVCC (Control de Concurrencia Multiversioning)
    • Rollback Segments
    • Deshacer el espacio
  • Metadatos de tabla (diccionario de datos)
  • Double Write Buffer (escritura en segundo plano para evitar la dependencia del almacenamiento en caché del sistema operativo)
  • Insertar memoria intermedia (administración de cambios en índices secundarios no exclusivos)
  • Ver la Pictorial Representation of ibdata1

Arquitectura InnoDB

Muchas personas crean múltiples archivos ibdata con la esperanza de una mejor administración y rendimiento del espacio de disco, sin embargo, esa creencia es errónea.

¿Puedo ejecutar OPTIMIZE TABLE ?

Desafortunadamente, ejecutar OPTIMIZE TABLE contra una tabla InnoDB almacenada en el archivo de espacio de tabla compartido ibdata1 hace dos cosas:

  • Hace que los datos e índices de la tabla sean contiguos dentro de ibdata1
  • Hace que ibdata1 crezca porque las páginas contiguas de datos e índices se anexan a ibdata1

Sin embargo, puede segregar los datos de tabla y los índices de tabla de ibdata1 y administrarlos de manera independiente.

¿Puedo ejecutar OPTIMIZE TABLE con innodb_file_per_table ?

Supongamos que añadiera innodb_file_per_table a /etc/my.cnf (my.ini) . ¿Puedes ejecutar OPTIMIZE TABLE en todas las tablas de InnoDB?

Buenas noticias : cuando ejecuta OPTIMIZE TABLE con innodb_file_per_table habilitado, esto producirá un archivo .ibd para esa tabla. Por ejemplo, si tiene la tabla mydb.mytable de /var/lib/mysql , producirá lo siguiente:

  • /var/lib/mysql/mydb/mytable.frm
  • /var/lib/mysql/mydb/mytable.ibd

El .ibd contendrá las páginas de datos y las páginas de índice para esa tabla. Estupendo.

Malas noticias : Todo lo que ha hecho es extraer las páginas de datos y las páginas de índice de mydb.mytable de vivir en ibdata . La entrada del diccionario de datos para cada tabla, incluido mydb.mytable , aún permanece en el diccionario de datos (consulte la representación pictórica de ibdata1 ). ¡NO PUEDE SIMPLEMENTE ELIMINAR ibdata1 EN ESTE PUNTO! Tenga en cuenta que ibdata1 no se ha reducido en absoluto.

InnoDB Infraestructura de limpieza

Para reducir ibdata1 una vez por todas, debe hacer lo siguiente:

  1. Vaciar (por ejemplo, con mysqldump ) todas las bases de datos en un archivo de texto .sql ( SQLData.sql se usa a continuación)

  2. Elimine todas las bases de datos (excepto mysql y information_schema ) CAVEAT : como medida de precaución, ejecute este script para asegurarse de tener todas las concesiones de usuario en su lugar:

    mkdir /var/lib/mysql_grants cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/. chown -R mysql:mysql /var/lib/mysql_grants

  3. Ingresa a mysql y ejecuta SET GLOBAL innodb_fast_shutdown = 0; (Esto eliminará por completo todos los cambios transaccionales restantes de ib_logfile0 y ib_logfile1 )

  4. Shutdown MySQL

  5. Agregue las siguientes líneas a /etc/my.cnf (o my.ini en Windows)

    [mysqld] innodb_file_per_table innodb_flush_method=O_DIRECT innodb_log_file_size=1G innodb_buffer_pool_size=4G

    (Nota: Sea cual sea su conjunto de innodb_buffer_pool_size , asegúrese de que innodb_log_file_size sea ​​25% de innodb_buffer_pool_size .

    Además: innodb_flush_method=O_DIRECT no está disponible en Windows)

  6. Eliminar ibdata* e ib_logfile* , Opcionalmente, puede eliminar todas las carpetas en /var/lib/mysql , excepto /var/lib/mysql/mysql .

  7. Inicie MySQL (Esto recreará ibdata1 [10MB por defecto] e ib_logfile0 e ib_logfile1 a 1G cada uno).

  8. Importar SQLData.sql

Ahora, ibdata1 seguirá creciendo, pero solo contendrá metadatos de tablas porque cada tabla de InnoDB existirá fuera de ibdata1 . ibdata1 ya no contendrá datos e índices de InnoDB para otras tablas.

Por ejemplo, supongamos que tiene una tabla InnoDB llamada mydb.mytable . Si busca en /var/lib/mysql/mydb , verá dos archivos que representan la tabla:

  • mytable.frm (encabezado del motor de almacenamiento)
  • mytable.ibd (Tabla de datos e índices)

Con la opción innodb_file_per_table en /etc/my.cnf , puede ejecutar OPTIMIZE TABLE mydb.mytable y el archivo /var/lib/mysql/mydb/mytable.ibd se reducirá.

Lo he hecho muchas veces en mi carrera como DBA de MySQL. De hecho, la primera vez que hice esto, reduje un archivo ibdata1 50 GB a solo 500 MB.

Darle una oportunidad. Si tiene más preguntas sobre esto, solo pregunte. Créeme; esto funcionará tanto a corto como a largo plazo.

ADVERTENCIA

En el Paso 6, si mysql no puede reiniciarse debido a que el esquema mysql comienza a soltarse, consulte el Paso 2. Ha realizado la copia física del esquema mysql . Puedes restaurarlo de la siguiente manera:

mkdir /var/lib/mysql/mysql cp /var/lib/mysql_grants/* /var/lib/mysql/mysql chown -R mysql:mysql /var/lib/mysql/mysql

Regrese al paso 6 y continúe

ACTUALIZACIÓN 2013-06-04 11:13 EDT

Con respecto a configurar innodb_log_file_size al 25% de innodb_buffer_pool_size en el Paso 5, esa es la regla general de la vieja escuela.

El July 03, 2006 , Percona tuvo un buen artículo sobre por qué elegir un innodb_log_file_size adecuado . Más tarde, el Nov 21, 2008 , Percona siguió con otro artículo sobre cómo calcular el tamaño adecuado en función de la carga de trabajo máxima manteniendo una hora de cambios .

Desde entonces, he escrito publicaciones en el DBA StackExchange sobre el cálculo del tamaño del registro y en las que hice referencia a esos dos artículos de Percona.

Personalmente, aún seguiría con la regla del 25% para una configuración inicial. Luego, como la carga de trabajo puede determinarse con mayor precisión a lo largo del tiempo en la producción, puede cambiar el tamaño de los registros durante un ciclo de mantenimiento en solo minutos.

El motor InnoDB no almacena datos eliminados. A medida que inserta y elimina filas, el espacio no utilizado se deja asignado dentro de los archivos de almacenamiento de InnoDB. Con el tiempo, el espacio global no disminuirá, pero con el tiempo el espacio de ''borrado y liberado'' será reutilizado automáticamente por el servidor de BD.

Puede sintonizar y administrar el espacio utilizado por el motor a través de una reorganización manual de las tablas. Para hacer esto, vacíe los datos en las tablas afectadas usando mysqldump, suelte las tablas, reinicie el servicio mysql y luego vuelva a crear las tablas de los archivos de volcado.

Puede usar la consulta OPTIMIZE TABLE periódicamente para optimizar su almacenamiento y acelerar un poco las consultas SELECT . Enlace al manual de MySQL: OPTIMIZE TABLE Sintaxis