Advanced
Mailing List Archive

Mailing List Archive

  1. Home >
  2. DBMail>
  3. users
db thread congestion issue with dbmail 3.2.3
mark at solinus
Apr 29, 2015, 7:28 AM
Post #1 of 4 (1556 views)
Permalink
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
mark@solinus.com
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
Re: db thread congestion issue with dbmail 3.2.3 [ In reply to ]
mark at solinus
Apr 29, 2015, 8:36 AM
Post #2 of 4 (1513 views)
Permalink
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 <mark@solinus.com> 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
> mark@solinus.com
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

--
Mark Maurer
mark@solinus.com
Programmer / Systems Administrator
Solinus, Mailfoundry, Xensr

_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
Re: db thread congestion issue with dbmail 3.2.3 [ In reply to ]
lordvan at lordvan
Apr 30, 2015, 12:30 AM
Post #3 of 4 (1505 views)
Permalink
Am 29.04.2015 um 17:36 schrieb Mark Maurer:
> 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 ]
>

Hi

Did you try running one/some of those queries manually?
If so what was the result?

Regards

_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
Re: db thread congestion issue with dbmail 3.2.3 [ In reply to ]
mark at solinus
Apr 30, 2015, 6:06 AM
Post #4 of 4 (1508 views)
Permalink
> On Apr 30, 2015, at 2:30 AM, Thomas Raschbacher <lordvan@lordvan.com> wrote:
>
> Am 29.04.2015 um 17:36 schrieb Mark Maurer:
>> 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 ]
>>
>
> Hi
>
> Did you try running one/some of those queries manually?
> If so what was the result?
>
> Regards

I did indeed. I got the same results of lock contention that was showing up in the processlist.
However, I did manage to find the culprit late last night, and am not seeing the issue anymore:

I traced all the message id’s back to a single user, who was running thunderbird 31.6.0. They had approximately 33k messages in Trash that
they were trying to delete. It was that action that was causing all the lock contention on the dbmail_messages table.

The user switched clients, deleted their trash with no issues, and switched back. The issue has not cropped up since, and everything is as
fast and stable as can be.

Not sure why thunderbird was having such a hard time deleting the trash, but there it is.

--
Mark Maurer
mark@solinus.com
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

©2024 GT.net. A Gossamer Threads company.
5th Floor, 455 Granville St., Vancouver, BC V6C 1T1, Canada | Legal