0

Ok, so I don't know if this is a repeat question so I'm sorry if it is (I couldn't find anything like it after searching =/)

My server seems to be running what seems to me a larger than normal mysql process:

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
31382 mysql     20   0  562m  97m 4244 S   73 19.7   2830:07 mysqld

But then when I do a SHOW PROCESSLIST in mysql, everything seems to be asleep:

mysql> SHOW PROCESSLIST;
+------+-------+-----------+------+---------+------+-------+------------------+
| Id   | User  | Host      | db   | Command | Time | State | Info             |
+------+-------+-----------+------+---------+------+-------+------------------+
| 3982 | dlp_w | localhost | dlp  | Sleep   |  767 |       | NULL             |
| 3983 | dlp_r | localhost | dlp  | Sleep   |    0 |       | NULL             |
| 3986 | dlp_w | localhost | dlp  | Sleep   |  788 |       | NULL             |
| 3987 | dlp_r | localhost | dlp  | Sleep   |    0 |       | NULL             |
| 3988 | dlp_w | localhost | dlp  | Sleep   |  727 |       | NULL             |
| 3989 | dlp_r | localhost | dlp  | Sleep   |    0 |       | NULL             |
| 4019 | root  | localhost | dlp  | Query   |    0 | NULL  | SHOW PROCESSLIST |
| 4020 | dlp_w | localhost | dlp  | Sleep   |  708 |       | NULL             |
| 4021 | dlp_r | localhost | dlp  | Sleep   |    0 |       | NULL             |
| 4022 | dlp_w | localhost | dlp  | Sleep   |   57 |       | NULL             |
| 4023 | dlp_r | localhost | dlp  | Sleep   |    0 |       | NULL             |
| 4024 | dlp_w | localhost | dlp  | Sleep   |  788 |       | NULL             |
| 4025 | dlp_r | localhost | dlp  | Sleep   |    0 |       | NULL             |
+------+-------+-----------+------+---------+------+-------+------------------+

Although, I am also confused as to why there are so many processes that are asleep. Shouldn't they all go away once a command is finished executing?

Thanks for any and all help =D

Aram Papazian
  • 133
  • 1
  • 3
  • 10

1 Answers1

0

The process is only using 100megs of memory. If you have any caching enabled in your config it will put the query+results in memory to make the next ones fast.

As for the sleeping connections. They are connections from your app that aren't closed yet. It would be a horrible assumption by mysql to close the connection after each query. It would mean your app would have to open a new connection for each query and slow down the render time for the page to load.

Mike
  • 22,310
  • 7
  • 56
  • 79
  • Ahhhh so from the looks of it the sleeping queries are queries that were not closed properly by my application? I'm pretty sure I have caching enabled (or will likely do it in the near future to speed up some queries). Would that increase processor time? – Aram Papazian May 18 '13 at 14:24
  • caching won't pass back anything to the db layer.. so yes it will. Sleeping queries take up little resources other then a connection slot. – Mike May 19 '13 at 14:45