1

We are facing problem of high cpu usage for mysql process (almost 100%). Here is the information related to server

Server Infos:

VPS - CENTOS 7.9 kvm - 6 GB RAM - 4 Core CPU - 180 GB SSD - MariaDB

And recently cpu usage was really high,

# uptime
 13:49:37 19 days,  0 users,  load average: 33.69, 35.28, 36.05

o i noticed mysqld is taking much more CPU

PID    USER   PR  NI  VIRT  RES     SHR  S  %CPU   %MEM  TIME+
14861  mysql  20  0   9.8g  476704  2028 S  196.0   8.0  237:44.81

running mysql

# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22885
Server version: 10.3.29-MariaDB MariaDB Server

status shows

mysql  Ver 15.1 Distrib 10.3.29-MariaDB, for Linux (x86_64) using readline5.1

Connection id:          22885
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.3.29-MariaDB MariaDB Server
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 2 hours 30 min 7 sec

Threads: 82  Questions: 3797854  Slow queries: 9763  Opens: 871  Flush tables: 1  Open tables: 864  Queries per second avg: 421.655

my /etc/my.cnf

# cat /etc/my.cnf
[mysqld]
log-error=/var/lib/mysql/vps-5972435.hdfilmes.xyz.err
performance-schema=0
default-storage-engine=MyISAM
max_allowed_packet=268435456
table_definition_cache=612
key_buffer_size=1G
query_cache_min_res_unit=2000
query_cache_size=0
query_cache_type=0
query_cache_limit=1048576
join_buffer_size=1M
tmp_table_size=64M
max_heap_table_size=64M
performance_schema = ON
innodb_file_per_table=1
innodb_buffer_pool_size=4600M
innodb_log_buffer_size=212M
innodb_buffer_pool_instances=5
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_log_file_size=642M
sort_buffer_size=2M
read_buffer_size=1M
read_rnd_buffer_size=1M
sql_mode=NO_ENGINE_SUBSTITUTION
character-set-server=utf8
collation-server=utf8_general_ci
open_files_limit=40000
table_open_cache=2200
wait_timeout=28800
interactive_timeout=28800
max_connections=1000

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

SHOW VARIABLES LIKE '%buffer%';

MariaDB [(none)]> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| aria_pagecache_buffer_size          | 134217728      |
| aria_sort_buffer_size               | 268434432      |
| 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        | 5              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 5368709120     |
| innodb_change_buffer_max_size       | 25             |
| innodb_change_buffering             | all            |
| innodb_log_buffer_size              | 222298112      |
| innodb_sort_buffer_size             | 1048576        |
| join_buffer_size                    | 1048576        |
| join_buffer_space_limit             | 2097152        |
| key_buffer_size                     | 1073741824     |
| mrr_buffer_size                     | 262144         |
| myisam_sort_buffer_size             | 134216704      |
| net_buffer_length                   | 16384          |
| preload_buffer_size                 | 32768          |
| read_buffer_size                    | 1048576        |
| read_rnd_buffer_size                | 1048576        |
| sort_buffer_size                    | 2097152        |
| sql_buffer_result                   | OFF            |
+-------------------------------------+----------------+
28 rows in set (0.024 sec)

SHOW VARIABLES LIKE 'query%';

MariaDB [(none)]> SHOW VARIABLES LIKE 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 16384   |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 2000    |
| query_cache_size             | 0       |
| query_cache_strip_comments   | OFF     |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 24576   |
+------------------------------+---------+
8 rows in set (0.002 sec)

i ran mysqltuner.pl

# ./mysqltuner.pl
 >>  MySQLTuner 1.7.21 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.3.29-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/lib/mysql/vps-5972435.hdfilmes.xyz.err exists
