Insert Delayed Lento ?

[agenti]

Ciao a tutti,
ho una situazione abbastanza paradossale.

Devo importare circa 14000 righe spalmate su varie tabelle.
Le tabelle sono con motore myisam acnhe se il motore del db è INNODB.

Le 14000 righe sono sottoposte a vari controlli e poi vanno in pasto alla funzione di add.
Ma il rallentamento avviene sulla funzione di add infatti escludendola si ottengono permormance intorno ai 15 secondi.

Dato che ho necessità di ottenere l'id del nuovo record nella prima query non uso il delayed solo nelle successive 3 che spalmano i dati in funzione del ID incrementale.

Il paradosso è che Non c'è differenza tra un INSERT INTO e un INSERT DELAYED.
Almeno nel mio caso.

Tra l'altro più faccio i test più il tempo del cron aumenta passando da un minimo di 150 a 450 secondi come se si saturasse la memoria.

C'è qualche variabile di sistema che può limitare una risposta rapida ?
Ho aumentato il bulk_buffer_size e il read_buffer_size a 128 mb.
Abbiamo una memoria di 8 giga su un centos molto potente.

Qualche suggerimento?
grazie.

[franzauker2.0]

Francamente non è che abbia capito un granchè.
Andiamo per ordine
1) qual'è l'origine dei dati? E' un file CSV? Un dump SQL? Un flusso generato da un programma?
2) "sono sottoposte a vari controlli" mi fa venire in mente che si tratti di un flusso generato da programma. Ma come? A singola riga? Cos'è la funzione di add?
3) che significa che le tabelle sono myisam ma il db è innodb? O le tabelle usano l'engine myisam, sicchè questo verrà usato, oppure innodb
4) è chiaro per te quale sia la semantica di insert delayed?

[agenti]

1) qual'è l'origine dei dati? E' un file CSV? Un dump SQL? Un flusso generato da un programma?
dati generati da un csv importanto in uan tabella temporanea, ma era chiaro che il problema non è l'origine dati ma il suo inserimento e credo che il post fosse chiaro.

2) "sono sottoposte a vari controlli" mi fa venire in mente che si tratti di un flusso generato da programma. Ma come? A singola riga? Cos'è la funzione di add?

Azzera questa frase , valuta che devo inserire 14000 righe successivamente e spalmarle su 3 tabelle quindi 14.000 x 3 query.

3) che significa che le tabelle sono myisam ma il db è innodb? O le tabelle usano l'engine myisam, sicchè questo verrà usato, oppure innodb

le system variables indicano come motore del DB INNODB mentre la tabella usa il motore myisam

4) è chiaro per te quale sia la semantica di insert delayed?

Non so cosa intendi per semantica, ma credo di avere capito che il delayed inserisce in memoria il dato senza che sia stato salvado in HD dando uno status positivo anche se il dato non è ancora disponibile, velocizzando pertanto l'inserimento.


Spero in un reply.
grazie.

[franzauker2.0]

Originariamente inviato da agenti
1) qual'è l'origine dei dati? E' un file CSV? Un dump SQL? Un flusso generato da un programma?
dati generati da un csv importanto in uan tabella temporanea, ma era chiaro che il problema non è l'origine dati ma il suo inserimento e credo che il post fosse chiaro.

Certo che il problema è anche l'origine dei dati.
Perchè, ancora francamente, non è stato scritto cosa vuoi fare, e come

Azzera questa frase , valuta che devo inserire 14000 righe successivamente e spalmarle su 3 tabelle quindi 14.000 x 3 query.

Non è affatto da azzerare, perchè devi specificare come vuoi inserire i dati
Vuoi fare una select into? Vuoi dumpare le righe in un file ASCII, e poi caricarle in un colpo solo? Vuoi farlo da applicazione

le system variables indicano come motore del DB INNODB mentre la tabella usa il motore myisam

Non c'entra nulla quale sia l'engine di default (che è stato cambiato a innodb nelle ultime versioni), la cosa da chiarire è se vuoi fare una trasmigrazione myisam=>myisam o myisam=>innodb.
Sono due casistiche diverse

4) è chiaro per te quale sia la semantica di insert delayed?

