htop mostra 4 processi mysql che stanno utilizzando il 100% del
processore ciascuno.
Questo che significa? Che avrei bisogno del 400% per farli funzionare
bene? Ovvero 4 volte la potenza attuale?
La macchina non è scarsa: quadcore xeon 2.50 con 2GB di ram (la
portiamo a 4 o 8 a giorni) e dischi SAS da 15k RPM in RAID 1 hardware.
Escludo quindi che i dischi siano il collo di bottiglia anche se mysql
mi indica molte tabelle lette e scritte su disco.
Aumentare la RAM può portare ad un miglioramento in questi casi? E
mettere il secondo quadcore?
> Questo che significa? Che avrei bisogno del 400% per farli funzionare
> bene? Ovvero 4 volte la potenza attuale?
potresti solo aver bisogno di migliorare gli indici del db. potrebbe
anche essere un indice rovinato. prova (per test) a fare un dump della
tabella, cancellarla e rimetterla dentro.
dipende anche da che tipo di operazioni fai sulla tabella. un update è
diverso da un select e da un delete, ovviamente.
>
> La macchina non è scarsa: quadcore xeon 2.50 con 2GB di ram (la
> portiamo a 4 o 8 a giorni) e dischi SAS da 15k RPM in RAID 1 hardware.
che non sia scarsa dipende da quel'è il carico.
Potrebbe essere, anche, un db non correttamente progettato, oppure query
non ottimizzate.
Per quanto un dbms possa cercare di ottimizzare, non può fare miracoli.
Mi spiego meglio (mi scuso con Alessandro se dico cose che magari sa
già, ma voglio essere chiaro).
Una scelta non corretta delle chiavi primarie, la totale mancanza di
chiavi primarie, l'abuso di indici o, anche qui, la mancanza o la
scorretta identificazione degli indici può portare ad effetti di
rallentamento visibili solamente con grosse moli di dati.
Anche una progettazione delle tabelle (intesa come divisione dei dati)
non ottimale può portare a rallentamenti notevoli solamente quando il db
inizia ad avere dimensioni considerevoli. (la quantificazione di
"considerevole" varia molto da situazione a situazione)
Guardando, invece, solo alle query di selezione (quelle di insert,
update e delete sono maggiormente influenzate dalla presenza e dalla
quantità di indici, e dalla concorrenza), il discorso si fa anche più
spinoso.
L'ottimizzatore delle query del dbms fa il suo lavoro, ma non prende
iniziative proprie (per fortuna), quindi la stessa query sugli stessi
dati può essere riscritta in decine di modi differenti, e può impiegare
da meno di 1 millisecondo (è maggiore il tempo di invocazione) a più di
30 secondi o anche minuti.
L'uso di subquery è da valutare attentamente. A volte sono utili e
necessarie, ma sarebbe meglio evitarle. Il prodotto cartesiano tra
tabelle non sempre è risolvibile in una join dall'ottimizzatore. Meglio
specificare la join in modo esplicito creando possibilmente dei percorsi
in cui si minimizza il numero di incroci che il dbms deve fare. E meglio
se le join possono essere risolte come "hash join" o "index scan" invece
che come "table scan", poiché la complessità scenderebbe a O(log(n))
invece che O(n).
Ultima cosa a cui bisogna stare molto attenti sono le funzioni di
aggregazione (sum, average, max, min, ecc) o le chiamate a funzioni
(come la concat) in modo che siano fatte al più alto livello possibile.
Questo perché ogni join risulta in una nuova tabella. Se durante la join
l'ottimizzatore riesce a mantenere i riferimenti ai vari indici, può
lavorare molto più velocemente.
Tutto questo sui più riassumere in "aiuta l'ottimizzatore e
l'ottimizzatore aiuterà te".
Per la mia esperienza, la maggior parte delle volte il problema è
imputabile a questi 2 punti.
PSK
Purtroppo sia database che database sono 'offlimits' fan parte di un
applicativo proprietario
che non posso modificare. Il cliente mi sta solo segnalando (ed io
posso confermarlo) che
l'accesso ai siti che fanno uso di database mysql su quella precisa
macchina è veramente lento.
Talmente lento che a volte da timeout il browser.
Stoppando/riavviando mysql ovviamente cadono tutte le connessioni,
l'uso del processore scende a volori inferiori
al 10% (per ogni processore) ed i siti tornano a volare.
Niente niente che aumenta il traffico ed il server collassa.
Non sono sicuro che l'aumento della ram e/o del processore possa
portare ad un miglioramento.
E poi, vedere 3 o 4 processi mysql con ciascuno il 100% di utilizzo
della CPU che significa? Non so
interpretare il dato...
Il cliente mi ha segnalato che lo stesso script con lo stesso database
viene utilizzato anche da un sito
che ha milioni di impression al giorno.
Loro come fanno? Useranno sicuramente un cluster.
Allora domanda di riserva: per un cluster mysql active/active (MySQL
Cluster ndb mi pare) bisogna mettere
mano allo script? Come dico al PHP di bilanciare le richieste su
entrambi i nodi? Mi viene generato un IP 'virtuale'
in stile VRRP ? (Lo so, mi vado a documentare)
Sono 55% select e 45% insert.
Non avendo accesso allo script (vedi mia risposta a luca) non saprei
nemmeno tecnicamente
come splittare il traffico su più server. Se potessi modificare lo
script potrei fare un cluster dove il master fa solo insert e lo slave
fa solo select.
Qualcosa dovrei risolvere. Ma senza modificare lo script come faccio?
> che non sia scarsa dipende da quel'è il carico.
In effetti hai ragione anche te :)
> Aumentare la RAM pu� portare ad un miglioramento in questi casi? E
> mettere il secondo quadcore?
Ricordo di aver letto il thread, ma non ricordo com'era finito. Prima di
fare modifiche hardware, hai la possibilita` di montare un ambiente
simile a quello di produzione e di generare la stessa mole di traffico?
Enrico
> Ricordo di aver letto il thread, ma non ricordo com'era finito.
Era finito che avevo spostato il database su una macchina dedicata.
Ha retto per tutta la settimana di ferragosto fino ad oggi,
probabilmente perchè era diminuito il traffico
poi oggi è collassato nonostante la macchina fosse sensibilmente più
potente rispetto a quella dove
era prima (oltre che adesso è una macchina dedicata)
> Prima di
> fare modifiche hardware, hai la possibilita` di montare un ambiente
> simile a quello di produzione e di generare la stessa mole di traffico?
>
> Enrico
Purtroppo no...
Altrimenti sarei già su quella strada.
Dovrei avere un server simile (dischi SAS da 15k, quadcore 2.50, perc
5) inutilizzato e
comunque non riuscirei a generare un traffico simile. Ne fa veramente
tantissimo.
> Loro come fanno? Useranno sicuramente un cluster.
> Allora domanda di riserva: per un cluster mysql active/active (MySQL
> Cluster ndb mi pare) bisogna mettere
> mano allo script? Come dico al PHP di bilanciare le richieste su
> entrambi i nodi? Mi viene generato un IP 'virtuale'
> in stile VRRP ? (Lo so, mi vado a documentare)
http://forge.mysql.com/wiki/MySQL_Proxy
http://blog.kovyrin.net/mysql-master-master-replication-manager/
ma prima di andare lě farei un semplice esame di:
mysql slow queries, controlla dove le salva e vedi un po' chi č che piega la
macchina.
ultima cosa devi anche partire dal presupposto che my.cnf di default non č
ottimizzato praticamente per NULLA.
quindi dovresti adeguare bene anche le dimensioni degli indici etc etc.
ciao
--
Well everybody's heard about the bird
Bird is the word
Don't you know about the bird well everybody knows that the bird is a word
Bird is a word
> Era finito che avevo spostato il database su una macchina dedicata.
Ok, direi che puoi gia` escludere un problema hardware. A questo punto,
provato a fare il tuning di MySQL a livello di indici, statistiche (se
le ha), memoria utilizzata?
Enrico
P.S. la generazione del traffico e` una cosa semplice: se il db riceve
1000 query al secondo, basta che crei uno script (in bash, perl,
python...) che faccia almeno 500 query contemporanee e lo metti su due
macchine
> Enrico
> P.S. la generazione del traffico e` una cosa semplice: se il db riceve
> 1000 query al secondo, basta che crei uno script (in bash, perl,
> python...) che faccia almeno 500 query contemporanee e lo metti su due
> macchine
a patto che l'hash delle query non sia sempre identico. altrimenti
interviene la cache.
Si, ho fatto girare mysqltuner per qualche volta ed ho sistemato.
Ho alzato paurosamente i valori delle cache, ma non è cambiato niente.
Sono tantissime query microscopiche, insert di un singoo record,
select di un singolo record e così via.
Sono contatori di accessi quindi niente elaborazioni complicate.
Solo che ne esegue una quantità sproporzionata, ogni visita al sito fa
almeno 3 query...
Ho notato che salva e legge molte tabelle temporanee su disco,
nonostante gli abbia detto di usare ben 384MB di ram per le
temporanee.
Chi diavolo è che occupa più di 384mb dato che il database è 500k ?
> Enrico
> P.S. la generazione del traffico e` una cosa semplice: se il db riceve
> 1000 query al secondo, basta che crei uno script (in bash, perl,
> python...) che faccia almeno 500 query contemporanee e lo metti su due
> macchine
Proverò...
Ma sono insert, quelle non si cachano ne ottimizzano credo.
Be ma allora meglio usare MySQL-Cluster di mysql che fa girare tutto
in ram. Più veloce di così...Il database è 500K
> ma prima di andare lì farei un semplice esame di:
>
> mysql slow queries, controlla dove le salva e vedi un po' chi è che piega la
> macchina.
0 slow queries.
Cosa vuol dire controlla dove le salva?
E se facessi un ramdisk da 1GB e facessi salvare le tabelle temporanee
nel ramdisk? Dovrei bypassare il disco definitivamente...
> ultima cosa devi anche partire dal presupposto che my.cnf di default non è
> ottimizzato praticamente per NULLA.
> quindi dovresti adeguare bene anche le dimensioni degli indici etc etc.
Ho già pompato la configurazione, ma essendo una quantità
sproporzionata di INSERT (la metà delle query, un buon 45-50%) quelle
dubito si riescano ad ottimizzare...
Secondo me il problema è appunto nelle insert,
controlla che tipo di tabella è quella dove viene fatta la insert,
se è una MyIsam sei "fregato" perchè ad ogni insert MySQL ti fa il lock
della tabella e quindi mette in coda tutte le altre .. questo su grandi
numeri ti blocca tutto.
Se fosse così, senza modificare lo script, potresti farti un backup del
DB e passare quelle tabelle a MyISAM dove l'insert fa il lock del
singolo record.
Puoi controllare il lock delle tabelle con
mysqladmin processlist
-- Yena --
Uhm... MyIsam != MyISAM ? :-P
Oppure è vero e io ancora non so che mysql è case sensitive?
Enrico
> On 28 Ago, 00:30, "motion musso: aka sathia" <sathia.mu...@libero.it>
> wrote:
>
>>
http://forge.mysql.com/wiki/MySQL_Proxyhttp://blog.kovyrin.net/mysql-master-master-replication-manager/
>
> Be ma allora meglio usare MySQL-Cluster di mysql che fa girare tutto
> in ram. Più veloce di così...Il database è 500K
>
>
>> ma prima di andare lì farei un semplice esame di:
>>
>> mysql slow queries, controlla dove le salva e vedi un po' chi è che piega
>> la macchina.
>
> 0 slow queries.
> Cosa vuol dire controlla dove le salva?
c'è un file di log generalmente mysql_slow_queries.log o qualcosa
> E se facessi un ramdisk da 1GB e facessi salvare le tabelle temporanee
> nel ramdisk? Dovrei bypassare il disco definitivamente...
potresti poi essere corto di CPU!
>
>> ultima cosa devi anche partire dal presupposto che my.cnf di default non
>> è ottimizzato praticamente per NULLA.
>> quindi dovresti adeguare bene anche le dimensioni degli indici etc etc.
>
> Ho già pompato la configurazione, ma essendo una quantità
> sproporzionata di INSERT (la metà delle query, un buon 45-50%) quelle
> dubito si riescano ad ottimizzare...
ma stai usandi mint?
sembra da quel che dici che stai usando un programma di statistiche che si
chiama mint.
se è quello mettiti il cuore in pace, non è fatto per reggere sopra i
200/300 utenti contemporanei.
Non ho slow queries...
> ma stai usandi mint?
> sembra da quel che dici che stai usando un programma di statistiche che si
> chiama mint.
> se è quello mettiti il cuore in pace, non è fatto per reggere sopra i
> 200/300 utenti contemporanei.
No, niente mint.
E' un altro 'affare' che serve a fare circuiti payperclick e che non
posso modificare.
Si è MyISAM, forse intendevi passare a InnoDB? Hai scritto MyISAM due
volte :)
mysqladmin processlist in che modo mi mostra le tabelle loccate scusa?
> On 28 Ago, 13:30, "motion musso: aka sathia"
>> c'è un file di log generalmente mysql_slow_queries.log o qualcosa
>
> Non ho slow queries...
>
nel tuo my.cnf
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
questi sono i miei parametri, però prendili proprio come un esempio
key_buffer = 256M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 128
max_connections = 500
table_cache = 1024
#thread_concurrency = 10
query_cache_limit = 2M
query_cache_size = 128M
prova a cambiare le tabelle in InnoDB (occhio che potrebbe bloccare il sito
per diversi minuti se il db è pesante)
innodb_log_file_size=64M
innodb_buffer_pool_size=4G
# 4G sono la metà della ram del mio server mysql master
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
> mysqladmin processlist in che modo mi mostra le tabelle loccate scusa?
lancia questa query da shell mysql:
SHOW PROCESSLIST;
> lancia questa query da shell mysql:
>
> SHOW PROCESSLIST;
Vedo un sacco di select, e parecchie insert, ma dove vedo se le query
sono in coda a causa di una tabella loccata?
ti dice da quanti secondi stanno girando. se una query dura più di un
secondo per me è considerata lenta.
quindi questo è il punto, modifica come ti ho detto sotto il my.cnf e vedi
bene qual è la query che più di tutto ti uccide il server.
considera di passare a InnoDB, che grazie al cielo ha un lock discreto e non
della table intera.
> ti dice da quanti secondi stanno girando. se una query dura più di un
> secondo per me è considerata lenta.
> quindi questo è il punto, modifica come ti ho detto sotto il my.cnf e vedi
> bene qual è la query che più di tutto ti uccide il server.
La media è sempre sui 0 secondi.
Raramente vedo query che girano più di un secondo.
Ho parlato con il proprietario del server. Si parla di 60milioni di
impression al mese.
E nel prossimo mese prevedono di quadrupricarlo quindi io vedo sono
una soluzione:
cluster MySQL-Cluster (che gira tutto in ram) su due macchine
biprocessore quadcore, dischi SAS da 15k RPM e 8GB di ram per ciascuna
macchina.
Il database è 632K (kilobyte no megabyte) e forse potrei fare con 4GB
di ram...
Premesso che mi informerò a dovere: in un cluster del genere, basta
aggiugere macchine per aumentare la potenza? Inoltre, il manager del
cluster, serve solo ad avviare il sistema? Posso installarlo sul
server web...
> considera di passare a InnoDB, che grazie al cielo ha un lock discreto e non
> della table intera.
Proverò, ma la tabella ha talmente tanti di quei record che fare un
dump/insert è un lavorone.
> La media è sempre sui 0 secondi.
Questo significa che il tuo è un problema non è causato da query troppo lente,
ma è meglio indagare nella direzione del lock sulle tabelle o dell I/O su disco.
> Il database è 632K (kilobyte no megabyte)
> Proverò, ma la tabella ha talmente tanti di quei record che fare un
> dump/insert è un lavorone.
Queste ultime due affermazioni mi sembrano un pò contrastanti, per far diventare
"un lavorone" un operazione di dump bisogna avere qualche centinaio di migliaia
di record e mi sembra difficile che un db con elevato numero di record possa
pesare meno di un mega, sei sicuro delle dimensioni che hai indicato? come le
hai verficate?
L'I/O su disco, come vedo se sta collassando? Comunque sono dischi SAS
da 15k RPM, non robetta SATA. Si sta parlando di insert/select di
quantità microscopiche di dati, ad esempio qualcosa tipo:
INSERT INTO xxx SET ip = '1.2.3.4', id='2222'
e
SELECT banner FROM banner WHERE campagna = '3456' LIMIT 1
> Queste ultime due affermazioni mi sembrano un pò contrastanti, per far diventare
> "un lavorone" un operazione di dump bisogna avere qualche centinaio di migliaia
> di record e mi sembra difficile che un db con elevato numero di record possa
> pesare meno di un mega, sei sicuro delle dimensioni che hai indicato? come le
> hai verficate?
# du -sh xxx/
632K xxx/
Per i motivi di cui sopra, il database è ZEPPO di dati, ma sono
semplicemente numeri o robetta piccola.
> considera di passare a InnoDB, che grazie al cielo ha un lock discreto e non
> della table intera.
Ho verificato ora, le tabelle sono già tutte InnoDB
> ti dice da quanti secondi stanno girando. se una query dura più di un
> secondo per me è considerata lenta.
> quindi questo è il punto, modifica come ti ho detto sotto il my.cnf e vedi
> bene qual è la query che più di tutto ti uccide il server.
Allora verificando meglio nel log, le query lente (e ce ne sono) sono
tutte SELECT.
Tutte select sulla tabella più grossa del database che contiene
pressapoco 1.500.000 di record.
Essendo tutte select, dovrei risolvere facendo una semplice replica
MASTER - SLAVE e proxando la richieste con MySQL Proxy. Così facendo
potrei splittare le select su entrambi i server e le scritture solo
sul master.
> Questo significa che il tuo è un problema non è causato da query troppo lente,
> ma è meglio indagare nella direzione del lock sulle tabelle o dell I/O su disco.
Guarda, ho verificato meglio.
Ho una marea di query lente, oltre 88 secondi.
Ma proprio tante.
Questo è la parte finale di SHOW INNODB STATUS:
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
6828 OS file reads, 10046 OS file writes, 8085 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 11.99 writes/s, 11.99 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 50, seg size 52,
685 inserts, 3163 merged recs, 510 merges
Hash table size 1106407, used cells 918508, node heap has 2894
buffer(s)
324252.75 hash searches/s, 39194.81 non-hash searches/s
---
LOG
---
Log sequence number 1 2724960302
Log flushed up to 1 2724960302
Last checkpoint at 1 2724937845
0 pending log writes, 0 pending chkp writes
7956 log i/o's done, 11.99 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 610050384; in additional pool allocated 1048576
Buffer pool size 32768
Free buffers 12240
Database pages 17634
Modified db pages 58
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 17619, created 15, written 2580
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 222 queries in queue
223 read views open inside InnoDB
Main thread process no. 5150, id 1140881760, state: sleeping
Number of rows inserted 2734, updated 1545, deleted 0, read 365512676
4.00 inserts/s, 2.00 updates/s, 0.00 deletes/s, 362575.42 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
Che diavolo significa in particolare l'ultima parte?
Ho 222 queries in coda?
E perchè sono in coda? Perchè ci sono INSERT che fanno il lock della
tabella?
L'ultima riga dice:
4.00 inserts/s, 2.00 updates/s, 0.00 deletes/s, 362575.42 reads/s
Questo significa, forse, che ho 362575.42 letture al secondo e 4
insert al secondo.
Be, dovrei cavarmela con una replica MASTER-SLAVE dirottando le
letture su entrambe le macchine (MASTER e SLAVE) e le scritture solo
sulla master.
Io dubito che questo singolo server, anche mettendo un secondo
processore e bombardandolo di ram riesca a reggere. Per me ci vuole o
un cluster o una replica.
Voi cosa consigliate?
Se, come hai detto nell'altro post, le tabelle sono InnoDB i dati non
sono nella directory xxx ma nei datafiles di innodb.
Quindi, il database sara' sicuramente molto piu' grosso dei 632k
riportati dal du.
Se hai phpmyadmin le dimensioni delle tabelle le vedi dalla struttura
del database, altrimenti prova con SHOW TABLE STATUS, una delle colonne
e' la grandezza in bytes delle colonne.
> On 28 Ago, 17:48, "ErConte" <21217inva...@mynewsgate.net> wrote:
>> Questo significa che il tuo è un problema non è causato da query troppo
>> lente, ma è meglio indagare nella direzione del lock sulle tabelle o dell
>> I/O su disco.
>
> L'I/O su disco, come vedo se sta collassando? Comunque sono dischi SAS
> da 15k RPM, non robetta SATA. Si sta parlando di insert/select di
> quantità microscopiche di dati, ad esempio qualcosa tipo:
man vmstat
vmstat 5
>
> INSERT INTO xxx SET ip = '1.2.3.4', id='2222'
> e
non importa che siano piccole, se ip è un indice e id anche etc etc può
diventare lento, al contrario
> SELECT banner FROM banner WHERE campagna = '3456' LIMIT 1
>
se campagna NON è un indice può andare lento.
>> Queste ultime due affermazioni mi sembrano un pò contrastanti, per far
>> diventare "un lavorone" un operazione di dump bisogna avere qualche
>> centinaio di migliaia di record e mi sembra difficile che un db con
>> elevato numero di record possa pesare meno di un mega, sei sicuro delle
>> dimensioni che hai indicato? come le hai verficate?
>
> # du -sh xxx/
> 632K xxx/
./ibdataX cosa dice
>
> Per i motivi di cui sopra, il database è ZEPPO di dati, ma sono
> semplicemente numeri o robetta piccola.
cmq devi capire come mai le insert sono lente ho visto che hai iniziato a
analizzare le query sei sulla strada giusta.
però attenzione
passare tutte le SELECT a uno slave non è detto che sia la soluzione
migliore. specie se dopo qualche minuto collezioni un lag di 10,000 righe
l'importante è capire cosa è che locca il server, CPU? HD?
fai un extract delle quey che stanno appese 88 secondi e rimandale al server
con davanti EXPLAIN SELECT [...]
e incolla il risultato
ciao
> Se, come hai detto nell'altro post, le tabelle sono InnoDB i dati non
> sono nella directory xxx ma nei datafiles di innodb.
>
> Quindi, il database sara' sicuramente molto piu' grosso dei 632k
> riportati dal du.
Hai ragione, si vede che non sono pratico di InnoDB :)
Il database è 297,1 MiB
Ora non posso farlo, non ho la vpn qui...
> > INSERT INTO xxx SET ip = '1.2.3.4', id='2222'
> > e
>
> non importa che siano piccole, se ip è un indice e id anche etc etc può
> diventare lento, al contrario
>
> > SELECT banner FROM banner WHERE campagna = '3456' LIMIT 1
>
> se campagna NON è un indice può andare lento.
E si, ci sono molti indici di mezzo, sia in scrittura che in lettura.
> ./ibdataX cosa dice
Come detto a Daniele Paoni, il database è 297,1 MiB quindi un tantino
più grosso dei miei 632 K ! :)
Però ho sufficientemente ram per farlo girare tutto in memora, oggi ho
aggiunto altri 2 GB (in totale 4GB)
ma non li ha neanche sentiti. Domani provo a passare a 6GB (se riesco,
perchè ho molti banchi occupati) ed
a mettere il secondo processore quadcore 2.50 visto che ne ho uno
disponibile.
E poi vediamo.
La macchina non è espandibile come disco, e se nemmeno aggiungendo ram
e processore la situazione non migliora
verso quali lidi posso andare a parare? Il cliente è disposto a
spendere, purchè abbia una soluzione veloce e scalabile.
> cmq devi capire come mai le insert sono lente ho visto che hai iniziato a
> analizzare le query sei sulla strada giusta.
Probabilmente perchè ci sono molti indici di mezzo...
> però attenzione
> passare tutte le SELECT a uno slave non è detto che sia la soluzione
> migliore. specie se dopo qualche minuto collezioni un lag di 10,000 righe
Cosa consigli? Come detto sopra, il cliente è disposto a spendere.
E' chiaro che non è disposto a spendere soldi per prendere un secondo
processore e ad aggiungere ram
se poi la situazione non migliora sensibilmente. In questo momento i
siti vanno addirittura in timeout...
Più che aggiungere ram e processore non saprei che altro fare senza
scomodare un bel clusterone active/active ed in bilanciamento.
> l'importante è capire cosa è che locca il server, CPU? HD?
> fai un extract delle quey che stanno appese 88 secondi e rimandale al server
> con davanti EXPLAIN SELECT [...]
>
> e incolla il risultato
Considerata la lentazza esasperante del server, mediante phpmyadmin ho
preso una query a caso in attesa da 88 secondi ed eccola qui:
EXPLAIN
SELECT ID
FROM XXX_IMPRESSIONS
WHERE XXX_ID = '1054'
AND IP = '79.32.28.241'
AND XXX_TIME >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
La query non è perfettamente uguale a quelle da 80s perchè da dove
sono ora non ho modo di vedere i processi al completo, comunque sia i
campi sono quelli ed anche la WHERE per cui il risultato non cambia.
id select_type table type possible_keys key key_len ref rows
Extra
1 SIMPLE XXX_IMPRESSIONS ref IndexXXX_TIME,IndexXXX_ID
IndexXXX_ID 4 const 559782 Using where
> Allora verificando meglio nel log, le query lente (e ce ne sono) sono
> tutte SELECT.
Delle query lente, hai modo di vedere un explain plan? Secondo me c'e`
un uso sconsiderato degli indici (sempre se ci sono)
Enrico
> Delle query lente, hai modo di vedere un explain plan? Secondo me c'e`
> un uso sconsiderato degli indici (sempre se ci sono)
L'ho appena mandato....
> Che diavolo significa in particolare l'ultima parte?
> Ho 222 queries in coda?
Sì
> E perchè sono in coda? Perchè ci sono INSERT che fanno il lock della
> tabella?
Ancora Sì
>
> L'ultima riga dice:
> 4.00 inserts/s, 2.00 updates/s, 0.00 deletes/s, 362575.42 reads/s
>
> Questo significa, forse, che ho 362575.42 letture al secondo e 4
> insert al secondo.
Anche quì la risposta è sì
> Be, dovrei cavarmela con una replica MASTER-SLAVE dirottando le
> letture su entrambe le macchine (MASTER e SLAVE) e le scritture solo
> sulla master.
>
> Io dubito che questo singolo server, anche mettendo un secondo
> processore e bombardandolo di ram riesca a reggere. Per me ci vuole o
> un cluster o una replica.
>
> Voi cosa consigliate?
>
Personalmente ti consiglio prima di implementare architetture più esoteriche
di cercare quanto più possibile di ottimizzare quello che hai.
La tua macchina è ben carrozzata e mysql è perfettamente in grado di servire
un numero di richieste ben maggiore di quelle che hai Tu su una macchina del
genere (noi abbiamo ca. 80k insert/update/select al secondo ripartite
10%/80%/10% su un DL360 G4 senza che la macchina segni mai il passo)
Una delle query che hai analizzato prima faceva delle differenze fra date
all'interno della select, quelle sono operazioni particolarmente pesanti,
modificare la query sarebbe sicuramente auspicabile, ma se, come hai già detto
più volte, non hai modo di far modificare le query allora devi cercare di
renderle il meno possibile dannose, individuando i campi giusti da indicizzare
e verificando ancora le opzioni del tuo X.cnf, ad esempio, magari dico una
banalità, ma non è che tante volte ti ritrovi il querylog abilitato? perché in
questo caso il db stà generando un log per ogni singola query che viene
eseguita, e capisci da solo che questo è un problema. Poi se non ricordo male,
il motore innoDB consente a MySQL l'uso delle transazioni, ma se non ti
servono puoi anche disabilitarle che sicuramente anche questo fa rcuperare
qualcosa in termini di performance.
Ho paura che siano solo dei piccoli pagliativi.
I log delle query non sono attivi, fa tanto di quel traffico il server
che se attivo
persino quelli mi crolla tutto.
I siti sono completamente fermi, quindi non devo ottimizzare un pelo
qui o un pelo
li, devo trovare una soluzione radicale.
Lo script potrei modificarlo, ma non è fatto da me e non so come
funziona, quindi ci
perderei molto tempo. Dovrei scrivere al programmatore originale e non
quanto abbia
voglia di implementare queste modifiche ad-hoc per noi su un programma
che lui vende.
Stessa cosa dicasi per il database, in teoria sarebbe offlimits anche
quello...
Il punto è: modificando script e database, risolvo la situazione o
potrei solo migliorarla un pelo?
Nel primo caso, non ci sono problemi, modifico tutto. Nel secondo no,
tra i due mali scelgo il male minore: creare un cluster
che mi permetterà una espansione futura senza perdere 1 settimana a
mettere mano ad uno script altrui che non conosco.
Tu quanti dati hai nel db?
Un db piccolo è in grado di gestire mooolte più query rispetto ad uno
da 300MB.
Comunque sia, mettendo un secondo processore, pensi che riesca ad
avere un po di respiro e ragionare con più calma?
Attualmente htop mi dice che due core sono occupati, di media, al 100%
(da mysql vedo) e gli altri due, sempre in media, al 85-95%.
Per me aggiungere un processore dovrebbe darmi respiro. 4GB di ram
dovrebbero essere abbastanza per un DB da 300MB.....
> Come detto a Daniele Paoni, il database č 297,1 MiB quindi un tantino
> piů grosso dei miei 632 K ! :)
>
il database č piccolo, 300 mega sono proprio pochini.
> Perň ho sufficientemente ram per farlo girare tutto in memora, oggi ho
> aggiunto altri 2 GB (in totale 4GB)
la ram non fa mai male, ma con un db cosě piccolo direi che l'ultima delle
cose da fare č aggiungere carrozzeria a una macchina che č giŕ super
performante.
> ma non li ha neanche sentiti. Domani provo a passare a 6GB (se riesco,
> perchč ho molti banchi occupati) ed
> a mettere il secondo processore quadcore 2.50 visto che ne ho uno
> disponibile.
dubito che possa cambiare qualcosa.
mysql NON č particolarmente ottimizzato per multi core processing.
>
> E poi vediamo.
> La macchina non č espandibile come disco, e se nemmeno aggiungendo ram
> e processore la situazione non migliora
> verso quali lidi posso andare a parare? Il cliente č disposto a
> spendere, purchč abbia una soluzione veloce e scalabile.
in questo caso particolare mi azzardo a dire che il problema sta nel
software, quando ho avuto questi problemi io mi sono rivolto a
www.percona.com e le loro consulenze sono tutt'ora preziose. perň a quanto
pare il software č una scatola chiusa quindi difficilmente modificabile,
quindi banalmente sei arrivato al limite dell'accrocchio.
comunque a questo punto mi affiderei alla loro consulenza sono gli stessi di
http://www.mysqlperformanceblog.com/ sicuramente sono in grado dirti con
esattezza cosa fare o cosa dovresti fare. č un consiglio spassionato non ho
nessun interesse economico nel consigliarteli.
>> cmq devi capire come mai le insert sono lente ho visto che hai iniziato a
>> analizzare le query sei sulla strada giusta.
>
> Probabilmente perchč ci sono molti indici di mezzo...
>
>> perň attenzione
>> passare tutte le SELECT a uno slave non č detto che sia la soluzione
>> migliore. specie se dopo qualche minuto collezioni un lag di 10,000 righe
>
> Cosa consigli? Come detto sopra, il cliente č disposto a spendere.
> E' chiaro che non č disposto a spendere soldi per prendere un secondo
> processore e ad aggiungere ram
> se poi la situazione non migliora sensibilmente. In questo momento i
> siti vanno addirittura in timeout...
mysql č potente, ma il suo ottimizzatore non puň fare miracoli. se le query
sono fatte bene č in grado tranquillamente di servire migliaia di utenti in
contemporanea, ma qui la struttura č sicuramente sballata.
Tecnicamente posso mettere mano sia al codice ed al db, ma non
avendolo fatto io
impiegherei del tempo ed ho bisogno che dopo aver perso 1 settimana a
capire come
funziona questo aggeggio la soluzione adottata sia definitiva.
Il cliente prevede di quintuplicare il traffico nel giro di un mese.
Ha già accordi, sta solo
aspettando me perchè il server non gli regge.
Attualmente fa 60 milioni di impression mensili, ogni impression sono
almeno 2 query (forse 3)
da quanto mi pare di capire e quindi se lo quintuplica le impression
diventano 300milioni...
Va da se che questo non è traffico pesante, ma solo bannerini da pochi
K, questo spiega come
mai il server web non abbia particolari problemi (anche se li avesse:
memcached o simili e/o reverse proxy)
ma li ha solo il database perchè deve leggere quale banner caricare
facendo computazioni e poi deve
aggiornare click e visualizzazioni...
Ripeto sempre: non vorrei perdere una settimana per avere una
soluzione che tra 1 mese sarà sicuramente da cambiare.
Certo, ottimizzare lo script non fa mai male, ma se poi devo comunque
andare verso un cluster (spero di no, ma centinaia di milioni di
impression
come diavolo li gestite se non con un clusterone?!?!) preferisco fare
un cluster google-like ovvero tante macchine poco potenti (amd opteron
della dell che ad esempio costano circa 600euro) anzichè investire in
hardware da oltre 2mila euro.
Sempre che l'ottimizzazione dello script non mi permetta di scalare
ALMENO a 6-7 volte il traffico attuale, ma ne dubito....
qui sembra che XXX_TIME non viene proprio considerato come chiave, lo č?
in tal caso avresti una scrematura su XXX_ID e IP molto veloce
ma poi un filesort (su tabella temporanea!) su XXX_TIME.
prima cosa da verificare č provare la query senza
> AND XXX_TIME >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
a bocce ferme e vedere quanto ci mette.
se č molto poco _dovrebbe_ bastare dare un hint a mysql dicendogli usare
anche la tabella XXX_TIME come chiave (se non sbaglio con la versione 5.1
mysql riesce a usare piů chiavi su una select, mentre prima era limitato a
una chiave per select).
ultima cosa, che versione di mysql hai installato?
dall'explain direi la 5.1 ma se non č cosě aggiorna all'ultima versione.
Be, l'indice è presente, ma a quanto pare il server non lo vuole
usare.
Si può forzare? Ma se lui non lo usa, forzandolo non si rischia di
peggiorare?
MySQL dovrebbe valutare da solo le chiavi migliori da usare..
> prima cosa da verificare è provare la query senza
>
> > AND XXX_TIME >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
>
> a bocce ferme e vedere quanto ci mette.
> se è molto poco _dovrebbe_ bastare dare un hint a mysql dicendogli usare
> anche la tabella XXX_TIME come chiave (se non sbaglio con la versione 5.1
> mysql riesce a usare più chiavi su una select, mentre prima era limitato a
> una chiave per select).
Con il time ci mette: 34.7507 sec
Senza: 26.2351 sec
Penso però sia dovuto da un momentaneo calo di traffico.
> ultima cosa, che versione di mysql hai installato?
> dall'explain direi la 5.1 ma se non è così aggiorna all'ultima versione.
5.0.32-Debian_7etch6-log
La 5.1 non mi pare ci sia su etch e no, non farmela compilare se non è
fondamentale.
La cosa buffa che sto notando adesso è che gli altri database presenti
sullo stesso server mysql collassato (load di 10-12)
vanno perfettamente, sono sensibilmente più piccoli ma non accennano
ralletamenti.
L'unica differenza è il server web su cui sono ospitati.
Altra pulce nell'orecchio è il fatto che persino la homepage di
phpmyadmin (senza nessuna query, completamente statica) è lenta.
Mi vien da pensare che anche il server web stia collassando.
Ora: un server web collassato può far collassare le query mysql di uno
specifico database?
Oppure: un database collassato, può far collassare un intero server
web? (compreso domini che non hanno niente a che vedere con il
database. Magari apache tiene le richieste in attesa e fa collassare
la macchina...)
> On 28 Ago, 23:56, "motion musso: aka sathia" <sathia.mu...@libero.it>
> wrote:
>> qui sembra che XXX_TIME non viene proprio considerato come chiave, lo è?
>> in tal caso avresti una scrematura su XXX_ID e IP molto veloce
>> ma poi un filesort (su tabella temporanea!) su XXX_TIME.
>
> Be, l'indice è presente, ma a quanto pare il server non lo vuole
> usare.
> Si può forzare? Ma se lui non lo usa, forzandolo non si rischia di
> peggiorare?
peggio di così!
> MySQL dovrebbe valutare da solo le chiavi migliori da usare..
>
sì, ma non avviene sempre, a volte devi dirgli tu cosa è meglio.
http://mysql2.mirrors-r-us.net/doc/refman/5.0/en/index-hints.html
>> prima cosa da verificare è provare la query senza
>>
>> > AND XXX_TIME >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
>>
>> a bocce ferme e vedere quanto ci mette.
>> se è molto poco _dovrebbe_ bastare dare un hint a mysql dicendogli usare
>> anche la tabella XXX_TIME come chiave (se non sbaglio con la versione 5.1
>> mysql riesce a usare più chiavi su una select, mentre prima era limitato
>> a una chiave per select).
>
> Con il time ci mette: 34.7507 sec
> Senza: 26.2351 sec
quindi già ti sta dicendo che il problema è a monte, quei due indici non
funzionano a dovere, prova con una where sola e vedi qual è che rallenta
bestialmente.
dovresti però provare col server web spento o su una macchina offline.
>
> Penso però sia dovuto da un momentaneo calo di traffico.
>
>> ultima cosa, che versione di mysql hai installato?
>> dall'explain direi la 5.1 ma se non è così aggiorna all'ultima versione.
>
> 5.0.32-Debian_7etch6-log
>
sì, ho detto una boiata, mi sono confuso. va bene la tua versione. io ho
# Server version: 5.0.51a-7.dotdeb.1-log
> La 5.1 non mi pare ci sia su etch e no, non farmela compilare se non è
> fondamentale.
>
> La cosa buffa che sto notando adesso è che gli altri database presenti
> sullo stesso server mysql collassato (load di 10-12)
> vanno perfettamente, sono sensibilmente più piccoli ma non accennano
> ralletamenti.
> L'unica differenza è il server web su cui sono ospitati.
> Altra pulce nell'orecchio è il fatto che persino la homepage di
> phpmyadmin (senza nessuna query, completamente statica) è lenta.
> Mi vien da pensare che anche il server web stia collassando.
>
ma attualmente fai girare mysql e apache insieme?
cmq è normale, se le table sono in lock o il db è sotto stress diventa lento
addirittura nel fornire le descrizioni delle table.
> Ora: un server web collassato può far collassare le query mysql di uno
> specifico database?
difficile
> Oppure: un database collassato, può far collassare un intero server
> web? (compreso domini che non hanno niente a che vedere con il
> database. Magari apache tiene le richieste in attesa e fa collassare
> la macchina...)
probabile
> Mi vien da pensare che anche il server web stia collassando.
Probabile. Perso per perso, potresti provare a spostare anche la parte
web, in modo da capire se e` la macchina
Enrico
> Per me aggiungere un processore dovrebbe darmi respiro. 4GB di ram
> dovrebbero essere abbastanza per un DB da 300MB.....
Se ne sei convinto puoi provare, personalmente non mi convince molto
come cosa. Per quanto riguarda la RAM, l'assioma "piu` ce n'e` meglio
e`" e` e rimane sempre valido
Enrico
> Be, l'indice è presente, ma a quanto pare il server non lo vuole
> usare.
Non lo usa in quanto c'e` una funzione di mezzo, ed e` probabilmente
quello uno dei problemi che ti affliggono. PEr forzarli, penso che si
possa fare, ma non so dirti come (di tutti i RDBMS che ho usato, MySQL
e` quello che ho sempre cercato di evitare a tutti i costi)
Enrico
Ok, allora ordino 4 o 8 GB di ram.
Così su questa cosa almeno son sicuro.
Per il processore non ne son convinto nemmeno io, ma guardando htop
che mi dice che i quattro core sono usati in media al 95% mi vien da
pensare questo: aggiungendone uno, il carico che grava sul singolo
forse diminuisce. Mah.
Qualcuno mi sa dire come interpretare le % di utilizzo dei core in
htop e la % di utilizzo cpu presente nei processi?
Se due processi usano il 100% della cpu ciascuno, cosa significa? In
totale è 200%, vuol dire che mi serve il doppio del processore?
Aggiungo anche che ho un processore qui da me, quindi la prova si può
sempre fare senza troppi problemi. Giusto per vedere come si
comporta...
> quindi già ti sta dicendo che il problema è a monte, quei due indici non
> funzionano a dovere, prova con una where sola e vedi qual è che rallenta
> bestialmente.
> dovresti però provare col server web spento o su una macchina
offline.
Oggi dovrebbe venire da me il proprietario.
Gli dico che prendere in mano la situazione: gli tiro giù tutti i siti
tutta la giornata. Fare questi lavori con sotto milioni di impression
è impossibile quindi per oggi lui si scorda i siti operativi (li avrà
a spizzichi e bocconi)
Tiro giù il server web così nessuno accede al database e poi faccio
tutti i test delle query.
> ma attualmente fai girare mysql e apache insieme?
No no figurati. Apache è un server web dedicato (HP DL380, 2GB, 1x2.33
quadcore, SAS da 10k RPM).
Il cliente ha 4 server, 3 web ed 1 mysql.
Su questo mysql ci sono almeno 3 database, 1 è quello collassato, 1 è
un altro database che genera 20milioni di impression al giorno (ad
almeno il doppio o triplo di query) che gira senza indugio alcuno e
poi un altro database ancora che fa talmente poco traffico che non lo
vedo nemmeno in mytop. Farò 50.000 impression al mese.
Ora:
sul server web 1 (hp ML 350 vecchiotto, un P4) gira il sito che fa
20milioni di impression: va che è una scheggia.
sul server web 2 (come l'1) gira il sito che fa pochissimo traffico:
ovviamente vola...
sul server web 3 (DL 360 di cui sopra, quello collassato) gira il sito
che fa 60 milioni di impression più phpmyadmin più altri sitarelli.
Ecco questo server web è collassato, sia phpmyadmin, sia altri
sitarelli statici che non hanno niente a che vedere con il database.
Ad esempio un semplice sito con un logo (solo il logo nella pagina)
impiega 20-30 secondi ad aprirsi.
Ho un fiume di processi apache in esecuzione, mi pare (vado a memoria)
di aver impostato come numero MINIMO di processi da tenere liberi 50,
per far fronte ai picchi.
I processi in sleep sulla macchina sono oltre 200 ma non è un problema
di risorse (comunque gli faccio comprare ram anche qui).
Mi stavo chiedendo se un sito che accede al database collassato
potesse far collassare anche gli altri siti statici...Teoricamente si,
apache resta occupato a gestire queste milioni di richieste per 50
secondi l'una e di conseguenza fa più fatica a servire le altre.
Ho disattivato il keepalive, però avendo la macchina così imbarcata
forse attivarlo è meglio... Che dite?
Comunque sia, il web lento non mi preoccupa, si ottimizza facilmente
(butto via apache e metto lighttpd o simili, metto qualche cacher, ram
disk e così via), mi preoccupa il db.
L'importante è che non sia il web ad essere collassato e che
conseguentemente mi incarta anche il database, ma dubito, mi pare
impossibile, vedo più probabile il contrario.
Ho pensato per un momento anche alla rete sottodimensionata (100mbit)
ma poi guardando con bwm-stat ho visto che il traffico tra i due
server è di circa 2mbit quindi.....
Se il sw è proprietario, la cosa migliore è contattare i produttori di
tale sw e chiedere perché hai prestazioni da confetto Falqui e cosa fare
per avere di nuovo prestazioni degne.
Loro ti dovrebbero saper dire quali configurazioni vanno adottate per
avere il sw funzionante.
Se è veramente offlimits, non dovrebbe essere compito tuo debuggare il
loro sw (lo so, questa frase vale solo in un mondo utopico)
> Il cliente mi ha segnalato che lo stesso script con lo stesso database
> viene utilizzato anche da un sito
> che ha milioni di impression al giorno.
>
> Loro come fanno? [...]
>
Fatti dare l'url di quel sito e verifica anche tu le prestazioni, se
possibile.
La favoletta "da altre persone funziona" detta dal cliente non è sempre
vera ed affidabile.
PSK
^^^^^^
ecco, ti sei risposto da solo :)
Io gli ho detto di mandare una mail agli sviluppatori facendosi dare
qualche
consiglio, avendo fatto loro il software, magari in un secondo sanno
dirmi
come velocizzare il tutto. Io, non avendo fatto il software, dovrei
fare del
debug from-scratch e impiegherei settimane, tantè che non so cosa fare
in questo momento.
Non ho fatto io il software, non ho idea di come poterlo ottimizzare
se non prendendo
pagina per pagina php e vede cosa combina ad ogni impression...
(questo spiega perchè chiedo una soluzione magari invasiva, ma che mi
tolga sto problema
di mezzo, essendo disposto a spendere, se una soluzione con tre server
mi permette
di avere scalabilità e velocità, si prendono i tre server e chi si è
visto si è visto)
> Fatti dare l'url di quel sito e verifica anche tu le prestazioni, se
> possibile.
> La favoletta "da altre persone funziona" detta dal cliente non è sempre
> vera ed affidabile.
Ma ho verificato anche io...
Però mi sembra strano, le pagine sono CFM, lo script invece è in PHP.
O è stato cambiato, oppure gira su un dominio diverso.
Stamattina dovrebbe venire qui il cliente, gli faccio vedere il sito e
gli dico:
"Uè, guarda che il sito da 10 milioni di query al giorno non usa il
tuo script"