MYSQL BLowing Server Up

[jaymc]

I run a very very busy website which uses MYSQL heavily

 

I have tried my best to index tables correctly, optimize queries as best as I can and run regular defrags of tables

 

However, MYSQL still goes wild sometimes when the site gets busy and causes the server load to fly from a steady 4.0 to 212 (when running top)

 

When this happens a quick check of the MYSQL process list shows about 40 queries, most of them locked

 

Some of them have even been running/locked for 240 seconds

 

The load will stay like that untill the queries clear

 

These queries are normal ones, in other words, its not some heavy query thats doin it, basically, any query appears to be setting it off, and once when locks obviously the rest follow and just que

 

Here are the specs of my server, which is my own, and used only by me

 

2x 1.4Ghz Opteron

3Gig Ram

1x SATA with SYSTEM and MYSQL files on

1x SATA for data

 

I have tweaked the mysql conf as best as I can, but here it is anyway

 

On the server I run 3 seperate sites, if you want to call them that

 

One is an invision 2.2 forum which usually has 100 active members hourly..

Another is a chatroom which usually has 50-60 people in the chat

And the other is a member website which usually has 550 people online

 

#DO NOT MODIFY THE FOLLOWING COMMENTED LINES!
#Created with ELS from www.servermonkeys.com
#els-build=4.1
[mysqld]
datadir=/var/lib/mysql
skip-locking
skip-innodb
skip-networking
skip-name-resolv
safe-show-database
query_cache_limit=3M
query_cache_size=128M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=440
max_connections=440
interactive_timeout=60
wait_timeout=30
connect_timeout=10
thread_cache_size=2M
key_buffer=1024M ## 128MB for every 1GB of RAM
join_buffer=12M
max_connect_errors=20
max_allowed_packet=16M
table_cache=5024
record_buffer=6M
tmp_table_size=196M
sort_buffer_size=12M ## 1MB for every 1GB of RAM
read_buffer_size=3M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=3M  ## 1MB for every 1GB of RAM
thread_concurrency=4 ## Number of CPUs x 2
myisam_sort_buffer_size=128M
server-id=1
collation-server=latin1_general_ci
old-passwords
#log-slow-queries=/etc/slowq.txt
#long_query_time = 5

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
open_files_limit=102400

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=356M
sort_buffer=356M
read_buffer=356M
write_buffer=356M

[myisamchk]
key_buffer=356M
sort_buffer=356M
read_buffer=356M
write_buffer=356M

#[mysqlhotcopy]
interactive-timeout

 

The reason I have used more than average memory for things is due to the amount of RAM I have and the fact it never ever goes into SWAP, so I assume if its not swapping I can afford to use more of it

 

Its CPU usage that is the killer, the server is almost constantly between 60-89%

 

Any help would be great!

 

 

[fenway]

Well, you're not using innodb, so that's not the problem.. what does the slow query log show?

[jaymc]

I dont have a slow query log

 

I had one, but it was useless

 

When the server load goes up to 30.... any query can take 20 seconds to finish for the simple reason its in a que

 

MYSQL cant distinquish between that, it treats it as a slow query when in actuallyfact its running slow because of the system resources, not the actual nature of the query

[fenway]

If you want to diagnose the problem yourself, go right ahead... but something's causing the server load to go up, and it has to be the queries.