Page MenuHomePhabricator

Some users use persistent connections that are idle, wasting memory and other resources that could be used for other users
Closed, ResolvedPublic

Description

MariaDB LABS localhost (none) > SHOW PROCESSLIST;
| 490134574 | p50380g50921    | 10.68.16.70:36721  | dewiki_p           | Sleep   |      149 |                                                                             | NULL                                                                                                 |    0.000 |
| 490138806 | p50380g50921    | 10.68.16.70:36860  | dewiki_p           | Sleep   |      148 |                                                                             | NULL                                                                                                 |    0.000 |
| 490138808 | p50380g50921    | 10.68.16.70:36862  | dewiki_p           | Sleep   |      149 |                                                                             | NULL                                                                                                 |    0.000 |
| 490149321 | p50380g50921    | 10.68.16.70:37174  | hewiki_p           | Sleep   |      298 |                                                                             | NULL                                                                                                 |    0.000 |
| 490149326 | p50380g50921    | 10.68.16.70:37177  | hewiki_p           | Sleep   |      297 |                                                                             | NULL                                                                                                 |    0.000 |
| 490152079 | p50380g50921    | 10.68.16.70:37340  | hewiki_p           | Sleep   |      298 |                                                                             | NULL                                                                                                 |    0.000 |
| 490153306 | p50380g50921    | 10.68.16.70:37370  | dewiki_p           | Sleep   |      149 |                                                                             | NULL                                                                                                 |    0.000 |
| 490153307 | p50380g50921    | 10.68.16.70:37371  | dewiki_p           | Sleep   |      149 |                                                                             | NULL                                                                                                 |    0.000 |
| 490153310 | p50380g50921    | 10.68.16.70:37374  | dewiki_p           | Sleep   |      148 |                                                                             | NULL                                                                                                 |    0.000 |
| 490153339 | p50380g50921    | 10.68.16.70:37381  | hewiki_p           | Sleep   |      298 |                                                                             | NULL                                                                                                 |    0.000 |
| 490154561 | p50380g50921    | 10.68.16.70:37400  | hiwiki_p           | Sleep   |      241 |                                                                             | NULL                                                                                                 |    0.000 |
| 490154562 | p50380g50921    | 10.68.16.70:37401  | hiwiki_p           | Sleep   |      244 |                                                                             | NULL                                                                                                 |    0.000 |
| 490154563 | p50380g50921    | 10.68.16.70:37402  | hiwiki_p           | Sleep   |      242 |                                                                             | NULL                                                                                                 |    0.000 |
| 490154564 | p50380g50921    | 10.68.16.70:37403  | hiwiki_p           | Sleep   |      245 |                                                                             | NULL                                                                                                 |    0.000 |
| 490154566 | p50380g50921    | 10.68.16.70:37405  | hiwiki_p           | Sleep   |      245 |                                                                             | NULL                                                                                                 |    0.000 |
| 490154568 | p50380g50921    | 10.68.16.70:37407  | hiwiki_p           | Sleep   |      242 |                                                                             | NULL                                                                                                 |    0.000 |
| 490154569 | p50380g50921    | 10.68.16.70:37408  | hiwiki_p           | Sleep   |      241 |                                                                             | NULL                                                                                                 |    0.000 |
| 490154716 | p50380g50921    | 10.68.16.70:37414  | ruwiki_p           | Sleep   |      147 |                                                                             | NULL                                                                                                 |    0.000 |
| 490154719 | p50380g50921    | 10.68.16.70:37415  | ruwiki_p           | Sleep   |      147 |                                                                             | NULL                                                                                                 |    0.000 |
| 490154723 | p50380g50921    | 10.68.16.70:37417  | ruwiki_p           | Sleep   |      147 |                                                                             | NULL                                                                                                 |    0.000 |
| 490154725 | p50380g50921    | 10.68.16.70:37419  | ruwiki_p           | Sleep   |      147 |                                                                             | NULL                                                                                                 |    0.000 |
| 490154726 | p50380g50921    | 10.68.16.70:37420  | ruwiki_p           | Sleep   |      147 |                                                                             | NULL                                                                                                 |    0.000 |
| 490154728 | p50380g50921    | 10.68.16.70:37422  | ruwiki_p           | Sleep   |      147 |                                                                             | NULL                                                                                                 |    0.000 |
| 490155514 | p50380g50921    | 10.68.16.70:37438  | ruwiki_p           | Sleep   |      147 |                                                                             | NULL                                                                                                 |    0.000 |
| 490155526 | p50380g50921    | 10.68.16.70:37439  | ruwiki_p           | Sleep   |      147 |                                                                             | NULL                                                                                                 |    0.000 |
| 490155745 | p50380g50921    | 10.68.16.70:37441  | hiwiki_p           | Sleep   |      244 |                                                                             | NULL                                                                                                 |    0.000 |
| 490155965 | p50380g50921    | 10.68.16.70:37445  | dewiki_p           | Sleep   |      148 |                                                                             | NULL                                                                                                 |    0.000 |
| 490155966 | p50380g50921    | 10.68.16.70:37446  | dewiki_p           | Sleep   |      149 |                                                                             | NULL                                                                                                 |    0.000 |
| 490156122 | p50380g50921    | 10.68.16.70:37448  | dewiki_p           | Sleep   |      149 |                                                                             | NULL                                                                                                 |    0.000 |
| 490156192 | p50380g50921    | 10.68.16.70:37449  | dewiki_p           | Sleep   |      149 |                                                                             | NULL                                                                                                 |    0.000 |
| 490156273 | p50380g50921    | 10.68.16.70:37452  | tlwiki_p           | Sleep   |      167 |                                                                             | NULL                                                                                                 |    0.000 |
| 490156274 | p50380g50921    | 10.68.16.70:37453  | tlwiki_p           | Sleep   |      167 |                                                                             | NULL                                                                                                 |    0.000 |
| 490156277 | p50380g50921    | 10.68.16.70:37454  | tlwiki_p           | Sleep   |      167 |                                                                             | NULL                                                                                                 |    0.000 |
| 490156287 | p50380g50921    | 10.68.16.70:37455  | tlwiki_p           | Sleep   |      167 |                                                                             | NULL                                                                                                 |    0.000 |
| 490156288 | p50380g50921    | 10.68.16.70:37456  | tlwiki_p           | Sleep   |      167 |                                                                             | NULL                                                                                                 |    0.000 |
| 490156289 | p50380g50921    | 10.68.16.70:37458  | tlwiki_p           | Sleep   |      167 |                                                                             | NULL                                                                                                 |    0.000 |
| 490156290 | p50380g50921    | 10.68.16.70:37457  | tlwiki_p           | Sleep   |      167 |                                                                             | NULL                                                                                                 |    0.000 |
| 490156291 | p50380g50921    | 10.68.16.70:37459  | tlwiki_p           | Sleep   |      167 |                                                                             | NULL                                                                                                 |    0.000 |
| 490156292 | p50380g50921    | 10.68.16.70:37460  | tlwiki_p           | Sleep   |      167 |                                                                             | NULL                                                                                                 |    0.000 |
| 490156293 | p50380g50921    | 10.68.16.70:37461  | tlwiki_p           | Sleep   |      167 |                                                                             | NULL                                                                                                 |    0.000 |
| 490157249 | p50380g50921    | 10.68.16.70:37464  | ruwiki_p           | Sleep   |      147 |                                                                             | NULL                                                                                                 |    0.000 |
| 490157252 | p50380g50921    | 10.68.16.70:37465  | ruwiki_p           | Sleep   |      147 |                                                                             | NULL                                                                                                 |    0.000 |
| 490157542 | p50380g50921    | 10.68.16.70:37469  | jawiki_p           | Sleep   |      112 |                                                                             | NULL                                                                                                 |    0.000 |
| 490157543 | p50380g50921    | 10.68.16.70:37470  | jawiki_p           | Sleep   |      112 |                                                                             | NULL                                                                                                 |    0.000 |
| 490157544 | p50380g50921    | 10.68.16.70:37471  | jawiki_p           | Sleep   |      113 |                                                                             | NULL                                                                                                 |    0.000 |
| 490157545 | p50380g50921    | 10.68.16.70:37472  | jawiki_p           | Sleep   |      112 |                                                                             | NULL                                                                                                 |    0.000 |
| 490157546 | p50380g50921    | 10.68.16.70:37473  | jawiki_p           | Sleep   |      112 |                                                                             | NULL                                                                                                 |    0.000 |
| 490157547 | p50380g50921    | 10.68.16.70:37474  | jawiki_p           | Sleep   |      112 |                                                                             | NULL                                                                                                 |    0.000 |
| 490157548 | p50380g50921    | 10.68.16.70:37476  | jawiki_p           | Sleep   |      113 |                                                                             | NULL                                                                                                 |    0.000 |
| 490157549 | p50380g50921    | 10.68.16.70:37477  | jawiki_p           | Sleep   |      112 |                                                                             | NULL                                                                                                 |    0.000 |
| 490157551 | p50380g50921    | 10.68.16.70:37478  | jawiki_p           | Sleep   |      113 |                                                                             | NULL                                                                                                 |    0.000 |
| 490157558 | p50380g50921    | 10.68.16.70:37479  | jawiki_p           | Sleep   |      112 |                                                                             | NULL     

