[script] Ottimizzazione automatica tabelle mysql

[script] Ottimizzazione automatica tabelle mysql postato il 27/10/2012 02:42:03 nel forum programmazione, open source e hosting e modificato da ilgrandeinverno il 27/10/2012 17:32:30

Cercavo una funzione utile in php per gestire automaticamente l'ottimizzazione delle tabelle con spazio libero "di scarto" (che si accumula con le varie delete periodiche) operazione che solitamente si fa da phpmyadmin selezionando le tabelle da ottimizzare.

Cercando qua e la ho trovato solo script che estraggono tutte le tabelle (tutte in generale) o tutte quelle da ottimizzare, e poi per ogni singola tabella eseguono una query per ottimizzarla.

Se servisse a qualcuno lo pubblico qui... é uno stralcio da una delle funzioni di manutenzione che lanciamo tutte le mattine presto (se non erro l'ottimizzazione delle tabelle mysql locka temporaneamente le tabelle e anche se il blocco é infinitesimale conviene farlo quando non ci sono utenti conessi).

Il vantaggio rispetto ad altri script che si trovano é che se non vi occorre uno stamp di ogni singola tabella ottimizzata, con un'unica query effettuate l'ottimizzazione di tutte le tabelle che richiedono manutenzione anzichè ripetere l'esecuzione di una query per ogni singola tabella.

L'utilità dell'ottimizzazione delle tabelle cresce in funzione della quantità di operazioni di DELETE che vengono effettuate su tabelle che hanno una mole consistente di dati all'interno (tipicamente tabelle contenti compi VARCHAR, TEXT, BLOB, BINARY*, o con moltissimi campi). Tipicamente tabelle che contengono dati della chat, della messaggeria, e via dicendo.

Noi ce la caviamo lanciando la funzione una volta al giorno al mattino presto DOPO tutte le altre operazioni di manutenzione (compresa la cancellazione dei record obsoleti o scaduti) e mediamente ottimizza ogni giorno circa mezzo mega di spazio "di scarto" su un db di dimensioni medie oscillanti tra i 42 e i 50 mb ma a seconda delle necessità l'ottimizzazione può essere usata a intervalli più frequenti o più diradati (ideale per chi ha la possibilità di impostare dei cron jobs).

Lo script può essere facilmente adattato sia per aggiungere la funzione di REPAIR, se lo si desidera, sia per concatenare i nomi delle tabelle e farne uno stamp a parte se occorre tenere traccia delle tabelle ottimizzate.

Non fate caso al crudissimo mysql_query senza manco un die() se la query fallisce, nel codice originale invece di mysql_query() c'è una funzione a parte che prende altri parametri ma che non sarebbe utilizzabile per chi non l'ha definita o ne usa altre personalizzate (es. gdr-cd 5 mi pare).

NB: ovviamente lo script dà per scontato che sia già attiva una connessione mysql e che sia già stato selezionato il database in uso.

NON USARE SU ALTERVISTA.ORG (segnalazione di leoblacksoul)
Altervista.org ha un servizio interno di manutenzione delle tabelle, e pare che l'uso delle funzioni mysql di ottimizzazione da parte degli utenti alla lunga corrompa le tabelle.

$q = "SHOW TABLE STATUS WHERE Data_Free > '0'";
$res = mysql_query($q); $TOOPT = mysql_num_rows($res);
$N = 0; // tabelle ottimizzate
if($TOOPT > 0)
{
$N = 1;
while($t = mysql_fetch_array($res))
{
$TNAME = $t['Name']; $TSPACE += $t['Data_free'];
if($N < 2)
{
$Q = "OPTIMIZE TABLE ".$TNAME."";
}
else
{
$Q .= ", ".$TNAME."";
}
$N++;
} // endwhile tabelle da ottimizzare
$N -= 1;
mysql_query($Q);
} // endif tabelle da ottimizzare trovate

NB: $TSPACE, $N e $TOOPT sono solo variabili di controllo che possono essere eventualmente restituite per fini statistici, ad esempio per sapere quanto spazio "di scarto" é stato recuperato (in bytes, quindi se vi interessano i Kb -> $TSPACE_KB = round(($TSPACE/1024),2);

Risorse utili:
- Guida all'ottimizzazione http://www.html.it/pag/32164/lottimizzazione/ ↗, su HTML.IT, segnalata da nodd

27/10/2012 09:13:29

Vorrei aggiungere che a questa pagina http://www.html.it/pag/32164/lottimizzazione/ ↗ c'è una guida base per come ottimizzare un database, io l'ho trovata molto utile :P

27/10/2012 14:21:08

Aggiungerei che è ALTAMENTE sconsigliato usare lo script postato qui sopra se il proprio gdr si trova su Altervista!
Io ho fatto ottimizzazione delle tabelle per un po' di tempo, finchè non mi si è corrotto l'intero database perchè per qualche ragione astrusa su Altervista non funziona bene. Il personale tecnico mi ha poi confermato che non è una buona cosa fare ottimizzazione delle tabelle sui loro server, e che ci pensano loro in automatico.

27/10/2012 16:11:17

leoblacksoul ha scritto: Aggiungerei che è ALTAMENTE sconsigliato usare lo script postato qui sopra se il proprio gdr si trova su Altervista!
Io ho fatto ottimizzazione delle tabelle per un po' di tempo, finchè non mi si è corrotto l'intero database perchè per qualche ragione astrusa su Altervista non funziona bene. Il personale tecnico mi ha poi confermato che non è una buona cosa fare ottimizzazione delle tabelle sui loro server, e che ci pensano loro in automatico.

Grazie della segnalazione, lo aggiungo come nota :)