Home Blog Help Info Newsletter
Blog
Feb 13

MySql query benchmark

webmaster: 13/02/09 @ 13:18
A costo di far sembrare questo un blog dedicato all'ottimizzazione e al benchmarking di query MySql, vorrei condividere con voi le mie ultime scoperte in materia.

Come ho già scritto, SELECT DISTINCT e GROUP BY sono istruzioni che rallentano l'esecuzione di una query in modo notevole. Vorrei agginugere a questa lista l'istruzione ORDER BY, che ho scoperto essere un'altra grossa causa di query lente.

Dove possible cerco di delegare i compiti di queste istruzioni al PHP, ma alcune volte questo non è proprio possibile. In questi casi il trucco è dividere la query in modo che sia più semplice possibile e non includa JOIN di alcun tipo.

Prendiamo ad esempio questa query che fa un JOIN su varie tabelle, deve leggersi un sacco di row e poi le deve ordinare. Su una tabella con tante row, questa query potrebbe diventare lentissima lentissima.
SELECT * FROM news_table, users_table, categories_table WHERE time > UNIX_TIMESTAMP () AND... ORDER BY time DESC

Visto che l'ORDER BY era molto importante la soluzione è stata di dividere la query in due parti. Nella prima si esegue solo una ricerca per gli id delle news e li si ordina (si potrebbe far fare questo ordinamento al PHP, ma a questo punto penso sia inutile). Poi si mette la seconda query nel loop dei risultati della prima query eseguendo quindi una query che, benché ricca di JOIN è molto veloce grazie all'assenza di elementi come GROUP BY e ORDER BY.
SELECT id FROM news_table WHERE time > UNIX_TIMESTAMP () ORDER BY time DESC LIMIT 0, 20
SELECT * FROM news_table, users_table, categories_table WHERE id = $row['id'] AND...

In realtà ho trovato un modo per ulteriormente velocizzare questa coppia di query usando l'istruzione IN. In pratica gli id sono infilati in un loop e costruiscono una stringa come $id1, $id2, $id3... che chiamo $ids e che poi viene usata nella seguente query. Il tempo d'esecuzione di questa query è meno del 10% della somma delle 20 query che servivano prima.
SELECT * FROM news_table, users_table, categories_table WHERE IN ($ids) AND...

Un'altra piccola scoperta che ho fatto è l'uso dell'istruzione MAX. Per vari motivi ho bisogno di sapere qual'è l'id dell'ultima news. Per molto tempo ho utilizzato la seguente query - che fa uso del famigerato ORDER BY.
SELECT id FROM news_table WHERE time > UNIX_TIMESTAMP () ORDER BY time DESC LIMIT 0, 1

Da quando ho capito quanto fosse importante evitare di usare l'ORDER BY è diventata una missione trovare alternative dove possibile. In questo caso MAX ottiene gli stessi risultati, ma in modo molto più efficiente.
SELECT MAX(id) as max FROM news_table WHERE time > UNIX_TIMESTAMP ()

Devo ammettere che arrivare a queste conclusioni sarebbe stato molto difficile senza uno strumento di analisi. Il mio piccolo segreto è stato quello di sviluppare ulteriormente lo script di cui parlavo nel precedente post trasformandolo in uno script per paragonare query, o grupppi di query, tra di loro. L'idea era di avere uno strumento che facilitasse il lavoro di dare una risposta a domande come "Se divido questa query in due, è meglio?", oppure "È meglio una query più complessa o tante query semplici?", oppure "Meglio questa query o quella?"

Lo script esegue i due gruppi di query n volte (almeno un centinaio per avere dei numeri statisticamente rilevanti) in modo da evitare errori dovuti alla misurazione del tempo o a tempi di reazione diversi del DB. Se qualcuno fosse interessato può trovare qui sotto lo script. Se qualcuno lo migliorasse mi faccia sapere, lo aggiornerò anch'io.

Test MySql

Commenti: 18
Post correlati:


I commenti dei lettori:

archimede archimede il 06 Marzo 09 @ 14:25 pm

Non conosco MySql, ma mi pare bizzarro essere costretti a trovare alternative a operazioni che per un db sono (o dovrebbero essere) il pane quotidiano.

Capisco (e approvo) MAX al posto di ORDER BY + LIMIT, ma smettere di usare clausole tipo GROUP BY o DISTINCT (quando sono usate a proposito) non dovrebbe essere una pratica corretta.

Hai considerato la possibilità di aggiungere degli indici per vedere se le prestazioni migliorano?

Alessandro

