db che delusione!!!

General
1

Ciao,

volevo inserire 15 000 000 coordinate gps provenienti da un file xml
all'interno di una tabella db.

Da java uso un parser sax per leggere l'xml. Tempo totale
dell'operazione: 2 minuti circa.

Ora ad ogni elemento xml faccio corrispondere una insert nel db (con
jdbc). Tempo dell'opeatione: decine di minuti

Ma c'e' qualcosa che non mi torna! Ma un db non dovrebbe essere una scheggia
a gestire dati?

Come piu' un parser xml (che parsa un testo) essere molto piu' veloce di un db
che e' fatto per organizzare dati?

Ho provato sia con mysql che postgresql. Il tempo e' analogo: decine
di minuti!!!

Ho provato a non fare commit ad ogni operazione ma ogni 3000 insert
e questo mi sembra aver migliorato la situazione ... ma non da
arrivare sotto i 10 minuti.

Voi sapete spiegare questa situazione?

Ho forse sbagliato qualcosa? Forse uso "male" il db?

2

Davide schrieb:

Ho forse sbagliato qualcosa? Forse uso "male" il db?

Difficile a dirsi senza vedere il codice :wink:

Hai usato una prepared statement o fai ogni volta un nuovo statement? Le
prepared statement danno al DBMS la possibilità di ottimizzare un pochino...

Happy hacking!
Patrick

3

Si uso prepared statement

