compattazione MySql

[Kelly]

Salve.....dopo l'argomento delle transazione che ho capito con successo grazie a voi.
Mi pongo un altra problematica, ovvero la compattazione della tabella.

Ho una tabella che popolo tutte le mattine exnovo e per evitare problemi di compattazione,
prima inserire nuovi record utilizzo il metodo TRUNCATE previsto da Mysql che distrugge la tabella e ricrea la struttura....

Ho però un'altra tabella, soggetta a cancellazioni, modifice e nuovi inserimenti, che vorrei mantenere come si conviene, avviando qualche procedura per la copia di sicurezza e la compattazione.

Ho trovato "OPTIMIZE TABLE", per la compattazione, procedura che svilupperei in delphi, quindi esterna al sito!


Potreste darmi dei chiarimenti anche in questo caso?


grazie mille

 

[flameseeker]

Ho una tabella che popolo tutte le mattine exnovo e per evitare problemi di compattazione,
prima inserire nuovi record utilizzo il metodo TRUNCATE previsto da Mysql che distrugge la tabella e ricrea la struttura....

A livello di numero di record che ogni giorno vengono inseriti in questa tabella di che ordine parliamo ? Perché prima che le ricerche su tabelle mysql diventino un peso, usando appropriatamente gli indici, potresti archiviare anche diversi milioni di record senza particolari problemi (finché non fai una select * senza limit )

Potresti quindi eventualmente pensare di delegare la procedura di TRUNCATE ad un eventuale cronjob a cadenza settimanale.

Ho però un'altra tabella, soggetta a cancellazioni, modifice e nuovi inserimenti, che vorrei mantenere come si conviene, avviando qualche procedura per la copia di sicurezza e la compattazione.

Ho trovato "OPTIMIZE TABLE", per la compattazione, procedura che svilupperei in delphi, quindi esterna al sito!

Qui dipende molto dall'engine della tabella.
Il comando OPTIMIZE TABLE non funziona sulle InnoDB, bensì trova utilità in particolar modo nelle tabelle MyISAM dove svuota le eventuali informazioni statistiche accumulate dalla tabella circa gli indici presenti che possono diventare semplicemente spazzatura dopo operazioni di scrittura/eliminazione molto frequenti sulla tabella.

Per chiarezza comunque, le InnoDB forniscono queste informazioni su richiesta, non le archiviano cone le MyISAM e sostanzialmente questo è il motivo per cui la cardinalità degli indici viene mostrata sempre come un valore approssimativo e mai reale.

L'unico consiglio che posso darti è che l'OPTIMIZE TABLE non dovrebbe a mio avviso essere lanciato automaticamente da software, è più una manutenzione che dovrebbe intervenire ad intervalli pianificati di tempo poiché è bene lanciare il comando quando non si ha un carico pesante di richieste da indirizzare alla tabella (il che equivale solitamente a mettere giù il sito per una decina di minuti così da evitare alle richieste utente di passare fino al db).

 

[marino51]

aggiungo le mie, come sempre, poche idee e ben confuse,

se la tabella che aggiorni con frequenza, contiene records che possono essere "archiviati",
ti suggerisco di creare una (o più) tabella "archivio"
che, con le dovute regole di archiviazione, puoi manutenere con semplici comandi,

DELETE FROM tabella OUTPUT DELETED.* INTO archivio
WHERE iv_year=2014 (ovvero, condizioni di archiviazione di tabella)

definendo una view che comprenda entrambe le tabelle, puoi far lavorare il tuo software
senza che si accorga della separazione

va detto che entrambe le tabelle devono essere indicizzate al meglio,
con le chiavi di ricerca utilizzate nel software

poi oltre al backup del database, fatto su dischi diversi dal db, per tranquillità personale, ho creato uno script
che genera un file di testo per ciascuna tabella, con all'interno le query necessarie all'insert dei dati
va da sè che, i dati archiviati vengono gestiti solo al momento della loro archiviazione perchè poi non più modificati

vantaggi,
i dati su cui operare sono sempre contenuti al minimo,
l'archivio è comunque a disposizione, "confuso" con i dati operativi
c'è sempre la possibilità di ripristinare o ricreare il database
le operazioni possono essere confinate nei momenti di scarsa attività, tipicamente notte e fine settimana
costa poco

svantaggi,
operazioni da non automatizzare, per la propria tranquillità (che si traduce nel max livello di servizio)