webmaster webmaster il 07 Marzo 09 @ 09:33 am

grazie archimede, ma senza indici penso che questo db sarebbe inchidato da anni. ormai indicizzo tutto quello che è possibile indicizzare :)

archimede archimede il 09 Marzo 09 @ 14:57 pm

Mah, confesso che la cosa mi sorprende un po': ritenevo che ormai anche MySQL avesse un motore sufficientemente ottimizzato.

Inoltre non sono del tutto sicuro che i tuoi test siano conclusivi; quando dici "Lo script esegue i due gruppi di query n volte" cosa intendi? Esecuzioni consecutive o concorrenti? In altre parole, la query più veloce in termini assoluti non necessariamente è quella maggiormente scalabile.

Alessandro

webmaster webmaster il 09 Marzo 09 @ 21:29 pm

Inoltre non sono del tutto sicuro che i tuoi test siano conclusivi; quando dici "Lo script esegue i due gruppi di query n volte" cosa intendi? Esecuzioni consecutive o concorrenti? In altre parole, la query più veloce in termini assoluti non necessariamente è quella maggiormente scalabile. intendo che il primo gruppo di query viene eseguito n volte e poi il secondo gruppo di query viene eseguito n volte. il gruppo di query con il tempo di esecuzione medio inferiore è il gruppo migliore.

ritengo che il mio codice possa essere migliorato molto, quindi se hai idee sei il benvenuto

archimede archimede il 10 Marzo 09 @ 09:58 am

Non sono un esperto di benchmarking nè, come dicevo, di MySql: temo quindi di non poterti aiutare concretamente.

Quel che posso dire è che non credo sia sufficiente confrontare i tempi medi di esecuzione di due query per un singolo utente: molto più significativo sarebbe creare una pagina per ciascuna query e simulare l'accesso contemporaneo a queste da parte di n utenti.

So che esistono tools che eseguono questi tipi di test ma, non avendone mai utilizzato alcuno, non saprei quale suggerire.

Alessandro

webmaster webmaster il 10 Marzo 09 @ 14:10 pm

alessandro, non penso di seguirti. in questo caso l'utnete non ha alcuna influenza sulla query. la query è eseguita dal server ed è indipendente dal browser, dalla banda o dal sistema operativo dell'utente. concordo con te che il tempo medio non tiene conto di alcune variabili, ma coinvolgere l'utente non aiuterebbe.

se c'è un problema con il mio script è il fatto che mysql tende a essere più veloce quando le query vengono ripetute a breve distanza di tempo, ma questo non so come includerlo nel mio script.

archimede archimede il 10 Marzo 09 @ 15:51 pm

webmaster, lavorare sul server cambia poco il discorso: vuoi ottimizzare le query per un unico utente/client/script/quelchetipare che interroga il db 1000 volte di seguito oppure per 1000 utenti/clients/scripts/quelchetipare che interrogano il db una volta sola ma tutti nello stesso momento?

A me la differenza fra i due scenari pare evidente e prescinde dalle tecniche usate per accedere al db.

Alessandro

webmaster webmaster il 10 Marzo 09 @ 22:04 pm

A me la differenza fra i due scenari pare evidente e prescinde dalle tecniche usate per accedere al db.
archimede, in questo caso mi aiuteresti molto se me la spiegassi, perché non sono sicuro di vedere la differenza, almeno per quanto riguarda la domanda: meglio la query x o la query y?

Dylan666 Dylan666 il 11 Marzo 09 @ 10:58 am

Se parliamo di MySQL 5penso che questo possa esserti utile:
http://dev.mysql.com/doc/mysqltest/en/index.html

archimede archimede il 11 Marzo 09 @ 14:59 pm

mi aiuteresti molto se me la spiegassiIn un determinato momento al database può arrivare la richiesta di eseguire una certa query da parte di 1 o di n utenti.

Se tu fai girare la query una volta supponiamo alle ore 09:00 e poi un'altra volta alle ore 09:01 stai misurando le prestazioni del sistema con un singolo utente. Se invece apri due sessioni (due finestre, due script, ...) e contemporaneamente alle ore 09:00 lanci la stessa query stai misurando le prestazioni del sistema con accessi concorrenti (in altri termini, stai verificando la [url=../../glossario/scalabilita/]scalabilità[/url] di quella query).

Meglio di così temo di non riuscire a spiegarmi.

Alessandro

webmaster webmaster il 11 Marzo 09 @ 16:49 pm

archimede, il ragionamento che stai facendo non ti porterà a capire se la query x è meglio della query y oppure no.

