MySQL levando muita CPU

Question

Abaixo está o meu arquivo .cnf e se eu ver o meu consumo de CPU 99,99% dos meus comandos mysqld. O servidor MySQL está conectado a partir de máquinas remotas que atualizam dados nele com freqüência, mas eu me certifico de que o servidor remoto abra uma conexão, leia / escreva / atualize e feche-o. Além disso, o servidor remoto lê muito.

O que posso fazer para reduzir o consumo de CPU? FYI, estou usando 2 core CPU com 4GB de RAM.

[client]
port          = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice          = 0

[mysqld]
user          = mysqluser
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port          = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
wait_timeout    = 20
interactive_timeout = 60

bind-address            = <IP-ADDRESS>
key_buffer            = 16M
max_allowed_packet  = 16M
thread_stack            = 192K
thread_cache_size   = 8
myisam-recover      = BACKUP
max_connections     = 300

query_cache_limit   = 20M
query_cache_size        = 128M

log_error = /var/log/mysql/error.log
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 4
log-queries-not-using-indexes

expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer      = 16M

Também colando a saída do mysqltuner.

 >>  MySQLTuner 1.4.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.38-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in InnoDB tables: 2G (Tables: 26)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 26

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21m 51s (37K q [28.525 qps], 31K conn, TX: 6M, RX: 7M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 304.0M global + 2.7M per thread (5000 max threads)
[!!] Maximum possible memory usage: 13.4G (347% of installed RAM)
[!!] Slow queries: 18% (6K/37K)
[OK] Highest usage of available connections: 0% (16/5000)
[OK] Key buffer size / total MyISAM indexes: 16.0M/100.0K
[OK] Query cache efficiency: 20.2% (7K cached / 36K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[OK] Temporary tables created on disk: 25% (54 on disk / 215 total)
[OK] Thread cache hit rate: 99% (16 created / 31K connections)
[OK] Table cache hit rate: 25% (74 open / 289 opened)
[OK] Open file limit used: 0% (49/25K)
[OK] Table locks acquired immediately: 100% (29K immediate / 29K locks)
[!!] InnoDB  buffer pool / data size: 128.0M/2.9G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    innodb_buffer_pool_size (>= 2G)

Eu não estava recebendo esse problema, mas quando comecei a conectar 10 servidores remotos a esse servidor MySQL e quando esses dez servidores começaram a fazer SELECT e UPDATE consultas neste único servidor MySQL, vejo isso aumento do consumo de CPU.

    

Answer 1

Eu suspeito que você tenha vários problemas aqui que estão acumulando e causando problemas.

1. O disco i / o é fragmentado e provavelmente ineficiente. OPTIMIZE TABLE em cada uma das suas tabelas deve resolver isso e ajudar um pouco.
2. A memória é ajustada de forma horrível, provavelmente causando problemas ao trocar com frequência.
3. Consultas lentas - esse é o grande suspeito de seu alto uso de CPU. Eu checaria seu log lento e veria o que você pode otimizar adicionando índices / reorganizando consultas / etc. Provavelmente há uma consulta bastante comum que faz dezenas ou centenas de milhares de comparações que estão matando seu desempenho.

Se nenhum deles aumentar o desempenho para o que você deseja, talvez seja hora de expandir seu servidor de banco de dados. 2 núcleos não me dizem nada sobre o poder de processamento da máquina, e 4 GB de memória RAM são bem pequenos atualmente.

Para um conselho mais específico, adicione mais detalhes à sua descrição. SSDs ou discos rotativos? Qual CPU? Que classe de carneiro? Quantas consultas / min? Quantos clientes, etc?

    

Answer 2

Se você puder brincar um pouco com as configurações, eu tentarei isso em my.cnf:

innodb_buffer_pool_size = 3000M

depois reinicie o mysql e verifique seu desempenho. Isso permitiria que você mantivesse muito mais do seu banco de dados na memória, reduzindo a debulha de disco / memória que você pode estar vendo. Dado que você tem 4Gb de memória, e assumindo que este servidor é SOMENTE um servidor db, você pode aumentar o tamanho do buffer para até 80% da memória do sistema.

    

Answer 3

O que acontece: você tentou colocar um elefante na bola do hamster.

O que fazer: diminuir a pegada de memória para o mysql

. . . . .
. . . . .
[mysqld]
user                    = mysqluser
pid-file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
log_error               = /var/log/mysql/error.log
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time         = 10
log-queries-not-using-indexes
expire_logs_days        = 10
max_binlog_size         = 100M

port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
skip-external-locking
wait_timeout            = 20
interactive_timeout     = 60

bind-address            = <IP-ADDRESS>
myisam-recover          = BACKUP

## REASONABLE MEMORY PARAMETERS
max_connections         = 64
thread_cache_size       = 2
thread_concurrency      = 4
key_buffer_size         = 64M
join_buffer_size        = 2M
sort_buffer_size        = 2M
read_buffer_size        = 2M
read_rnd_buffer_size    = 8M
myisam_sort_buffer_size = 16M
query_cache_limit       = 2M
query_cache_size        = 32M
table_open_cache        = 512
max_allowed_packet      = 1M
. . . . .
. . . . .

    

Answer 4

Você deve analisar suas consultas mais frequentes com o comando EXPLAIN . A saída mostrará como suas consultas usam índices de tabela, o que afeta diretamente o desempenho.