Hi Norman,
(Please read all of this... I think there is a problem that needs
addressing...)
I was pulling the info you requested together to send to you, and decided to
do a little more investigation. I think I've figured out what is happening,
and it's not good news. I believe the problem is the new (at least new
sometime since 2.2.0a17...) OverQuota mailet.
I figured if James was having trouble getting connections, and MySQL was not
reporting any errors, it must simply mean that the number of connections
MySQL allocated is maxing out. But that begs the question why my 2.3
version is using more connections at any one instant than my 2.2 version
used (which HAD to be the case since no connection errors with 2.2.0a17).
I went back to my MySQL trace file from last night and I found there are a
"billion" 'SELECT length(message_body)..." SQL statements.
It took me a second to figure out where these were coming from and then I
realized what's happening... This OverQuota mailet is going through every
message in the target inbox for each incoming message, apparently adding up
the total message length looking for an exceeded quota.
This might be fine for an environment that typically has 10-12 emails at any
one time in a user's inbox. But sometimes over weekends, some of my users'
mail gets backed up. Also, I have a system where I route probable spam from
spamassassin to a secondary 'spam' inbox associated with each real inbox.
Spam sits there for a couple of weeks before I auto-clean it, just in case a
valid note got routed to it and needs to be recovered. So I have these
"spam inboxes" with sometimes 1000-2000 emails.
Granted, these spam inboxes are over the mailet's default 20M quota, and I
should have thought about that. But the comments about the mailet in the
default config file said that the mailet was benign and would simply flag
the note with an over-quota message. I figured this might be a nice way to
nudge users to keep their inboxes a little cleaner. So, when creating the
new config file for 2.3, I figured, "why not?..." and left the mailet in
there.
So here's the picture.... every time a new spam note arrives for me, the
mailet goes to my associated spam inbox and does ~1000-2000 INDIVIDUAL
QUERIES!!! One for EACH NOTE currently in the inbox!!!!
Surprise... MySQL has no available connections when a POP3, Spool, or any
other service needs to get to the db.
I'm flabbergasted that this mailet reads every message ONE AT A TIME from
the inbox to get the length. How about instead simply doing ONE query to
read all of the records at one time??? The current implementation does not
follow best practices.
In lieu of that.... I strongly encourage you to put a warning in the config
file that says that enabling this mailet will take your database to its
knees and very likely can cause connections to periodically be unavailable.
I know I could add more connections to my MySQL config.... but I really
don't want to... And I had no idea I needed to simply because I enabled a
mailet.
Thanks for looking into this.
Jerry
P.S. The mailet's gone from my 2.3.0 config now, and I've so far run an hour
with no connection problems...
A few lines from MySQL's trace file:
5231 Query SELECT length(message_body) FROM inbox
WHERE message_name = 'Mail1164150791718-2049' AND repository_name =
'spam.jerry.xxxxxx.com'
5233 Query SELECT length(message_body) FROM inbox
WHERE message_name = 'Mail1164151015687-2074' AND repository_name =
'spam.jerry.xxxxxx.com'
5235 Query SELECT length(message_body) FROM inbox
WHERE message_name = 'Mail1164151324375-2093' AND repository_name =
'spam.jerry.xxxxxx.com'
5237 Query SELECT length(message_body) FROM inbox
WHERE message_name = 'Mail1164151412093-2109' AND repository_name =
'spam.jerry.xxxxxx.com'
-----Original Message-----
From: Norman Maurer [mailto:[EMAIL PROTECTED]
Sent: Saturday, November 25, 2006 12:32 PM
To: James Users List
Subject: Re: DB Datasource connection differences between 2.2.0a17 and
2.3.0??
Hi Jerry,
thats really strange, i think we changed nothing in the pool handling.
Maybe you give some more informations about your config.xml and tell us
the mysql version + mysql jdbc connector version.
bye
Norman
JWM schrieb:
> I am attempting to move up to 2.3.0 from 2.2.0a17. 2.3.0 runs somewhat...
> Mail is passing through it, getting stored, etc. But after running a
short
> time, Outlook started popping up those "can't logon to POP.... give me
> another password" popups. I'd hit enter and they'd go away, mail would
> arrive. But then a few minutes later they'd start popping up again.
>
> I went to the logs and saw hundreds of the following error message (in all
> the logs... not just POP):
>
> java.sql.SQLException: Cannot connect to MySQL server on 127.0.0.1:3306.
Is
> there a MySQL server running on the machine/port you are trying to connect
> to? (java.net.BindException)
>
> (The answer is 'yes'... there is a MySQL server on that port... James
talked
> to it a few milliseconds before and a few milliseconds after each of these
> messages...)
>
> My debug attempts so far:
>
> 1) I simply shut down 2.3.0 and fired up 2.2.0a17 again. It ran solid
with
> zero errors for several hours. Swapped back to 2.3.0, and started getting
> errors within 10 minutes. NO other changes in the machine except the
> version of James that is running.
>
> 2) I never recycled mysql during any of this. Same exact mysql for both.
> So the old James can talk to the db consistently just fine, and the new
> James can't seem to connect ever few times it tries.
>
> 3) There are no errors in the mysql log. So it doesn't look like a mysql
> connection issue.
>
> I'm running with 40 max connections (same with both versions of the
server).
> But the default config file has 20. It also says to bump it up if I get a
> certain error. But it's not this error message.
>
> I figure I could go in and start bumping it higher. But I don't like
simply
> opening throttles up with no idea why.
>
> So my question is... what has changed between 2.2.0a17 and 2.3.0 in the
area
> of db connections? This message seems to indicate a socket error with the
> mysql server. But mysql never changes and it only fails with the new
> version of James.
>
> I'm a software architect and can do debug. So feel free to talk as techie
> as you like about this.
>
> Please give me some hints as to what might be happening. I can't move to
> 2.3.0 until I figure this out.
>
> Thx.
>
> Jerry
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
