MariaDb High Cpu %300

Hi mariadb consumes a lot of cpu. The settings are here. Server features CPU 6 cores/ 12 threads @ 3.4 GHz (4.8 GHz)

RAM 32GB DDR4 • ECC Server Grade

Disk Drive 2 x 480GB SSD SATA Software RAID

[client-server]

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

key_buffer_size = 32M  # from 23G whoa, likely an accident. only used by ISAM
  



back_log = 5
max_connections = 300
wait_timeout = 64
max_connect_errors = 10

table_open_cache = 2048
max_allowed_packet = 2M
binlog_cache_size = 512M
max_heap_table_size = 512M



thread_cache_size = 0
thread_concurrency = 8
thread_stack = 240K

query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = InnoDB
transaction_isolation = REPEATABLE-READ
tmp_table_size = 256M

log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 2

server-id = 1

# INNODB options
innodb_buffer_pool_size = 64G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:10M:autoextend

innodb_write_io_threads = 8
innodb_read_io_threads = 8

innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 1GB
innodb_change_buffering = all
innodb_change_buffer_max_size = 25

innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 256

unix_socket=OFF
[mysqldump]
quick
max_allowed_packet = 50M

[mysql]
no-auto-rehash

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192




thread_handling=pool-of-threads  # from one-thread-per-connection for scalability
max_heap_table_size=512M  # from 16M to increase RAM capacity
tmp_table_size=512M # from 256K 2 be = max_heap_table_size and reduce created_tmp_disk_tables
innodb_io_capacity=5000  # from 200 limit for SSD possible RPS
read_buffer_size=256K  # from 128K to reduce handler_read_next RPS
read_rnd_buffer_size=256K  # from 256K to reduce handler_read_rnd_nxt RPS
aria_pagecache_division_limit=50  # from 100 for WARM cache
key_cache_division_limit=50  # from 100 for WARM cache
innodb_buffer_pool_instances=8  # from 12 for your current data volume
innodb_buffer_pool_size=24G  # from 64G to support 11G of data with room to grow
innodb_lru_scan_depth=200  # from 1024 to reduce CPU every SECOND see refman
innodb_thread_concurrency=0  # from 8 see dba.stackexchange Question 5666
[client-server]

It could be normal that MySQL has 300% CPU usage.

What is the real problem? If you are trying to troubleshoot a slow query, you should log them, then analyze them. Activate slow_query_log, run SHOW FULL PROCESSLIST and SHOW ENGINE INNODB STATUS\G to have an overview of MySQL, analyze the slow log with mysqldumpslow and pt-query-digest, use EXPLAIN FORMAT=TREE and EXPLAIN ANALYZE to profile a query.

As @AlexD mentioned, innodb_buffer_pool_size should be lower than available memory when the MySQL is stopped. E.g. if you stop MySQL and you have 28GB available, you could set innodb_buffer_pool_size to around 25GB. Do not copy paste MySQL configs from Internet, without understanding them and without verifying that they are pertinent for your use case (OLAP vs OLTP, more writes or more reads...), the DB storage engines used (InnoDB, MyISAM ...), and your version of MySQL.