Follow my blog with Bloglovin

Raimondo Fanale

web(re)sponsability

Quando ti chiedono di ottimizzare mysql

Scritto da Raimondo Fanale • Mercoledì, 20 settembre 2006 • Categoria: MySQL

Soprattutto nell'ultimo periodo non ho avuto molto tempo di scrivere sul mio blog, a causa di impegni lavorativi e personali molto pressanti.

Siccome però sono uno di quelli che pensa che i periodi di riflessione siano ottimi per poi poter condividere le proprie esperienze con gli altri eccomi di nuovo a scrivere sull'argomento perfomances ed in particolare ad affrontare il problema del set-up di mysql.




Nell'ultimo mese ho avuto l'esigenza di ottimizzare delle istallazioni di mysql per ben 4 volte su quattro ambiti diversi e su 4 servers diversi. La richiesta in genere è sempre la stessa e la domanda comincia sempre con un generico:

"il mio server va molto più lento che all'inizio, mi hanno consigliato di cambiarlo. Che devo fare?"

Di questa cosa ne parlavamo anche durante un aperitivo due giorni fa con un mio amico che si occupa di sutio ed analisi di algoritmi a livello matematico-teorico.

Ora: escludiamo i casi in cui effettivamente l'hardware del server sia obsoleto o sia diventato inefficace per l'erogazione dei servizi richiesti.

Quello che posso dire è che non esiste una soluzione standard per l'ottimizzazione di una istallazione di mysql, ma esiste un metodo, ovvero intendo una metodologia di affrontare il problema. Una scaletta è la seguente:

  • analisi dell'hardware
  • analisi della configurazione del filesystem
  • analisi del ripartizionamento
  • analisi della configurazione di mysql
  • tests delle performances di mysql
  • adeguamento delle configurazioni del filesystem e del my.cnf

L'analisi dell'hardware non è certo cosa di cui voglio occuparmi in questa sede. Vediamo invece quali sono le cose da considerare quando si passa all'analisi del filesystem:

  • sicuramente il tipo di filesystem scelto :-)
  • l' allocation block size
  • le journaling options
  • i reserved blocks
  • il check interval
  • il directory hash
  • l' inode options e soprattutto il suo size
  • se state usando ext2fs vi consiglio vivamente di passare a qualcosa di più nuovo...
  • se siete abbastanza sicuri di quello che fate potete usare elvtune

Analisi del ripartizionamento. Spesso mi capita di trovare servers istallati con una prospettiva che nel tempo è cambiata. Questo si traduce in una diversa esigenza di ripartizionamento degli spazi. A tal proposito è utile considerare che mysql prevede una opzione datadir nel file my.cnf per impostare la directory dove si volgiono memorizzare i files di database.

Analisi della configurazione di mysql. Questa è l'attività più ingrata... bisogna andare a leggere tutte le opzioni del my.cnf e vedere se in base alle "dichiarazioni" del cliente corrispondono alla giusta scelta strategica. Di esempi di files my.cnf ce ne sono "a iosa" nella rete. Quello che consiglio è di leggere un buon manuale per capire e comprendere tutte le opzioni (o almeno quelle utili) del file my.cnf. Un avvertimento: l'istallazione base non prevede di per sè delle ottimizzazioni e le opzioni che si trovano nel file my.cnf sono minime. Faccio un esempio di file my.cnf e vedrete che di opzioni ce nesoon alcune che non trovate nel file standard:

[mysqld]

back_log = 75

skip-innodb

max_connections = 500

key_buffer = 384M

myisam_sort_buffer_size = 64M

join_buffer_size = 1M

read_buffer_size = 1M

sort_buffer_size = 2M

table_cache = 1800

thread_cache_size = 384

wait_timeout = 7200

connect_timeout = 10

tmp_table_size = 64M

max_heap_table_size = 64M

max_allowed_packet = 64M

max_connect_errors = 1000

read_rnd_buffer_size = 524288

