Need help to reduce mysqld very high loading

pachiko

Active Member
Nov 11, 2013
30
0
6
cPanel Access Level
Root Administrator
Hello,
M server's load is very high, i have noticed that MYSQL process causing high cpu usage.
by runing MySQL tuner script , it suggests to adjust those variables:
Code:
Variables to adjust:
    query_cache_size (> 200M) [see warning above]
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 10000)
    innodb_buffer_pool_size (>= 468M)
This is my my.cnf configuration:

Code:
[myisamchk]
read_buffer=2M
sort_buffer_size=256M
write_buffer=2M
key_buffer_size=256M
[mysqld]
myisam_sort_buffer_size=64M
read_rnd_buffer_size=8M
log-slow-queries
query_cache_size=200M
thread_cache_size=8
max_allowed_packet=32M
table_open_cache=10000
local-infile=0
max_connections=400
key_buffer_size=384M
read_buffer_size=2M
server-id=1
datadir="/home/var_mysql/mysql"
thread_concurrency=4
sort_buffer_size=2M
port=3306
bind-address=127.0.0.1
skip-external-locking
socket="/var/lib/mysql/mysql.sock"
default-storage-engine=MyISAM
innodb_file_per_table=1
open_files_limit=50000
[client]
port=3306
socket="/var/lib/mysql/mysql.sock"
[mysqldump]
max_allowed_packet=16M
quick
[mysql]
no-auto-rehash
[mysqlhotcopy]
interactive-timeout
Thanks.
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,913
2,201
363
Hello :)

Check the MySQL process list using the "mysqladmin processlist" command to see if you can find any databases using excessive resources.

Thank you.
 

pachiko

Active Member
Nov 11, 2013
30
0
6
cPanel Access Level
Root Administrator
Hello,
I executed the commad : mysqladmin processlist, and of course there is a database of one client that use excessive ressouces of the server
any recommendations to resolve the problem with this constumer?
 

cPanelMichael

Administrator
Staff member
Apr 11, 2011
47,913
2,201
363
You may need to work with the client and see if there is anything they can do to reduce their MySQL usage. It could be an issue with the specific script they are using, or it could indicate the client needs to upgrade to their own VPS or dedicated server.

Thank you.
 

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
You obiously have wrong config of mysql
especially becouse of
innodb_buffer_pool_size (>= 468M)



But you didnt post FULL result of mysqltuner, once you post complete result, I can help you adjust my.cnf