[JAMES-717] AbstractStorageQuota use one queriesper mail to calculate the mailbox size . This is very ineffective and can cause problems with sql

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.3.0
• Fix Version/s: 3.0-M2
• Assignee: Norman Maurer
• Reporter: Norman Maurer
• Created: 27/Nov/06 06:50
• Updated: 29/Oct/10 16:33
• Resolved: 29/Oct/10 16:33

Description

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 [e-mail]
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 [e-mail]
> > 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 [e-mail]
>> >> 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: [e-mail]
>> >> For additional commands, e-mail: [e-mail]
>> >>
>> >>
>> >>
>> >>
>> >> ---------------------------------------------------------------------
>> >> To unsubscribe, e-mail: [e-mail]
>> >> For additional commands, e-mail: [e-mail]
>> >>
>> >> Unable to render embedded object: File (1,45692de153071722031479) not found.
>> >>
>> >>
> >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [e-mail]
> > For additional commands, e-mail: [e-mail]
> >
> >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [e-mail]
> > For additional commands, e-mail: [e-mail]
> >
> > Unable to render embedded object: File (1,4569d54053075792758459) not found.
> >

---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]

---------------------------------------------------------------------
To unsubscribe, e-mail: [e-mail]
For additional commands, e-mail: [e-mail]