Newsgroup: it.comp.os.linux.sys
Titolo: MySQL - nuovamente lentissimo

Dopo un funzionamento accettabile di circa 2 settimane, un mio server
MySQL è diventato paurosamente lento.
Collassa letteralmente.
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?

 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

 non ottimizzate.
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)

 che non sia scarsa dipende da quel'è il carico.
In effetti hai ragione anche te :)

 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

 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.

 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

 macchine
a patto che l'hash delle query non sia sempre identico. altrimenti
interviene la cache.


 macchine
Proverò...

 interviene la cache.
Ma sono insert, quelle non si cachano ne ottimizzano credo.

 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...

Alessandro ha scritto:

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 --

 singolo record.
Uhm... MyIsam != MyISAM ? :-P
Oppure è vero e io ancora non so che mysql è case sensitive?
Enrico

 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.


 200/300 utenti contemporanei.
No, niente mint.
E' un altro 'affare' che serve a fare circuiti payperclick e che non
posso modificare.

 mysqladmin processlist
Si è MyISAM, forse intendevi passare a InnoDB? Hai scritto MyISAM due
volte :)
mysqladmin processlist in che modo mi mostra le tabelle loccate scusa?

 
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;


 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?

Alessandro wrote:

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.


 della table intera.
Proverò, ma la tabella ha talmente tanti di quei record che fare un
dump/insert è un lavorone.

 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?

 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.

 della table intera.
Ho verificato ora, le tabelle sono già tutte InnoDB

 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.

 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?

 semplicemente numeri o robetta piccola.
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.

 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


 riportati dal du.
Hai ragione, si vede che non sono pratico di InnoDB :)
Il database è 297,1 MiB

 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

 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

 un uso sconsiderato degli indici (sempre se ci sono)
L'ho appena mandato....

 
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.

 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.....

 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.


 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....

 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.


 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...)

 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

 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

 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

 e`" e` e rimane sempre valido
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...

 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.....

 
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

 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"

 dovresti però provare col server web spento o su una macchina offline.
Per il momento ho fatto il test con il server attivo e il WHERE che
manda in bomba tutto quanto è XXX_ID.
Senza quello guadagno 40 secondi.
Altra query piuttosto pesante è la data.
Stavo pensando: avendo al 95% sempre i soliti 4 campi che vengono
usati nella
stessa where, potrei trovare vantaggi a fare un indice con i 4 campi
insieme anzichè
quattro indici separati?
(aggiungere un indice del genere adesso su una tabella da oltre
1.500.000 record impiegherà ore)

 dovresti però provare col server web spento o su una macchina offline.
Come da tuo suggerimento ho cambiato l'indice, ho forzato l'uso di
XXX_TIME.
Da 80-85 secondi per query ora ne impiega meno di 1.
INCREDIBILE.
Adesso i siti sembrano volare.
Bisogna vedere se è in grado di reggere un traffico 6 o 7 volte
tanto...
Hai qualche idea su come generare 10-12milioni di impression al
giorno? che sarebbero
circa 148 impression al secondo....

 
La prova comunque falla, se non ti crea problemi, una cpu in + sicuramente non
gli fa male.

 circa 148 impression al secondo....
ci sono dei programmi in perl che servono per fare stress test,
altrimenti puoi provare con degli script in php ad aprire molte molte url
random, magari da diverse macchine, poi puoi loggare il quantitativo di
richieste fatte e vedere se è quello che ti serviva.


> tanto...
una cosa che potresti valutare č: 
http://www.openx.org/
da quanto ho capito quel software dovrebbe gestire degli ads.
noi abbiamo in produzione openx ed č veramente buono. 


 noi abbiamo in produzione openx ed è veramente buono.
Il software usato dal mio cliente è un software simile a AdBrite.
Si creano dei 'circuiti' e la gente può acquistare impression.
Esempio: tu hai un sito che fa 100milioni di pageview mensili.
Io e te facciamo un accordo dove tu metti un mio 'circuito' di
advertising.
Io vendo sul mio sito banner, link testuale (stile google) e robette
simili sul tuo sito.
Va da se che se il tuo sito fa 100milioni di impression, io avrò
100milioni di impression per la visualizzazione dei banner.
Il mio cliente ha contatti con alcuni portali nella top 10 dei siti
più visitati al mondo (ovviamente non posso dare ulteriori dettagli
qui) e non ha modo di attivare questi circuiti per il problema del
traffico. Per questo lui ha la certezza che in un mese porterà il
traffico attuale a 5 o 6 volte, e addirittura a 10-15 o 20 volte nei
prossimi 4-6 mesi.
Sta aspettando solo me.

 Sono contento!
Ora però ho 20-30 query in esecuzione da 15 secondi.
Sempre meglio che averne 250 in esecuzione da 90, però se si potesse
ottimizzare......
Ho forzato gli indici, ma più di così non saprei cosa si possa fare se
non modificando il database e lo script (cosa che non voglio fare per
ovvi motivi)

 ovvi motivi)
devi darlo in mano a qualcuno che č in grado di profilare il flusso di query 
e dirti cosa fare (cambia software, aggiungi slave, cambia query etc).
da qui č voodoo dirti cosa fare :)
ciao


 da qui � voodoo dirti cosa fare :)
