Mysql using so much cpu

I have recently 2000 + user connected to my server , apache and all the other applications answering well and fast but mysql is using so much cpu always how could i optimize it without changing php codes ?
[root@lin ~]# top
top - 23:31:05 up  1:48,  6 users,  load average: 2.32, 2.94, 3.31
Tasks: 204 total,   4 running, 200 sleeping,   0 stopped,   0 zombie
Cpu(s): 57.9%us,  7.8%sy,  0.0%ni, 33.9%id,  0.0%wa,  0.1%hi,  0.3%si,  0.0%st
Mem:   8092384k total,  2828476k used,  5263908k free,   179312k buffers
Swap: 19631420k total,        0k used, 19631420k free,  1242520k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 3447 mysql     20   0  407m  82m 6120 S 100.3  1.0  78:24.05 mysqld

Open in new window

LVL 1
CahitEyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


0
fosiul01Commented:
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
0
CahitEyAuthor Commented:
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]#

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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


0
CahitEyAuthor Commented:
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
0
fosiul01Commented:
in my.cnf file

under [mysqld] section add like this


  tmp_table_size = 40MB
max_heap_table_size = 16M
query_cache_size = 12M
key_buffer = 16M
max_allowed_packet = 1M
table_cache =  64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size =  256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size =4
 join_buffer_size=128



then restart mysqld daemon

but i need to c heck in mysql documentation for bellow , its guess it meant 4 and 128. try it

thread_cache_size =4
 join_buffer_size=128
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CahitEyAuthor Commented:
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

Open in new window

0
CahitEyAuthor Commented:
Thank you
0
fosiul01Commented:
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
0
fosiul01Commented:
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!!!
0
fosiul01Commented:
please past the cpu utilization of top result

i just want to see how much it helped

0
CahitEyAuthor Commented:
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

Open in new window

0
fosiul01Commented:
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..


0
fosiul01Commented:
i think if you add

skip-bdb in my my.cnf file ( under mysqld section)
it will off bdb database e ngine
0
CahitEyAuthor Commented:
Yes it is superrrr thank you so much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.