Norman,
That type of solution would work, but it would be a more involved solution.
I'm not against a dynamic calculation.... just against the way it's being
calculated...
I haven't actually looked at the code, but just guessing... it looks like it
reads all message_name values from the inbox in a single query, then goes
back and gets the length of each message one at a time using the
message_name in the WHERE clause:
SELECT length(message_body) FROM inbox WHERE message_name =
'Mail1164150791718-2049' AND repositoryname ='spam.jerry.xxxxxx.com'
Instead, can't you simply do a single:
SELECT length(message_body) FROM inbox WHERE repositoryname
='spam.jerry.xxxxxx.com'
...and then loop through the ResultSet adding things up? Basically
identical philosophy of adding things up each time, but only a single query
to the db, then all the looping is internal code.
Am I missing something obvious that would prevent this type of solution? (I
know just enough SQL to be dangerous... :-) ) Or do you think it would
work?
Thx.
Jerry
-----Original Message-----
From: Norman Maurer [mailto:[EMAIL PROTECTED]
Sent: Sunday, November 26, 2006 5:42 AM
To: James Users List
Subject: Re: DB Datasource connection differences between 2.2.0a17 and
2.3.0??
Hi Jerry,
thx for all your informations .. And yes you are right its a "bad" way
to do the counting everytime again. I have to think about howto fix this
in a nice backward compatible way..
At the moment i think about a QuotaService which hold the current quota
of each user. The quota should be modified on the fly. So on every new
deliver or retrieve it should remove or add the needed message size to
it. So we have only to check the actual quota in the mailet and not need
to calculate it every time again.
Any thoughts ?
bye
Norman
JWM schrieb:
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]
!EXCUBATOR:1,45692de153071722031479!
---------------------------------------------------------------------
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]
!EXCUBATOR:1,4569d54053075792758459!
