Kopano is using up all mysql connections ?


  • I have just updated (using apt) kopano to the latest available version.

    We only have 5 users, and a VERY small volume of email sends / receive per day.

    I am also running a few other CMS apps that use VERY low volume mysql database connections, however none of these are able to connect due to kopano using up all the available connections.

    When I “SHOW PROCESSLIST;” in mysql, every single connection is being used by kopano and they are all sleeping, thereby blocking connections from any other application.

    How can I limit the number of connections that kopano is making, or potentially implement pooling instead ?

    > SHOW PROCESSLIST;
    +-----+--------+-----------+--------------+---------+-------+-------+------------------+----------+
    | Id  | User   | Host      | db           | Command | Time  | State | Info             | Progress |
    +-----+--------+-----------+--------------+---------+-------+-------+------------------+----------+
    |   2 | kopano | localhost | kopanoserver | Sleep   | 20942 |       | NULL             |    0.000 |
    |   4 | kopano | localhost | kopanoserver | Sleep   | 20941 |       | NULL             |    0.000 |
    |   5 | kopano | localhost | kopanoserver | Sleep   | 20942 |       | NULL             |    0.000 |
    |   6 | kopano | localhost | kopanoserver | Sleep   |     2 |       | NULL             |    0.000 |
    |   7 | kopano | localhost | kopanoserver | Sleep   |  2941 |       | NULL             |    0.000 |
    |   8 | kopano | localhost | kopanoserver | Sleep   |     2 |       | NULL             |    0.000 |
    |   9 | kopano | localhost | kopanoserver | Sleep   |     2 |       | NULL             |    0.000 |
    |  10 | kopano | localhost | kopanoserver | Sleep   |     2 |       | NULL             |    0.000 |
    |  11 | kopano | localhost | kopanoserver | Sleep   |     2 |       | NULL             |    0.000 |
    |  12 | kopano | localhost | kopanoserver | Sleep   |     2 |       | NULL             |    0.000 |
    |  13 | kopano | localhost | kopanoserver | Sleep   |     2 |       | NULL             |    0.000 |
    |  14 | kopano | localhost | kopanoserver | Sleep   |     2 |       | NULL             |    0.000 |
    |  15 | kopano | localhost | kopanoserver | Sleep   |     2 |       | NULL             |    0.000 |
    |  19 | kopano | localhost | kopanoserver | Sleep   |    50 |       | NULL             |    0.000 |
    |  20 | kopano | localhost | kopanoserver | Sleep   |  4851 |       | NULL             |    0.000 |
    |  25 | kopano | localhost | kopanoserver | Sleep   | 20371 |       | NULL             |    0.000 |
    |  27 | kopano | localhost | kopanoserver | Sleep   | 20310 |       | NULL             |    0.000 |
    |  42 | kopano | localhost | kopanoserver | Sleep   | 19270 |       | NULL             |    0.000 |
    |  86 | kopano | localhost | kopanoserver | Sleep   | 17548 |       | NULL             |    0.000 |
    | 106 | kopano | localhost | kopanoserver | Sleep   | 16768 |       | NULL             |    0.000 |
    | 109 | kopano | localhost | kopanoserver | Sleep   | 16708 |       | NULL             |    0.000 |
    | 175 | kopano | localhost | kopanoserver | Sleep   | 13947 |       | NULL             |    0.000 |
    | 176 | kopano | localhost | kopanoserver | Sleep   | 13172 |       | NULL             |    0.000 |
    | 177 | kopano | localhost | kopanoserver | Sleep   | 13112 |       | NULL             |    0.000 |
    | 184 | kopano | localhost | kopanoserver | Sleep   | 10351 |       | NULL             |    0.000 |
    | 185 | kopano | localhost | kopanoserver | Sleep   |  9571 |       | NULL             |    0.000 |
    | 186 | kopano | localhost | kopanoserver | Sleep   |  9511 |       | NULL             |    0.000 |
    | 190 | kopano | localhost | kopanoserver | Sleep   |  6750 |       | NULL             |    0.000 |
    | 195 | kopano | localhost | kopanoserver | Sleep   |  5970 |       | NULL             |    0.000 |
    | 196 | kopano | localhost | kopanoserver | Sleep   |  5910 |       | NULL             |    0.000 |
    | 259 | kopano | localhost | kopanoserver | Sleep   |  3149 |       | NULL             |    0.000 |
    | 305 | kopano | localhost | kopanoserver | Sleep   |  2369 |       | NULL             |    0.000 |
    | 306 | kopano | localhost | kopanoserver | Sleep   |  2308 |       | NULL             |    0.000 |
    | 355 | kopano | localhost | kopanoserver | Sleep   |   228 |       | NULL             |    0.000 |
    | 356 | kopano | localhost | kopanoserver | Sleep   |    35 |       | NULL             |    0.000 |
    | 361 | root   | localhost | NULL         | Query   |     0 | init  | SHOW PROCESSLIST |    0.000 |
    +-----+--------+-----------+--------------+---------+-------+-------+------------------+----------+
    36 rows in set (0.01 sec)
    
  • Kopano

    Hi @crankshaft,

    this sounds a bit like https://forum.kopano.io/topic/3164/problems-after-upgrade-kc-8-7-9-to-8-7-12. Which version are you using exactly?


  • @fbartels - thanks, yes I am using 8.7.12 which was done a couple of days ago using apt upgrade.

    I have just limited the number of USER connections to 25 in mysql.cnf:

    max_connections       = 50  # DEFAULT 100
    max_user_connections  = 25  # DEFAULT 38
    

  • maybe connection pooling would be a better solution given that 99% of these persistent connections appear to be idle 99% of the time ?!

  • Kopano

    Yes, normally the server will pool connections (a connection per thread). Its a defect that is causing the additional connections to be created.


  • There is no SQL connection pooling at present (ZCP just did not do it and it has not changed since); each thread keeps its own connection state. You can look at the thread list with utilities like htop, gdb, or stabbing in /proc/N/task manually.


  • Is there a fix in the pipeline for this problem ??

  • Kopano

    yes, see the post I linked you to in my first reply.


  • Thanks, but I already checked that post, there does not appear to be any fix for this yet ?

  • Kopano

    @crankshaft not, a public one. but there was a pull request linked which will be part of the next Kopano release. We are currently busy doing last tests and if no errors are found there will be a release probably next week.


  • Understood, thanks

  • Kopano

    Hi @crankshaft,

    8.7.13 is now available in the pre-final download directory. Your feedback would be appreciated.

    See https://forum.kopano.io/topic/3215/kopano-core-8-7-13-pre-final for more information.


  • Thanks, but I have only ever installed and updated using debian apt update and there are no updates showing right now ?


  • @crankshaft you probably used the repositorie https://kb.kopano.io/display/WIKI/1.+Core

    you need to change

    #Core
    deb https://serial:<INSERT_SERIALKEY>@download.kopano.io/supported/core:/final/xxx/ ./
    

    to

    #Core
    deb https://serial:<INSERT_SERIALKEY>@download.kopano.io/supported/core:/pre-final/xxx/ ./
    

  • @xthbx said in Kopano is using up all mysql connections ?:

    deb https://serial:<INSERT_SERIALKEY>@download.kopano.io/supported/core:/final/xxx/ ./

    Thanks, I forgot about that, long time since I installed it, I have added the repo and will test later after taking a snapshot of the server first.


  • The upgrade was successful and at the moment there are 13 processes open, and I saw a new process start and stop again, which I dont recall noticing before.

    But it will have to run for a day before I can see if there’s any real difference.


  • There is still 38 (the default mysql max) connections to mysql and given that we only have 5 users and today nobody is accessing emails, I am surprised there are so many active connections.

    Why not implement proper mysql connection pooling and have an option to set the pool size, so that the threads can share the connection pool ?

    This seems to be a better approach than all of these open connections which are sleeping 99% of the time ?


  • i also don’t understand what changed from 8.7.9 to 8.7.1x


  • @crankshaft said in Kopano is using up all mysql connections ?:

    Why not implement proper mysql connection pooling and have an option to set the pool size, so that the threads can share the connection pool ?

    At worst, every thread (some number N) will have work to do, and then you need N connections anyway, whether you use the current static (thread-private on-first-use) approach or a (shared) pool.

    Running with a conn pool smaller than that would mean requests to kopano-server via the SOAP-HTTP interface would have to block waiting for a free SQL connection, at which point you could just reduce the threadcount with the same effect.