High cpu load after tuning

Nabbello

Active Member
Nov 9, 2016
38
5
8
italy
cPanel Access Level
Root Administrator
Hello Guys, after tuning MariaDb with mysqltuner i have high server load on CPU, ram seems to be ok. I only have this 2 suggestion.

Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
join_buffer_size (> 6.0G, or always use indexes with JOINs)
table_open_cache (> 18000)

Server spec
Intel Xeon V6
64gb ram (actually user like 32gb) before tuning about 15/16gb
----------------------------------------------------
Some load avarage:
05:00:02 PM 56 878 12.78 10.82 9.26 0
05:10:01 PM 44 868 12.92 11.75 10.59 1
05:20:01 PM 43 856 9.01 10.47 10.54 3
05:30:01 PM 27 842 12.06 11.97 11.33 1
----------------------------------------------------

Here is my.cfg :
Code:
[client-server]
!includedir /etc/my.cnf.d

[mysqld]
log-error = /var/lib/mysql/titanium.err
performance-schema=ON
default-storage-engine=MyISAM
datadir=/var/lib/mysql
local-infile=0
innodb_file_per_table=1
innodb_buffer_pool_size=20G ## lower than server ram ( for 8GB ram+ only )
innodb_buffer_pool_instances=20 ## same with innodb_buffer_pool_size
innodb_log_file_size=2G
query_cache_size=0      
query_cache_type=0
max_user_connections=50
max_connections=200
interactive_timeout=100
wait_timeout=100
connect_timeout=100
thread_cache_size=128
key_buffer_size=8192M ## 128MB for every 1GB of RAM
join_buffer_size=6G
max_connect_errors=20
max_allowed_packet=268435456
table_open_cache=18000 ## should be modified depending on the tables
table_definition_cache=26000
open_files_limit=240239 ## should be modified depending on the opened files
tmp_table_size=1024M
max_heap_table_size=1024M
sort_buffer_size=4M ## 1MB for every 1GB of RAM
read_buffer_size=4M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=4M  ## 1MB for every 1GB of RAM
myisam_sort_buffer_size=128M
server_id=1
slow_query_log=1
slow_query_log_file=/var/log/mysql-slow-queries.log
long_query_time=2
collation_server=latin1_general_ci
sql-mode=

[mysqldump]  
max_allowed_packet=128M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer_size=128M
sort_buffer_size=128M
read_buffer_size=32M
write_buffer_size=32M

[myisamchk]
key_buffer_size=128M
sort_buffer_size=128M          
read_buffer_size=32M
write_buffer_size=32M

[mysqlhotcopy]
interactive_timeout
 
Last edited by a moderator:

cPRex

Jurassic Moderator
Staff member
Oct 19, 2014
7,519
1,029
313
cPanel Access Level
Root Administrator
Hey there! It's important to note that just because the values in the my.cnf are high, they won't actually be used unless there is MySQL traffic on the server. Can you check the output of the "mysqladmin proc status" command to see if you can determine if a certain query or website is causing the increased load?