mysqld tendo 800% de CPU desde que o tráfego aumentou em meu site, como consertar

1

eu tenho centos 7 x64 64 GB RAM e 500 GB do servidor dedicado SSD.

E recentemente o uso da CPU foi muito alto,

$ uptime
 13:50:30 up 169 days,  1:53,  1 user,  load average: 37.48, 37.64, 33.60

então eu notei que o mysqld está levando muito mais CPU

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
10252 mysql     20   0 69.680g 0.040t  17164 S 792.0 65.4 366:19.06 mysqld

executando o mysql

Server version: 5.7.21-log MySQL Community Server (GPL)

status mostra

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using  EditLine wrapper

Connection id:          248236
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.21-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 1 hour 15 min 34 sec

Threads: 57  Questions: 1261965  Slow queries: 5923  Opens: 323  Flush tables: 1  Open tables: 297  Queries per second avg: 278.333

meu /etc/my.cnf

$ cat /etc/my.cnf
[mysqld]

bind-address = 127.0.0.1

innodb-ft-min-token-size = 1
innodb-ft-enable-stopword = 0

innodb_buffer_pool_size = 40G

#innodb_io_capacity = 2000
#innodb_read_io_threads = 64
#innodb_thread_concurrency = 0
#innodb_write_io_threads = 64

#character-set-server = utf8

max_connections = 2000

slow-query-log = on
slow-query-log-file = /var/log/mysql-slow-queries1.log
long_query_time = 1

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

general-log = /var/log/mysqld.log
log-error = /var/log/mysqld.error.log

pid-file=/var/run/mysqld/mysqld.pid

Como posso descobrir por que o mysqld está usando muita CPU? e como posso resolver isso?

Eu tenho apenas 1 banco de dados mysql com 8 tabelas.

MOSTRAR VARIÁVEIS COMO '% buffer%'

mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| bulk_insert_buffer_size             | 8388608        |
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 42949672960    |
| innodb_change_buffer_max_size       | 25             |
| innodb_change_buffering             | all            |
| innodb_log_buffer_size              | 16777216       |
| innodb_sort_buffer_size             | 1048576        |
| join_buffer_size                    | 262144         |
| key_buffer_size                     | 8388608        |
| myisam_sort_buffer_size             | 8388608        |
| net_buffer_length                   | 16384          |
| preload_buffer_size                 | 32768          |
| read_buffer_size                    | 131072         |
| read_rnd_buffer_size                | 262144         |
| sort_buffer_size                    | 262144         |
| sql_buffer_result                   | OFF            |
+-------------------------------------+----------------+
24 rows in set (0.02 sec)

MOSTRAR VARIÁVEIS COMO "consulta%"

mysql> SHOW VARIABLES LIKE 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
7 rows in set (0.00 sec)

eu corri mysqltuner.pl

