Newsgroup: it.comp.os.linux.sys
Titolo: Mysql guru cercasi (cpu 100%)

Ho una centos 5 con cpanel con hostati circa 160 siti, uno dei quali � 
un portale giornalistico che genera un botto di traffico.
Ho mysql che tiene le 2 cpu al 100% tutto il tempo. Il load del server 
arriva fino a 40 (sic).
La macchina � una virtual machine in ESX 3.5 con 2 virtual cpu e 4 giga 
di ram.
La ram � libera al 50% e la macchina non swappa.
Incollo my.cnf
[mysqld]
# Memory tuning
key_buffer_size         = 1500M
thread_cache            = 100
thread_cache_size       = 50
thread_concurrency      = 4
query_cache_type        = 1
query_cache_size        = 512M
query_cache_limit       = 5M
table_cache             = 10000
open_files_limit        = 25000
tmp_table_size          = 64M
max_heap_table_size     = 64M
join_buffer_size        = 3M
sort_buffer_size        = 2M
read_buffer_size        = 2M
read_rnd_buffer_size    = 8M
myisam_sort_buffer_size = 64M
# Timeouts
interactive_timeout=25
wait_timeout=10
connect_timeout=5
# Tweaks
skip-bdb
skip-innodb
# Log slow-queries
log_slow_queries        = /var/log/mysql-slow.log
long_query_time         = 3
Qualunque aiuto � aprrezzato. Io non so piu dove sbattere la testa :|

 long_query_time         = 3
Io inizierei guardando questo log, poi analizzerei le query che fa quel 
portale e ci metterei un po' di caching delle pagine. Anche visualizzare 
la stessa pagina senza aggiornamenti per 5-10 minuti potrebbe abbatterti 
il carico sul DB.
Bye.

 il carico sul DB.
