MYSQL overload

i am having problems with mysql overloading now and then when it is very busy the server will stop responding completely. i know one of my databases has a loot of trafic but how can i deal with this.do i need a better server or do i need a different engion for the database. at the moment i am using MyISAM. some of the tables i am dealing with have about 420000 records(82.7 MiB) there is a lot of inserts and selects and speed is very important any help would be much

i get this message on when i look at the status on whm

/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.sy2.com.pid --skip-external-locking

the server load for that is 180%-220% most of the time.

i typed this on the server

netstat -nat | awk '{print $6}' | sort | uniq -c | sort -n

   1 established)
   1 Foreign
  17 SYN_RECV
  28 FIN_WAIT1
  30 LISTEN
 154 ESTABLISHED
 188 FIN_WAIT2
1206 TIME_WAIT

Apache Status

Current Time: Saturday, 25-Jun-2011 19:00:34 BST
Restart Time: Friday, 24-Jun-2011 20:41:36 BST
Parent Server Generation: 11
Server uptime: 22 hours 18 minutes 58 seconds
Total accesses: 1866618 - Total Traffic: 12.2 GB
CPU Usage: u25.92 s20.03 cu1142.19 cs0 - 1.48% CPU load
23.2 requests/sec - 159.5 kB/second - 6.9 kB/request
206 requests currently being processed, 12 idle workers


this is the server spec
Linux - Dual Quad Core Intel Xeon E5620
Hard开发者_运维百科 Disk: 4x300GB SAS 15k RPM
RAM: 12 GB DDR3 RAM
RAID Options: RAID 10 (needs 4 identical Drives)
Operating System: CentOS 5.x
Control Panel: cPanel/WHM
Bandwidth: 5000 GB

There is no simple answer to this question. Just some guidelines on how to troubleshoot your issue. You may actually be running into the peak load that your server can handle and may not be able to optimize much.

Start with show processlist This will give you an idea how many connections are open and which ones might be stuck due to locking issues.

Enable slow query log, look for slowly responding queries and use explain to see the execution plan. You may be missing indexes or have structural issues that are causing your queries to run slow.

You can tweak your database server settings to allow more data to be cached in memory. There are some tools like mysqltuner that can guide you to settings you should look at.

You may also want to take a look at this guide: http://www.fromdual.com/mysql-performance-tuning-key