When I run "top" command on my Linux it shows that MySQL is taking 400%-500% CPU usage. But when I run "watch mysqladmin pr" command to see a list of queries being run by MySQL then it shows just 2, 3 queries and that too only taking 2, 3 seconds. The "top" command shows MySQL constantly fluctuating CPU usage like 130%, 200%, 340%, 230%, 400%, 440%, 635%, 335% e.t.c. but "watch mysqladmin pr" command is not showing queries that are taking up CPU usage. How do I check why MySQL is using so much CPU?
Two things come to mind here :
When MySQL causes a constatntly fluctuating CPU, I would first check what kind of installation of MySQL was performed. MySQL RPMs usually contain binaries that have been optimized for specific platforms. On the other hand, MySQL binaries that have been compiled from source tend not to be lean and mean. Such binaries could be bloated and not optimized at compile time to take advantage of memory.
If the InnoDB Buffer Pool (innodb_buffer_pool_size) or the MyISAM Key Cache (key_buffer_size) is underconfigured or overconfigured, you could potentially be spending time clean out caches without the presence of new queries.