Purtroppo apparte lartare i tipi (che tra l'altro si sono persi il 
programmatore che ha messo su tutto l'ambaradan) non posso fare...
Per il caching come lo implementeresti?

 
questo � il mio, prova cos�
mettere cos� tanta cache serve come un culo nel gomito mi hanno spiegato.
[client]
port            = 3306
socket          = /var/run/mysql/mysql.sock
[mysqld]
port            = 3306
socket          = /var/run/mysql/mysql.sock
skip-locking
key_buffer = 32M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 1024M
thread_cache_size = 8
query_cache_size = 128M
thread_concurrency = 8
log-slow-queries = /home/sathia/mysql_slow
long_query_time = 10000000
default-character-set=utf8
max_connections = 200
tmp_table_size = 128M
tmpdir = /var/tmp
# by percona:
max_heap_table_size = 128M
skip-name-resolve
default-character-set=utf8
collation_server=utf8_unicode_ci
character_set_server=utf8
ft_min_word_len=5
# innodb:
innodb_log_file_size=64M
innodb_buffer_pool_size=1G
# innodb_flush_method=O_DIRECT
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
[mysqldump]
quick
max_allowed_packet = 16M
default-character-set=utf8
[mysql]
no-auto-rehash
default-character-set=utf8
[mysqladmin]
default-character-set=utf8
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 1024M
sort_buffer_size = 1024M
read_buffer = 24M
write_buffer = 24M
ft_min_word_len=5
[mysqlhotcopy]
interactive-timeout
-- 
have you tried turning off and on your computer?

majale wrote:

Grazie, l'unica differenza sensibile � myisam_sort_buffer_size, ma non 
ha fatto gran differenza...

 Qualunque aiuto � aprrezzato. Io non so piu dove sbattere la testa :|
non sar� un problema di indici mancanti o corrotti?


Eh so una madonna in carriola...

Cristian "sengo" Mammoli ha scritto:

Esempio:
hai una tabella con N mila record sempio nomi, cognomi ecc..
quando fai un select e ricerchi il signor Rossi il db deve cominciare a 
leggere dal primo all'ultimo record il ch� richiede tempo e risorse 
ovviamente, l'indicizzazione permette in poche parole di raggruppare per 
esempio il campo "cognome" in modo da abbattere i tempi di ricerca come 
se mettessi tutti i "Rossi" in un file, i "Bianchi" in un altro ecc.. il 
db ricercher� solo nell'area "Rossi" ed il tempo di ricerca (SELECT) 
sar� abbattuto.
Ovviamente l'altra faccia della medaglia � cherallenta parecchi le 
operazioni di INSERT ma nel caso in questione non dovrebbe essere un 
problema.
Suggerisco di postare il tipo di software utilizzato per il portale ed 
ed il link al sito in questione, se il software non � standard dovresti 
fare almeno il dump dello schema delle tabelle
mysqldump -uroot -ppassword nomedb --no-data=true > dump-schema

 table_cache             = 10000
ciao,
   a me pare sporporzionato questo dato, io facevo anche un botto di 
connessioni e poi usavao innodb che � una cacca, per� quel table_cache 
mi pare troppo esagerato.
Prova a calarlo, diciamo mettilo ad un 25%, e poi valuta di nuovo.
http://dev.mysql.com/doc/refman/5.0/en/table-cache.html

 il caching come lo implementeresti?
Se non sei tu/voi il programmatore, c'e` poco da fare.
Va modificato il codice in modo da usare, per esempio, memcached per 
tenere le pagine prerenderizzate, oppure un banale caching fatto a mano 
su disco con un check sulla data del file in cache, e accedi al DB solo 
se sono passati x minuti.
Dopo averli avvisati, segagli le gambe limitando il numero di connessioni 
dallo stesso utente o la durata massima di una query, cosi` corrono a 
cercare un nuovo programmatore.
Bye.

 connessioni e poi usavao innodb che � una cacca,
innodb una cacca?
AFAIK, viene valutato meglio di MyISAM sotto molti aspetti.
no, non chiedetemi quasi, non sono esperto.
L'unica cosa che so, � che su svariati record, innodb � sensibilmente
pi� veloce di myisam.
(intendo milioni di record)

 Eh so una madonna in carriola...
1 - Senza un minimo di analisi sulle query, non ne esci
2 - Esistono in giro un tot di script sia shell che perl per analizzare
    il tuo db e suggerirti i parametri migliori per my.cnf, me ne ricordo
    un paio, 
mysqltuner.pl
 e tuning-primer.sh

 mi pare troppo esagerato.
Ciao, l'ho alzato in quel modo su "suggerimento" di tuning-primer.
Da show status:
| Open_files                        | 11363     |
| Open_streams                      | 0         |
| Open_tables                       | 5898      |
| Opened_tables                     | 0         |
Comunque il valore � stato alzato per cercare di risolvere, di sicuro 
non � la causa del problema visto che prima di sicuro andava peggio...

     un paio, 
mysqltuner.pl
 e tuning-primer.sh
La maggior parte dei tweak li ho fatti proprio usando tuning-primer, 
mentre mysqltuner come warning mi da sta roba:
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
colpa mia che non ho insistito per installare una x64
[!!] Maximum possible memory usage: 2.9G (77% of installed RAM)
E mi sta bene: fa solo sql+httpd
[!!] Joins performed without indexes: 1221
E qui come ho detto a parte lartare i pisquani posso fare ben poco.

 mysqldump -uroot -ppassword nomedb --no-data=true > dump-schema
Sono 180k di roba, se interessa lo uploado da qualche parte.

c'� solo una cosa che devi fare.
analizza il log slow_query vedi cosa ci mette pi� di un secondo 
e sistemi gli indici.
non � che devi toccare il codice del sito, basta un add index somewhere


 non � che devi toccare il codice del sito, basta un add index somewhere
La query che appare pi� volte dovrebbe essere sta cosa:
# Query_time: 5  Lock_time: 0  Rows_sent: 9  Rows_examined: 8781
SELECT a.*, 
u.name
, cc.image AS image
  FROM jos_content AS a
  LEFT JOIN jos_content_frontpage AS f ON f.content_id = 
a.id
  LEFT JOIN jos_users AS u ON 
u.id
 = a.created_by
  INNER JOIN jos_categories AS cc ON 
cc.id
 = a.catid
  INNER JOIN jos_sections AS s ON 
s.id
 = a.sectionid
  WHERE ( a.state = 1 AND a.sectionid > 0 )
  AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= 
'2009-06-14 09:32' )
  AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= 
'2009-06-14 09:32' )
  AND ( a.catid=13 OR a.catid=16 OR a.catid=17 OR a.catid=18 OR 
a.catid=19 OR a.catid=20 OR a.catid=21 OR a.catid=22 OR a.catid=23 OR 
a.catid=24 OR a.catid=25 OR a.catid=26 OR a.catid=27 OR a.catid=28 OR 
a.catid=29 OR a.catid=30 OR a.catid=31 OR a.catid=33 OR a.catid=36 OR 
a.catid=45 OR a.catid=63 OR a.catid=72 OR a.catid=73 OR a.catid=78 )
  AND ( a.sectionid=4 OR a.sectionid=5 OR a.sectionid=9 )
  AND ((TO_DAYS('2009-06-14') - TO_DAYS(a.created)) <= '1200')
  AND f.content_id IS NULL
  AND s.published = 1
  AND cc.published = 1
  ORDER BY a.created DESC
  limit 0,9;

Il Fri, 12 Jun 2009 19:32:46 +0200, Cristian "sengo" Mammoli ha scritto:
In sintesi, considerato che hai il 50% di memoria disponibile ed in base
alla mia esperienza, ti confermo come altri ti hanno detto che:
1) devi ottimizzare gli indici
2) devi ottimizzare le query (lo stesso risolutato si ottiene scrivendo le
query grosse in modo diverso, ma con molte meno risorse).
3) oltre certi limiti � necessario il caching (memcached � solo overhead in
locale senza cluster) che andrebbe implementato.
Se puoi farlo tu bene.
Se lo devono fare loro, faglielo fare.
L'altra alternativa �: cazzi tuoi :-D
Max
-- 
A: Because we read from top to bottom, left to right.
Q: Why should i start my reply below the quoted text?

Cristian "sengo" Mammoli wrote:

e un explain di questo obbrobrio?
se sei gentile la cosa migliore che puoi fare �:
a) sistemi gli indici attraverso explain $tuttalaquery
b) crei un crontab che esegue quella query e salvi tutto il contenuto in una
tabella ad esempio cache_1 poi dirotti tutte le letture di quell'obbrobrio
a SELECT * FROM cache_1 e sei a posto.
ciao


 innodb una cacca?
SI, certifato e garantito.
Postgres , per dire, � vecole da sue a tre volte sulle transazioni...

Michele wrote:

non tutti i db hanno transazioni, quindi la risposta � errata.


 non tutti i db hanno transazioni, quindi la risposta � errata.
Esatto.
Non puoi prendere un punto negativo e farne una crociata.
se io non uso trnasazioni, non vedo perch� dovrei passare
a postgresql solo perch� fa transazioni pi� velocemente.

   LEFT JOIN jos_users AS u ON 
u.id
 = a.created_by
[... cut ...]
A naso (e data la nomenclatura adottata per le tabelle)
sembra una query prodotta con Jooma.
Rif:
http://www.joomla.org
http://www.joomla.it
In primis valuta la possibilita' di documentarti su questo
CMS. Sarebbe di ottimo auspicio sapere che supporta
qualche funzione di caching dei risultati. Mi
pare che in una della ultime versioni sia disponibile.
IMHO un sistema di caching a livello di applicativo
in questo caso e' la migliore delle soluzioni al tuo
problema.
Come gia' suggerito, un'analisi degli indici e' altrettanto
necessaria soprattutto se:
1. hai un gran numero di accessi
2. la quantita di record stoccati nelle tabelle e' elevata
In ultima analisi una piccola considerazione. Quanto
tieni alla sicurezza della macchine ?
Joomla (sempre che di lui si tratti) non e' certo una roccia.
Funge piuttosto bene, ma due per tre esce qualche
minor release per fixare le voraggini lasciate qua e la.
Se non ci sta nessuno a sorveggliare l'aggeggio, magari
rischi si trovarti un personalissimo "penis enlargment"
in Home page e magari la macchina buggata :)
Ciao
Beppe

 programmatore che ha messo su tutto l'ambaradan) non posso fare...
Larta anche chi ha deciso di ficcare nello stesso ambiente reale (anzi, 
nella stessa istanza di Mysql) 160 siti di cui uno molto trafficato.
-- 
Vide

 nella stessa istanza di Mysql) 160 siti di cui uno molto trafficato.
Scusa la domanda, ma forse non ho capito cosa intendi.
Se mettendo due server distinti in ascolto, per dire uno sulla
3306 e l'altro sulla 3307, ma sempre sulla stessa macchina, cosa
cambierebbe? MySQL dovrebbe gestire in maniera pi� o meno buona
l'accesso a database multipli.
Mettere due istanze sullo stesso server dovrebbe essere solo un
brutale spreco di risorse, IMHO

 Qualunque aiuto � aprrezzato. Io non so piu dove sbattere la testa :|
Scaricati questo:
http://www.day32.com/MySQL/tuning-primer.sh
Potrebbe darti dritte molto utili.
Ciao!
		-- Yena --

 Potrebbe darti dritte molto utili.
Ha gi� detto di aver fatto la maggior parte
delle ottimizzazioni proprio usando tuning-primer.

 minor release per fixare le voraggini lasciate qua e la.
Quindi il fatto che rilascino aggiornamenti di sicurezza spesso, ï¿œ un 
male?

 brutale spreco di risorse, IMHO
Ovviamente sprecherai un po' più di risorse, ma manco troppe.
-- 
Vide

 male?
E' in quale parte della mia considerazione hai percepito
questo abbaglio ?

>>minor release per fixare le voraggini lasciate qua e la.
Questo l'hai scritto tu.
Ora, partendo dall'assunto che nessun software ï¿œ esente da bachi, se 
vengono rilasciati aggiornamenti di sicurezza, significa che comunque 
c'e' qualcuno che lavora al progetto, che per me ï¿œ un bene.
Per quanto riguarda le voraggini, beh, ho joomla in produzione da anni 
e i problemi che ho avuto sono riconducibili a una mia momentanea 
configurazione tonnesca del server.

 configurazione tonnesca del server.
:)
Era proprio per questo che dicevo "FUNGE BENE".
Ciao
Beppe

 Non puoi prendere un punto negativo e farne una crociata.
Uno no, tanti messi assieme si. Personalmente l'unico motivo che ho per
usare MySQL e` perche` ho bisogno di servire un numero veramente elevato
di accessi (anche se potrei ottenere lo stesso risultato ottimizzando
veramente bene la base dati)
Enrico

 non tutti i db hanno transazioni, quindi la risposta è errata.
Cosa c'entra questa puntualizzazione con quello che e` stato detto in
precedenza? Si sta paragonando InnoDB (che e` transazionale), non tutto
MySQL
Enrico

 colpa mia che non ho insistito per installare una x64
Questo e` male a prescindere, al giorno d'oggi ha senso solo in casi
particolari installare una macchina a 32 bit (e sicuramente, con un
database di mezzo, questo non e` uno di quei casi)
Enrico

 brutale spreco di risorse, IMHO
Dipende dai casi. Personalmente 160 siti su di uno stesso server li
eviterei, non fosse altro che per il fatto che ne basta uno bucabile e
tutti sono a piedi
Enrico

 tutti sono a piedi
Non hai capito, se metto due istanze mysql in ascolto sullo stesso
server fisico, dal punto di vista prestazionale cosa cambia rispetto
ad una singola istanza?

Peregrino "Pipino" Tuc ha scritto:

Le query pi� leggere, sull'altra istanza, molto probabilmente
ringrazierebbero, potendo contare su uno slice ogni tanto, e
non su uno slice alla fine della query massacrante.
-- 
           |Save our planet!
    Ciao   |Save wildlife!
   roberto |For your E-MAIL use ONLY recycled Bytes  !!
           |roberto poggi   
rpo...@softhome.net

 non su uno slice alla fine della query massacrante.
Mi stai dicendo quindi che MySQL mette in coda tutte le query
anche se su database differenti?
Pensavo le facesse in parallelo e che accodasse solo le query
destinate alla stessa tabella o al limite allo stesso db.

 un portale giornalistico che genera un botto di traffico.
come ti hanno detto, guarda le slow query.
Domanda: e' sempre stato alto, (o cresciuto gradualmente) oppure ti e' 
salito di colpo?
--
tinto

 destinate alla stessa tabella o al limite allo stesso db.
Quando sei al 100% di cpu per colpa del DB, imho i parallelismi
sono pi� teorici che pratici.
Non sono cos� addentro a mysql nelle sue ultime versioni per
sapere come funziona sotto stress un eventuale ottimizzatore
di query.


 ad una singola istanza?
Innanzitutto se hai server con molti core (>=8) non usi versione 
recentissime di Mysql (recentissime >= 5.4.0) con 2 istanze scali meglio che 
con una, dato che Mysql non è(ra) eccezionale con più di 4 CPU.
Secondo come già detto almeno puoi isolare meglio il problema, se arriva a 
causare un downtime (o un riavvio del servizio).
-- 
Vide

 causare un downtime (o un riavvio del servizio).
Interessante, potrei fare alcuni test...
Qualche modo per installare due istanze mysql su una debian
senza dover creare a mano 30 configurazioni, 45 directory per i
db e così via?
Altrimenti, i test resteranno nel cassetto...

 db e così via?
Bastano 2 my.cnf separati - con un bind address che punta a IP diverso e - e 
una copia dello script d'init (in cui ovviamente devi toccare la riga 
CONF=).
Occhio anche ai PID file e al socket locale.
-- 
Vide