[--] Log file: /var/lib/mysql/vps-5972435.hdfilmes.xyz.err(840M)
[OK] Log file /var/lib/mysql/vps-5972435.hdfilmes.xyz.err is not empty
[!!] Log file /var/lib/mysql/vps-5972435.hdfilmes.xyz.err is bigger than 32 Mb
[OK] Log file /var/lib/mysql/vps-5972435.hdfilmes.xyz.err is readable.
[!!] /var/lib/mysql/vps-5972435.hdfilmes.xyz.err contains 137 warning(s).
[!!] /var/lib/mysql/vps-5972435.hdfilmes.xyz.err contains 15930 error(s).
[--] 30 start(s) detected in /var/lib/mysql/vps-5972435.hdfilmes.xyz.err
[--] 1) 2021-06-15 11:32:30 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2021-06-15 10:27:44 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2021-06-14 21:51:45 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2021-06-13 23:22:14 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2021-06-13 22:59:25 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2021-06-13 22:38:14 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2021-06-13 22:32:08 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2021-06-13 22:30:32 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2021-06-13 22:19:49 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2021-06-13 22:19:20 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 18 shutdown(s) detected in /var/lib/mysql/vps-5972435.hdfilmes.xyz.err
[--] 1) 2021-06-15 11:32:27 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2021-06-15 10:27:40 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2021-06-14 21:50:59 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2021-06-13 23:21:27 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2021-06-13 22:37:33 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2021-06-13 22:32:05 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2021-06-13 22:30:21 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2021-06-13 22:11:29 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2021-06-13 22:01:52 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2021-06-13 21:56:42 0 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 244.2M (Tables: 82)
[--] Data in InnoDB tables: 642.9M (Tables: 59)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2h 41m 46s (4M q [422.906 qps], 24K conn, TX: 11G, RX: 530M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 5.7G
[--] Max MySQL memory    : 262.2G
[--] Other process memory: 0B
[--] Total buffers: 6.4G global + 261.3M per thread (1000 max threads)
[--] P_S Max memory usage: 622M
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 26.6G (470.10% of installed RAM)
[!!] Maximum possible memory usage: 262.2G (4624.61% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (10K/4M)
[OK] Highest usage of available connections: 7% (77/1000)
[OK] Aborted connections: 0.02%  (5/24969)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 952K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 90% (875K on disk / 968K total)
[OK] Thread cache hit rate: 99% (77 created / 24K connections)
[OK] Table cache hit rate: 99% (874 open / 881 opened)
[OK] table_definition_cache(612) is upper than number of tables(406)
[OK] Open file limit used: 0% (324/40K)
[OK] Table locks acquired immediately: 99% (82K immediate / 82K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 622.1M
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 4 thread(s).
[--] Using default value is good enough for your version (10.3.29-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.6% (199M used / 1B cache)
[OK] Key buffer size / total MyISAM indexes: 1.0G/66.8M
[OK] Read Key buffer hit rate: 99.9% (4M cached / 3K reads)
[OK] Write Key buffer hit rate: 100.0% (46 cached / 46 writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 5.0G/642.9M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 642.0M * 2/5.0Gshould be equal to 25%
[OK] InnoDB buffer pool instances: 5
[--] Number of InnoDB Buffer Pool Chunk : 40 for 5 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: 100.00% (13859785816 hits/ 13859814226total)
[!!] InnoDB Write Log efficiency: 47.62% (15697 hits/ 32965 total)
[OK] InnoDB log waits: 0.00% (0 waits / 17268 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[!!] Aria pagecache hit rate: 92.7% (12M cached / 875K reads)

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

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

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

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

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    /var/lib/mysql/vps-5972435.hdfilmes.xyz.err is > 32Mb, you should analyze why or implement a rotation log strategy such as logrotate!
    Control warning line(s) into /var/lib/mysql/vps-5972435.hdfilmes.xyz.err file
    Control error line(s) into /var/lib/mysql/vps-5972435.hdfilmes.xyz.errfile
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)

Additional details.

# free -h
              total        used        free      shared  buff/cache   available
Mem:           5.7G        5.4G        134M        8.7M        180M 94M
Swap:          4.0G        3.2G        816M

top for most active apps

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+
14861 mysql     20   0    9.8g 493096   1892 S 162.9  8.3 287:12.22
32657 pitaraf+  20   0  517220  51044   4196 R  15.2  0.9   1:00.54
32642 pitaraf+  20   0  490076  24728   4184 R  12.3  0.4   1:09.38
31514 pitaraf+  20   0  519328  52932   4076 R  11.9  0.9   1:40.88
32656 pitaraf+  20   0  487976  25284   4068 R  11.9  0.4   0:55.19
32623 pitaraf+  20   0  517168  54448   4712 R  11.3  0.9   1:21.76
  441 pitaraf+  20   0  506356  42752   4172 R  10.9  0.7   0:54.74
  444 pitaraf+  20   0  506408  42188   4060 R  10.9  0.7   0:48.77
  520 pitaraf+  20   0  504452  38544   4084 R  10.9  0.6   1:10.51
32750 pitaraf+  20   0  506464  42336   4128 R  10.9  0.7   1:01.96
  524 pitaraf+  20   0  490072  25860   4728 R  10.6  0.4   0:56.50
32724 pitaraf+  20   0  479908  14852   4024 R  10.6  0.2   1:02.63
 3519 pitaraf+  20   0  516988  53432   4164 R  10.3  0.9   0:07.88
30953 pitaraf+  20   0  525536  58888   4012 R  10.3  1.0   2:05.08
32618 pitaraf+  20   0  513192  48092   4096 R   9.9  0.8   1:05.61
32749 pitaraf+  20   0  529428  63972   4720 R   9.6  1.1   0:43.49
  447 pitaraf+  20   0  492160  27064   4060 R   8.9  0.5   0:59.22

ulimit -a

# ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) unlimited
file size               (blocks, -f) unlimited
pending signals                 (-i) unlimited
max locked memory       (kbytes, -l) unlimited
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1048576
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) unlimited
real-time priority              (-r) unlimited
stack size              (kbytes, -s) unlimited
cpu time               (seconds, -t) unlimited
max user processes              (-u) unlimited
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

df -h

# df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        2.9G     0  2.9G   0% /dev
tmpfs           2.9G     0  2.9G   0% /dev/shm
tmpfs           2.9G  113M  2.8G   4% /run
tmpfs           2.9G     0  2.9G   0% /sys/fs/cgroup
/dev/sda1       178G   98G   72G  58% /
/dev/loop0      3.9G   16M  3.6G   1% /tmp

SHOW FULL PROCESSLIST - TXT (PasteBin)

4
  • Your SHOW FULL PROCESSLIST is not available, at this time. Jun 16 at 15:59
  • Additional information request. Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; F) complete www.MySQLTuner.pl (perl) report or similar after 24 h uptime. AND Optional very helpful information, if available includes - htop OR top for most active apps, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Jun 16 at 16:15
  • MariaDB 10.3.29 became GA May 7, 2021, could it be you have an edge case that was not tested before this version released recently? Many people will not try a version with less than 90 days in the wild to avoid reporting problems and waiting on resolutions. Jun 16 at 16:30
  • Any chance you could post additional information requested Jun 16, 21 at 16:15 to allow workload analysis? Jul 1 at 0:20
2

High CPU means inefficient queries. Often the cure is as simple as a carefully chosen INDEX.

Find the slow queries and present them here, together with SHOW CREATE TABLE and EXPLAIN SELECT ...

More: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

As for the settings...

If you have 6GB of RAM, this is too tight:

key_buffer_size=1G   -- change to 100M
innodb_buffer_pool_size=4600M  -- change to 2G

You should migrate your tables from Engine=MyISAM to Engine=InnoDB.

(These changes will not explain high CPU, but would explain high I/O, namely "swapping".)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.