Non so cosa intendi per semantica, ma credo di avere capito che il delayed inserisce in memoria il dato senza che sia stato salvado in HD dando uno status positivo anche se il dato non è ancora disponibile, velocizzando pertanto l'inserimento.

Ovviamente no.
E' il meccanismo usato per le tabelle myisam per consentire l'accodamento senza, nel contempo, porre lock in scrittura.
Ogni volta che aggiungi una riga, in una tabella myisam, la blocchi totalmente (per tutti gli altri utenti mysql) fino al termine.
Ciò non accade per innodb (oddio, accade se imposti autocommit e serializable) - ecco perchè per inciso va chiarito "da dove" a "dove" devono fluire i dati

delayed fa sì che i dati ACCODATI, quindi non le modifiche che ontologicamente bloccano la tabella, non creino lock di tipo WRITE sulla tabella. In sostanza lo stock storico dei dati rimane accessibile dagli altri utenti mysql, mentre i dati aggiunti (tipo log) non rompono le scatole.

Ciò fa sì che l'utente mysql sia rapidamente liberato, ma ciò non accelera la scrittura sul db.
Non esiste insert delayed per innodb.

Suggerisco quindi che dettagli un po' meglio dove sono i dati
(tabelle myisam?), dove li vuoi mettere (tabelle innodb? myisam? hanno indici? ci sono trigger? hanno vincoli di integrità referenziale? usi l'UPDATE sull'insert?), e come lo vuoi fare.
E poi devi usare delle tabelle temporanee? LOAD DATA da un file (in mysql) è tipicamente moooolto più veloce

[agenti]

Ripeto:

Ho 3 query da effettuare per 14.000 volte.

La prima isnerisce un prodotto e genera un id uniconella tbl1

Ottengo l?ID e lo passo ad una seconda query che aggiunge dati alla tbl2
Suiccessivamente creo una altra query che passa i dati nella tabella 3

Le tabelle hanno motore myisam.
La fonte dati è uan tabella mysam e le tabelle di destinazioen sonoa nche tabelle myisam.

Innodb non c'entra se non nelle variabili globali come già detto.

Ho usato L' INSERT DELAYED tramite una query pdo tramite php.
E pensavo che avrebbe potuto aiutarmi nella seconda e terza query per velocizzare.
Ma sembra che così non è.

A questo punto non so quali altri dettagli darti se non la query stessa ma è una banale INSERT DELAYED (field,field...) VALUES(1,2,...)


La mia domanda rimane però:
c'è qualche variabile globale che mi permetta di velocizzare l'inserimento o no ?
ad esempio il concurrent_limit ?
Grazie.

[agenti]

Dimenticavo:
nessun trigger.

LOAD DATA da un file (in mysql) è tipicamente moooolto più veloce

Lo so e lo uso per popolare la tabella temporanea che poi comparo con i dati attuali epr capire:
quali eliminare , quali cancellare e quali aggiungere.

A questo punto penso che userò un SELECT INTO filtrando i dati dalla tabella temporanea e spalmandola priam sulla prima tabella e poi ottenuto gli id sempre tramite select into, popolerò le altre due tabelle.


Se esiste un modo per popolare 3 tabelle utilizzando il nuovo ID generato proprio dal LOAD DATA dimmelo perchè io non lo conosco.

Ma non mi dire un trigger perchè rischia di mandarmi in crash il db.

[franzauker2.0]

Originariamente inviato da agenti
Ripeto:
Ho 3 query da effettuare per 14.000 volte.
La prima isnerisce un prodotto e genera un id uniconella tbl1
Ottengo l?ID e lo passo ad una seconda query che aggiunge dati alla tbl2
Suiccessivamente creo una altra query che passa i dati nella tabella 3

Ripeto:
quindi alimenti le 3 tabelle da un'applicazione, da quanto posso intuire PHP.
Se è così, la prima domanda da porsi è MISURARE il tempo che viene impiegato dall'interprete PHP.
Perchè se fai un ciclo PHP, per 14.000 volte, che crea 14.000 INSERT diverse, una per ogni riga, allora questo è male.
Stai usando (da quanto intuisco) PHP, non mysql (INSERT INTO...SELECT).

La mia domanda rimane però:
c'è qualche variabile globale che mi permetta di velocizzare l'inserimento o no ?
ad esempio il concurrent_limit ?
Grazie.