ps, ho scritto più di sicurezza che non di ottimizzazione del db,
ma conviene essere attrezzati al meglio prevedendo possibili fault, soprattutto con applicazioni importanti
non a caso, quando succede un guaio, il panico si diffonde senza controllo
ciao
Marino

 

[Kelly]

Grazie
dunque:

CRONJOB:
Ho un database di articoli nel formato txt che viene modificato tutti i giorni.
Importo questi dati in un database mysql sempre tramite delphi..parliamo di circa 10.000 record.
la procedura parte tutte le mattine e ci impiega un bel pò...20' circa....Nel frattempo il sito è off (o meglio è in "manutenzione in corso")
Mi parli del cronjob per il TRUNCATE perchè quest'ultimo rallenta di parecchio la procedura?(pensavo fosse abbastanza veloce) quindi mi consigli ad esempio di fare un update la dove il rec è presente o un insert dove non esiste ed eventualmente avviare il truncate con il cronjob (che sarebbe una sorta di schedulazione visto ora su internet) 1 volta a settimana per risolvere i problemi di compattazione, così velocizzo tutto e magari non ho bisogno di mettere il sito "in manutenzione?"

OPTIMIZE TABLE:
Quindi nel mio caso "OPTIMIZE TABLE" non è appropriato perchè le tabelle in questione sono InnoDB.
Comunque lo avrei avviato subito dopo il popolamento dei rec della tabella articoli, in quel momento il sito è già "in manutenzione".
Inoltre, se non ho capito male quindi le tabelle InnoDb non hanno grandi problemi di compattazione perchè non memorizzazione le "informazioni" così come fanno le MyISAM ?
Quindi sto tranquilla o devo cmq trovare un'altra soluzione? :love: grazie

A livello di numero di record che ogni giorno vengono inseriti in questa tabella di che ordine parliamo ? Perché prima che le ricerche su tabelle mysql diventino un peso, usando appropriatamente gli indici, potresti archiviare anche diversi milioni di record senza particolari problemi (finché non fai una select * senza limit )

Potresti quindi eventualmente pensare di delegare la procedura di TRUNCATE ad un eventuale cronjob a cadenza settimanale.



Qui dipende molto dall'engine della tabella.
Il comando OPTIMIZE TABLE non funziona sulle InnoDB, bensì trova utilità in particolar modo nelle tabelle MyISAM dove svuota le eventuali informazioni statistiche accumulate dalla tabella circa gli indici presenti che possono diventare semplicemente spazzatura dopo operazioni di scrittura/eliminazione molto frequenti sulla tabella.

Per chiarezza comunque, le InnoDB forniscono queste informazioni su richiesta, non le archiviano cone le MyISAM e sostanzialmente questo è il motivo per cui la cardinalità degli indici viene mostrata sempre come un valore approssimativo e mai reale.

L'unico consiglio che posso darti è che l'OPTIMIZE TABLE non dovrebbe a mio avviso essere lanciato automaticamente da software, è più una manutenzione che dovrebbe intervenire ad intervalli pianificati di tempo poiché è bene lanciare il comando quando non si ha un carico pesante di richieste da indirizzare alla tabella (il che equivale solitamente a mettere giù il sito per una decina di minuti così da evitare alle richieste utente di passare fino al db).

 

[Kelly]

Grazie Marino....

In effetti potrebbe cmq tornarmi utile questo discorso dell'archiviazione....ma ci devo ragionare sopra .....non so ancora come dirigermi....

 

[flameseeker]

Non so perché ero convinto di averti già risposto, in un momento di lucidità notturna mi sono accorto che così non era :jolly:

Ho un database di articoli nel formato txt che viene modificato tutti i giorni.
Importo questi dati in un database mysql sempre tramite delphi..parliamo di circa 10.000 record.

Direi di iniziare da qui.
Immagino che per tutti e 10'000 record operi una query di update/insert singolarmente per ciascuna riga, giusto ?
In questo caso ci credo che la procedura impieghi 20 minuti.
Una soluzione altamente performante potrebbe essere l'uso della clausola INSERT ... ON DUPLICATE KEY UPDATE tramite cui puoi inserire/aggiornare un numero indefinito di record con una singola query (piccolo articolo di approfondimento).

Ti consiglio comunque di aggregare fino ad un massimo di 1000 record insieme, in questo modo con 10 query dovresti riuscire a trasferire tutti i tuoi dati e sicuramente in molto meno tempo.