Guarda è voodoo anche da quà.
Codice non scritto da me e database fatto da altri.
Un mistero. So solo che è lento.
Ovviamente quando diminuiscono le visite tutto va come un razzo...
Io ho paura che ci siano troppi indici, MySQL quando fa una INSERT
ritarda le select che vanno a interessare tale tabella e se la insert
impiega 2 secondi tutto si accoda, nel frattempo arriva una nuova
insert da fare e quindi i secondi diventano 4 e così via.....
Considerato che gli indici, sopratutto su tabella da oltre un milione
e mezzo di record, portano via un sacco di tempo ad essere modificati,
secondo me è questo il motivo per cui tutte le volte il server
collassa. 5 insert e 50000 select mettono in crisi tutto.
Provo a sentire se può splittare i database, magari tenere 'online'
l'archivio degli ultimi 7 giorni e tutto il resto spostarlo in un
database di servizio, magari su un altro server...
Poi lato admin, è compito di PHP prendere un po qui ed un po la....
Così facendo, secondo me si avrebbe una struttura alquanto scalabile,
basta rimuovere i record obsoleti per tenere snello il db principale.

 
E' probabile che sia così, ma per sincerartene dovresti fare una prova
"fermando" per un pò le INSERT, hai modo di farlo?
Perché se così fosse a questo punto acquisterebbe senso la soluzione da Te
ipotizzata all'inizio:
2 nodi DB master e slave, uno che fà solo select l'altro solo insert, il tutto
gestito da un nodo mysql-proxy. In questo modo, configurando opportunamente le
cose, avresti anche un db ridondato, cosa sicuramente auspicabile visto che
senza db i siti web sono off-line giusto?

 senza db i siti web sono off-line giusto?
Dei siti web mi importa poco, mi interessa UN sito, questo che blocca
tutto.
Gli altri anche se stan giù 43 giorni è uguale.
Però facendo due nodi, mi pare che qualcuno mi avesse fatto notare
(giustamente) che
se il master ha veramente un sacco di insert da fare, il problema si
replicherebbe
anche sugli slave, alla fin fine le insert si fanno anche negli slave,
solo che
anzichè essere fatte direttamente dal client sono fatte dal master.
Che cambia? Niente.
Se il master è in bomba perchè riceve troppe query dal client
(le select stanno in coda quindi non dovrebbero 'pesare'), andrà
in bomba anche lo slave perchè riceve troppe query dal master.

 in bomba anche lo slave perchè riceve troppe query dal master.
sì, conta che facebook gira su mysql, quindi non è che mysql sia
particolarmente stupido.
secondo me quel che devi fare è fare una analisi approfondita con un
professionista molto molto in gamba.


 professionista molto molto in gamba.
(non io, il cliente, fino a prova contraria questo è un problema di
ottimizzazione di script)
Poi, fatta l'analisi, io mi interfaccio con il DBA per fare le
eventuali modifiche sistemistiche richieste.

 eventuali modifiche sistemistiche richieste.
esatto


 totale è 200%, vuol dire che mi serve il doppio del processore?
Devi pensare ad un dual/quad core come ad un sistema con due o quattro
processori. In pratica, se su un dual core trovi due processi al 100%,
significa che stai sfruttando tutta la potenza dei core presenti sulla
cpu. Considera comunque una cosa fondamentale: MySQL scala di merda,
PostgreSQL da questo punto di vista e` molto piu` performante
Enrico

 insieme anzichč quattro indici separati?
Direi di si, se non fosse che per la differenza tra date usi una
funzione (e questo ti fotte l'uso dell'indice)
Enrico

 database di servizio, magari su un altro server...
Prova anche un'altra via: partiziona (realmente o per finta) la tabella
Enrico

 tutti i test delle query.
'Sta cosa pero` non mi piace. Va bene che come dici gli altri siti
generano poco traffico, ma e` vero anche che lo fanno. Di conseguenza,
generano query, occupano banda, fanno casino
Enrico

 Prova anche un'altra via: partiziona (realmente o per finta) la tabella
Mai fatto in vita mia.
Ho proposto al cliente di farsi fare una modifica al programma:
splittare il database in due distinti, in uno si tiene
i dati degli ultimi X giorni e tutto il resto viene archiviato su un
secondo db che magari può stare su un secondo server.
L'admin in php pescherà i dati da entrambi i database, mentre le
impression pescheranno i dati solo da quello piccolo
evitando quindi tabelle da oltre 1.500.000 di record.
Con le partizioni potrei ottenere lo stesso in maniera trasparente?

On 29 Ago, 22:53, Enrico 'Henryx' Bianchi

Il rapporo è questo:
per ogni query fatta dai siti piccoli, ce ne sono 100.000 di quello
grande.
Traffico o no, credo siano completamente ininfluente. Qui si parla di
decine di milioni (milioni no migliaia)
di impression in decuplicazione nei prossimi 3 mesi. Attualmente
60milioni, prevedono un *7, *8 tra poco....

 Con le partizioni potrei ottenere lo stesso in maniera trasparente?
Piu` o meno. Il partizionamento reale su MySQL lo ottieni dalla versione
5.1 in poi. Per il partizionamento fittizio, puoi partire dal
Message-ID: <g14s4i$80h$1...@abraham.hellfish.home> presente su
it.comp.software.database . In sostanza, si tratta di creare n tabelle,
una vista che unisce le tabelle, ed un trigger di tipo BEFORE
INSERT sulla vista[1], in modo da reindirizzare i dati sulle tabelle
corrette. Non so come si comporta il planner di MySQL, ma al momento
della query dovrebbe selezionare correttamente gli indici che gli servono
Enrico
[1] non so se e` fattibile, pero`, dai un'occhiata alla documentazione e
fai qualche prova

On 30 Ago, 13:12, Enrico 'Henryx' Bianchi

Troppi sbattimenti, upgraderò alla 5.1....
Quando arriverà su etch? Si sa niente?

 Quando arriverà su etch? Si sa niente?
Probabilmente dopo il rilascio di Lenny, previsto a settembre
(attualmente e` in experimental, e gli sforzi sono concentrati sul
freeze)
Enrico