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 <m...@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 > m...@solinus.com > _______________________________________________ > DBmail mailing list > DBmail@dbmail.org > http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail -- Mark Maurer m...@solinus.com Programmer / Systems Administrator Solinus, Mailfoundry, Xensr _______________________________________________ DBmail mailing list DBmail@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
