Скрипт оптимизации баз MySQL (OPTIMIZE TABLE)

Нашел полезный скриптик, который оптимизирует все таблицы во всех базах данных MySQL в системе. Можно поставить в crontab.

<?php
echo '<pre>' . "\n\n";
set_time_limit( 100 );
//
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$start = $time;
//
//Connection variables :
$h = 'localhost';
$u = 'root';
$p = 'password';
//
$dummy_db = 'mysql';
//
/*The php->mysql API needs to connect to a database even when executing scripts like this.
If you got an error from this(permissions),
just replace this with the name of your database*/
//
$db_link = mysql_connect($h,$u,$p);
//
$res = mysql_db_query($dummy_db, 'SHOW DATABASES', $db_link) or die('Could not connect: ' . mysql_error());
echo 'Found '. mysql_num_rows( $res ) . ' databases' . "\n";
$dbs = array();
while ( $rec = mysql_fetch_array($res) )
{
$dbs [] = $rec [0];
}
//
foreach ( $dbs as $db_name )
{
echo "Database : $db_name \n\n";
$res = mysql_db_query($dummy_db, "SHOW TABLE STATUS FROM `" . $db_name . "`", $db_link) or die('Query : ' . mysql_error());
$to_optimize = array();
while ( $rec = mysql_fetch_array($res) )
{
if ( $rec['Data_free'] > 0 )
{
$to_optimize [] = $rec['Name'];
echo $rec['Name'] . ' needs optimization' . "\n";
}
}
if ( count ( $to_optimize ) > 0 )
{
foreach ( $to_optimize as $tbl )
{
mysql_db_query($db_name, "OPTIMIZE TABLE `" . $tbl ."`", $db_link );
}
}
}
//
$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 6);
echo 'Parsed in ' . $total_time . ' secs' . "\n\n";
?>

10 Comments

KuLiBiNMarch 2nd, 2011 at 12:19

Все таблицы не всегда есть смысл оптимизировать. Например таблицу логов с 10 лямами записей, которая раз в 2-3 дня очищается.
А в целом, OPTIMIZE по крону это true.

www8888comDecember 27th, 2011 at 14:30

У MySQL есть свои средства для этого – события.
Что-то типа этого:
CREATE EVENT optimize ON SCHEDULE EVERY 1 WEEK
DO
CALL optimize_tables(‘dbName’);
optimize_tables(…) – это хранимая процедура, в которой и нужно вызывать оптимизацию таблиц.

bladerootMarch 6th, 2012 at 12:27

а чем sh неугодил?

#!/bin/sh

username=’root’
password=”

mysql -u $username -p”$password” -NBe “SHOW DATABASES;” | grep -v ‘lost+found’ | while read database ; do
mysql -u $username -p”$password” -NBe “SHOW TABLE STATUS;” $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength d
if [ “$datafree” -gt 0 ] ; then
fragmentation=$(($datafree * 100 / $datalength))
echo “$database.$name is $fragmentation% fragmented.”
mysql -u “$username” -p”$password” -NBe “OPTIMIZE TABLE $name;” “$database”
fi
done
done

Плюшевый КрысMarch 6th, 2012 at 14:01

Насчет языка – вопрос к автору, не я писал скрипт.

t3mppMarch 9th, 2012 at 16:21

>bladerootMarch 6th, 2012 at 12:27
>а чем sh неугодил?
Пилите, Шура, пилите, они золотые…

всем угодил, кроме работоспособности…

#!/bin/sh

username=”user”
password=”password”

mysql -u $username -p”$password” -NBe ‘SHOW DATABASES;’ | while read database
do
mysql –user=$username –password=$password -NBe ‘SHOW TABLE STATUS;’ $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree a b c d e f g h j k
do
echo $datafree
if [ $datafree -gt 0 ] ; then
fragmentation=$(($datafree * 100 / $datalength))
echo “$database.$name is $fragmentation% fragmented.”
mysql -u “$username” -p”$password” -NBe “OPTIMIZE TABLE $name;” “$database”
#rm -rf ./tmp_st
fi
done
done

пользователь должен иметь права на чтение, запись, удаление и создание таблиц. иначе робить не будет.

Ну и кавычки надо проверять при копировании, они движком сайта косячно переводятся.

bladerootMarch 12th, 2012 at 15:21

к сожалению не проверял, так вытягивал скрипт с окна PuTTy. И в поле password= надо имеенно одинарные ковычки, так как, если в пароле встречается символ ` то точно работать не будет)))

З.Ы. В оригинале скрипта логин и пароль пользователя вводятся с клавиатуры.

bladerootMarch 12th, 2012 at 15:24

З.Ы. И не знаю почему у вас не работает, у меня работает просто замечательно. просто для запуска с крона надо таки прописывать полные пути к исполнимым файлам, делать экспорт путей…

FilshSeptember 4th, 2012 at 15:51

Не стоит этого делать, потому как optimize может занимать длительное время и в это время таблица блокируется!! Если у вас база работает на продакшене, это может ее уронить случайно))

Плюшевый КрысSeptember 4th, 2012 at 23:48

Для довольно нагруженного магазина эта операция занимает пару минут и проводится во время запланированного обслуживания. Ничего не падает, разумется.

ВячеславJanuary 31st, 2013 at 13:31

Блин, чего то я не допетрил написать этот скрипт на PHP. Писал на Perl :( В любом случае, полезная статья

Leave a comment

Your comment