my.cnf MariaDB configuration - hitting the CPU ceiling even with the optimizations in place

Question

I'm trying to get around this issue of mySQL hitting the ceiling in terms of CPU/Server load, as the server specs are as follows:

• List item

• 2 x 2.4GHz Octa-Core E5-2630 v3 Haswell Xeon

• 128GB RAM
• SSD Main Drive
• CloudLinux 7.5
• MariaDB 10.2.16
• LiteSpeed 5.2.8

And with the below config mySQL/MariaDB constantly hits between 80-150% CPU and is putting quite the strain on the server.

Any suggestions on what I could tweak/enable/disable/add/remove to make mySQL less of a CPU hog, as the RAM usage is steady under 25%, but the CPU goes thru the roof a lot.

[mysqld]
skip_name_resolve               = 1
default_storage_engine          = InnoDB
log_error                       = /var/lib/mysql/mysql_log.err
performance_schema              = 0
sql_mode                        = "NO_ENGINE_SUBSTITUTION"

max_allowed_packet              = 256M
max_connections                 = 400
open_files_limit                = 10000
wait_timeout                    = 120
connect_timeout                 = 120
interactive_timeout             = 180
tmpdir                          = /var/lib/mysql/tmp
tmp_table_size                  = 256M
max_heap_table_size             = 256M
max_tmp_tables                  = 300
max_statement_time              = 60
innodb_strict_mode              = OFF
#innodb_file_per_table          = ON

slow_query_log                  = ON
slow_query_log_file             = /var/lib/mysql/mysql_slow_queries.log
long_query_time                 = 10

table_open_cache                = 128
table_open_cache_instances      = 16
query_cache_type                = ON
query_cache_size                = 256M
thread_cache_size               = 40
query_cache_limit               = 300M 
key_buffer_size                 = 3G
join_buffer_size                = 512M
max_heap_table_size             = 16M

innodb_buffer_pool_size         = 64G  #80% of system memory?
innodb_log_file_size            = 2G
#innodb_log_buffer_size         = 1G
innodb_buffer_pool_instances    = 12
innodb_file_format              = Barracuda
#innodb_max_dirty_pages_pct     = 0
#innodb_io_capacity             = 400
#innodb_io_capacity_max         = 600
#innodb_flush_sync              = OFF
innodb_buffer_pool_dump_pct     = 80
innodb_flush_log_at_trx_commit  = 0 #or 2
sync_binlog                     = 0
innodb_flush_method             = O_DIRECT
innodb_thread_concurrency       = 8
innodb_read_ahead_threshold     = 8

concurrent_insert               = 1
local_infile                    = 0
group_concat_max_len            = 102400

Here are all the relevant readings that might help get a better understanding of the environment:

• SHOW GLOBAL VARIABLES: https://pastebin.com/xuwGb0Ec
• SHOW GLOBAL STATUS: https://pastebin.com/7XStDDQU
• MySQL Tuner Report: https://pastebin.com/N5595vA4
• top -c: http://prntscr.com/kh4f8b
• htop: http://prntscr.com/kh4ec9
• ulimit, df -h, iostat -x: http://prntscr.com/kh4nyz

Answer 1

Suggestions to consider for your my.cnf [mysqld] section Rate per Second = RPS

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=10000  # from 200 limit for SSD possible RPS
read_buffer_size=256K  # from 128K to reduce handler_read_next RPS
read_rnd_buffer_size=192K  # 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=100  # from 1024 to reduce CPU every SECOND see refman
innodb_thread_concurrency=0  # from 8 see dba.stackexchange Question 5666

for additional suggestions, view profile, Network profile for contact info including Skype ID.

There are many more opportunities to improve your system performance.

ulimit -n 48000 would be very helpful. After testing, make it persistent.

Remember only ONE change per day, monitor, next change in 24 hours or more. If any change is detrimental, please let me know @wilsonhauck after you remove it from your my.cnf.

Please let us know of your progress in a few days.