MariaDB LABS localhost (none) > SHOW PROCESSLIST;
| 480934463 | s52584          | 10.68.18.53:44228  | plwiktionary_p     | Sleep   |       12 |                                                                             | NULL                                                                                                 |    0.000 |
| 480934466 | s52584          | 10.68.18.53:44229  | plwiktionary_p     | Sleep   |       12 |                                                                             | NULL                                                                                                 |    0.000 |
| 480934467 | s52584          | 10.68.18.53:44230  | plwiktionary_p     | Sleep   |       12 |                                                                             | NULL                                                                                                 |    0.000 |
| 480934468 | s52584          | 10.68.18.53:44231  | plwiktionary_p     | Sleep   |       22 |                                                                             | NULL                                                                                                 |    0.000 |
| 480934469 | s52584          | 10.68.18.53:44232  | plwiktionary_p     | Sleep   |       22 |                                                                             | NULL                                                                                                 |    0.000 |
| 480934470 | s52584          | 10.68.18.53:44233  | plwiktionary_p     | Sleep   |       22 |                                                                             | NULL                                                                                                 |    0.000 |
| 480934471 | s52584          | 10.68.18.53:44234  | plwiktionary_p     | Sleep   |       27 |                                                                             | NULL                                                                                                 |    0.000 |
| 480934472 | s52584          | 10.68.18.53:44235  | plwiktionary_p     | Sleep   |       27 |                                                                             | NULL                                                                                                 |    0.000 |
| 480934473 | s52584          | 10.68.18.53:44236  | plwiktionary_p     | Sleep   |       27 |                                                                             | NULL                                                                                                 |    0.000 |
| 480934474 | s52584          | 10.68.18.53:44237  | plwiktionary_p     | Sleep   |        2 |                                                                             | NULL                                                                                                 |    0.000 |
```                                                                                            |    0.000 |

Event Timeline

jcrespo created this task.Jun 21 2016, 9:06 AM
Restricted Application added subscribers: Zppix, Aklapper. · View Herald TranscriptJun 21 2016, 9:06 AM

I'm sorry, this shouldn't have happened. I've killed those processes and disabled any scripts and webapps that had access to that db until I fix this issue.

valhallasw added a subscriber: valhallasw.

10.68.16.70 is maps-wma1.maps.eqiad.wmflabs.

Restricted Application added a project: Discovery. · View Herald TranscriptJun 21 2016, 11:49 AM

I've disabled connection pooling by enforcing the number of idle connections to zero with an immediate eviction. In case someone finds this useful, I'm putting here the specific <Resource> attributes in Tomcat's context.xml that had to be set (in my case) in order to disable pooling:

  • minIdle="0"
  • maxIdle="0"
  • initialSize="0"
  • minEvictableIdleTimeMillis="0"

@jcrespo in spite of that policy, would it be acceptable to keep a minimal pool of short-lived idle connections (let's say 1-5 minutes, only one idle connection per finished active connection), leaving it empty if there is no activity? Does said policy apply to databases created on the shared server (tools.labsdb), too? Are there any other configuration aspects related to resource saving that should be considered (e.g. maximum number of connections)?

jcrespo closed this task as Resolved.Jun 28 2016, 3:29 PM
jcrespo claimed this task.

@PeterBowman Thank you for taking the time to check this. We can agree to any reasonable decision, please open a new ticket, express your needs and we can work out something. Your previous config was using 50+ connections per server that I was never able to see in *actual usage*.

Indeed, non-replica access is less concerning- the main problem right now was replica connection handling.

The idea is try to be as lax as possible by default, and I ping people when I see how we can improve the overall performance for all users. If you want a suggestion, do never connect more than 10 times simultaneously per server, but that is not a rigid rule- someone using 20 busy connections 5 minutes a month is not an issue, while someone using 1 single connection but spending all resources would be.

I will consider this as resolved now, but let's continue talking- let's open a communication channel to see what needs you have and how we can satisfy those.

Hi, @jcrespo, thank you for your quick response. I'm just starting to develop some simple tools for a relatively small project and their usage is still scarce. By now, I'll happily follow your recommendations, keep an eye on the resource usage and contact you if needed.