mysqld taking 800% CPU since traffic increased on my site, how to fix

mysql centos7 optimization high-load

7,575

Solution 1

Suggestions to consider for your my.cnf-ini [mysqld] section with current information analyzed.

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 appears to average ONE every 11 seconds.

For 3 minutes, SET GLOBAL innodb_print_all_deadlocks=1; then 3 minutes later, turn it OFF with 0. Any detected deadlocks will be in your error log. If prevention is possible, significant workload will go away.

Solution 2

Suggestions to consider for your my.cfg-ini [mysqld] section

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

For starters, these will help, multiple CPU's makes > 100% CPU used reasonable. Additional suggestions will be proposed after May 4 request for more details can be posted and analyzed.

7,575

Author by

Admin

Updated on September 18, 2022

Comments

Admin almost 2 years

i have centos 7 x64 64 GB RAM and 500 GB of SSD dedicated server.,

And recently cpu usage was really high,

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

so i noticed mysqld is taking much more 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

running mysql

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

status shows

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

my /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

How can i find out why is mysqld is using so much CPU ? and how can i fix it ?

i have only 1 mysql database with 8 tables in it.

SHOW VARIABLES LIKE '%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)

SHOW VARIABLES LIKE 'query%'

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)

i ran 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)

Additional details.

[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

here is my updated /etc/my.cnf https://pastebin.com/rdzhAWtC

A) SHOW GLOBAL STATUS;

https://pastebin.com/9u8SHMJ4

B) SHOW GLOBAL VARIABLES;

https://pastebin.com/ytn5RC9u

C) D) SHOW ENGINE INNODB STATUS;

https://pastebin.com/WEtuHFiU

top for most active apps

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: mysql was crashing/restarting every few hours so i added swap space and changed swappiness from 10 to default 60.

here is latest report by mysqltuner.pl

https://pastebin.com/3xMXvvZv

I added these in my.cnf as per suggested by 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

and restarted mysql.

now

top

  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

mysql status

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

Mike about 6 years

a lot of CPU is usually full table scans caused by no or bad indexes. Enable slow query log and watch that log for issues.
Admin about 6 years

@Mike i have enabled slow query log and majority slow queries are of FULL text search
Yuri Lachin about 6 years

Looks like you need to add/tune indexes. Also check mysql client's connection rate and enable persistent connection in application if not done already. Tuning mysql query cache may help too.
Mike about 6 years

Mysql isn't good at text searches.. that's why something like elasticsearch exists.
Tero Kilkanen about 6 years

One can also add application-level caching for the searches so that all work is not done by the DB.
Wilson Hauck about 6 years

@AMB Additional information request, please. Post here or in pastebin.com. RAM on your Host server complete (not edited) my.cnf-ini Text results of: A) SHOW GLOBAL STATUS; B) SHOW GLOBAL VARIABLES; C) D) SHOW ENGINE INNODB STATUS; Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, free -m for a linux/unix free memory report for analysis.
Wilson Hauck over 4 years

I keep hoping you will get in touch with me someday. Why so many START's compared to SHUTDOWN's reported by MySQLTuner?

Wilson Hauck about 6 years

@amb Please run current mysqltuner.pl and post NEW complete report. The old one is for 2h 18m. Thanks for earlier postings. Looks very interesting.
Admin about 6 years

please check added details.
Wilson Hauck about 6 years

@AMB Early in Question indication is 550 GB of SSD. df -h indicates 510M on filesystem /dev/sda1. Is /dev/root rotating conventional 438G? Also, please post results of cat /proc/meminfo to pastebin.com or here. A 15 minute SKYPE session would be helpful, if permitted for Screen Share to view my analysis and use your speakers/microphone to discuss results. Search for [email protected] or Wilson Hauck Birmingham AL USA I am at UTC -6 hrs.
Admin about 6 years

currently system load is normal as there is not much traffic, it only happens when traffic increases.
Wilson Hauck about 6 years

@amb what is your time of day, part of the world you are located in?
Admin about 6 years

Sorry i missed last reply, load average: 2.81, 3.18, 3.39 . apparently traffic haven't increased since then so i havent seen any surges in cpu usage although i am keeping watch regularly, one more thing i found is user input for fulltext search was not properly sanitized before, causing some queries to run for longer than 10 minutes and eating up resources and crashing mysql, i have fixed that too, majority queries takes less than 1 second now, so no high load right now., surely would love to do skype session i am from NC., system is stable , mysql is up from last 7 days without crash. thanks.
Admin about 6 years

i havent not applied these configs yet, as system is stable now, if i see spikes in mysql cpu usage again, ill apply it, i have applied your other answer's config. thanks
Admin about 6 years

above, status shows Threads: 57 , it was becuase the 'not properly sanitized query' hitting mysql and taking longer to return result and increasing the number of active threads, since i fixed it, i have not seen threads count more than 15 at all.
Wilson Hauck about 6 years

@AMB Thanks for the GOOD NEWS. 7 days for you is a milestone, recently. While you can, please post SHOW GLOBAL STATUS; and SHOW GLOBAL VARIABLES; for current analysis. Are you from NC USA or some other NC? My Skype offer is still available to you. Would like to discuss df -h information to understand what is SSD and/or rotating devices in your config and the boot device for the OS. Failure to continue on with Suggestions provided will not improve your general response time.
Wilson Hauck about 6 years

@AMB if you want to see significant improvement in response time, please apply the suggestions, one a day and you will be pleased with the effort. Looking forward to an update in a couple of weeks.l
Admin about 6 years

will it help fulltext search performance ? traffic has boosted again, and load average: 7.29, 8.39, 8.92
Admin about 6 years

load average: 88.57, 86.41, 56.62
Admin about 6 years

added your suggested config in my.cnf restarted mysqld and load is still load average: 83.99, 72.89, 77.70
Wilson Hauck about 6 years

@AMB so I take it, the changes are still favorable as opposed to 800%? Thanks for the upvote. Also, please post results of A) cat /proc/meminfo and B) iostat -x to pastebin.com or here.
Admin about 6 years

please check updated question with added details.
Wilson Hauck about 6 years

@AMB Please check my profile to find contact info. My SKYPE ID is listed in Network Profile.