Per una tabella myisam? Direi che dipende dall'utilizzo.
concurrent_insert determina l'aggiunta in coda delle righe, come accennato prima lasciando la possibilità di fare select sulla parte iniziale.

Ma le cose da fare sono parecchie
0) perchè non trasferisci con 3 query i dati dalle sorgenti alle destinazioni, invece di farne 14.000?
1) nella tabella destinazione esistono indici? Questo è molto importante
2) nella tabella destinazione esistono trigger?
3) la tabella destinazione è modificata durante la procedura di inserimento dalle tabelle temporanee?
4) come materialmente avviene (ed è la terza volta che lo chiedo) il trasferimento tra sorgente e destinazione? Mediante 14.000 INSERT diverse? Se sì allora
LOCK TABLES / UNLOCK TABLES
Anzi meglio
LOCK TABLES... fai 1.000 insert... UNLOCK TABLES
e cicla 14 volte (per evitare problemi di hammering se hai un server moscio)
5) puoi usare programmi pianificati (mysqldump e mysql nella fattispecie)?
6) durante il trasferimento le tabelle destinazione devono essere utilizzabili? Se no _>disable keys / enable keys

Riassumendo la situazione per myisam è abbastanza semplice.
I rallentamenti sono dovuti a
- indici
- lock posti da altri, soprattutto in scrittura
- overhead del protocollo che usi per scrivere i dati (le INSERT)

La situazione più veloce è
- nessun indice in destinazione (o parimenti disabilitati)
- lock della tabella in scrittura preso da te (nessun altro rompe le @@)
- overhead minimo, quindi LOAD DATA INFILE (#1 per velocità), oppure una INSERT unica. O addirittura (se usi cron) un mysqldump pipato su mysql

Vedi come ciò si attaglia alla tua situazione

[franzauker2.0]

Originariamente inviato da agenti
Dimenticavo:
nessun trigger.

LOAD DATA da un file (in mysql) è tipicamente moooolto più veloce

Lo so e lo uso per popolare la tabella temporanea che poi comparo con i dati attuali epr capire:
quali eliminare , quali cancellare e quali aggiungere.

A questo punto penso che userò un SELECT INTO filtrando i dati dalla tabella temporanea e spalmandola priam sulla prima tabella e poi ottenuto gli id sempre tramite select into, popolerò le altre due tabelle.


Se esiste un modo per popolare 3 tabelle utilizzando il nuovo ID generato proprio dal LOAD DATA dimmelo perchè io non lo conosco.

Ma non mi dire un trigger perchè rischia di mandarmi in crash il db.

La risposta alla domanda, sia pure un po' cervellotica è banale: non usare una chiave autoincrementante.
Usa una chiave "vera" o, nel caso davvero peggiore, crea una chiave calcolata (anche tagliata più corta se hai davvero molti dati, ma per 14.000 puoi usare approcci rozzissimi).

Parafrasando Einstein, se la chiave autoincrementante ti sta sullo stomaco, toglila

[agenti]

esempio:
copyfile:2.88538
import_query:0.62418

qui ho già copiato il file e ho eseguito la query che mi permette di definire Le righe da modificare piuttosto che le nuove

import_process:13.42040
Qui esegui il processo di update ed insert

deleteproducts_getdata:0.05456
deleteproducts_process:0.65251
Qui eseguo la distruzione dei dati non più in archivio

Azioni totali 1769

Non capisco come faccioa spalmare i dati tramite un loadinfile su tre tabelle diverse discriminando quelle da inserire rispetto a quelle da aggiornare senza utilizzare un altra query.

E poi non posso eliminare la colonna ID autoincrement pechè devo relazionarla alla tabella relativa alle categorie ottenendo il nuovo ID.

Cmq credo di avere capito che se effettuo un lock sulla fase di inserimento ogni 1000 record posso velocizzare il processo di inserimento, l'indice purtroppo c'è ma essendoci il pericolo di una duplicazione su una colonna REFERENCE non me la sento di eliminarlo.
Rispondo qui alle tue domande:


Ma le cose da fare sono parecchie
0) perchè non trasferisci con 3 query i dati dalle sorgenti alle destinazioni, invece di farne 14.000?

