disparity between Top and SHOW FULL PROCESSLIST

D

djmix

Verified User
Joined
Mar 30, 2009
Messages
6
Hi. I ask to forgive for my English, but i have a problem.

My server shows information when I enter a command "top":
Code: 
14490 mysql     15   0  545m 110m 2508 S    0 11.0   0:04.33 mysqld
14493 mysql     18   0  545m 110m 2508 S    0 11.0   0:00.02 mysqld
14494 mysql     15   0  545m 110m 2508 S    0 11.0   0:14.16 mysqld
14495 mysql     15   0  545m 110m 2508 S    0 11.0   3:29.07 mysqld
14496 mysql     15   0  545m 110m 2508 S    0 11.0   4:13.73 mysqld
14497 mysql     15   0  545m 110m 2508 S    0 11.0   3:26.93 mysqld
14498 mysql     15   0  545m 110m 2508 S    0 11.0   3:55.80 mysqld
14505 mysql     15   0  545m 110m 2508 S    0 11.0   3:42.23 mysqld
14526 mysql     19   0  545m 110m 2508 S    0 11.0   3:37.07 mysqld
14527 mysql     15   0  545m 110m 2508 S    0 11.0   4:25.67 mysqld
14528 mysql     16   0  545m 110m 2508 S    0 11.0   2:58.04 mysqld
14529 mysql     17   0  545m 110m 2508 S    0 11.0   3:03.98 mysqld
14693 mysql     15   0  545m 110m 2508 S    0 11.0   3:44.44 mysqld
14774 mysql     15   0  545m 110m 2508 S    0 11.0   4:16.73 mysqld
14810 mysql     17   0  545m 110m 2508 S    0 11.0   4:01.64 mysqld
14811 mysql     19   0  545m 110m 2508 S    0 11.0   2:48.59 mysqld
14812 mysql     15   0  545m 110m 2508 S    1 11.0   3:28.23 mysqld
14813 mysql     15   0  545m 110m 2508 S    0 11.0   2:42.21 mysqld
14814 mysql     15   0  545m 110m 2508 S    0 11.0   2:39.98 mysqld
14815 mysql     15   0  545m 110m 2508 S    0 11.0   3:43.90 mysqld
14816 mysql     17   0  545m 110m 2508 S    0 11.0   4:02.41 mysqld
14817 mysql     18   0  545m 110m 2508 S    0 11.0   3:29.08 mysqld
14818 mysql     15   0  545m 110m 2508 S    0 11.0   3:30.98 mysqld
15119 mysql     15   0  545m 110m 2508 S    0 11.0   3:56.62 mysqld
15120 mysql     16   0  545m 110m 2508 S    0 11.0   2:44.64 mysqld
15121 mysql     15   0  545m 110m 2508 S    0 11.0   3:23.13 mysqld
15122 mysql     18   0  545m 110m 2508 S    4 11.0   3:18.97 mysqld
15123 mysql     16   0  545m 110m 2508 S    0 11.0   3:04.11 mysqld
15124 mysql     17   0  545m 110m 2508 S    0 11.0   4:43.29 mysqld
15125 mysql     17   0  545m 110m 2508 S    0 11.0   3:12.38 mysqld
15126 mysql     15   0  545m 110m 2508 S    0 11.0   4:08.56 mysqld
15598 mysql     18   0  545m 110m 2508 S    0 11.0   3:54.38 mysqld
15599 mysql     22   0  545m 110m 2508 S    0 11.0   2:57.09 mysqld
18285 mysql     15   0  545m 110m 2508 S    0 11.0   4:20.18 mysqld
18286 mysql     20   0  545m 110m 2508 S    0 11.0   2:46.55 mysqld
18287 mysql     18   0  545m 110m 2508 S    0 11.0   3:15.55 mysqld
18288 mysql     15   0  545m 110m 2508 S    0 11.0   4:08.21 mysqld
18289 mysql     15   0  545m 110m 2508 S    0 11.0   2:21.98 mysqld
18290 mysql     20   0  545m 110m 2508 S    1 11.0   3:43.81 mysqld

but when I enter a command "SHOW FULL PROCESSLIST" I'm see:
Code: 
+-------+-----------+-----------+-----------+---------+------+--------------+--+
| Id    | User      | Host      | db        | Command | Time | State        | I|
+-------+-----------+-----------+-----------+---------+------+--------------+--+
| 48986 | da_admin  | localhost | database | Sleep   |    4 |              | N
| 49178 | da_admin  | localhost | database | Sleep   |    4 |              | N
| 51245 | da_admin  | localhost | NULL      | Query   |    0 | NULL         | S
| 51373 | db_user | localhost | database | Query   |    0 | Sending data | S
+-------+-----------+-----------+-----------+---------+------+--------------+--+