Mi parli del cronjob per il TRUNCATE perchè quest'ultimo rallenta di parecchio la procedura?

No, affatto il truncate è il metodo più rapido possibile per svuotare una tabella, questo infatti elimina e ricrea la tabella e l'operazione è più veloce di un delete effettuato nei confronti di ciascuna riga.

Soltanto che ho avuto brutte esperienze in passato effettuando il comando per svuotare tabelle che venivano in quel momento richieste da un discreto numero di query, a seguito del quale mysql corrose i files delle stesse tabelle.

Il consiglio di limitare temporalmente l'esecuzione del comando aiuta notevolmente ad evitare il verificarsi di eccessive richieste concorrenti, se poi si tratta di una tabella non richiestissima tanto meglio, se invece riesci a mettere tramite script il sito in manutenzione durante l'esecuzione del comando meglio ancora.

Inoltre, se non ho capito male quindi le tabelle InnoDb non hanno grandi problemi di compattazione perchè non memorizzazione le "informazioni" così come fanno le MyISAM ?
Quindi sto tranquilla o devo cmq trovare un'altra soluzione? :love: grazie

Le InnoDB non soffrono di quei problemi è corretto, pesano un pochino di più delle myISAM a parità di record archiviati, ma i vantaggi che offrono per questo prezzo le rendono pressoché indispensabili (crash recovery, foreign key e transactions).

 

[Kelly]

Per fortuna che l'hai avuto !!!
il momento di lucidità notturna intendo....aspettavo con ansia la tua perla :love:

Si esatto, inserisco i record con query insert..pertanto seguirò il tuo esempio ! grazie tante per l'articolo lo leggo
subito !

:fonzie::love:

Non so perché ero convinto di averti già risposto, in un momento di lucidità notturna mi sono accorto che così non era :jolly:



Direi di iniziare da qui.
Immagino che per tutti e 10'000 record operi una query di update/insert singolarmente per ciascuna riga, giusto ?
In questo caso ci credo che la procedura impieghi 20 minuti.
Una soluzione altamente performante potrebbe essere l'uso della clausola INSERT ... ON DUPLICATE KEY UPDATE tramite cui puoi inserire/aggiornare un numero indefinito di record con una singola query (piccolo articolo di approfondimento).

Ti consiglio comunque di aggregare fino ad un massimo di 1000 record insieme, in questo modo con 10 query dovresti riuscire a trasferire tutti i tuoi dati e sicuramente in molto meno tempo.



No, affatto il truncate è il metodo più rapido possibile per svuotare una tabella, questo infatti elimina e ricrea la tabella e l'operazione è più veloce di un delete effettuato nei confronti di ciascuna riga.

Soltanto che ho avuto brutte esperienze in passato effettuando il comando per svuotare tabelle che venivano in quel momento richieste da un discreto numero di query, a seguito del quale mysql corrose i files delle stesse tabelle.

Il consiglio di limitare temporalmente l'esecuzione del comando aiuta notevolmente ad evitare il verificarsi di eccessive richieste concorrenti, se poi si tratta di una tabella non richiestissima tanto meglio, se invece riesci a mettere tramite script il sito in manutenzione durante l'esecuzione del comando meglio ancora.




Le InnoDB non soffrono di quei problemi è corretto, pesano un pochino di più delle myISAM a parità di record archiviati, ma i vantaggi che offrono per questo prezzo le rendono pressoché indispensabili (crash recovery, foreign key e transactions).

 

[Kelly]

Ho letto l'articolo su ON DUPLICATE KEY e ringrazio...articolo molto interessante...
e sicuramente utilizzandola potrei ottenere una procedura piu performante,
nel mio caso però nel magazzino aggiornato (file txt)potrei non avere articoli che ho invece nel vecchio magazzino, e quindi non devo ritrovarmeli nella nuova tabella, proprio per questo motivo utilizzo truncate e query insert a seguire.....ma se "INSERT.....ON DUPLICATE KEY" E' comunque consigliata anche nel mio caso(previo utilizzo di truncate) magari per velocizzare l'inserimento, potrei decidere di cambiare la query....


grazie mille

Per fortuna che l'hai avuto !!!
il momento di lucidità notturna intendo....aspettavo con ansia la tua perla :love:

Si esatto, inserisco i record con query insert..pertanto seguirò il tuo esempio ! grazie tante per l'articolo lo leggo
subito !

:fonzie::love: