Mysql muestra un uso de CPU del 100%

Question

Estoy experimentando algunos problemas con mi servidor MYSQL instalado con Cpanel. Mi servidor muestra un uso del 100% de MySQL incluso si no tengo demasiado tráfico en la web.

Estoy ejecutando CentOS 6.5x64 con SSD de 80 GB y Ram de 8 GB con núcleo de 4 CPU.

mi.cnf:

[mysqld]
max_connections=500
key_buffer=128M
myisam_sort_buffer_size=35M
join_buffer_size=1M
read_buffer_size=1M
sort_buffer_size=2M
table_cache=4000
thread_cache_size=286
interactive_timeout=25
wait_timeout=7000
connect_timeout=10
max_allowed_packet=268435456
max_connect_errors=10
query_cache_limit=2M
query_cache_size=256M
query_cache_type=1
tmp_table_size=16M
innodb_buffer_pool_size=134217728
open_files_limit=10000
[mysqld_safe]
[mysqldump]
quick
max_allowed_packet=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

Aquí está el resultado de MySQLTuner:

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 164M (Tables: 88)
[--] Data in InnoDB tables: 33M (Tables: 239)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 129

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15m 2s (83K q [92.549 qps], 13K conn, TX: 44M, RX: 5M)
[--] Reads / Writes: 61% / 39%
[--] Total buffers: 544.0M global + 4.5M per thread (500 max threads)
[OK] Maximum possible memory usage: 2.7G (34% of installed RAM)
[OK] Slow queries: 0% (0/83K)
[OK] Highest usage of available connections: 23% (116/500)
[OK] Key buffer size / total MyISAM indexes: 128.0M/59.2M
[OK] Key buffer hit rate: 97.9% (24K cached / 527 reads)
[OK] Query cache efficiency: 54.3% (18K cached / 33K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (5 temp sorts / 388 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 374 total)
[OK] Thread cache hit rate: 99% (116 created / 13K connections)
[OK] Table cache hit rate: 98% (465 open / 474 opened)
[OK] Open file limit used: 3% (311/10K)
[!!] Table locks acquired immediately: 58%
[OK] InnoDB buffer pool / data size: 128.0M/33.6M
[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
Enable the slow query log to troubleshoot bad queries
Optimize queries and/or use InnoDB to reduce lock wait

Aquí está el resultado de mi TOP:

top - 12:01:06 up 1 day,  1:02,  1 user,  load average: 10.37, 9.22, 8.61
Tasks: 399 total,   1 running, 397 sleeping,   0 stopped,   1 zombie
Cpu(s): 84.1%us, 15.1%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.2%si,      0.7%st
Mem:   8191528k total,  6042780k used,  2148748k free,   242064k buffers
Swap:        0k total,        0k used,        0k free,  4785012k cached

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
24515 mysql     20   0 2732m 165m 6264 S 303.8  2.1  42:43.18 mysqld
19471 root  20   0  174m  10m 6148 S  1.0  0.1   0:00.03 php
19523 root  20   0  172m  10m 5864 S  1.0  0.1   0:00.03 php
19533 root  20   0  172m   9m 5804 S  0.7  0.1   0:00.02 php
19535 root  20   0  172m  10m 5864 S  0.7  0.1   0:00.02 php

¿Cúal podría ser la causa de este problema?
¿Tengo que hacer cambios en my.cnf para evitar que el servidor se cuelgue?
¿O necesito comprar un servidor nuevo con 16 GB de RAM?

Answer 1

Viendo sus datos, podemos concluir estas recomendaciones generales:

1. SuLECTURA: relación de ESCRITURA (61: 39)da la pista de que sus tipos de motor deben cambiarse a Innodb. Antes de eso, verifique la salida de

   SHOW ENGINE INNODB STATUS\G; 
   SHOW FULL PROCESSLIST;
   

   durante las horas pico. Lo más probable es que vea consultas en LOCKel estado. Los bloqueos de tablas son la única gran limitación de MyISAM. Si tiene una gran cantidad de escrituras y selecciones simultáneas y el rendimiento de las consultas debe ser consistentemente rápido, Innodbes la única opción debido a un mejor mecanismo de bloqueo ( row-level)

2. Total de tablas fragmentadas: 129. Ejecute OPTIMIZE TABLEy desfragmente todo para un mejor rendimiento

3. Échale un vistazo a tuslow_query.log, Marque Query_time, Lock_time, Rows_sent, Rows_examined. Si encuentra consultas en las que la proporción del Rows Sent / Rows Examinedconjunto es alta, entonces esas consultas son buenas para optimization. Es posible que algunas consultas en slow_query.log estén utilizando muchos recursos de CPU.

4. Verifique el estado del servidor con respecto a la RAM y el espacio disponible en disco: free -m , df -h. Si tiene memoria disponible, aumente innodb_buffer_pool_sizepara que haya más almacenamiento en caché.

5. Siempre es bueno ajustar estos parámetros en la configuración de MySQL

   innodb_fast_shutdown=0 innodb_log_buffer_size innodb_log_file_size innodb_flush_method=O_DIRECT query_cache_size=0

---

ADVERTENCIA:

• Lista de verificación 1: CPU de procesos MySQL, si es posible aumentar la CPU

• Lista de verificación 2: RAM, si el conjunto de datos cabe en la RAM.

• Lista de verificación 3: Parámetros relacionados con la conexión ABORTED_CONNECTS, CONNECTIONS, MAX_USED_CONNECTIONS, OPEN_FILES, OPEN_TABLES, OPENED_FILES, OPENED_TABLES, QUERIES, SLOW_QUERIES, THREADS_CONNECTED, THREADS_CREATED, THREADS_RUNNING, , , , , , , , , , , , , , , ,PERFORMANCE_SCHEMAQUERY_CACHE_SIZEWAIT_TIMEOUTLONG_QUERY_TIMEINTERACTIVE_TIMEOUTCONNECT_TIMEOUTMAX_CONNECTIONSLOCK_WAIT_TIMEOUTTX_ISOLATIONMAX_ALLOWED_PACKETGENERAL_LOGTABLE_OPEN_CACHESLOW_QUERY_LOGOPEN_FILES_LIMITINNODB_LOCK_WAIT_TIMEOUT

• Lista de verificación 4: Parámetros relacionados con la memoria , key_buffer_size, query_cache_size, innodb_buffer_pool_size,innodb_log_buffer_size, max_connections, read_buffer_size, read_rnd_buffer_size, sort_buffer_size, join_buffer_size, binlog_cache_size,thread_stacktmp_table_size

• Lista de verificación 5: Otros parámetros importantes de InnoDB innodb_log_file_size, innodb_file_per_table, innodb_flush_log_at_trx_commit, innodb_flush_method, innodb_fast_shutdown

• Lista de verificación 6: mysqltuner

  Uniones realizadas sin índices: Tasa de aciertos de la caché de tablas:

  -------- MyISAM Metrics -------------------------------------------- 
  [!!] 
  [!!] Write Key buffer hit rate: 
  -------- InnoDB Metrics -------------------------------------------- 
  [!!] InnoDB buffer pool / data size: 
  [!!] Ratio InnoDB log file size / InnoDB Buffer pool size : 
  [!!] InnoDB buffer pool instances:
  

• Lista de verificación 7: Lista de procesos Las consultas a continuación se encuentran en el estado de 'Enviando datos' durante mucho tiempo

• Lista de verificación 8: Registro de consultas lento: proporción de filas_examinadas frente a filas_enviadas muy alta,

• Lista de verificación 9: índices duplicados (si los hay)

• Lista de verificación 10: explique el plan de algunas consultas lentas

• Lista de verificación 11: comprobar si las columnas de la cláusula Where están indexadas correctamente (consultas no indexadas)

• Lista de verificación 12: Verifique el bloqueo de la tabla, el bloqueo de metadatos o el punto muerto, si lo hubiera.

• Lista de verificación 13: cualquier trabajo de mantenimiento se ejecuta en intervalos (por ejemplo, mysqlcheck, mysqldump, etc.)

• Lista de verificación 14: Recursos de hardware, discos lentos, reconstrucción de RAID, E/S de disco, red saturada, ancho de banda de red con rendimiento con aumento en el número de subprocesos.