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]