per essere più chiari è come se cercassimo di misurare la velocità massima di una macchina nel traffico di milano. certo ci darebbe un'idea della velocità massima a cui potremmo usare la macchina, ma non risponderebbe alla domanda: è più veloce la macchina x o la macchina y?

quello che serve a noi è conoscere la velocità assoluta delle query. che poi ci siano 1 o 1000 utenti collegati contemporaneamente questo non influirà sul risultato finale in quanto possiamo correttamente presumere che entrambe sarebbero penalizzate allo stesso modo dai 1000 utenti.

webmaster webmaster il 11 Marzo 09 @ 16:51 pm

Se parliamo di MySQL 5penso che questo possa esserti utile:
http://dev.mysql.com/doc/mysqltest/en/index.html
molto interessante, non lo conoscevo. purtroppo non ho accesso alle cartelle di test fornite da mysql, ma vedo se riesco a ottenere qualcosa lo stesso.

archimede archimede il 11 Marzo 09 @ 17:37 pm

che poi ci siano 1 o 1000 utenti collegati contemporaneamente questo non influirà sul risultato finale
Ah sì? Allora supponiamo di avere due query che tirano fuori lo stesso risultato: la prima impega mediamente 5 secondi e l'altra 3. Secondo il tuo ragionamento preferiremo automaticamente la seconda, ovviamente.

Supponiamo però che per far girare la seconda query il sistema necessiti del triplo di RAM e/o CPU rispetto alla prima: ogni utente che farà girare la query in un determinato momento avrà bisogno di quelle risorse.

Personalmente preferisco una query che impiega 2 secondi in più ma non mi mette in ginocchio il server al quinto accesso concorrente, poi è pur sempre vero che non sono un esperto di queste cose.

Alessandro

Dylan666 Dylan666 il 11 Marzo 09 @ 18:00 pm

Caro webmaster dai retta a archimede che è uno che ha dimostrato di sapere molto, ma veramente molto in materia di DB ;)
E io pure, per quel pochissimo che ne so, devo dargli ragione.
Il test sulle query va fatto simulando più accessi concorrenti...

Una query non deve essere solo "veloce" ma anche utilizzare una porzione accettabile di risorse.

Con un paragone un po' forzato capirai bene anche tu che una cosa è aprire una file 100 volte in un'ora, un'altra è aprirne 100 identici nello stesso identico secondo...

Dylan666 Dylan666 il 11 Marzo 09 @ 18:07 pm

EDIT: miglioriamo un attimo il paragone di prima, altrimenti credo che lo fraintenderesti...
Mettiamo che il file (cioè la risposta che ti serve dal DB) possa essere aperto da due programmi diversi (che sarebbero le query).

Il primo lo apre in 4 secondi, utilizzando il 10% del processore e una modesta quantità di RAM
Il secondo apre lo stesso file in 1 secondo, utilizzando l'80% del processore e molta più RAM

Con i tuoi test quale riterresti il vincitore?
E se invece dovessi aprire contemporaneamente più file su quale faresti cadere la scelta?

Come vedi la rsposta cambia di netto...

webmaster webmaster il 11 Marzo 09 @ 19:54 pm

alessandro, finalmente ci siamo capiti.
che la quantità di ram sia importante sono d'accordo con te e il mio script non ne tiene conto al momento, ma provvederò ad aggiungere l'informazione.

continuno però a sostenere che simulare più utenti collegati contemporaneamente non aiuterebbe, basta sapere quanta ram una query consuma.

Michelangelo Michelangelo il 16 Marzo 09 @ 20:40 pm

Come dicevano su, non devi controllare solo quanta RAM consuma, ma anche quante risorse del processore.
Fino a quando gli accessi contemporanei sono relativamente pochi, il tuo metodo di benchmarking potrebbe anche essere il più efficace. Ma se, come speriamo tutti, gli accessi aumenteranno esponenzialmente, probabilmente dovresti riguardare il benchmark.
Comunque per me è vergognoso che un DB sia battuto nel suo campo da uno script php.

Mich

webmaster webmaster il 16 Marzo 09 @ 20:42 pm

Comunque per me è vergognoso che un DB sia battuto nel suo campo da uno script php.
concordo con te, ma purtroppo è così!

Lascia un commento

Insulti, volgarità e commenti ritenuti privi di valore verranno modificati e/o cancellati.
Nome:

Commento:
Conferma visiva: (ricarica)

Inserisci la targa della città indicata nell'immagine.

Login | Iscriviti

Username:

Password: