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
i

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".)

StackExchange Database Administrators Q#294329, answer score: 3