1

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:

  • You should not assume that all mysql performance issues can be resolved with a configuration change. Please see the most recent answer I gave regarding this. It applies to your situation. serverfault.com/questions/925138/poor-website-performance/… – QuentinMoss Aug 8 '18 at 17:57
  • I am not assuming that all mysql issues can be resolved with a config change, but I know that the ones I am having CAN be solved with a config change, as nothing else changed in terms of variables on the server and we've tried optimizing everything else as much as possible without CPU usage changing much, until the only other option left was mySQL... and since mySQL is the one pushing CPU limits through the roof, I think that's where I should be looking at. – Crazy Serb Aug 8 '18 at 19:11
  • @CrazySerb Additional information request, please. Post on pastebin.com or here. Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report - after 24 hours UPTIME Optional very helpful information, if available includes - htop, top & mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, for server tuning analysis. – Wilson Hauck Aug 8 '18 at 19:39
  • Please post some information regarding the DB workload, statement type (ie: select vs insert), query time, etc. Withtout knowing the current workload it is impossible to answer your question. – shodanshok Aug 9 '18 at 8:05
  • 1
    I've updated the original listing with detailed stats you guys asked for... – Crazy Serb Aug 10 '18 at 16:44
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.

  • Yes, I restart the SQL service every single time. – Crazy Serb Aug 14 '18 at 0:03
  • Much better, it seems. Only had one instance of mySQL riding up the CPU cycles a few days ago for some reason, but other than that it's been pretty stable! Thank you – Crazy Serb Sep 4 '18 at 13:27
  • @CrazySerb Since your server is "Much better", would you please consider posting a review at www.mysqlservertuning.com, SHOP, select FREE My TOP 10 Global Variable Suggestions, scroll down to Review and you may post a review with NO LOGIN. Thanks – Wilson Hauck Oct 11 '18 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.