Server High Load On Mysql

hi we have problem on our server database high load problem on our server. some time 100% usage of cpu
mysql . we use innodb and myislm both on our database . we have too many queries selected and inserted at a time.
our server detail
Intel Xeon E3-1230 3.20Ghz
8GB Ram
our my.cnf file this

local-infile=0
max_connections = 600
max_user_connections=1000
key_buffer_size = 1G
myisam_sort_buffer_size = 64M
read_buffer_size = 64M
table_open_cache = 5000
thread_cache_size = 384
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 128M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
concurrent_insert = 2
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 5M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65535
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
max_write_lock_count = 8
slow_query_log
log-error
external-locking=FALSE
open_files_limit=50000

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 1G
sort_buffer = 384M
read_buffer = 512M
write_buffer = 256M

[myisamchk]
key_buffer = 1G
sort_buffer = 384M
read_buffer = 512M
write_buffer = 256M

#### Per connection configuration ####
sort_buffer_size = 1M
join_buffer_size = 1M
thread_stack = 192K

Code (markup):

please tell us what changes on it .

 

You could try to tune your MySQL performance using MySQL tuner script. MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. 

Download the script using:

wget --no-check-certificate https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

Then, run the following commands:

chmod +x mysqltuner.pl
perl mysqltuner.pl

More information on this you can find at mysqltuner.com . Also, you may find this article useful.

 

One of the widely used method to reduce the server load and memory consumption on servers is apachebooster. If your server is based on cpanel, then you may consider using the apachebooster.

 

You might want to have your code reviewed, and decrease max_connections, key_buffer, and raise query_cache_limit to half of query_cache_size. Also do 'explain' runs on your SQL queries to see if it's using indexes.

 

did you tried to learn which scripts eating most of the CPU? Example i have in WHM control panel section called "Daily Process Log"
when you know this, maybe you can take a look what these do with mysql?

 

thanks for all . actually here is not load with script . load problem mysql . actually one time too many query insert and selected . then processor high load
we are using webuzo panel . when load high above 100 percent then server crash mysql database. may this problem of webuzo panel if yes then tell me cpanel best for high load

 

No, it has nothing to do with the control panel. You should look at the queries that are running and see if they are actually required.. sometimes some queries are placed in scripts which shouldn't be there and when a visitor request that web page, the query is executed but isnt of any importance. This is where a developer can optimize as well as place queries where they are required so it doesnt add up to resource usage. This also happens if some 3rd party plugins or such are installed on the website.

 

can its possible mysql load on ram not a cpu load. we want when MySQL queries executes its all load on our ram

 

Yes, it is possible and in that case, you should tweak the mysql parameters to lessen the RAM usage although, if some queries are not optimized properly, tweaking of parameters is of little help.

 

how to can you send me any tutorial

 

There are lots of tutorials available on the net of tweaking the parameters but most importantly, you must know what exactly is causing the problem so to tweak the parameters properly.

 

try to see slow query logs to find out the bottleneck. see the output mysql show full processlist