Mysql using so much cpu

Down load this

http://mediakey.dk/~cc/optimize-mysql-performance-with-mysqltuner/

run this .. and it will tell you what options you need to increase for mysql tunning

also

turn on slow log query to see how many slow log you got in your sql query

also this one

http://forge.mysql.com/projects/project.php?id=44

both perl and shell script will do that same but good to check with both
and both script want you to run mysql server for at least 48 hours

I had run the first one and the result as given below is that all

 >>  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

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 972M (Tables: 1347)
[--] Data in InnoDB tables: 2M (Tables: 145)
[--] Data in MEMORY tables: 3M (Tables: 1913)
[!!] BDB is enabled but isn't being used
[!!] Total fragmented tables: 47

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 17m 43s (2M q [288.895 qps], 43K conn, TX: 77B, RX: 722M)
[--] Reads / Writes: 96% / 4%
[--] Total buffers: 34.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 309.0M (3% of installed RAM)
[OK] Slow queries: 0% (21/2M)
[OK] Highest usage of available connections: 53% (53/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/632.7M
[OK] Key buffer hit rate: 99.1% (197M cached / 1M reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (7K temp sorts / 876K sorts)
[!!] Joins performed without indexes: 8456
[!!] Temporary tables created on disk: 45% (640K on disk / 1M total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 126K opened)
[OK] Open file limit used: 11% (121/1K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[OK] InnoDB data size / buffer pool: 2.4M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-bdb to MySQL configuration to disable BDB
    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
Variables to adjust:
    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)

[root@lin tmp]#

Select allOpen in new window

you must change this .. try to put bigger value then its asking


  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)

also its it said

Add skip-bdb to MySQL configuration to disable BDB
    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
 Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits

I guess you would not be able to do this

   Adjust your join queries to always utilize indexes

i know this will be a bad question but i am completely out of mind i checked my.cnf but could not figure it out how could i change this values what do you advice me to use bigger then this values

ohh i am sorry i see i had modified it seems better

[root@lin tmp]# vi /etc/my.cnf
[mysqld]
tmp_table_size=1000M
query_cache_size = 2000M
join_buffer_size = 4096K
max_heap_table_size = 512M
thread_cache_size = 8
table_cache = 64

Select allOpen in new window

Thank you

also

enable
 Enable the slow query log to troubleshoot bad queries

if you have too much slow log query it will be a problem for performance

so whats the cpu utilization of mysql server now ???

and you put too big value of

query_cache_size = 2000M


12 to 20 MB would be better for time being i guess!!!

please past the cpu utilization of top result

i just want to see how much it helped

And now i am optimizing again and again i think it will be better :D
i only could not understand how to do a :
Add skip-bdb to MySQL configuration to disable BDB
Thank you thousands of time

top - 00:30:03 up  2:47,  4 users,  load average: 1.91, 2.23, 2.73
Tasks: 211 total,   5 running, 206 sleeping,   0 stopped,   0 zombie
Cpu(s): 40.7%us,  4.8%sy,  0.0%ni, 53.4%id,  0.5%wa,  0.1%hi,  0.4%si,  0.0%st
Mem:   8092384k total,  3609536k used,  4482848k free,   206180k buffers
Swap: 19631420k total,        0k used, 19631420k free,  1402356k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
28923 apache    20   0  330m  47m 3912 S 19.6  0.6   0:00.59 httpd
28832 mysql     20   0 1310m 287m 5604 S 18.3  3.6   0:14.92 mysqld

Select allOpen in new window

owwowo from 100 % to 18.3

its good value

dont worry about the skip-bdb, it will not help you too much about cpu utilization.

when you start mysql server you add this line skip-bdb thats it. i will sent you a link of this

about apache .

you could make Apache more smaller if you could of compile this from source..

i think if you add

skip-bdb in my my.cnf file ( under mysqld section)
it will off bdb database e ngine

Yes it is superrrr thank you so much