bulk_insert_buffer_size = 8M

query_cache_limit = 4M

query_cache_size =128M

query_cache_type = 1

query_prealloc_size = 65536

query_alloc_block_size = 131072

default-storage-engine = MyISAM


[mysqld_safe]

nice = -5

open_files_limit = 8192



[mysqldump]

quick

max_allowed_packet = 16M



[myisamchk]

key_buffer = 64M

sort_buffer = 64M

read_buffer = 16M

write_buffer = 16M

Tests delle perfomances di mysql. Dopo tanto penare siamo arrivati all'analisi delle prestazioni del database. Ci sono vari tools che danno un'idea di come vanno le cose anche su macchine inproduzione (mi raccomando... cautela!!!) e sono il mysqlreport o il più noto mtop

E siamo arrivati alla fine: raccogliamo tutti i dati di cui siamo entrati in possesso e finalmente possiamo "tirare le somme" e configurare o riconfigurare in modo adeguato il nostro bel mysql!!!




| Top Exits (0)
38079 hits
Twitter Bookmark Quando ti chiedono di ottimizzare mysql  at del.icio.us Facebook Google Bookmarks FriendFeed Digg Quando ti chiedono di ottimizzare mysql Mixx Quando ti chiedono di ottimizzare mysql Bloglines Quando ti chiedono di ottimizzare mysql Technorati Quando ti chiedono di ottimizzare mysql Fark this: Quando ti chiedono di ottimizzare mysql Bookmark Quando ti chiedono di ottimizzare mysql  at YahooMyWeb Bookmark Quando ti chiedono di ottimizzare mysql  at Furl.net Bookmark Quando ti chiedono di ottimizzare mysql  at reddit.com Bookmark Quando ti chiedono di ottimizzare mysql  at blinklist.com Bookmark Quando ti chiedono di ottimizzare mysql  at Spurl.net Bookmark Quando ti chiedono di ottimizzare mysql  at NewsVine Bookmark Quando ti chiedono di ottimizzare mysql  at Simpy.com Bookmark Quando ti chiedono di ottimizzare mysql  at blogmarks Bookmark Quando ti chiedono di ottimizzare mysql  with wists wong it! Bookmark using any bookmark manager! Stumble It! Print this article! E-mail this story to a friend! Identi.ca

0 Trackbacks

  1. Nessun Trackbacks

9 Commenti

