0

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]
  • 3
    innodb_buffer_pool_size = 64G with 32GB RAM is suboptimal. Start with mysqltuner.pl – AlexD Mar 12 '23 at 07:14
  • What exactly is the problem you are trying to solve. The CPU usage in and of itself is not an issue. – user9517 Mar 12 '23 at 11:47
  • @user9517 Mariadb consumes a lot of cpu. I changed the settings but it didn't fix it. I want to get these settings right – Fuat Yavuz Mar 12 '23 at 11:57
  • CPU is there to be consumed. You have not presented an issue to be solved, you have presented an observation. What exactly is the problem you are trying to solve ? – user9517 Mar 12 '23 at 17:29
  • Additional DB information request, please. Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*), sum(data_length), sum(index_length), sum(data_free) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions. – Wilson Hauck Mar 13 '23 at 15:11
  • Post TEXT data on justpaste.it and share the links. Additional very helpful OS information includes - please, htop 1st page, if available, TERMINATE, top -b -n 1 for most active apps, top -b -n 1 -H for details on your mysql threads memory and cpu usage, ulimit -a for list of limits, iostat -xm 5 3 for IOPS by device & core/cpu count, df -h for Used - Free space by device, df -i for inode info by device, free -h for Used - Free Mem: and Swap:, cat /proc/meminfo includes VMallocUused, for server workload tuning analysis to provide suggestions. – Wilson Hauck Mar 13 '23 at 15:12
  • Please post your complete my.cnf what you have is all fouled up in the section usage. The last twelve lines following section [mysqld_safe] BELONG at the end of [mysqld] section, before [mysqldump] section. Then after 24 hours of uptime, please post data requested on 3/13/2023, please. – Wilson Hauck Mar 13 '23 at 15:28

1 Answers1

2

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.

Mircea Vutcovici
  • 17,619
  • 4
  • 56
  • 83