Re: db thread congestion issue with dbmail 3.2.3

apologies, I forgot to add this originally. In addendum to below:

while this occurs, I’m seeing this in the logs from dbmail:

Apr 29 15:26:31 mail dbmail/imap4d[4621]: Error:[db] db_exec(+388): SQLException: Lock wait timeout exceeded; try restarting transaction
Apr 29 15:26:31 mail last message repeated 2 times
Apr 29 15:26:31 mail dbmail/imap4d[4621]: Error:[db] db_exec(+389): failed query [UPDATE dbmail_messages SET status=2 WHERE message_idnr=24497739 ]
Apr 29 15:26:31 mail dbmail/imap4d[4621]: Error:[db] db_exec(+389): failed query [UPDATE dbmail_messages SET status=2 WHERE message_idnr=24497733 ]
Apr 29 15:26:31 mail dbmail/imap4d[4621]: Error:[db] db_exec(+389): failed query [UPDATE dbmail_messages SET status=2 WHERE message_idnr=24497745 ]
Apr 29 15:26:32 mail dbmail/imap4d[4621]: Error:[db] db_exec(+388): SQLException: Lock wait timeout exceeded; try restarting transaction
Apr 29 15:26:32 mail last message repeated 2 times
Apr 29 15:26:32 mail dbmail/imap4d[4621]: Error:[db] db_exec(+389): failed query [UPDATE dbmail_messages SET status=2 WHERE message_idnr=24497735 ]
Apr 29 15:26:32 mail dbmail/imap4d[4621]: Error:[db] db_exec(+389): failed query [UPDATE dbmail_messages SET status=2 WHERE message_idnr=24497747 ]
Apr 29 15:26:32 mail dbmail/imap4d[4621]: Error:[db] db_exec(+389): failed query [UPDATE dbmail_messages SET status=2 WHERE message_idnr=24497741 ]

And I am seeing repeating message_idnr numbers, which would make sense if the queries time out and retry constantly.

> On Apr 29, 2015, at 9:28 AM, Mark Maurer <[email protected]> wrote:
> 
> Hey all —
> 
> I recently upgraded our install from 2.2.11 to 3.2.3.  An issue has cropped up since the upgrade, where db threads seem to get congested, stuck running the same type of query:
> 
> a current sample:
> 
> +---------+--------+-----------------+--------+---------+------+----------+---------------------------------------------------------------------------------------------+
> | Id      | User   | Host            | db     | Command | Time | State    | Info                                                                                        |
> +---------+--------+-----------------+--------+---------+------+----------+---------------------------------------------------------------------------------------------+
> | 1288823 | dbmail | 10.0.0.88:21865 | dbmail | Sleep   |   15 |          | NULL                                                                                        |
> | 1294724 | dbmail | 10.0.0.88:39566 | dbmail | Query   |   21 | Updating | UPDATE dbmail_messages SET status=2 WHERE message_idnr=24497928                             |
> | 1294729 | dbmail | 10.0.0.88:39578 | dbmail | Sleep   |    0 |          | NULL                                                                                        |
> | 1294731 | dbmail | 10.0.0.88:39584 | dbmail | Query   |   51 | Updating | UPDATE dbmail_messages SET status=2 WHERE message_idnr=24498190                             |
> 
> Those update queries will grow in number until it consumes the number of max_connections that I’ve set in dbmail.conf.
> 
> In searching the lists, I’ve seen mention of this, but all referring to dbmail 2.x  
> 
> Current environment:
>  frontend:
>    Freebsd 10.1.p5, quad core xeon 2.66 GHz, 4 GB ram, dbmail 3.2.3 from ports, nginx proxy (for ssl), imap only (no pop3), postfix mail server
> 
>  backend:
>    Freebsd 9.2.p10, running mysql 5.5.25 (from ports), 16 GB ram, zfs disk, pure db server, 95% for dbmail.
> 
>  51 email users, 214 aliases, busy mail intake throughout the day
> 
>  approx 1 million messages in the DB, over 50 GB of mail data
> 
>  majority of mail clients are apple mail, and iphones.  2 outlook clients (2007 and 2010)
> 
> I know it’s not recommended, but I’ve upped max_db_connections to 50 at this point, just to allow a longer time between restarts, as the UPDATE queries will tend to grow over time, until they flood the available connections.
> 
> Could anyone help shed some light as to what may be going on?  
> 
> --
> Mark Maurer
> [email protected]
> _______________________________________________
> DBmail mailing list
> [email protected]
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

--
Mark Maurer
[email protected]
Programmer / Systems Administrator
Solinus, Mailfoundry, Xensr

_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail