Web Hosting Talk : Web Hosting Main Forums : Hosting Security and Technology : Mysql Process using too much CPU

[rcpkrc]

hello friends, while l check the mysql process list l see this which causing high page load.

PHP Code:

/usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/servername.pid --skip-external-locking 


what can l do to solve it?

[fabin]

use mtop to find the queries that are causing the problem.

[rcpkrc]

here is the top list of my server....

4631 mysql 15 0 195m 93m 3768 S 133.7 1.1 28:12.45 mysqld
8725 nobody 17 0 43184 29m 5388 S 45.2 0.4 0:00.22 php
8728 nobody 18 0 29516 15m 5068 R 14.4 0.2 0:00.07 php
4238 root 15 0 2428 1116 792 R 2.1 0.0 0:01.25 top
7266 dailynew 15 0 389m 375m 5268 S 2.1 4.6 0:00.89 php
7828 nobody 15 0 11532 4664 1744 S 2.1 0.1 0:00.07 httpd
13405 nobody 15 0 38788 33m 844 S 2.1 0.4 8:56.95 nginx
1 root 15 0 2160 644 556 S 0.0 0.0 0:02.60 init
2 root RT -5 0 0 0 S 0.0 0.0 0:00.20 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.11 ksoftirqd/0
4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0
5 root RT -5 0 0 0 S 0.0 0.0 0:00.23 migration/1
6 root 37 19 0 0 0 S 0.0 0.0 0:00.11 ksoftirqd/1
7 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/1
8 root RT -5 0 0 0 S 0.0 0.0 0:00.28 migration/2
9 root 34 19 0 0 0 S 0.0 0.0 0:00.12 ksoftirqd/2
10 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/2
11 root RT -5 0 0 0 S 0.0 0.0 0:00.18 migration/3
12 root 39 19 0 0 0 S 0.0 0.0 0:00.05 ksoftirqd/3
13 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/3
14 root RT -5 0 0 0 S 0.0 0.0 0:00.24 migration/4
15 root 34 19 0 0 0 S 0.0 0.0 0:00.12 ksoftirqd/4
16 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/4
17 root RT -5 0 0 0 S 0.0 0.0 0:00.18 migration/5
18 root 34 19 0 0 0 S 0.0 0.0 0:00.09 ksoftirqd/5
19 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/5
20 root RT -5 0 0 0 S 0.0 0.0 0:00.25 migration/6
21 root 34 19 0 0 0 S 0.0 0.0 0:00.15 ksoftirqd/6
22 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/6
23 root RT -5 0 0 0 S 0.0 0.0 0:00.15 migration/7
24 root 34 19 0 0 0 S 0.0 0.0 0:00.09 ksoftirqd/7
25 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/7
26 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/0
27 root 10 -5 0 0 0 S 0.0 0.0 0:00.06 events/1
28 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/2
29 root 10 -5 0 0 0 S 0.0 0.0 0:00.24 events/3
30 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/4
31 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/5
32 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/6
33 root 10 -5 0 0 0 S 0.0 0.0 0:00.01 events/7
34 root 10 -5 0 0 0 S 0.0 0.0 0:02.51 khelper

[fabin]

what you need to look at is the result of "mtop", not top

http://mtop.sourceforge.net/

[eNetCloud-CEO]

You can try mysqladmin proc command to see the exact queries to exact databases which is causing these issues. It will give you the exact output and an idea of what causing the issues.

[rcpkrc]

l tried to install mtop but l couldnt manage. and l dont know how to try mysqladmin proc command.

[rcpkrc]

here is the mysqladmin proc list for u mate

+-------+-------------+-----------+--------------+---------+------+------------- ---+---------------------------------------------------------------------------- --------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------------+-----------+--------------+---------+------+------------- ---+---------------------------------------------------------------------------- --------------------------+
| 28115 | rcpkrc_rk | localhost | rcpkrc_video | Sleep | 0 | | |
| 28410 | rcpkrc_rk | localhost | rcpkrc_video | Query | 0 | Writing to n et | SELECT p.post_title, p.comment_count, p.post_date, p.ID, COUNT(tr.object_id ) AS counter
FROM w |
| 28479 | newstv_rk | localhost | newstv_db | Sleep | 0 | | |
| 28566 | rcpkrc_rk | localhost | rcpkrc_video | Query | 1 | Sending data | SELECT p.* FROM wp_posts AS p WHERE p.post_date < '2011-03-04 07:27:09' AN D p.post_type = 'post' AN |
| 28570 | dailynew_rk | localhost | dailynew_db | Query | 0 | Sending data | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_po sts.post_type = 'post' AN |
| 28572 | euronews_rk | localhost | euronews_db | Sleep | 0 | | |
| 28573 | root | localhost | | Query | 0 | | show processlist

[eNetCloud-CEO]

The database in the above post "rcpkrc_video" looks the culprit , however contact your developer who have developed the database and have them to optimize the DB , since its showing sleep status.