[
https://issues.apache.org/jira/browse/JAMES-717?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Norman Maurer resolved JAMES-717.
---------------------------------
Resolution: Fixed
Fix Version/s: 3.0-M2
Assignee: Norman Maurer
We now use the mailbox-api which helps todo such things more performant. So
close it
> AbstractStorageQuota use one queriesper mail to calculate the mailbox size
> . This is very ineffective and can cause problems with sql
> ---------------------------------------------------------------------------------------------------------------------------------------
>
> Key: JAMES-717
> URL: https://issues.apache.org/jira/browse/JAMES-717
> Project: JAMES Server
> Issue Type: Bug
> Affects Versions: 2.3.0
> Reporter: Norman Maurer
> Assignee: Norman Maurer
> Fix For: 3.0-M2
>
>
> From ml:
> Ahh, the old multiple-repository-type problem... I know it goes against the
> philosophy of transparent pluggable repository types. But it looks like the
> only option here is to have a different mailet version that works only with
> db repositories. Or, just put a warning.
> Thanks for the explanation. The good news is that this indeed was the
> problem I originally encountered, and I've run almost 24 hours on 2.3.0 with
> no apparent problems.
> Jerry
> -----Original Message-----
> From: Norman Maurer [mailto:[email protected]]
> Sent: Sunday, November 26, 2006 12:16 PM
> To: James Users List
> Subject: Re: DB Datasource connection differences between 2.2.0a17 and
> 2.3.0??
> Hi Jerry,
> the problem with your calculation is that we not can know which
> MailRepository implementation is used.. So we need to have a solution
> which work independ of he useded MailRepository implementation. The best
> solution whould be to place the quota stuff in the repository layer..
> But this will not work will keeping backward compatiblity.
> I think at the meoment we should add a warning or bette remove the
> mailets from repos till its fixed!
> bye
> Norman
> JWM schrieb:
> > > 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!
> > >
> ---------------------------------------------------------------------
> 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]
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
