[MySQL] Milioni di record

[Supercolli]

Buonasera a tutti.
Scrivo per ricevere consigli in merito ad un recente lavoro che ho intrapreso. In pratica mi si riechiede di costruire un database mirato ad ottenere visuali (ed esportazioni) di tabelle ridotte sulla base di determinati criteri. Piccola complicazione, la base di dati è composta da un centinaio di milioni di record! Ogni record è composto da 17 campi (interi, decimali e stringhe).
Parto completamente da 0 conoscenze in ambito di base di dati e programmazione php. Mi compro un manuale, installo in locale easyphp e mi metto a fare esercizi e studio la struttura del DB in base alle regole di normalizzazione. Ora sto provando a creare il mio database in locale con la speranza di acquisire le conoscenze necessarie per caricare il tutto su un vero server in futuro, per fortuna non ho frettissima quindi con santa pazienza mi ci sono messo.
I dati sono organizzati in una moltitudine di file txt CSV molto pesanti, li sto importato un poco per volta (sono al 10% dell'attuale database dopo tre giorni di importazioni).
Ho fatto bene ad utilizzare MySQL? Noto con dispiacere che più carico e più tutto diventa lento, sia da maneggiare si per fare queries. Cerco di indicizzare le colonne sulle quali focalizzo le ricerche ma non so davvero se sarà sufficiente. Che dite? Mi conviene utilizzare una tabella di engine MERGE per spezzettare la base di dati in tante tabelle più piccole? Questo mi consente di guadagnare in velocità per le queries ed eventuali operazioni sul DB o è inutile? Consigli su come gestire il più velocemente possibile una base così ingombrante? Sto sbagliando approccio?

Ringrazio tutti per l'aiuto.

Mat

[cdimauro]

Prima di procedere all'importazione hai provato ad analizzare i dati per vedere se è possibile normalizzarli, in modo da ridurre la loro occupazione e ottimizzare anche inserimenti e ricerche?

Coi dati normalizzati potresti pensare di tenere in memoria tutti i dati delle "master" table, disattivando al contempo indici e integrità referenziale nella fase di importazione e riattivandoli alla fine, quando tutti i dati saranno stati trasferiti.

Dovrebbe velocizzarti di un bel po' l'operazione.

[Supercolli]

Grazie cdmauro, ti riferisci alle tre regole di normalizzazione delle tabelle giusto? Si, l'ho fatto. Grazie mille per i consigli sugli indici, in effetti ne ho alcuni già attivi che potrei disattivare per velocizzare l'import.

Altra cosa per completezza.
Non ho specificato in maniera sufficientemente chiara che il database consiste nella sua parte più importante in un unica struttura di tabella, o quali campi sono necessari per tutti i record in questionem. E' proprio questa la parte che da grossi problemi di pesantezza e di velocità di esecuzione e che ho pensato di "spezzettare" usando poi un engine MERGE per riunire.

[pabloski]

onestamente io rifletterei un attimino proprio su mysql

lo sto usando per un progetto che implica la gestione di circa 3 milioni di record e barcolla di brutto...sarà che isam non è un granchè ma mysql mi sembra ubriaco

centinaia di milioni di record penso che non siano alla portata di mysql

siamo d'accordo che si può utilizzare MERGE per velocizzare le operazioni ma io ho notato problemi di stabilità, crash dell'engine, perdite di dati

[khelidan1980]

poi dipende pure dove viene messo questo db, in un installazione locale, su un pc normalissimo per intenderci con 6 milioni di righe ho visto barcollare pure oracle, ovviamente il barcollare è "relativo" rispetto al barcollare di mysql

[pabloski]

Originariamente inviato da khelidan1980

ovviamente il barcollare è "relativo" rispetto al barcollare di mysql

ottimo paragone

io non mi lamento mai delle peformance ( beh fino ad un certo punto ) ma vederlo crashare come un miserabile sotto il peso di qualche milione di record mi ha fatto invecchiare di 10 anni

[khelidan1980]

Originariamente inviato da pabloski

ottimo paragone

io non mi lamento mai delle peformance ( beh fino ad un certo punto ) ma vederlo crashare come un miserabile sotto il peso di qualche milione di record mi ha fatto invecchiare di 10 anni

crash?Addirittura?
Poi le performance dipendono tanto anche da come scrivi le query, Oracle ad esempio è molto sensibile a questo, ho visto stored procedure passare da 12 ore a 40 minuti di esecuzione agendo puramente sul codice PL/SQL

[pabloski]

Originariamente inviato da khelidan1980

crash?Addirittura?
Poi le performance dipendono tanto anche da come scrivi le query, Oracle ad esempio è molto sensibile a questo, ho visto stored procedure passare da 12 ore a 40 minuti di esecuzione agendo puramente sul codice PL/SQL

si crash non molti frequenti in verità ma molti fallimenti nell'inserimento/update dei record e si tratta di query banalissime

[nico159]

Se gli index sono al posto giusto allora il problema può essere risolto in due modi:
* Fai acquistare al cliente altri server e metti in sharding MySQL partizionando il database (architettura orizzontalmente scalabile)
* Il cliente compra un singolo nuovo server molto potente prevedendo quando crescerà il database negli anni (architettura verticalmente scalabile)

La prima scelta ti permetterà anche quando il db sarà cresciuto oltre le dimensioni che lo shard riesce a gestire di aggiungere semplicemente un nuovo server
E' di certo la soluzione migliore quando si parla di ambienti professionali, e scusami, ma con gente che SA DOVE METTERE LE MANI E CONOSCE PIENAMENTE IL SOFTWARE CHE STA USANDO, COME GESTIRE UNO SHARDING, LE SUE LIMITAZIONI E COME MODIFICARE LE APPLICAZIONI CHE FANNO USO DEL DATABASE IN RELAZIONE ALLE LIMITAZIONI DEL MOTORE DI SHARDING CHE SCEGLIERAI.
Questa opzione tu ora NON sei in grado di metterla in atto

La seconda possibilità invece è quella che tu DEVI proporre al cliente
La tua capacità sarà nel capire di quale caratteristiche il nuovo server da dedicare al database avrà bisogno. Solo questo
Considera con attenzione di quanto crescerà il database nel tempo per non finire di fare un investimento troppo a breve termine


Non esistono miracoli che puoi fare tu e nè alcun PostgreSQL, Oracle o MySQL. Sarà sempre limitato dalla velocità dello storage in utilizzo

Cosa deve avere di importante un server da dedicare ad un database? Purtroppo tutto è importante
L'ideale è che il db possa essere caricato tutto nella ram
Se non è possibile che almeno gli index possano entrare nella ram ed il db abbia dischi MOLTO veloci (insomma i classici da 15.000 )
Avere una buona cpu è importante

Ricordati anche di una cosa: il backup
Devi prevedere il backup giornaliero a tutti i costi

Questo sempre nell'ipotesi che i dati contenuti nel database abbiano un valore per il cliente
Se non hanno valore, non c'è bisogno che sia tu a dargliene
Mettilo in "produzione" e fregatene che non sarà possibile farci neanche un paio di query

MySQL va bene per quello che devi farci
C'è gente che lo usa per database che contengono più tabelle da più di 60 milioni di records ognuna (certo, non basi di dati da svariati TB, ma la si parla veramente di ambito enterprise) e con l'hardware ed impostazioni giuste va che è una bellezza. Non c'è motivo per il quale tu non debba avere gli stessi risultati

[cdimauro]

Concordo. MySQL richiede delle ottime conoscenze per la corretta configurazione (perché ha miliardi di opzioni), ma certamente non è l'instabilità il suo problema (le rogne sono altre ), per lo meno per l'esperienza che mi sono fatto sia in ambito enterprise che personale, con tabelle aventi anche più di 20 milioni di record e usandolo anche in cluster.

Poi dipende anche da quello che ci si deve fare. Bisognerebbe vedere la struttura dati e le tipiche query (perché immagino che, passata la fase di importazione, quasi sempre lo si userà per estrarre dati), per vedere se è in grado di soddisfare le esigenze.

Una nota sul backup. E' una gran rottura se vuoi ottenerne una versione stabile/coerente, perché devi lockare tutte le tabelle, con tutti i rischi che comporta.
Soltanto per InnoDB esiste un tool di backup "live" che consente di eseguire l'operazione in maniera trasparente, sfruttando un'apposita transazione mentre il db può servire tranquillamente qualunque altro tipo di richiesta, ma... costa un patrimonio.

[Supercolli]

Versione MySQL: 5.1.54 community

Grazie per la risposta!

Le operazioni che devo fare sarebbero delle ricerca (quindi visualizzazioni) basata sulla condizione posta su intervalli temporali (infatti ho una colona con il timestamp) e su certi intervalli di valori di numeri decimali ed interi presi sempre dalle stesse due o tre colonne. Quindi in tutto le colonne su cui porrò le condizioni di ricerca per le query sono sempre le stesse 3-4.
Al momento mysql sta importando quindi non posso copiare una tabella comunque ho una domanda: se dichiaro la dimensione di una colonna (tipo INT(10)) ma poi molti dei valori che vado ad inserire hanno un minor numero di cifre, il peso in memoria occupato dalla tabella è meggiore oppure si calibra sui valori effettivamente inseriti? Ed i tempi di esecuzione dell query ne risentono anch'essi?

[cdimauro]

Dipende tutto dagli interi in questione. Se ne hai pochi, ma che richiedono 32 bit, ad esempio, il campo dovrà per forza avere il tipo INTEGER.

Se, invece, sai già che andranno da -10000 a 10000, ad esempio, allora sarà meglio utilizzare il tipo SMALLINT perché è sufficiente per memorizzarli, e otterrai record più ridotti (anche se di soli 2 byte) e, soprattutto, indici di dimensione inferiore (questi hanno un peso non indifferente).

Comunque nel primo caso se hai pochi interi, potresti valutarne la normalizzazione, in modo da compattarne lo spazio e ridurre la dimensione degli indici. Considerato che hai qualche centinaio di milioni di record, un'operazione del genere potrebbe benissimo incidere sui tempi d'esecuzione delle query.

[nico159]

Versione MySQL: 5.1.54 community

Vedi di aggiornare alla 5.5, sono state fatte un pò di cose per migliorare le performance