Nessuna modifica apportta ma la CPU di overload di MySQL InnoDB

Non ho cambiato alcun script o impostazioni così tanto. Ora ho carico CPU alless 4 volte superiore. Mysqld prende circa 360% di CPU secondo TOP. È Debian, sto eseguendo alcune tabelle MyISAM, ma la maggior parte sono InnoDB. Ho verificato che il carico ottiene alto quando il sito che utilizza InnoDB va in diretta. Anche ora di notte quando il carico complessivo è di solito inferiore a 1, c'è 12. Non so cosa è andato storto. Non ho cambiato nulla. Ho già provato a riavviare completamente la macchina.

Ho cambiato il mio innodb_log_file_size a 2047M invece di 512M per nessun miglioramento visibile del carico. C'è il resto della sezione my.cnf Fine Tuning:

key_buffer = 4000M max_allowed_packet = 32M thread_stack = 256K thread_cache_size = 192 myisam-recover = BACKUP max_connections = 10000 query_cache_limit = 1M query_cache_size = 256M innodb_buffer_pool_size=8G innodb_additional_mem_pool_size=20M sort_buffer=2M thread_concurrency=16 record_buffer=2M tmp_table_size=128M max_heap_table_size=128M table_cache=1024 innodb_log_file_size = 2047M innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 2 innodb_file_per_table innodb_flush_method = O_DIRECT 

E 'una macchina RAM da 24 GB in esecuzione sia Apache che MySQL. Ci sono circa 20k visite al giorno. In questo momento c'è ancora 20 GB di RAM libera (è la notte e ho appena riavviato). Il sistema HDD è solo 8% pieno. Tutti gli HDD sembrano scrivere / leggere a velocità adeguate.

C'è qualcosa che non va nella mia configuration? È ansible che il carico sia alto, anche se non ho modificato alcuna impostazione della macchina server o script PHP? Cosa altro potrebbe causare questo?

EDIT: output da vmstat

 vmstat 5 10 procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- rb swpd free buff cache si so bi bo in cs us sy id wa 13 1 868 280368 179164 18089764 0 0 511 579 16 89 87 2 10 1 24 2 868 348948 183916 18136144 0 0 5791 610 1916 7552 89 2 7 1 23 0 868 501744 185972 18169456 0 0 3995 5877 2401 6277 91 3 5 1 6 0 868 694992 186136 18202684 0 0 3385 4783 1006 5958 91 2 7 0 22 0 868 825240 186372 18243540 0 0 4133 4087 1098 5364 91 2 7 0 19 0 868 284452 186540 18281960 0 0 3907 4380 537 6468 91 3 7 0 44 0 868 123408 177236 17022560 0 0 3896 5173 483 6914 88 5 7 0 17 0 868 159388 173236 16729360 0 0 4625 8856 1433 7072 89 3 8 0 14 0 868 248836 173380 16763992 0 0 5284 698 819 7357 88 2 9 0 15 0 868 406092 173592 16809708 0 0 4730 5794 1148 7224 90 2 8 0 

command ps -eo, pid, ppid,% cpu,% mem, rss, pri, psr, size –sort = -% cpu | head -n 20

 COMMAND PID PPID %CPU %MEM RSS PRI PSR SZ /usr/sbin/mysqld --basedir= 379 342 364 11.1 2747020 19 1 14249120 gzip 5537 5536 30.1 0.0 756 0 3 452 /usr/sbin/apache2 -k start 2435 27735 6.6 0.3 75408 19 1 47184 /usr/sbin/apache2 -k start 2388 27735 6.7 0.3 77928 19 3 48144 /usr/sbin/apache2 -k start 2323 27735 5.9 0.3 79660 19 3 48464 /usr/sbin/apache2 -k start 2363 27735 5.8 0.3 77156 19 4 47256 /usr/sbin/apache2 -k start 2418 27735 5.7 0.3 77248 19 7 46684 /usr/sbin/apache2 -k start 2350 27735 5.8 0.3 78504 19 2 48092 /usr/sbin/apache2 -k start 2437 27735 5.5 0.3 75928 19 3 47436 /usr/sbin/apache2 -k start 2439 27735 5.5 0.3 75716 19 3 47596 /usr/sbin/apache2 -k start 2356 27735 5.7 0.3 78560 19 3 48708 /usr/sbin/apache2 -k start 2284 27735 5.6 0.3 79532 19 3 47896 /usr/sbin/apache2 -k start 2349 27735 5.6 0.3 78248 19 7 48548 /usr/sbin/apache2 -k start 2368 27735 5.6 0.3 77100 19 3 45852 /usr/sbin/apache2 -k start 2387 27735 5.5 0.3 79964 19 7 48952 /usr/sbin/apache2 -k start 2383 27735 5.4 0.3 79212 19 1 48448 /usr/sbin/apache2 -k start 2169 27735 5.4 0.3 81740 19 3 48636 /usr/sbin/apache2 -k start 2411 27735 5.3 0.3 77292 19 3 47628 /usr/sbin/apache2 -k start 1779 27735 5.4 0.3 88876 19 0 48384 