$ ./mysqltuner.pl
 >>  MySQLTuner 1.7.9 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 5.7.21-log
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.error.log(2M)
[OK] Log file /var/log/mysqld.error.log exists
[OK] Log file /var/log/mysqld.error.log is readable.
[OK] Log file /var/log/mysqld.error.log is not empty
[OK] Log file /var/log/mysqld.error.log is smaller than 32 Mb
[!!] /var/log/mysqld.error.log contains 705 warning(s).
[!!] /var/log/mysqld.error.log contains 38 error(s).
[--] 144 start(s) detected in /var/log/mysqld.error.log
[--] 1) 2018-04-22T16:37:18.163712Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-04-22T13:17:09.928956Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-04-22T05:16:06.966510Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-04-21T23:54:21.362840Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-04-21T07:49:49.997197Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-04-20T22:51:33.487738Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-04-20T14:24:28.470530Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-04-20T07:49:56.282519Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2018-04-20T05:19:46.045516Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2018-04-19T20:21:42.044572Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4 shutdown(s) detected in /var/log/mysqld.error.log
[--] 1) 2018-01-28T16:34:30.099495Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2017-11-16T02:11:54.584047Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2017-11-15T15:12:49.597191Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2017-11-13T06:13:48.319136Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 1G (Tables: 2)
[--] Data in InnoDB tables: 51G (Tables: 7)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] Bug #80860 MySQL 5.7: Avoid testing password when validate_password is activated

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2h 18m 22s (1M q [188.934 qps], 463K conn, TX: 2G, RX: 207M)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is disabled
[--] Physical Memory     : 62.9G
[--] Max MySQL memory    : 42.2G
[--] Other process memory: 1.2G
[--] Total buffers: 40.0G global + 1.1M per thread (2000 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 40.2G (63.87% of installed RAM)
[OK] Maximum possible memory usage: 42.2G (67.18% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 1% (18K/1M)
[OK] Highest usage of available connections: 5% (109/2000)
[OK] Aborted connections: 0.00%  (1/463809)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 640K selects)
[OK] Query cache prunes per day: 0
[!!] Sorts requiring temporary tables: 11% (23K temp sorts / 194K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 14 total)
[OK] Thread cache hit rate: 99% (333 created / 463K connections)
[OK] Table cache hit rate: 91% (360 open / 393 opened)
[OK] Open file limit used: 0% (13/5K)
[OK] Table locks acquired immediately: 100% (104 immediate / 104 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (1M used / 8M cache)
[!!] Key buffer size / total MyISAM indexes: 8.0M/393.5M
[!!] Read Key buffer hit rate: 50.0% (6 cached / 3 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 40.0G/51.5G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.234375 %): 48.0M * 2/40.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 320 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.97% (2966978249 hits/ 2967941460 total)
[!!] InnoDB Write Log efficiency: 4554.14% (12114 hits/ 266 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12380 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysqld.error.log file
    Control error line(s) into /var/log/mysqld.error.log file
    MySQL started within last 24 hours - recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: https://dev.mysql.com/doc/refman/5.7/en/innodb-data-log-reconfiguration.html
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    sort_buffer_size (> 256K)
    read_rnd_buffer_size (> 256K)
    key_buffer_size (> 393.5M)
    innodb_buffer_pool_size (>= 51G) if possible.
    innodb_log_file_size should be (=5G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances(=40)

Detalhes adicionais.

[root@ ]# free -h
              total        used        free      shared  buff/cache   available
Mem:            62G         57G        592M        755M        4.9G        3.9G
Swap:          2.0G        2.0G         20K

aqui está minha /etc/my.cnf https://pastebin.com/rdzhAWtC

atualizada

A) MOSTRE ESTADO GLOBAL;

https://pastebin.com/9u8SHMJ4

B) MOSTRAR VARIÁVEIS GLOBAIS;

https://pastebin.com/ytn5RC9u

C) D) EXIBE O STATUS DO MOTOR INNODB;

https://pastebin.com/WEtuHFiU

topo da maioria dos aplicativos ativos

https://pastebin.com/hBzN8Kr4

ulimit -a

[root@]# 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) 256907
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65535
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 256907
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

df -h

[root@]# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/root       438G  130G  286G  32% /
devtmpfs         32G     0   32G   0% /dev
tmpfs            32G     0   32G   0% /dev/shm
tmpfs            32G  2.7G   29G   9% /run
tmpfs            32G     0   32G   0% /sys/fs/cgroup
/dev/sda1       510M  2.6M  508M   1% /boot/efi
tmpfs           6.3G     0  6.3G   0% /run/user/0
tmpfs           6.3G     0  6.3G   0% /run/user/1000

update: o mysql estava travando / reiniciando a cada poucas horas, então eu adicionei swap e mudei o swappiness de 10 para o padrão 60.

aqui é o último relatório por mysqltuner.pl

https://pastebin.com/3xMXvvZv

Eu adicionei estes em my.cnf como sugerido por Wilson Hauck

