Mysql showing 100% CPU usage

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 >> 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)](https://pastebin.com/raw/KFevUEWu)