Mostra commenti (Cronologicamente | Per argomento)
  1. Ciao Raimondo, la tua analisi mi è piaciuta abastanza a parte qualche errore di grammatica :-) (che purtroppo attanaglia anche me), mi è sembrata sensata. L' unica cosa a cui forse avrei dato maggiore enfasi è il chiedersi che tipo di database vado a gestire? OLTP datawarehosing etc... Quanto sono grandi le mie tabelle? Le posso indicizzare in qualche modo e ottimizzare i miei indici? Che tipo di dati ci sono dentro (campi fissi, variabili)? E' pulito e ottimizzato il codice sql che gira? E soprattutto quali sono le operazioni che fanno sedere la macchina? E non ultimo (questo l'hai suggerito anche tu) il mio filesystem va bene o è meglio provvedere ad usare qualcosa di diverso?
    Io credo che sicuramente non hai voluto postare cose pesanti per non scoraggiare gli addetti ai lavori alle prime armi ma sicuramente questo mio commento sarà utile a qualcuno.
    Ciao
  2. Orrori di grammatica? :-P
    spesso scrivo troppo veloce e non ricontrollo :-P

    in realtà tutto il post era indirizzato + ad un target di sistemisti che non dba puri.

    Quello che dici è giustissimo, l'ottimizzazione degli indici così come la targetizzazione in base al tipo di database sono due attività importantissime, ma spero di poter scrivere ancora sull'argomento, stavolta rivolgendomi ad un settore dba/programmazione piuttosto che al dba/sistemista.
  3. Ciao!
    Molto interessante come articolo, a me piacerebbe capire come ottimizzare MySQL, magari il mio che noto rallenta molto le prestazioni del mio server, ma non saprei bene come strutturare il tutto...

    Vediamo ti do' una breve spiegazione di una situazione e poi capendo come sistemare quella cerchero' di capire io come sistemare eventuali altri server.

    Per prima cosa, come dici tu, la configurazione hardware:
    - Server dedicato
    - CPU Xeon HT 2,4Ghz
    - 2gb di ram
    - 2 hd da 75gb in mirror (unica partizione)
    - FS: EXT3
    - OS: Debian etch aggiornata

    Il server fa da:
    - mail server (traffico basso, maggiormente manda)
    - FTP (traffico tendente a zero, gli utenti sono sul db)
    - web server con apache2 (traffico niente male)
    - database server con Mysql 5.0.32-Debian_7etch1

    Come dovrei procedere per migliorare le prestazioni?
    Premetto che l'indicizzazione del database dovrebbe essere gia' a puntino, o almeno cosi' credo che sia, in ogni caso si puo' vedere in seguito.
  4. Dipende anche dal traffico che prevedi di gestire.
    Siccome puoi dire a mysql di gestire un toto di ocnnessioni, di usare un tot di meoria per ogni operazione etc etc etc, puoi anche sfruttare queste opzioni.
    Puioi vedere il file my.cnf che ho postato per farti un'idea.
    Altra cosa: okkio al memory leak che genera apache+php se non setti per bene i tempi di sessione: spesso non è mysql che genera problemi, ma la calibrazione dei due server e del linguaggiodi script.
  5. Il traffico e' "enorme" guarda il sito: http://www.softairmania.it
    Abbiamo qualcosa come 2 milioni di pagine visitate al mese.

    Per php+apache si potrebbe essere anche se gli altri siti sullo stesso server non risentono del carico del primo, il sito che fa' piu' traffico e che sente di piu' il peso e' cambiato un pochino quando ho cercato di "sistemare" (quasi a caso :-|) i settaggi del my.cnf

    Hai msn/skype o simili? forse li ci viene più facile parlarne
  6. non vedo l'altro mio commento, comunque ho provato il tuo my.cnf... mi e' morto tutto... ho dovuto rimettere di corsa il vecchio
  7. Ciao,
    non che non lo vedi.. sono moderati e devo avere il tempo di connettermi e approvarli.
    per quanto riguarda l'utlizzo del mio my.cnf, mi scuso, ma quando dicevo di dargli un'occhiata per farti un'idea intendevo un'occhiata.. quello è tarato per uno dei delle mie configurazioni, non è detto che funzioi bene su altre istallazioni o configurazioni.
  8. si immaginavo, cmq il mio cnf e' piu' complesso ma disicuro piu' disarticolato e andrebbe emglio configurato, guarda questo link cosi' capisci che intendo http://xxxxxxxxxxxxxxxxxxxxxx

    CMQ se vuoi puoi contattarmi tramite skype cercando xxxxxxxx (in italia sono l'unico... anche in europa ma... meglio se cerchi italia :-) ) o su msn xxxxxxxxxxxxxxxx (togli questa parte di messaggio quando moderi il commento plz)
  9. Ciao, intanto rispondo pubblicamente a beneficio di tutti, ti contatto domani via skype.

    Puoi prevnetivamente eseguire queste cinque/sei queries?

    SHOW STATUS;
    SHOW VARIABLES LIKE 'query%';
    SHOW VARIABLES LIKE 'max_join_size';
    SHOW SESSION VARIABLES LIKE 'max_join_size';
    SHOW VARIABLES LIKE '%size%';
    SHOW GLOBAL VARIABLES LIKE '%size%';

Aggiungi Commento


Standard emoticons like :-) and ;-) are converted to images.


I commenti sottoposti sono soggetti a moderazione prima di essere mostrati.