MySQL CPU Usage Spiking

[stephen_wq]

Having major problems with mysqld causing CPU usage to rocket to 100% on one core for a short time, which usually drops back down again to 1-7%, though sometimes it can last an extended period of time.
This is a problem because things using mysql become unresponsive in this time.

What is using mysql:
-PHPbb forum with light usage
-Sourcebans game ban database - quite a large database but shouldnt be causing problems since it does a few simple lookups per minute
-HLStatsX game stats database - very large, has 3 daemons running which constantly receive statistics from approximately ~150 players on game servers (kills, deaths etc)

Heres my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
# these lines and adjust the connectstring as needed.
#ndbcluster
#ndb-connectstring="nodeid=4;host=localhost:1186"

#Disable DNS Hostname Lookup
skip-name-resolve

#Use a 50mb Query Cache
query_cache_size = 100m;

#Turn on query cacheing
query_cache_type = 1

query_cache_limit=2M

#log-slow-queries = /var/log/mysql-slow.log

long_query_time = 2

wait_timeout=15

connect_timeout=10

interactive_timeout=100

join_buffer_size=3M

max_allowed_packet=16M

table_cache=2048

thread_cache_size=64

sort_buffer_size=2M

read_buffer_size=1M

key_buffer_size=400M

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

[ndbd]
# If you are running a MySQL Cluster storage daemon (ndbd) on this machine,
# adjust its connection to the management daemon here.
# Note: ndbd init script requires this to include nodeid!
connect-string="nodeid=2;host=localhost:1186"

[ndb_mgm]
# connection string for MySQL Cluster management tool
connect-string="host=localhost:1186"

mysqld is using 650mb of ram which is fine.

mysql> SHOW STATUS LIKE '%THREAD%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 14    |
| Slow_launch_threads    | 0     |
| Threads_cached         | 20    |
| Threads_connected      | 18    |
| Threads_created        | 24    |
| Threads_running        | 1     |
+------------------------+-------+
6 rows in set (0.00 sec)

mysqltuner

 perl mysqltuner.pl

 >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.39
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 729M (Tables: 347)
[--] Data in InnoDB tables: 416K (Tables: 18)
[--] Data in MEMORY tables: 1023K (Tables: 1)
[!!] Total fragmented tables: 19

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 13h 20m 56s (2M q [18.666 qps], 98K conn, TX: 2B, RX: 584M)
[--] Reads / Writes: 25% / 75%
[--] Total buffers: 526.0M global + 10.2M per thread (151 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.0G (50% of installed RAM)
[OK] Slow queries: 0% (1K/2M)
[OK] Highest usage of available connections: 15% (24/151)
[OK] Key buffer size / total MyISAM indexes: 400.0M/370.3M
[OK] Key buffer hit rate: 100.0% (5B cached / 728K reads)
[OK] Query cache efficiency: 45.0% (349K cached / 776K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (325 temp sorts / 53K sorts)
[!!] Joins performed without indexes: 1005
[OK] Temporary tables created on disk: 2% (1K on disk / 51K total)
[OK] Thread cache hit rate: 99% (24 created / 98K connections)
[!!] Table cache hit rate: 1% (99 open / 7K opened)
[OK] Open file limit used: 3% (144/4K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 416.0K/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    join_buffer_size (> 3.0M, or always use indexes with joins)
    table_cache (> 2048)

Already run

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

How do i find out what is causing this?

[coopstah13]

I know more about oracle than mysql, but I'll give it a shot here. You mentioned a query that isn't executed often, but this might match up with your periodic spikes in CPU. You might try increasing the amount of memory available to mysql, based on your database sizes you could fit it all into memory if you have enough (it seems you do based on your output). I would try searching for mysql profilers, there might be some free ones out there that can let you see what the query being executed is that is causing the CPU spike, this should make it obvious what you need to do.

[Xasir]

There is some query which is taking the time.. In mysql

# show full processlist\G

this will tell you that which taking time u need to run it when it start unreponsive... Or load start increasing