Revisé los registros lentos y solo obtuve 4 consultas en 2 horas y todas eran similares a esta:
"SELECT HEX(uhash) AS uhash, vehid, IF(deleted = 0 AND follow_price_drop = 1, 1, 0) AS follow_price_drop, email, deleted
FROM wp_ product_favorite_count AS cfc
INNER JOIN wp_ product_favorite_user AS cfu ON cfc. product_favorite_user_uhash = cfu.uhash
WHERE cfc.updated > '2021-09-23 12:49:02' OR cfu.updated > '2021-09-23 12:49:02'"
Revisé top y htop y a menudo obtengo un uso de 100 CPU en los 6 núcleos de CPU.
La mayor parte del uso de la CPU proviene de mysqld, así que registré la base de datos:
iostat -xm 5 3 me dio:
avg-cpu: %user %nice %system %iowait %steal %idle
11.34 0.01 1.80 1.13 0.08 85.65
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
xvda 39.75 720.61 79.81 192.29 0.99 3.57 34.30 0.02 0.09 0.19 0.04 0.09 2.53
^[[A^[[A^[[Aavg-cpu: %user %nice %system %iowait %steal %idle
84.15 0.00 6.16 0.05 0.03 9.61
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
xvda 0.80 31.00 14.40 19.80 0.65 0.20 50.95 0.02 0.73 0.93 0.58 0.43 1.48
^[[A^[[Bavg-cpu: %user %nice %system %iowait %steal %idle
84.54 0.00 4.95 0.10 0.05 10.36
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
xvda 0.00 2.40 22.60 1.60 1.77 0.02 151.40 0.02 1.02 1.04 0.75 0.64 1.56
ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 128341
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) 128341
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
Revisé el registro de consultas generales después de verificar el registro de consultas lentas y me sorprendió recibir tantas consultas. Cuando el tráfico es normal, obtuve: 136235 consultas, la mayoría de las cuales son consultas SELECT después de 10 minutos. Y cuando el tráfico es alto, recibí: 195650 consultas en 10 minutos. Dudo que sean 195650 visitantes, pero por alguna razón las llamadas están dentro del general_log. slow_query_log tenía solo 4 consultas y no parecían consultas no optimizadas. ¿Hay algo más que deba mirar o es suficiente para suponer que se debe al tráfico y que debemos actualizar el servidor?
La parte superior se ve así, no pude capturarlo a tiempo, pero cuando alcanzó más del 95% de la CPU, la pantalla se veía así:
top - 13:04:51 up 1140 days, 19:59, 2 users, load average: 26.57, 16.21, 8.92
Tasks: 429 total, 12 running, 421 sleeping, 0 stopped, 0 zombie
Cpu(s): 91.3%us, 1.6%sy, 0.0%ni, 65.7%id, 3.1%wa, 0.0%hi, 0.2%si, 0.1%st
Mem: 32877280k total, 31367584k used, 1509696k free, 3960824k buffers
Swap: 0k total, 0k used, 0k free, 3980580k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14576 mysql 20 0 12.9g 8.5g 8424 S 951.6 27.2 18841:47 mysqld
6032 martind 20 0 510m 65m 9160 S 61.4 0.2 2:49.40 php-fpm
7329 martind 20 0 498m 63m 5556 R 57.6 0.2 0:47.15 php-fpm
7321 martind 20 0 487m 52m 5532 R 46.1 0.2 0:45.18 php-fpm
7160 martind 20 0 488m 52m 5540 R 44.1 0.2 1:02.67 php-fpm
6031 martind 20 0 511m 67m 8076 S 42.2 0.2 2:50.87 php-fpm
6696 martind 20 0 498m 63m 5700 S 38.4 0.2 1:36.38 php-fpm
7283 martind 20 0 494m 59m 5268 S 34.5 0.2 0:46.19 php-fpm
7314 martind 20 0 490m 55m 5536 R 33.0 0.2 0:44.22 php-fpm
7330 martind 20 0 496m 60m 5436 R 26.4 0.2 0:46.82 php-fpm
7305 martind 20 0 494m 58m 5572 R 25.4 0.2 0:48.85 php-fpm
6706 martind 20 0 507m 62m 8060 S 13.7 0.2 1:40.55 php-fpm
7276 martind 20 0 498m 63m 5264 S 7.7 0.2 0:49.89 php-fpm
17464 redis 20 0 4328m 2.3g 888 R 7.7 7.3 7827:30 redis-server
6402 martind 20 0 511m 67m 8056 S 5.8 0.2 2:15.21 php-fpm
6405 martind 20 0 512m 69m 9204 S 5.8 0.2 2:14.32 php-fpm
6703 martind 20 0 513m 67m 8056 S 5.8 0.2 1:39.40 php-fpm
6705 martind 20 0 513m 68m 9040 S 5.8 0.2 1:36.18 php-fpm
7303 martind 20 0 493m 57m 6556 S 5.8 0.2 0:47.04 php-fpm
7304 martind 20 0 494m 59m 5264 S 5.8 0.2 0:48.70 php-fpm
7323 martind 20 0 511m 67m 7772 S 5.8 0.2 0:45.53 php-fpm
24515 nginx 20 0 123m 66m 2452 S 5.8 0.2 7231:17 nginx
6039 martind 20 0 507m 63m 8200 S 3.8 0.2 2:48.39 php-fpm
6400 martind 20 0 511m 68m 8204 S 3.8 0.2 2:13.54 php-fpm
6401 martind 20 0 510m 66m 9052 S 3.8 0.2 2:13.36 php-fpm
6404 martind 20 0 512m 68m 9048 S 3.8 0.2 2:12.75 php-fpm
Entonces, debido a que hay tantas consultas SQL que tienden a ralentizarse mucho, creo que se debe a un alto tráfico. Revisé los cronjobs (cronjobs de wordpress y cronjobs de php) y nada parece ejecutarse cuando se ralentiza, puede haber un proceso rsync ejecutándose al mismo tiempo, pero el proceso rsync se ejecuta en todo momento, así que dudo que sea causado por esto. ¿Hay algo que pueda comprobar?
Respuesta1
Análisis de ESTADO GLOBAL y VARIABLES:
Observaciones:
- Versión: 10.4.12-MariaDB
- 32 GB de RAM
- Tiempo de actividad = 19d 23:11:43
- Parece que estás ejecutando MyISAM e InnoDB.
- 240 QPS
Las cuestiones más importantes:
Cambie long_query_timea 1para que pueda capturar más consultas en el registro lento. (Ahora tiene 10 segundos; esto probablemente explica por qué encontró solo 4 consultas). Hay varias pistas de que algunas de las consultas se están ejecutando de manera ineficiente. A continuación se muestra una manera de encontrar este tipo de consultas: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
¿Por qué estás usando MyISAM? Los valores son confusos: es como si [re]construyeras un índice para una tabla MyISAM grande, pero no hicieras mucho más. En la mayoría de los casos, es mejor utilizar InnoDB.
innodb_buffer_pool_sizeProbablemente se pueda aumentar para mejorar la velocidad de consulta de InnoDB.
Tenga cuidado con el general_log: llena el disco con bastante rapidez.
La "Caché de consultas" se ejecuta de manera ineficiente. Recomiendo apagarlo por completo: query_cache_type=offy query_cache_size=0.
Max_used_connectionsLlegó a 152, lo que indica que muchos usuarios están conectados al mismo tiempo. (Esto no significa que se estuvieran ejecutando 152 consultas simultáneamente).
Detalles y otras observaciones:
Conversión de MyISAM a InnoDB
( Key_blocks_used * 1024 / key_buffer_size ) = 460 * 1024 / 128M = 0.35%-- Porcentaje de key_buffer utilizado. Alta marca de agua. -- Reduzca key_buffer_size (ahora 134217728) para evitar el uso innecesario de memoria.
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 8192M / 0.70)) / 32768M = 37.7%-- La mayor parte de la RAM disponible debería estar disponible para almacenamiento en caché. --http://mysql.rjweb.org/doc.php/memory
( general_log ) = general_log = ON-- Registro (ARCHIVO o TABLA) de todas las consultas ejecutadas. -- Apague general_log (ahora ENCENDIDO) cuando no esté en uso. Ese registro puede llenar el disco muy rápidamente.
( innodb_buffer_pool_size ) = 8,192 / 32768M = 25.0%-- % de RAM utilizada para InnoDB buffer_pool -- Establecido en aproximadamente el 70 % de la RAM disponible. (Un nivel demasiado bajo es menos eficiente; un nivel demasiado alto corre el riesgo de sufrir un intercambio).
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 8192M / 0.70)) / 32768M = 37.7%-- (métrica para juzgar el uso de RAM)
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096-- Cantidad de trabajo para los limpiadores de páginas cada segundo. -- "InnoDB: page_cleaner: el bucle previsto tomó 1000 ms ..." puede solucionarse reduciendo lru_scan_ Depth: considere 1000/innodb_page_cleaners (ahora 4). También verifique el intercambio.
( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: el bucle previsto tardó 1000 ms..." puede solucionarse reduciendo lru_scan_ Depth
( innodb_io_capacity ) = 200-- Al lavar, utilice esta cantidad de IOP. -- Las lecturas pueden ser lentas o puntiagudas.
( Innodb_log_writes ) = 43,856,157 / 1725103 = 25 /sec
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 137,804,939,264 / (1725103 / 3600) / 2 / 48M = 2.86-- Ratio -- (ver acta)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,725,103 / 60 * 48M / 137804939264 = 10.5-- Minutos entre rotaciones de registros de InnoDB. A partir de 5.6.8, esto se puede cambiar dinámicamente; asegúrese de cambiar también my.cnf. -- (La recomendación de 60 minutos entre rotaciones es algo arbitraria). Ajuste innodb_log_file_size (ahora 50331648). (No se puede cambiar en AWS).
( innodb_flush_method ) = innodb_flush_method = fsync- Cómo InnoDB debería pedirle al sistema operativo que escriba bloques. Sugiera O_DIRECT u O_ALL_DIRECT (Percona) para evitar el doble almacenamiento en búfer. (Al menos para Unix). Consulte chrischandler para conocer las advertencias sobre O_ALL_DIRECT.
( default_tmp_storage_engine ) = default_tmp_storage_engine =
( innodb_flush_neighbors ) = 1-- Una optimización menor al escribir bloques en el disco. -- Utilice 0 para unidades SSD; 1 para disco duro.
( innodb_io_capacity ) = 200- Capacidad de operaciones de E/S por segundo en disco. 100 para unidades lentas; 200 para unidades de hilado; 1000-2000 para SSD; multiplicar por el factor RAID.
( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON-- Normalmente debería estar ENCENDIDO. -- Hay casos en los que APAGADO es mejor. Consulte también innodb_adaptive_hash_index_parts (ahora 8) (después de 5.7.9) e innodb_adaptive_hash_index_partitions (MariaDB y Percona). ON ha estado implicado en fallos poco frecuentes (error 73890). 10.5.0 decidió desactivarlo por defecto.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- Si se deben registrar todos los interbloqueos. -- Si estás plagado de puntos muertos, activa esto. Precaución: si tiene muchos puntos muertos, esto puede escribir mucho en el disco.
( character_set_server ) = character_set_server = latin1
-- Los problemas con el juego de caracteres se pueden solucionar configurando carácter_set_server (ahora latin1) en utf8mb4. Ésa es la situación futura por defecto.
( local_infile ) = local_infile = ON
-- local_infile (ahora ON) = ON es un posible problema de seguridad
( Key_blocks_used * 1024 / key_buffer_size ) = 460 * 1024 / 128M = 0.35%- Porcentaje de key_buffer utilizado. Alta marca de agua. -- Reduzca key_buffer_size (ahora 134217728) para evitar el uso innecesario de memoria.
( Key_writes / Key_write_requests ) = 19,978,377 / 40284646 = 49.6%-- efectividad de key_buffer para escrituras -- Si tiene suficiente RAM, valdría la pena aumentar key_buffer_size (ahora 134217728).
( query_cache_size ) = 524,288 = 0.5MB-- Tamaño del control de calidad -- Demasiado pequeño = no sirve de mucho. Demasiado grande = demasiados gastos generales. Se recomienda 0 o no más de 50 M.
( Qcache_lowmem_prunes ) = 125,234,412 / 1725103 = 73 /sec-- Quedarse sin espacio en control de calidad -- aumentar query_cache_size (ahora 524288)
( Qcache_lowmem_prunes/Qcache_inserts ) = 125,234,412/146211296 = 85.7%-- Ratio de eliminación (frecuencia de necesidad de podar debido a que no hay suficiente memoria)
( Qcache_not_cached ) = 78,413,835 / 1725103 = 45 /sec-- Se intentó SQL_CACHE, pero se ignoró -- Repensar el almacenamiento en caché; sintonizar qcache
( Qcache_hits / Qcache_inserts ) = 37,201,050 / 146211296 = 0.254-- Proporción de aciertos e inserción -- alto es bueno -- Considere desactivar el caché de consultas.
( Qcache_hits / (Qcache_hits + Com_select) ) = 37,201,050 / (37201050 + 282029692) = 11.7%-- Proporción de aciertos -- SELECCIONES que usaron control de calidad -- Considere desactivar el caché de consultas.
( Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) ) = 37,201,050 / (37201050 + 146211296 + 78413835) = 14.2%-- Tasa de aciertos de la caché de consultas -- Probablemente sea mejor desactivar el control de calidad.
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (524288 - 78344) / 82 / 16384 = 0.332-- query_alloc_block_size vs fórmula -- Ajustar query_alloc_block_size (ahora 16384)
( Created_tmp_tables ) = 96,501,765 / 1725103 = 56 /sec-- Frecuencia de creación de tablas "temporales" como parte de SELECT complejos.
( Created_tmp_disk_tables ) = 23,539,653 / 1725103 = 14 /sec-- Frecuencia de creacióndiscoTablas "temp" como parte de SELECT complejos: aumente tmp_table_size (ahora 16777216) y max_heap_table_size (ahora 16777216). Consulte las reglas para las tablas temporales sobre cuándo se usa MEMORIA en lugar de MyISAM. Quizás cambios menores en el esquema o en las consultas puedan evitar MyISAM. Es más probable que ayuden unos mejores índices y una reformulación de las consultas.
( Created_tmp_disk_tables / Questions ) = 23,539,653 / 414140316 = 5.7%-- Porcentaje de consultas que necesitaban una tabla tmp en el disco. -- Mejores índices / Sin manchas / etc.
( Select_full_join / Com_select ) = 30,333,225 / 282029692 = 10.8%-- % de selecciones que son uniones sin índice -- Agregue índices adecuados a las tablas utilizadas en JOIN.
( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (87669877 + 27242 + 0 + 0 + 1452911 + 0) / 1725103 = 52 /sec-- escrituras/seg -- 50 escrituras/seg + vaciados de registros probablemente maximizarán la capacidad de escritura de E/S de las unidades HDD. Si tiene SSD, entonces esta métrica probablemente esté bien.
( binlog_format ) = binlog_format = MIXED-- DECLARACIÓN/FILA/MIXTO. -- ROW es preferido por 5,7 (10,3)
( long_query_time ) = 10-- Corte (Segundos) para definir una consulta "lenta". -- Sugerir 2
( Max_used_connections / max_connections ) = 152 / 151 = 100.7%-- % máximo de conexiones -- aumente max_connections (ahora 151) y/o disminuya el tiempo de espera (ahora 28800). O acelerar las consultas.
( Connections ) = 11,987,448 / 1725103 = 6.9 /sec-- Conexiones -- Aumentar el tiempo de espera (ahora 28800); utilizar la agrupación?
( Connection_errors_accept + Connection_errors_internal + Connection_errors_peer_address + Connection_errors_select + Connection_errors_tcpwrap ) = 0 + 26 + 0 + 0 + 0 = 26-- Errores de conexión distintos de max_connections. -- Para obtener más información, consulte MOSTRAR ESTADO GLOBAL COMO 'Connection_errors%'
Anormalmente pequeño:
Created_tmp_files = 0.094 /HR
innodb_spin_wait_delay = 4
Anormalmente grande:
Aria_pagecache_writes = 34 /sec
Aria_transaction_log_syncs = 25,641
Com_show_warnings = 40 /HR
Connection_errors_internal = 0.054 /HR
Handler_read_key = 85109 /sec
Handler_tmp_update = 839 /sec
Innodb_buffer_pool_read_requests = 675158 /sec
Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ) = 100.0%
Innodb_rows_updated = 356 /sec
performance_schema_max_cond_classes = 90
Cadenas anormales:
Innodb_have_punch_hole = OFF
aria_recover_options = BACKUP,QUICK
disconnect_on_expired_password = OFF
ft_boolean_syntax = + -><()~*:
innodb_fast_shutdown = 1
log_output = TABLE
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
optimizer_trace = enabled=off