Parli tramite un SELECT INTO ?
si è questo sicuramente il prossimo upgrade.

1) nella tabella destinazione esistono indici? Questo è molto importante

Si esistono
2) nella tabella destinazione esistono trigger?
no
3) la tabella destinazione è modificata durante la procedura di inserimento dalle tabelle temporanee?
no, la tabella temporanea viene creata e non aggiorna la tabella di destinazione.

4) come materialmente avviene (ed è la terza volta che lo chiedo) il trasferimento tra sorgente e destinazione? Mediante 14.000 INSERT diverse? Se sì allora
LOCK TABLES / UNLOCK TABLES
Anzi meglio
LOCK TABLES... fai 1.000 insert... UNLOCK TABLES
e cicla 14 volte (per evitare problemi di hammering se hai un server moscio)
ok.

5) puoi usare programmi pianificati (mysqldump e mysql nella fattispecie)?
parli di un cron ?

6) durante il trasferimento le tabelle destinazione devono essere utilizzabili? Se no _>disable keys / enable keys

Lo faccio sulla tabella temporanea ma non su quella di destinazione per evitare duplicazioni sull'indice.

grazie.

[franzauker2.0]

Originariamente inviato da agenti
Non capisco come faccioa spalmare i dati tramite un loadinfile su tre tabelle diverse discriminando quelle da inserire rispetto a quelle da aggiornare senza utilizzare un altra query.

Guarda che è possibile effettuare un aggiornamento con un load.
Se la riga non esiste => viene inserita
Se esiste già => la modifichi

E poi non posso eliminare la colonna ID autoincrement pechè devo relazionarla alla tabella relativa alle categorie ottenendo il nuovo ID.

Non la devi eliminare, devi sostituira con una vera chiave primaria pre-calcolata, cosa che puoi fare benissimo da applicazione.

Parli tramite un SELECT INTO ?
si è questo sicuramente il prossimo upgrade.

mi sembra di gran lunga la soluzione più semplice, e anche veloce.

1) nella tabella destinazione esistono indici? Questo è molto importante

Questo rallenta

3) la tabella destinazione è modificata durante la procedura di inserimento dalle tabelle temporanee?
no, la tabella temporanea viene creata e non aggiorna la tabella di destinazione.

E' da intendersi "esiste qualcuno che può modificare le tabelle destinazione, mentre la procedura di aggiornamento è in corso"?
Se sì => allora è questo probabilmente il problema principale, ovvero i lock di altri utenti mysql che ti rallentano la fase di aggiornamento

5) puoi usare programmi pianificati (mysqldump e mysql nella fattispecie)?
parli di un cron ?

Esattamente.
Puoi fare un mysqldump delle 3 tabelle, seguito (o anche con un pipe) da un mysql su un dump temporaneo.
Questo ti dà la massima velocità possibile di caricamento che non sia del tipo LOAD, perchè usi programmi eseguibili e non script PHP. Attenzione però ai parametri di mysqldump coi quali puoi facilmente mettere LOCK e/o disabilitare gli indici

Lo faccio sulla tabella temporanea ma non su quella di destinazione per evitare duplicazioni sull'indice.

Duplicazioni?
Mah... è la logica del tuo programma che deve prevenire chiavi duplicate, non ha molto senso (e penso te ne stia accorgendo) delegare al database.
Anche perchè, in questo caso, o ti spara un messaggio d'errore ed abortisce, oppure - alla peggio - abortisce direttamente e silenziosamente.

Puoi sostituire tranquillamente la chiave autoincrementante (con una certa perdita di prestazioni, ma è irrilevante per così poche righe) ad esempio con qualcosa tipo

select MD5(CONCAT(connection_id()*RAND(),now(),'pippo'))
dove al posto di "pippo" puoi metterci un po' di campi della tabella.

In tal modo non ti serve minimamente agire inserendo-e-leggendo le righe: la chiave la generi te e bon la scrivi in tutte le tabelle che vuoi.

In realtà ci sono tecniche più raffinate per mantenere le prestazioni con una chiave più lunga, che consiste nel prendere una parte della chiave lunga, trasformarla in intero, ed usarla come chiave separata con una condizione AND con la chiave intera.
Non te la faccio lunga, dubito che interessi a qualcuno come gestire db da centinaia di milioni di righe.