PreparedStatement ptInsertNode = conn.prepareStatement("insert into
NODE (id, lon, lat) values (?,?,?)");

for (Node n: nodes)
{
               ptInsertNode.setLong(1, n.id);
               ptInsertNode.setDouble(2, n.lon);
               ptInsertNode.setDouble(3, n.lat);
               assert 1 == ptInsertNode.executeUpdate();
}

4

Davide wrote:

Ciao,

volevo inserire 15 000 000 coordinate gps provenienti da un file xml
all'interno di una tabella db.
Ho provato sia con mysql che postgresql. Il tempo e' analogo: decine
di minuti!!!

In MySQL prova con LOAD DATA INFILE:

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

dovrebbe permetterti di ridurre i tempi di circa 20 volte.
Qui trovi un benchmark in php:

http://kevin.vanzonneveld.net/techblog/article/improve_mysql_insert_performance/

Ciao,
Massimo

5

Ciao,

volevo inserire 15 000 000 coordinate gps provenienti da un file xml
all'interno di una tabella db.

Da java uso un parser sax per leggere l'xml. Tempo totale
dell'operazione: 2 minuti circa.

Ora ad ogni elemento xml faccio corrispondere una insert nel db (con
jdbc). Tempo dell'opeatione: decine di minuti

Ma c'e' qualcosa che non mi torna! Ma un db non dovrebbe essere una
scheggia
a gestire dati?

Come piu' un parser xml (che parsa un testo) essere molto piu'
veloce di un db
che e' fatto per organizzare dati?

Ho provato sia con mysql che postgresql. Il tempo e' analogo: decine
di minuti!!!

Ho provato a non fare commit ad ogni operazione ma ogni 3000 insert
e questo mi sembra aver migliorato la situazione ... ma non da
arrivare sotto i 10 minuti.

Voi sapete spiegare questa situazione?

Ho forse sbagliato qualcosa? Forse uso "male" il db?

15M record in 10min sono 25K record al secondo.

Nel mondo ETL [1], ottenere 25K rec / sec sul tuo
portatile (presumo) sono un risultato ragionevole.

Hai fatto bene a fare un commit solo ogni 3000 insert.
Imagino che usi gia` prepared statement? Se no, usali.

Cosa devi fare alla fine con quei 15M record -- i 10 min
sono un problema?

Bye,
Chris.

[1] http://en.wikipedia.org/wiki/Etl

6

Ciao,

volevo inserire 15 000 000 coordinate gps provenienti da un file xml
all'interno di una tabella db.
Ho provato sia con mysql che postgresql. Il tempo e' analogo: decine
di minuti!!!

In MySQL prova con LOAD DATA INFILE:

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

dovrebbe permetterti di ridurre i tempi di circa 20 volte.
Qui trovi un benchmark in php:

http://kevin.vanzonneveld.net/techblog/article/improve_mysql_insert_performance/

Ciao,
Massimo

Guardando il blog entry che citi, l'autore usando load
data dice che e` passato da ~ 1K rec / sec a ~ 28K rec / sec.

Ignorando per un momento che ovviamente i casi erano ben
diversi, ecco che i 25K rec / sec che Davide ha ottenuto
non mi sembrano mica cosi` schifosi.

Sono curioso di sapere se Davide per qualche motivo deve
*ripetutamente* caricare questi dati. Altrimenti io
aspetterei quei 10 minuti e mi metterei l'anima in pace.

Bye,
Chris.

7

Chris,

no quei dati li devo caricare una sola volta, non era quello il problema :slight_smile:

Mi interessava capire.

Capire come mai un db ci mette molto piu' (circa 2-5 volte)
che la semplice scrittura su disco dei dati.

Infatti se mysql e postgres ci mettono >10 minuti (a seconda
anche di come committi ... committando ad ogni insert si arriva anche
a 30-40 minuti)
per esempio hsqldb (db puro java :-))) ce ne mette 5 minuti! Questo e'
il tempo vicino a quello
di scrittura sull'hard disk. (lui poi ha problemi su alcune query)

Allora mi chiedevo, come mai 2 super banche dati come mysql e postgres
ci mettono
moolto di piu'.

Io anzi avrei pensato ci avrebbero messo di meno in quanto non avrebbero
scritto subito tutti i record su disco ma gli avrebbero tenuti una
parte in memoria.

Invece non e' cosi.

Non so se dalla vostra esperienza anche avete notato questa cosa.

Probabilmente io presumo il tempo potrebbe derivare da una delle seguenti cose:

A) transazioni: il fatto che uno possa fare rollback richiede alla
banca un lavoro aggiuntivo?
    isolamento richiede che diverse sessioni vedano record diversi?
B) affidabilita: probabilmente il db deve essere in grado di
ripristinare la situazione anche in
     caso di spegnimento "brutale" della macchina, quindi
probabilmente deve scrivere
     dei log su disco di ogni comando sql e non piu' tenere dati
inseriti solo in memoria?
C) primary, unique, foreign key contraint check?

Che sara' per questo che hanno introdotto i comandi spiegati sopra da Massimo
e il comando copy in postgresql ...

8

Non sono un esperto, ma forse la gestione degli index (e field
auto_increment) può influire?

In caso prova a vedere se le cose vanno più veloci senza.

Andrea

2010/4/13 Davide <d(a)vide.bz>

attachment.htm (2.82 KB)

9

Sono curioso di sapere se Davide per qualche motivo deve
*ripetutamente* caricare questi dati. Altrimenti io
aspetterei quei 10 minuti e mi metterei l'anima in pace.

Chris,

no quei dati li devo caricare una sola volta, non era quello il
problema :slight_smile:

Mi interessava capire.

Ah, ok.

Beh, legittimissimo :slight_smile:

Capire come mai un db ci mette molto piu' (circa 2-5 volte)
che la semplice scrittura su disco dei dati.

Infatti se mysql e postgres ci mettono >10 minuti (a seconda
anche di come committi ... committando ad ogni insert si arriva anche
a 30-40 minuti)
per esempio hsqldb (db puro java :-))) ce ne mette 5 minuti! Questo e'
il tempo vicino a quello
di scrittura sull'hard disk. (lui poi ha problemi su alcune query)

Allora mi chiedevo, come mai 2 super banche dati come mysql e postgres
ci mettono
moolto di piu'.

Io anzi avrei pensato ci avrebbero messo di meno in quanto non
avrebbero
scritto subito tutti i record su disco ma gli avrebbero tenuti una
parte in memoria.

Invece non e' cosi.

Non so se dalla vostra esperienza anche avete notato questa cosa.

Probabilmente io presumo il tempo potrebbe derivare da una delle
seguenti cose:

A) transazioni: il fatto che uno possa fare rollback richiede alla
banca un lavoro aggiuntivo?
   isolamento richiede che diverse sessioni vedano record diversi?
B) affidabilita: probabilmente il db deve essere in grado di
ripristinare la situazione anche in
    caso di spegnimento "brutale" della macchina, quindi
probabilmente deve scrivere
    dei log su disco di ogni comando sql e non piu' tenere dati
inseriti solo in memoria?
C) primary, unique, foreign key contraint check?

Che sara' per questo che hanno introdotto i comandi spiegati sopra
da Massimo
e il comando copy in postgresql ...

Si`, essenzialmente si`. a), b) e c) valgono.

Un DB relazionale tradizionale eccella su fronti diversi: quando i tuoi
dati diventano piu` grande della tua RAM, se hai tanti accessi
concorrenziali, se ti serve l'isolamento (transazioni), se ti serve
robustezza rispetto a crash ecc. ecc. Insomma giusti i punti che citi
tu.

Infine c'e` un overhead "contabile": 1 int e 2 double sarebbero 20 byte,
ma se li metto in un record Postgres consumeranno ~ 50. E` un po` come
l'Integer in Java consuma piu` dei 4 byte del int.

Chiaro che HSQLDB, essendo in-memory, ha altri punti d'ecellenza -
il buon vecchio "use the right tool for the right job e sempre valido,
insomma.

Come tu hai gia` capito ci sono dei pipeline non-standard per inserire
tanti dati (appunti COPY di postgres), e se guardi il link che ho
mandato a "ETL" su Wikipedia scoprirai che il loading dei data e`
un po` una scienza a parte.

Bye,
Chris.

10

Davide wrote:

Mi interessava capire.

Pare non sei l'unico. :slight_smile: Com'e' il confronto se misuri le query su XML e
DB? Mi aspetterei che li' il DB sia piu' veloce.

Thomas

11

Davide wrote:

Mi interessava capire.

Pare non sei l'unico. :slight_smile: Com'e' il confronto se misuri le query su
XML e
DB? Mi aspetterei che li' il DB sia piu' veloce.

Thomas

XML e` un formato e per DB propabilmente intendi un software
(i.e. PG, MySQL), quindi la domanda e` un po` ill-posed.

Se la query di Davide e` del tipo "trovami tutte le coordinate
a distanza di x dal punto y" direi che una query XPath non ha
molta speranza di poter raggiungere la performance di una query
SQL, premesso che entrambi sono implementati con un effort
simile.

Noto infine che le due cose non si escludono. P.e. Postgres ha
supporto nativo per XML e Xpath. Puoi buttare query SQL e query
Xpath verso lo stesso server. Carino, no? :slight_smile:

Bye,
Chris.

12

2010/4/13 Thomas Pircher <tehpeh(a)gmx.net>:

Davide wrote:
Pare non sei l'unico. :slight_smile: Com'e' il confronto se misuri le query su XML e
DB? Mi aspetterei che li' il DB sia piu' veloce.

Le query su xml erano un problema. Tutto il file xml non ci sta in memoria.
Infatti per leggere i valori ho dovuto usare sax (seriale) e non dom.

Per cui se volevo fare su xml avrei dovuto ripassare piu' volte tutto il file
xml per ogni "condizione" che volevo calcolare. Parsarlo tutto ci volevano
2 minuti, quindi se devo fare 1000 controlli ... calcola tu il tempo :wink:

Si il db qui e' tutta un'altra cosa!!! where e join vanno secondo
tutt'altro tempo (secondi).

E credo che il "valore" dei db sia qui! Le query su grandi quantita' di dati!!!

Poi non sottovaluterei un'altra cosa: i tool che i db mettono a disposizione.

Per esempio postgresql ha la possibilita' di vedere il "piano" della query
e quindi capire dove e' lenta.

E in un caso mi ha aiutato a capire la mancanza di alcuni indici.

13

Chris Mair wrote:

XML e` un formato e per DB propabilmente intendi un software
(i.e. PG, MySQL), quindi la domanda e` un po` ill-posed.

Ack. Avevo letto male il post originale. Pensavo che il confronto tra XML
(SAX+ Java, 2min) e DB (MySQL o Postgres, x*10min) fossero entrambe in
scrittura.

Se la query di Davide e` del tipo "trovami tutte le coordinate
a distanza di x dal punto y" direi che una query XPath non ha
molta speranza di poter raggiungere la performance di una query
SQL, premesso che entrambi sono implementati con un effort
simile.

Era quello che volevo capire. (Non sono esperto in databases.) Immagino
che i DB SQL sono ottimizzati sopratutto in lettura, e quando le queries
diventano piu' complicate di una semplice ricerca (key, value) allora
diventa difficile battere un DB relazionale.

Noto infine che le due cose non si escludono. P.e. Postgres ha
supporto nativo per XML e Xpath. Puoi buttare query SQL e query
Xpath verso lo stesso server. Carino, no? :slight_smile:

Interessante, si'. :slight_smile:

Thomas

14

Dico la mia.
Un db relazionale è "forte" in lettura, a fronte di un maggiore peso in
scrittura; la gestione delle transazioni, indici, chiavi primarie ed
eventualmente esterne, archive log etc. ovviamente pesa.

Faccio una osservazione sul codice utilizzato da Davide, senza conoscere
però il linguaggio in cui sta operando.
Se comprendo bene l'oggetto ptInsertNode è basato sulla connessione al
db conn. Nel ciclo for vengono impostati singolarmente i i 3 campi
dell'elemento n. (=record del db). Questo dovrebbe presupporre che ho 3
roundtrip tra la procedura ed il db. Se esiste una istruzione .set...
che premetta di assegnare tutti i valori del recordo in una sola volta,
il tempo di comunicazione con il db verrebbe a mio vedere ridotto ad
1/3. Su un db locale probabilmente il risparmio netto non è molto. Se
però il db fosse remoto, la cosa potrebbe avere effetti ben differenti.

Ultima considerazione: le prove fatte da Davide in xml avevano comunque
il db sottostante attivo e tale da impegnare comunque le risorse di
macchina?

ciao,
diego

15

Davide wrote:

E credo che il "valore" dei db sia qui! Le query su grandi quantita' di
dati!!!

Ack. A che velocita' generi i dati? Se il rate con cui i dati vengono
generati e' molto inferiore alla velocita' di inserimento di Postgres, e
ti rimane sufficiente spazio per le queries, allora non ci sono problemi,
no? Parafrasando Chris: fregatene... :stuck_out_tongue:

Thomas

I "sistemi" che ho visto negli ultimi 2 anni avevano 128 Byte di RAM,
quindi ti conviene prendere le mie considerazioni in materia di DB con un
pizzico di
sale... :wink: