centos6 - high mysql CPU usage more than %1500 on centos 6

I am running a high traffic websites on a Dedicated-Virtual 24GB RAM CentOS server hosted by the planet.

Very often the server dies because of high CPU usage (%1500)

When i check the running processes using "top" command i see a hight load average more than 100 always and cpu mysql often more than 1500%

I have tried to tune mysql, tune apache, my.cnf, httpd.cnf, removed
Apache modules that i do not use but that does not solve the problem.

Please let me know what values do you need to know in my server config in order to help me diagnosis the problem.

Thanks in advance.

    Architecture:          x86_64

CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                16
On-line CPU(s) list:   0-15
Thread(s) per core:    2
Core(s) per socket:    4
Socket(s):             2
NUMA node(s):          2
Vendor ID:             GenuineIntel
CPU family:            6

Model:                 44
Stepping:              2
CPU MHz:               2400.093
BogoMIPS:              4799.88
Virtualization:        VT-x
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              12288K
NUMA node0 CPU(s):     0-3,8-11

NUMA node1 CPU(s):     4-7,12-15


top - 05:37:00 up 4 days, 22:04,  2 users,  load average: 136.61, 145.42, 139.03
Tasks: 565 total,   8 running, 554 sleeping,   0 stopped,   3 zombie
Cpu(s): 28.6%us, 70.6%sy,  0.1%ni,  0.2%id,  0.0%wa,  0.0%hi,  0.5%si,  0.0%st
Mem:  24596732k total, 13172200k used, 11424532k free,   630632k buffers
Swap:  2097144k total,        0k used,  2097144k free,  7648348k cached
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
  4850 mysql     15  -5 12.4g 1.1g 6036 S 1545.1  4.7  12992:15 mysqld

  15846 root      20   0  162m  22m  916 R  4.2  0.1   0:00.62 lfd
  15509 apache    20   0  632m 129m 5460 S  3.0  0.5   0:01.59 httpd
  15276 apache    20   0  632m 128m 5504 R  2.7  0.5   0:01.85 httpd
  15505 apache    20   0  634m 130m 5460 R  2.1  0.5   0:01.49 httpd
  15792 apache    20   0  630m 125m 4816 S  2.1  0.5   0:00.43 httpd
  3058 nginx     20   0 76576  34m 2268 S  1.8  0.1 107:48.32 nginx
  15585 apache    20   0  632m 128m 5268 S  1.8  0.5   0:01.25 httpd
  15827 apache    20   0  629m 124m 3752 S  1.8  0.5   0:00.10 httpd



cat /proc/meminfo
MemTotal:       24596732 kB
MemFree:        13807688 kB
Buffers:         1108524 kB
Cached:          4888972 kB
SwapCached:            0 kB
Active:          4929640 kB
Inactive:        3225108 kB
Active(anon):    1523440 kB
Inactive(anon):   635484 kB

Active(file):    3406200 kB
Inactive(file):  2589624 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:       2097144 kB
SwapFree:        2097144 kB
Dirty:             16032 kB
Writeback:             0 kB
AnonPages:       2114752 kB
Mapped:            52792 kB

Shmem:              1648 kB
Slab:            2155004 kB
SReclaimable:    2028484 kB
SUnreclaim:       126520 kB
KernelStack:        5184 kB
PageTables:       105004 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    14395508 kB

Committed_AS:   16484584 kB
VmallocTotal:   34359738367 kB
VmallocUsed:      463816 kB
VmallocChunk:   34345823536 kB
HardwareCorrupted:     0 kB
AnonHugePages:    800768 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0

Hugepagesize:       2048 kB
DirectMap4k:        5696 kB
DirectMap2M:     2082816 kB
DirectMap1G:    23068672 kB


my.cnf
[mysqld]
tmpdir=/var/tmpfs
local-infile=0

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#general_log=1
slow_query_log= 0
long_query_time =9
log_queries_not_using_indexes =0
max_connections = 400

key_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
max_heap_table_size = 600M
tmp_table_size = 600M
read_buffer_size = 320M
sort_buffer_size = 320M
join_buffer_size = 3G
table_definition_cache = 8000
table_open_cache = 220000

thread_cache_size = 384
wait_timeout = 50
interactive_timeout = 30
connect_timeout = 10
max_allowed_packet = 265M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000

concurrent_insert = 2
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 256M
query_cache_size = 200M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
range_alloc_block_size = 4096
transaction_alloc_block_size = 8192

transaction_prealloc_size = 4096
default-storage-engine = MyISAM
open_files_limit = 3G
max_write_lock_count = 8
innodb_open_files = 500
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 60M
innodb_additional_mem_pool_size = 32M
innodb_log_files_in_group = 2
innodb_log_file_size = 5M

innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0



trafic shown by phpmyadmin for just one hour and half
Trafic réseau depuis le démarrage : 123 Gio


Ce serveur MySQL fonctionne depuis 0 jours, 1 heures, 33 minutes et 58 secondes. Il a démarré le Jeu 28 Janvier 2016 à 13:21.
Trafic  ø par heure
Reçu    445 Mio 284,2 Mio
Envoyé  122,6 Gio   78,3 Gio
Total   123 Gio 78,6 Gio
Connexions  ø par heure %
max. de connexions simultanées  170 ---     ---
Tentatives échouées 3 253   2 077,12    0,55%
Arrêts prématurés   306     195,39  0,05%
Total   591 k   377,53 k    100,00%

Answer

thanks, changing myisam tables to innodb solved the problem, now mysql CPU is at most 300% and normal load average peak 5%