Where I must find what processes executed PID from command "top".
Thank you!
 
MySQL uses multi-threading and doesn't kill all threads when a client disconnect but let live automatically a number of threads in idle as "spare" threads.
That's probably why you see more processes running with "top" than within MySQL.

To be sure that all those MySQL processes are really threads of the running MySQL distribution just run "pstree -p".
You should see something like that:
Code: 
        |-mysqld_safe(6158)---mysqld(6208)-+-{mysqld}(6210)
        |                                  |-{mysqld}(6211)
        |                                  |-{mysqld}(6212)
        |                                  |-{mysqld}(6213)
        |                                  |-{mysqld}(6215)
        |                                  |-{mysqld}(6216)
        |                                  |-{mysqld}(6217)
        |                                  |-{mysqld}(6218)
        |                                  |-{mysqld}(26605)
        |                                  |-{mysqld}(26612)
        |                                  |-{mysqld}(26708)
        |                                  |-{mysqld}(26710)
        |                                  |-{mysqld}(26736)
        |                                  |-{mysqld}(26808)
        |                                  |-{mysqld}(26815)
        |                                  |-{mysqld}(26887)
        |                                  |-{mysqld}(26916)
        |                                  |-{mysqld}(26917)
        |                                  |-{mysqld}(26920)
        |                                  |-{mysqld}(26921)
        |                                  |-{mysqld}(26927)
        |                                  |-{mysqld}(26932)
        |                                  |-{mysqld}(26934)
        |                                  `-{mysqld}(26935)
 
Thank you for a quick answer. I entered a command "pstree -p" and got such:

Code: 
        ├─mysqld(14494)
        ├─mysqld(14495)
        ├─mysqld(14496)
        ├─mysqld(14497)
        ├─mysqld(14498)
        ├─mysqld(14505)
        ├─mysqld(14526)
        ├─mysqld(14527)
        ├─mysqld(14528)
        ├─mysqld(14529)
        ├─mysqld(14693)
        ├─mysqld(14774)
        ├─mysqld(14810)
        ├─mysqld(14811)
        ├─mysqld(14812)
        ├─mysqld(14813)
        ├─mysqld(14814)
        ├─mysqld(14815)
        ├─mysqld(14816)
        ├─mysqld(14817)
        ├─mysqld(14818)
        ├─mysqld(15119)
        ├─mysqld(15120)
        ├─mysqld(15121)
        ├─mysqld(15122)
        ├─mysqld(15123)
        ├─mysqld(15124)
        ├─mysqld(15125)
        ├─mysqld(15126)
        ├─mysqld(15598)
        ├─mysqld(15599)
        ├─mysqld(18285)
        ├─mysqld(18286)
        ├─mysqld(18287)
        ├─mysqld(18288)
        ├─mysqld(18289)
        ├─mysqld(18290)
        ├─mysqld(18291)
        ├─mysqld(18292)
        ├─mysqld(18293)
        ├─mysqld(18294)
        ├─mysqld(18295)
        ├─mysqld(18296)
        ├─mysqld(18297)
        ├─mysqld(18298)
        ├─mysqld(18299)
        ├─mysqld(18300)
        ├─mysqld(18301)
        ├─mysqld(18302)
        ├─mysqld(18303)
        ├─mysqld(18304)
        ├─mysqld(18305)
        ├─mysqld(18306)
        ├─mysqld(18307)
        ├─mysqld(18308)
        ├─mysqld_safe(14469)───mysqld(14490)───mysqld(14493)

it I so understand what you talked about.
 
Can you please tell me why would you want to influence them? They are managed automatically by MySQL.
If you experience performance issues that's not the way to go; read all posts on this forum about it.
 
Because all these processes in the end eat up much memory. Can it is possible to influence certain amount of memory for MySQL?
 
Those processes share most of their memory.
Yes you can influence the memory used by MySQL, by modifying all the cache settings. That has also been discussed a lot on this forum, look for "mysql performance".
I warn you, using less memory will make MySQL to do much more IO and CPU load therefore making it slower and less performing.

If you need practical assistance hire someone to look at your server and suggest you the best settings.
 
You must log in or register to reply here.
Back
Top