mpstat 5 10

 Linux 2.6.26-2-amd64 12/12/2014 _x86_64_ 03:38:23 PM CPU %user %nice %sys %iowait %irq %soft %steal %idle intr/s 03:38:28 PM all 87.44 3.56 1.44 0.17 0.15 0.40 0.00 6.83 2363.80 03:38:33 PM all 86.82 3.31 1.82 0.12 0.05 0.25 0.00 7.62 1703.00 03:38:38 PM all 88.52 2.39 1.76 0.30 0.15 0.32 0.00 6.56 2552.68 03:38:43 PM all 85.12 3.92 2.17 0.27 0.10 0.42 0.00 7.99 2810.60 03:38:48 PM all 87.72 3.34 1.82 0.10 0.10 0.30 0.00 6.61 2368.00 03:38:53 PM all 85.36 3.83 1.59 0.40 0.05 0.17 0.00 8.59 1589.60 03:38:58 PM all 85.74 4.01 1.50 0.20 0.07 0.17 0.00 8.30 1648.00 03:39:03 PM all 85.26 4.16 1.75 0.20 0.12 0.60 0.00 7.91 1764.20 03:39:08 PM all 87.20 3.62 1.70 0.17 0.10 0.45 0.00 6.76 2221.80 03:39:13 PM all 85.96 3.12 2.67 0.27 0.05 0.52 0.00 7.41 2829.60 Average: all 86.52 3.53 1.82 0.22 0.09 0.36 0.00 7.46 2185.35 

EDIT, MySQL 5.5

L'aggiornamento a MySQL 5.5 ha fatto una grande differenza. Il carico è sceso da 20 a 10 di giorno, da 10 a 6 di notte. Traffico vicino a none = carico 6, alto traffico = carico 10.

=> Ora è interessante, i carichi correnti sono esattamente 5 punti sopra i valori prima dell'incidente. Non import il traffico. Questo non mi fa molto senso. Non c'è motivo per il carico 6 su CPU a 8-core quasi senza traffico e ancor più strano, che il carico solo raddouble, mentre il traffico pesante. Il carico giornaliero era di 5 volte superiore alla notte.

==> O MySQL 5.5 è miracolosamente efficiente (rispetto a 5.0) O c'è qualcosa che aggiunge quei 5 punti, non import cosa. Non riesco tuttavia a vedere alcun process speciale. Deve essere Apache / PHP / MySQL correlato.

EDIT, SOLVED (da solo)

4 giorni fa, il carico aumenta nel cielo, senza alcun motivo apparente. Oggi la mattina presto, il carico scende ai valori normali proprio così.

immettere qui la descrizione dell'immagine

Non sono un fan di accusare DDOS per tutto, in verità ho deciso di guardare le statistiche di networking. Ma poi di nuovo, non ho mai sperimentato alcun attacco, forse non lo riconosco.

Fatto: dopo 3 giorni sono riuscito ad aggiornare a MySQL 5.5 che non ha risolto il problema integer ma ha abbassato il carico abbastanza per rendere nuovamente il sito ai visitatori finali. Un giorno dopo, il problema va via. È quasi come se qualcuno ha perso interesse quando il sito non soffriva di lunghi tempi di caricamento.

