heavy load on mysql

Posted by payal on Server Fault See other posts from Server Fault or by payal
Published on 2012-10-20T15:33:46Z Indexed on 2012/10/20 23:05 UTC
Read the original article Hit count: 293

Filed under:

whm

i have dedicated server with very good configuation like 16 gb ram etc but i am facing heavy load from mysql however only one database is running and 5-10 pages are only running.

However whm load is always less than one but when i click on whm load it shows 20% of cpu usage by mysql and after some time it starts saying can not connect to mysql . mysql server has gone away

 1691 (Trace) (Kill)    mysql   0   
 19.2   
 2.7    /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-   error=/var/lib/mysql/server.xyz.com.err --pid-file=/var/lib/mysql/server.xyz.com.pid

i have tested static pages they are coming blezing fast but all dynamic pages which are using mysql is coming damn slow it takes years to open..

ihave checked log error file it says nothing.i have increased maximum connnection also to 1000 but still same problem is there .if i disconnect that one databasejust by changing the name of database i can see withing half hour the load of server and mysql goes down to negliglble .i have tested everything and if there are some query which can cause heavy load to server can you please list which type of query can cause heavy load on server then also for 5-10 pages it will never cause that much heavy load. i have seen server with 500 websites but was working just fine.

heavy load on mysql

Posted by payal on Server Fault See other posts from Server Fault or by payal
Published on 2012-10-20T15:52:00Z Indexed on 2012/10/20 17:03 UTC
Read the original article Hit count: 293

Filed under:

apache2

|

mysql

|

php

|

whm

i have dedicated server with very good configuation like 16 gb ram etc but i am facing heavy load from mysql i am running a music wesbite however only one database is running and 5-10 pages are only running.when i click on whm show processlist it shows only 2-3 processes

However whm load is always less than one but when i click on whm load it shows 20% of cpu usage by mysql and after some time it starts saying can not connect to mysql . mysql server has gone away

  1691 (Trace) (Kill)    mysql   0     19.2     2.7    /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-   error=/var/lib/mysql/server.xyz.com.err --pid-file=/var/lib/mysql/server.xyz.com.pid

i have tested static pages they are coming blezing fast but all dynamic pages which are using mysql is coming damn slow it takes years to open..

my.conf file is

 [mysqld]
 key_buffer = 1536M
 max_allowed_packet = 1M
 max_connections = 250
 max_user_connections = 15
 wait_timeout=40
 connect_timeout=10
 table_cache = 512
 sort_buffer_size = 2M
 read_buffer_size = 2M
 read_rnd_buffer_size = 8M
 myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
server-id       = 14
old-passwords = 1

 [mysqldump]
 quick
 max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer = 256M
 sort_buffer_size = 256M
 read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

ihave checked log error file it says nothing.i have increased maximum connnection also to 1000 but still same problem is there .if i disconnect that one databasejust by changing the name of database i can see withing half hour the load of server and mysql goes down to negliglble .i have tested everything and if there are some query which can cause heavy load to server can you please list which type of query can cause heavy load on server then also for 5-10 pages it will never cause that much heavy load. i have seen server with 500 websites but was working just fine.

Developer IT