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]
Improve this question
7
  • 3
    innodb_buffer_pool_size = 64G with 32GB RAM is suboptimal. Start with mysqltuner.pl Commented Mar 12, 2023 at 7:14
  • What exactly is the problem you are trying to solve. The CPU usage in and of itself is not an issue. Commented Mar 12, 2023 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 Commented Mar 12, 2023 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 ? Commented Mar 12, 2023 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. Commented Mar 13, 2023 at 15:11

1 Answer 1

Reset to default
3

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.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.