mysql - slow - table does not support optimize, doing recreate+analyze instead

MySQL OPTIMIZE todas las tablas?

Question

MySQL tiene un comando OPTIMIZE TABLE que se puede usar para recuperar espacio no utilizado en una instalación de MySQL. ¿Hay alguna forma (comando incorporado o procedimiento almacenado común) para ejecutar esta optimización para cada tabla en la base de datos y / o la instalación del servidor, o es algo que tendría que crear un script?

Answer 1

Desde la línea de comando:

mysqlcheck -o <db_name> -u<username> -p

luego escribe la contraseña

Answer 2

Desde phpMyAdmin y otras fuentes puede usar:

SET SESSION group_concat_max_len = 99999999;
SELECT GROUP_CONCAT(concat(''OPTIMIZE TABLE `'', table_name, ''`;'') SEPARATOR '''') AS O
FROM INFORMATION_SCHEMA.TABLES WHERE 
TABLE_TYPE = ''BASE TABLE''
AND table_name!=''dual''
AND TABLE_SCHEMA = ''<your databasename>''

Luego, puede copiar y pegar el resultado en una nueva consulta o ejecutarlo desde su propia fuente.

Answer 3

El MySQL Administrator (parte de las Herramientas de GUI de MySQL) puede hacerlo por usted en un nivel de base de datos.

Simplemente seleccione su esquema y presione el botón de Maintenance en la esquina inferior derecha.

Dado que las herramientas de GUI han alcanzado el estado de final de vida, son difíciles de encontrar en la página mysql. Los encontré en Google: http://dev.mysql.com/downloads/gui-tools/5.0.html

No sé si el nuevo MySQL Workbench también puede hacer eso.

Y puede usar la herramienta de línea de comandos mysqlcheck que también debería poder hacer eso.

Answer 4

El siguiente ejemplo de script php puede ayudarlo a optimizar todas las tablas en su base de datos

<?php

dbConnect();

$alltables = mysql_query("SHOW TABLES");

while ($table = mysql_fetch_assoc($alltables))
{
   foreach ($table as $db => $tablename)
   {
       mysql_query("OPTIMIZE TABLE ''".$tablename."''")
       or die(mysql_error());

   }
}

?>

Answer 5

Este script de bash aceptará la contraseña de root como opción y la optimizará una por una, con salida de estado:

#!/bin/bash

if [ -z "$1" ] ; then
  echo
  echo "ERROR: root password Parameter missing."
  exit
fi
MYSQL_USER=root
MYSQL_PASS=$1
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
TBLLIST=""
COMMA=""
SQL="SELECT CONCAT(table_schema,''.'',table_name) FROM information_schema.tables WHERE"
SQL="${SQL} table_schema NOT IN (''information_schema'',''mysql'',''performance_schema'')"
for DBTB in `mysql ${MYSQL_CONN} -ANe"${SQL}"`
do
    echo OPTIMIZE TABLE "${DBTB};"
    SQL="OPTIMIZE TABLE ${DBTB};"
    mysql ${MYSQL_CONN} -ANe"${SQL}"
done

Answer 6

Hice este script ''simple'':

set @a=null,@c=null,@b=concat("show tables where",ifnull(concat(" `Tables_in_",database(),"` like ''",@c,"'' and"),'''')," (@a:=concat_ws('','',@a,`Tables_in_",database(),"`))");

Prepare `bd` from @b;
EXECUTE `bd`;
DEALLOCATE PREPARE `bd`;

set @a:=concat(''optimize table '',@a);
PREPARE `sql` FROM @a;
EXECUTE `sql`;
DEALLOCATE PREPARE `sql`;

set @a=null,@b=null,@c=null;

Para ejecutarlo, simplemente péguelo en cualquier IDE de SQL conectado a su base de datos.

Aviso: este código NO funcionará en phpmyadmin.

Cómo funciona

Ejecuta una declaración de show tables y la almacena en una declaración preparada. Luego ejecuta una optimize table en el conjunto seleccionado.

Puede controlar qué tablas optimizar al establecer un valor diferente en la var @c .

Answer 7

Puede optimizar / verificar y reparar todas las tablas de la base de datos, utilizando el cliente mysql.

Primero, debes obtener todas las listas de tablas, separadas con '','':

mysql -u[USERNAME] -p[PASSWORD] -Bse ''show tables'' [DB_NAME]|xargs|perl -pe ''s/ /,/g''

Ahora, cuando tengas todas las listas de tablas para optimización:

mysql -u[USERNAME] -p[PASSWORD] -Bse ''optimize tables [tables list]'' [DB_NAME]

Answer 8

Realice todos los procedimientos necesarios para arreglar todas las tablas en todas las bases de datos con un simple script de shell:

#!/bin/bash
mysqlcheck --all-databases
mysqlcheck --all-databases -o
mysqlcheck --all-databases --auto-repair
mysqlcheck --all-databases --analyze

Answer 9

Si accede directamente a la base de datos, puede escribir la siguiente consulta:

OPTIMIZE TABLE table1,table2,table3,table4......;

Answer 10

Si desea analizar, reparar y optimizar todas las tablas en todas las bases de datos en su servidor MySQL, puede hacer esto de una sola vez desde la línea de comandos. Necesitará root para hacer eso sin embargo.

mysqlcheck -u root -p --auto-repair --optimize --all-databases

Una vez que lo ejecute, se le solicitará que ingrese su contraseña de root de MySQL. Después de eso, comenzará y verás los resultados a medida que suceda.

Ejemplo de salida:

yourdbname1.yourdbtable1       OK
yourdbname2.yourdbtable2       Table is already up to date
yourdbname3.yourdbtable3
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK

etc..
etc...

Repairing tables
yourdbname10.yourdbtable10
warning  : Number of rows changed from 121378 to 81562
status   : OK

Si no conoce la contraseña de root y está usando WHM, puede cambiarla desde WHM yendo a: Inicio> Servicios SQL> Contraseña de MySQL Root

Answer 11

Un script de inicio bash para listar y ejecutar una herramienta contra los DBs ...

#!/bin/bash

declare -a dbs
unset opt

for each in $(echo "show databases;" | mysql -u root) ;do

        dbs+=($each)

done



echo " The system found [ ${#dbs[@]} ] databases." ;sleep 2
echo
echo "press 1 to run a check"
echo "press 2 to run an optimization"
echo "press 3 to run a repair"
echo "press 4 to run check,repair, and optimization"
echo "press q to quit"
read input

case $input in
        1) opt="-c"
        ;;
        2) opt="-o"
        ;;
        3) opt="-r"
        ;;
        4) opt="--auto-repair -c -o"
        ;;
        *) echo "Quitting Application .."; exit 7
        ;;
esac

[[ -z $opt ]] && exit 7;

echo " running option:  mysqlcheck $opt in 5 seconds  on all Dbs... "; sleep 5

for ((i=0; i<${#dbs[@]}; i++)) ;do
        echo "${dbs[$i]} : "
        mysqlcheck $opt ${dbs[$i]}  -u root
    done

Answer 12

mis 2centos: comenzar con la tabla con mayor fragmentación

for table in `mysql -sss -e "select concat(table_schema,".",table_name) from information_schema.tables where table_schema not in (''mysql'',''information_schema'',''performance_schema'') order by data_free desc;"
do
mysql -e "OPTIMIZE TABLE $table;"
done

Answer 13

para todas las bases de datos:

mysqlcheck -Aos -uuser -p 

Para una optimización de base de datos:

mysqlcheck -os -uroot -p dbtest3

Answer 14

Puede usar mysqlcheck para hacer esto en la línea de comandos.

Una base de datos:

mysqlcheck -o <db_schema_name>

Todas las bases de datos:

mysqlcheck -o --all-databases