La corruzione del sistema operativo non sarebbe andata via da sola. Non ci sono stati compiti di fondo. L'attacco sembra l'unica spiegazione sinistra, anche se non ho notato alcuna attività di networking strana. Una cosa che posso dire, il nostro pubblico è esattamente il tipo di persone che spesso fa questo tipo di cose.

EDIT 29/12/2014

Non sono sicuro se qualcuno sta ancora guardando questo thread. Vorrei solo pubblicare un aggiornamento. Alti carichi sono tornati dopo alcuni giorni, poi sono andati per coppia, indietro, ecc. A volte il carico è costantemente alto, a volte spuntano. Il giorno può diminuire del 50% un altro aumento del 100%. Il tempo differisce – non esiste alcuna connessione al traffico effettivo, alle attività di background, niente. Anche se sembra veramente DDOS, non esiste alcuna attività di networking strana.

La grande differenza è però MySQL 5.5, ora la macchina può gestire meglio qualunque cosa sia la sua alimentazione, mantenendo il sito in esecuzione. Il collo di bottiglia sembra essere di nuovo la CPU, che non ha molto senso, di nuovo.

Il più "divertente" è l'imprevedibilità di questo comportmento. Questo non è adatto a qualsiasi errore SW / HW, o fa ?

  • combinare due file di log di replica di mysql
  • MySQL in topologia stellare
  • Mysql: Lavorare con i record di 192 trilioni ... (Sì, 192 trilioni)
  • È ansible connettersi a MySQL da PHP utilizzando il vecchio protocollo di crittografia delle password?
  • Gestione della memory LAMP (CentOS)
  • Qual è la differenza tra la replica e la sincronizzazione?
  • MySQL smise di funzionare - Errore 2002 - Istanza sconosciuta
  • MySQL Master-Master Replica di tutti i database. Come?
  • 2 Solutions collect form web for “Nessuna modifica apportta ma la CPU di overload di MySQL InnoDB”

    Oltre l'ombra di each dubbio, credo fermamente che sia il motore di stoccaggio di InnoDB.

    È quasi come un organismo vivente e respiro.

    Ecco una rappresentazione pittorica del CTO di Percona Vadim Tkachenko

    InnoDB Architettura

    Si prega di notare il pool di buffer InnoDB. Se ha molte pagine sporche (modifiche per scrivere indietro alle tabelle fisiche) e corrispondenti modifiche all'indice (sezione Inserisci buffer del pool di buffer), notare le seguenti scritture

    • Scrivere le discussioni di I / O dal pool di buffer a file .ibd (file di tabelle fisiche). Questo può richiedere I / O di lettura / scrittura, forse le tabelle di apertura e chiusura tramite algoritmi LRU
    • Le modifiche al buffer di inserimento vengono scritte all'interno di un file di tabella di sistema (ibdata1)
    • Dal momento che hai innodb_flush_log_at_trx_commit = 2, il buffer di registro viene lavato una volta al secondo (non un problema in un sistema inattivo poiché innodb_log_buffer_size è 16, può essere un problema durante le scritture pesanti)
    • Le pagine sporche sono scritte sul doppio buffer di scrittura (aiuta mysqld a sopravvivere ad un crash mysqld (o alless una quantità decente di controllo danni di InnoDB)

    Qual è il punto di discutere del InnoDB Storage Engine?

    Se hai molte pagine sporche scritte durante il giorno lavorativo, potrebbe esserci qualche attività per scorporare le modifiche attorno al motore come un cuore che circola il sangue. Anche le scritture mite possono hide per un po '. Poiché si dispone di un pool di buffer 2G, InnoDB potrebbe solo eseguire la pulizia del pool di buffer (tramite il suo thread di spurgo) .

    Dall'aspetto del mio.cnf, direi che probabilmente hai MySQL 5.5 o superiore. InnoDB, quando non sintonizzata, tende ad essere più aggressivo con il filo di spurgo .

    Adesso, per una sorpresa. Lo sapevi che InnoDB può stare tranquillo con alless il 75% del tampone ancora sporco?

    • MySQL 5.5 e 5.6 , innodb_max_dirty_pages_pct = 75
    • MySQL 5.0 e 5.1 , innodb_max_dirty_pages_pct = 90

    Ciò significa che InnoDB non tollera più di 75% (o 90%) di pagine sporche. Inizia l'attività aggressiva del thread di spurgo quando le pagine sporche superano la soglia di innodb_max_dirty_pages_pct . Una volta che scende al di sotto della soglia del 75% o del 90%, InnoDB risolve in modo costante le pagine sporche, quando è buono e pronto . Questo si manifesta come I / O di scrittura, caricamento del server e CPU aumentata anche quando non ci sono INSERT, UPDATE e DELETE in corso. Nonostante ciò, InnoDB deciderà quando veramente le vecchie pagine sporche devono essere pulite.

    Quello che ti può essere bisogno è solo una sintonizzazione. Ecco un esempio di alcune impostazioni necessarie

     [mysqld] innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_thread_concurrency = 0 

    Per una visione più approfondita dell'avvio di InnoDB con più hyperthreading e CPU, vedere i miei post DBA StackExchange:

    • Possibile utilizzare MySQL più di un nucleo?
    • Utilizzo di più core per query singole MySQL su Debian
    • Informazioni sulle performance dei database basati su singoli threaded versus multithreaded

    BTW, hai 24 GB di RAM e solo 2 GB di buffer pool? Vedi il mio vecchio post Quanto grande deve essere mysql innodb_buffer_pool_size? . Perché? Una piscina di size insufficiente, anche con una corretta sintonizzazione per il hyperthreading e la CPU, può ancora avere intermittenti scrive fuori dal nulla.

    AGGIORNAMENTO 2014-12-12 10:30 EST

    Dal momento che sei al momento MySQL 5.0, probabilmente puoi fare tutto il lavaggio come più completo ansible con le seguenti impostazioni:

     [mysqld] innodb_thread_concurrency = 0 innodb_max_dirty_pages_pct = 0 

    Questo dovrebbe minimizzare, o alless ridurre, events di improvviso aumento di carico e CPU

    Questi sono opzioni

     [mysqld] innodb_fast_shutdown = 0 innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 

    Perché queste impostazioni facoltative?

    • Impostazione innodb_fast_shutdown = 0
      • assicura che tutte le transactions siano scaricate sul disco
      • per un avvio più veloce
    • Impostando innodb_flush_log_at_trx_commit a 1 scompare correttamente nelle tabelle e ripetere i registri. CAVEAT: Alcuni hardware potrebbero ancora non rispondere a questa impostazione.
    • Se si abilita la logging binaria, sync_binlog farà correre correttamente i registri binari

    A lungo termine, è necessario davvero andare con MySQL 5.6.

    Il problema non è correlato al motore di archiviazione, il problema potrebbe essere perché alcune query SQL stanno prendendo risorsa CPU troppo elevata da elaborare per quel particolare sito / applicazione. Ciò potrebbe essere dovuto al fatto che la query utilizza colonne "non indicizzate" o la query non è efficiente. Meglio capire il problema abilitare il log "slow query" sul mysql.

     log_slow_queries=/var/log/mysql/slow-query.log long_query_time=1 

    L'impostazione precedente causerà query che richiedono più di un secondo per elaborare l'accesso registrato in /var/log/mysql/slow-query.log . Successivamente è ansible fare riferimento al registro per identificare le query e perfezionarle.

    Un altro modo per identificare il problema è controllare il command SHOW FULL PROCESSLIST per get l'elenco delle query attualmente in esecuzione. Guardandolo, è ansible restringere il tipo di query che causano questo problema.

    E anche incollare l'output di vmstat 5 10 . Ciò aiuterà a trovare se c'è un bottleneck hardware.

    Suggerimenti per Linux e Windows Server, quali Ubuntu, Centos, Apache, Nginx, Debian e argomenti di rete.