key_cache_age_threshold=64800  # from 300 second discard, only to RD again
innodb_lru_scan_depth=100  # from 1024 to reduce CPU load see v8 refman
innodb_log_buffer_size=1G  # from 16M to better support 3G per hour
innodb_log_file_size=4G  # from 50M before rotation to next logfile.
innodb_io_capacity=1000  # from 200 to 'let it run'
innodb_change_buffer_max_size=10  # from 25 to reduce BP set-aside RAM
sort_buffer_size=2M  # from 256K to reduce sort_merge_passes of 1m+
max_write_lock_count=16  # from a HUGE number to allow RD after nn lcks

e reiniciado o mysql.

agora

topo

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
10957 mysql     20   0   63.1g  42.8g  17472 S 768.8 68.1   3560:16 mysqld

free -h

              total        used        free      shared  buff/cache   available
Mem:            62G         54G        592M         27M        7.9G        7.6G
Swap:           34G        3.4G         30G

df -h

Filesystem      Size  Used Avail Use% Mounted on
/dev/root       438G  132G  284G  32% /
devtmpfs         32G     0   32G   0% /dev
tmpfs            32G     0   32G   0% /dev/shm
tmpfs            32G  3.2G   29G  11% /run
tmpfs            32G     0   32G   0% /sys/fs/cgroup
/dev/sda1       510M  2.6M  508M   1% /boot/efi
tmpfs           6.3G     0  6.3G   0% /run/user/0
tmpfs           6.3G     0  6.3G   0% /run/user/1000

cat / proc / meminfo

https://pastebin.com/WshJiLP3

iostat -x

Linux 4.9.58-xxxx-std-ipv6-64 (ns540545)        06/12/2018      _x86_64_        (8 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.50    0.08    1.03    4.37    0.00   76.02

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               2.20    79.15   31.77  238.97  1285.23  5032.09    46.67     0.22    0.82    1.46    0.74   0.38

status do mysql

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

Connection id:          1650513
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.22 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 7 hours 39 min 28 sec

Threads: 102  Questions: 16743958  Slow queries: 0  Opens: 495  Flush tables: 1  Open tables: 486  Queries per second avg: 607.369
    
por AMB 23.04.2018 / 03:43

2 respostas

2

Sugestões a considerar para sua seção my.cfg-ini [mysqld]

max_connections=400  # from 2000 - max_used_connections was 109 in 2 hours
thread_cache_size=100  # from autocalc CAP at 100 to avoid OOM & reduce threads_created
query_cache_size=0  # to disable QC - it will not be available in V8
log_warnings=2  # for addl aborted_connection information

Para começar, isso ajudará várias origens de CPU > 100% da CPU usada razoavelmente. Sugestões adicionais serão propostas depois que o pedido de mais detalhes possa ser publicado e analisado em 4 de maio.

    
por 08.05.2018 / 12:57
1

Sugestões para considerar sua seção my.cnf-ini [mysqld] com a informação atual analisada.

key_cache_age_threshold=64800  # from 300 second discard, only to RD again
innodb_lru_scan_depth=100  # from 1024 to reduce CPU load see v8 refman
innodb_log_buffer_size=1G  # from 16M to better support 3G per hour
innodb_log_file_size=4G  # from 50M before rotation to next logfile.
innodb_io_capacity=1000  # from 200 to 'let it run'
innodb_change_buffer_max_size=10  # from 25 to reduce BP set-aside RAM
sort_buffer_size=2M  # from 256K to reduce sort_merge_passes of 1m+
max_write_lock_count=16  # from a HUGE number to allow RD after nn lcks

handler_rollback parece ter uma média a cada 11 segundos.

Por 3 minutos, SET GLOBAL innodb_print_all_deadlocks = 1; em seguida, 3 minutos depois, desative-o com 0. Todos os deadlocks detectados estarão em seu log de erros. Se a prevenção for possível, uma carga de trabalho significativa desaparecerá.

    
por 10.05.2018 / 15:42