Mysql High CPU consumption

Hi all,

I am getting problems in a couple of hosts where mysqld eats a lot of cpu resources up to leave the server unresponsive. We recently increased physical memory to 8gb and updated the kernel to PAE since we are running under 32bits architecture.

Today, I was able to capture this in one of my servers:

Code:

root@host [~]# uptime
 11:08:16 up 2 days,  1:01,  1 user,  load average: 208.70, 206.76, 163.35

root@host [~]# top
top - 11:09:12 up 2 days,  1:02,  1 user,  load average: 224.49, 210.20, 166.95
Tasks: 689 total,   6 running, 680 sleeping,   0 stopped,   3 zombie
Cpu(s):  6.4%us, 92.6%sy,  0.1%ni,  0.6%id,  0.0%wa,  0.1%hi,  0.3%si,  0.0%st
Mem:   8309024k total,  8016416k used,   292608k free,   387928k buffers
Swap:  8385888k total,        0k used,  8385888k free,  5100948k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 6277 mysql     15   0  132m  70m 3632 S 388.9  0.9   1342:25 mysqld
22538 XXXXXX  16   0 91448  10m 5796 S  1.3  0.1   0:00.21 php
21065 XXXXXX  18   0 94936  28m  22m R  1.0  0.3   0:00.73 php
22471 XXXXXX  16   0 97008  15m 6268 S  1.0  0.2   0:01.86 php

As you can see load was very high, I checked mysql process list and there was only one user doing a select sentence. I restarted mysql and load dropped immediately.


I am not a an expert, so I would like to have your recommendations to tune mysql on my servers. I'm running version 5.0.95 on RHEL 5.9 on both boxes.

Here is my.cnf file:

Code:

[mysqld]
skip-bdb
skip-innodb
set-variable = max_connections=500
tmpdir = /dev/shm
safe-show-database
max_allowed_packet=16M

and here the result of mysqltuner.pl:

Code:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.95-community
[!!] 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: 1G (Tables: 2549)
[--] Data in MEMORY tables: 0B (Tables: 14)
[!!] Total fragmented tables: 414

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 27m 26s (208K q [39.673 qps], 9K conn, TX: 578M, RX: 25M)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.3G (16% of installed RAM)
[OK] Slow queries: 0% (79/208K)
[OK] Highest usage of available connections: 3% (17/500)
[!!] Key buffer size / total MyISAM indexes: 8.0M/347.7M
[!!] Key buffer hit rate: 87.7% (40M cached / 4M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 13K sorts)
[!!] Joins performed without indexes: 324
[!!] Temporary tables created on disk: 27% (2K on disk / 9K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 15K opened)
[OK] Open file limit used: 5% (128/2K)
[OK] Table locks acquired immediately: 99% (227K immediate / 227K locks)
[!!] Connections aborted: 68%

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Your applications are not closing MySQL connections properly
Variables to adjust:
    key_buffer_size (> 347.7M)
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)

Thanks in advance for your help.

 

Thanks for your response Michael, but I have not applied any tune yet, that is what I am trying to confirm. I know there are a lot of posts about this issue on this forum, I went through many of them, but as you know every environment is different.

Basically, I would like to know if all recommendations suggested by mysqltuner will apply to my environment or just some of them.

Thanks,

 

Thanks Michael.

 

Thanks 24x7 for your reply, I will follow your recommendations and will monitor the server for 24hrs.
After that, I will post the results here.


Thanks,