Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
You can make mysql log slow queries to find out which ones are causing trouble. Then use explain to understand what's happening and modify the query or add necessary indexes. Another useful tool for overall mysql config is the mysqltuner script. –  user6803 Oct 20 '12 at 17:40
1  
MySQL is advanced storage system you should not mess around like this. You make sure that it's properly scaled up to handle additional load, like it has indexes as well it runs innodb and that the memory is OK as well it's I/O, plus the underlying hardware is not too slow. –  Andrew Smith Oct 20 '12 at 20:18

2 Answers 2

How large is your database ? Which storage engine are you using ? They are mainly two: myIsam (the default one) and InnoDB. myIsam doesn't handle concurrent write very well, and delay can occur while inserting/updating lines.

If your using myIsam, you may also try myisamchk, described here: http://dev.mysql.com/doc/refman/5.5/en/myisamchk.html

You probably want to use the -d option to gather information about your tables before running OPTIMIZE statement.

The database files may also be hosted on a networked path, or on an bogus drive. It the later case, check your syslog for drive error.

share|improve this answer
    
my databases are mostly myisam .my database size is 1 gb and yes i am inserting and using update statement for all tasks –  payal Oct 21 '12 at 5:21

In that case, if they are two concurrent accesses to your database (select and update), a deadlock may appear.

I strongly suggest to use innoDB engine in that case. You'll have to setup an innoDB file storage (in my.cnf, see "innodb_data_file_path"), then convert your tables with

ALTER TABLE 'YOURTABLENAME' ENGINE=InnoDB;

Beware that innoDB uses a lot more space than MyIsam. A 1GB table may probably eats 2GB InnoDB data space.

InnoDB is slower on read request, but it handles concurrent update/select flawlessy.

Edit: If you already have an "innodb_data_file_path" line in your innoDB, altering it may break your innoDB dataspace. If none of your tables uses that storage it is ok to create new files from scratch, otherwise, you may don't need to update this line, and rely on autoextend, or you may ADD files, like that:

innodb_data_file_path=ibdata1:10M;ibdata2:2000M:autoextend

This will add a file with 2GB initial file size, that may grow)

share|improve this answer
    
is there any way i can fine which process is taking much cpu usage ???? i have mysql slow enabled but nothing is coming there. i tried changing everything but no use. can add me bydbest at g m ail –  payal Oct 21 '12 at 8:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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