Solved

problems with large volume of emails in coldfusion

Posted on 2004-09-16
19
261 Views
Last Modified: 2013-12-24
Hey there,
I currently run coldfusion enterprise 6.1.... I have written an auto contact app for a clients website which parses our database (over 3 million users) and sends them personalized email newsletters. The problem Im running into is that the scheduled task which generates and sends the emial does not seem to send any mail whatsoever... the memory usage on the server gets gigantic and then I get a coldfusion out of memory error.... how do I correct this? I chose coldfusion 6.1 for this solution because of the 1000000+ emails an hour it can supposedly output..... does anyone have a solution to this problem... I can post the code if need be.

Cheers,
Rick
0
Comment
Question by:richardsimnett
  • 9
  • 5
  • 2
  • +1
19 Comments
 
LVL 17

Accepted Solution

by:
Tacobell777 earned 500 total points
ID: 12081021
Why not get the users in batches, example

SELECT TOP 500 *
FROM yourtable
WHERE userID NOT IN (SELECT userID FROM tblSentMail)
send out the emails with cfquery and use the query attribute on the tag, do not put a loop around the cfmail tag

when done, flag those 500 as having send them an email, you could create a bew table
[mailOutID] [userID]
mailOutID is an id created at the beginning of the process and userID is each user that has been send an email

around all this you can do a loop with a condition of while queryName.recordCount
0
 
LVL 2

Expert Comment

by:dldeeds
ID: 12081305
I do not have a solution, but am interested in this as we are about to write an app that will do the same, althought no where near as big. Probably around 2,000-3,000. Ours is more of an email notice that some new document has been added to the site, (users will register to get "alerted" for specific types of documents). The email will contain links to the documents that the users registered to get notices on. No attachments in the mail, and much smaller email size overall.

I can only think of a few suggestions which you may have already done...

1) Does the schedule task dump any mail into the "undelivr" folder (in the "Mail" folder under your cf root).

2) Does the app work if you run it "manually" as opposed to via a scheduled task.

3) Do the logs show any other errors occuring before the out of memory error

4) Are you running the latest updater for cfmx (a new one was released Aug 25)

0
 
LVL 17

Expert Comment

by:Tacobell777
ID: 12081484
can you post the code?
0
 

Author Comment

by:richardsimnett
ID: 12081538
dldeeds,
   1) no it doesnt go anywhere at all..... it doesnt write anything to the disk....
   2) no the app does not run properly run manually. It does the same thing.
   3) no there are no other errors
   4) I am running version 6.1 as of some time in aug, with updated drivers.
 

I am going to post the code. Take a look at that.

Cheers,
Rick
0
 

Author Comment

by:richardsimnett
ID: 12081567
Here is the code exactly as it appears in my cfm page. As I stated before It just sucks up a ton of memory and then ends with an out of memory error.

<!---- Send an email campaign: THIS IS A SCHEDULED TASK! ---->
<!---- first see if the process is already running ---->
<!----<cfif isdefined("application.delivery")>---->
<!----      <cfif application.delivery neq "true">---->
<!----            <cfset application.delivery="true">---->
<!---- first get the next campaign record from the database ---->

<!---- generate todays date string ---->
<cfset tdate = dateformat(now(),"mmddyyyy")>
<!---- get the first unsent campaign in the list ---->
<cfquery
      name="getCampaign"
      datasource="MailPro">
      select top 1 * from campaigns
      where maildate = '#tdate#'
      and completed = -1
      order by id desc
</cfquery>
<!---- see if there is anything to process ---->
<cfif getCampaign.recordcount gt 0>
      <!---- set the status ---->
      <cfquery
            name="setStatus"
            datasource="mailpro">
            update campaigns
            set status = 'In Delivery Queue.'
            where id = #getCampaign.id#
      </cfquery>

      <!---- get/set the subject lines ---->
      <cfset sbjlines = ListToArray(getCampaign.subjectlines,'#chr(10)##chr(13)#')>

      <!---- get/set the from lines ---->
      <cfset frmLines = ListToArray(getCampaign.fromlines,'#chr(10)##chr(13)#')>

      <!---- get / set the domains ---->
      <cfquery
            name=getDomains
            datasource="mailpro">
            select * from domains
      </cfquery>

      <cfset dCount = 1>
      <cfset domainList = ArrayNew(1)>
      <cfset temp = ArraySet(domainList, 1,getdomains.recordcount, "")>

      <cfloop query=getDomains>
            <cfset domainList[dCount] = getDomains.domain>
            <cfset dCount = dCount + 1>
      </cfloop>

      <cfset dcount = 1>
      <cfset scount = 1>
      <cfset fcount = 1>

      <!---- get the footers for the message ---->
      <cfquery
            name=getFooters
            datasource=mailpro>
            select * from footers
      </cfquery>

      <cfset HTMLFooter = getfooters.HTMLFooter>
      <cfset TEXTFooter = getfooters.TextFooter>


      <!---- now generate the email ---->

      <!---- now get the data table name ---->
      <cfquery
            name="getTablename"
            datasource="MailPro">
            select datatablename from lists
            where id = #getcampaign.listid#
      </cfquery>

      <!---- now get the email addresses ---->
      <cfquery
            name="getRecipients"
            datasource="ListData">
            select top #getcampaign.numsend# * from #getTablename.datatablename#
            where id >= #getcampaign.startid#
      </cfquery>

      Start Time: <cfoutput>#now()#</cfoutput><br>
      <!---- Generate and send the email ---->
      <cfloop query=getrecipients>
            <!---- figure out which domain to use ---->
            <!---- generate message serial number ---->
            <!---- List Key   xxx-xxxxxxxxxxxx-xxx-xxxx-xxxx-???
                  Item1 - campaign id
                  Item2 - user id
                  Item3 - subject line id
                  Item4 - list id
                  Item5 - any items after this are extended and not used
            ---->

            <cfset messageid = #getcampaign.id# & '-'&getrecipients.id &'-1-' & getCampaign.listid & '-1234mpx'>

            <!---- set up a work template ---->
            <cfset templateHTML = getCampaign.htmlbody>
            <cfset templateTEXT = getCampaign.textbody>


            <!---- figure out the next subject line ---->
            <cfif scount eq arraylen(sbjLines)>
                  <cfset scount=1>
            <cfelse>
                  <cfset scount = scount+1>
            </cfif>

            <cfset subject = sbjLines[scount]>

            <!---- figure out the next domain ---->
            <cfif dcount eq getdomains.recordcount>
                  <cfset dcount = 1>
            <cfelse>
                  <cfset dcount = dcount + 1>
            </cfif>

            <cfset cdomain = domainList[dcount]>

            <!---- figure out the next from line ---->
            <cfif fcount eq arraylen(frmlines)>
                  <cfset fcount = 1>
            <cfelse>
                  <cfset fcount = fcount + 1>
            </cfif>

            <cfset frmLine = frmLines[fcount]>


            <cfif getcampaign.personalized eq 1>
                  <!---- personalize the message ---->
                  <cfif (getcampaign.emailtype eq 0) or (getcampaign.emailtype eq 2)>
                        <!---- email: Text ---->
                        <!---- parse for the TEXT Body ---->
                        <!---- insert the TEXT Footer ---->
                        <cfset templateTEXT = templateTEXT & Chr(10) & Chr(13) & TEXTFooter>

                        <!---- insert the message id ---->
                        <cfset templateTEXT = templateTEXT & Chr(10) & Chr(13) & "MPES:" & messageid>

                        <!---- find and replace for @email@ ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@email@",getrecipients.email,"ALL")>

                        <!---- find and replace for @domain@ ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@domain@",cdomain,"ALL")>

                        <!---- find and replace for @address@ ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@address@",getrecipients.address,"ALL")>

                        <!---- find and replace for @ip@ ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@ip@",getrecipients.ip,"ALL")>

                        <!---- find and replace for @datestamp@ ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@datestamp@",getrecipients.datestamp,"ALL")>

                        <!---- find and replace for @optinsite@ ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@optinsite@",getrecipients.site,"ALL")>

                        <!---- find and replace for @city@ ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@city@",getrecipients.city,"ALL")>

                        <!---- find and replace for @zipcode@ ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@zipcode@",getrecipients.zip,"ALL")>

                        <!---- find and replace for @state@ ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@state@",getrecipients.state,"ALL")>

                        <!---- find and replace for @firstname@ ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@firstname@",getrecipients.first,"ALL")>

                        <!---- find and replace for @lastname@ ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@lastname@",getrecipients.last,"ALL")>

                        <!---- find and replace for @unsubscribe@ ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@unsubscribe@","http://" & cdomain &"/unsubscribe.cfm?" & messageid,"ALL")>

                        <!---- find and replace for href= ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@href@","http://" & cdomain &"/c.cfm?" & messageid,"ALL")>

                        <!---- find and replace for href= ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@date@",dateformat(Now(),'mm/dd/yyyy'),"ALL")>

                        <!---- find and replace for href= ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@dateshort@",dateformat(Now(),'mm/dd/yyyy'),"ALL")>

                        <!---- find and replace for href= ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@datelong@",dateformat(Now(),'mmmm d, yyyy'),"ALL")>

                        <!---- insert the open tracking & messageid ---->
                        <cfset templateTEXT = REReplaceNoCase(templateTEXT,"@open@","http://" & cdomain & "o.cfm?" & messageid,"ALL")>
            </cfif>

            <cfif (getcampaign.emailtype eq 1) or (getcampaign.emailtype eq 2)>
                        <!---- parse for the HTML Body ---->
                        <!---- insert the message id ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"<body","<body name=" & messageid & " ","ALL")>

                        <!---- insert the HTML Footer ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"</body>",HTMLFooter & "</body>","ALL")>

                        <!---- find and replace for @email@ ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@email@",getrecipients.email,"ALL")>

                        <!---- find and replace for @domain@ ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@domain@",cdomain,"ALL")>

                        <!---- find and replace for @address@ ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@address@",getrecipients.address,"ALL")>

                        <!---- find and replace for @ip@ ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@ip@",getrecipients.ip,"ALL")>

                        <!---- find and replace for @datestamp@ ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@datestamp@",getrecipients.datestamp,"ALL")>

                        <!---- find and replace for @optinsite@ ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@optinsite@",getrecipients.site,"ALL")>

                        <!---- find and replace for @city@ ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@city@",getrecipients.city,"ALL")>

                        <!---- find and replace for @zipcode@ ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@zipcode@",getrecipients.zip,"ALL")>

                        <!---- find and replace for @state@ ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@state@",getrecipients.state,"ALL")>

                        <!---- find and replace for @firstname@ ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@firstname@",getrecipients.first,"ALL")>

                        <!---- find and replace for @lastname@ ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@lastname@",getrecipients.last,"ALL")>

                        <!---- find and replace for href= ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@date@",dateformat(Now(),'mm/dd/yyyy'),"ALL")>

                        <!---- find and replace for href= ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@dateshort@",dateformat(Now(),'mm/dd/yyyy'),"ALL")>

                        <!---- find and replace for href= ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@datelong@",dateformat(Now(),'mmmm d, yyyy'),"ALL")>

                        <!---- find and replace for @unsubscribe@ ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@unsubscribe@","http://" & cdomain &"/unsubscribe.cfm?" & messageid,"ALL")>

                        <!---- find and replace for href= ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@href@","http://" & cdomain &"/c.cfm?" & messageid,"ALL")>

                        <!---- insert the open tracking & messageid ---->
                        <cfset templateHTML = REReplaceNoCase(templateHTML,"@open@","http://" & cdomain & "/o.cfm?" & messageid,"ALL")>
            </cfif>
      </cfif>

      <!---- parse the subject line ---->

                              <!---- find and replace for @email@ ---->
                              <cfset subject = REReplaceNoCase(subject,"@email@",getrecipients.email,"ALL")>

                              <!---- find and replace for @domain@ ---->
                              <cfset subject = REReplaceNoCase(subject,"@domain@",cdomain,"ALL")>

                              <!---- find and replace for @address@ ---->
                              <cfset subject = REReplaceNoCase(subject,"@address@",getrecipients.address,"ALL")>

                              <!---- find and replace for @ip@ ---->
                              <cfset subject = REReplaceNoCase(subject,"@ip@",getrecipients.ip,"ALL")>

                              <!---- find and replace for @datestamp@ ---->
                              <cfset subject = REReplaceNoCase(subject,"@datestamp@",getrecipients.datestamp,"ALL")>

                              <!---- find and replace for @optinsite@ ---->
                              <cfset subject = REReplaceNoCase(subject,"@optinsite@",getrecipients.site,"ALL")>

                              <!---- find and replace for @city@ ---->
                              <cfset subject = REReplaceNoCase(subject,"@city@",getrecipients.city,"ALL")>

                              <!---- find and replace for @zipcode@ ---->
                              <cfset subject = REReplaceNoCase(subject,"@zipcode@",getrecipients.zip,"ALL")>

                              <!---- find and replace for @state@ ---->
                              <cfset subject = REReplaceNoCase(subject,"@state@",getrecipients.state,"ALL")>

                              <!---- find and replace for @firstname@ ---->
                              <cfset subject = REReplaceNoCase(subject,"@firstname@",getrecipients.first,"ALL")>

                              <!---- find and replace for @lastname@ ---->
                              <cfset subject = REReplaceNoCase(subject,"@lastname@",getrecipients.last,"ALL")>

                              <!---- find and replace for href= ---->
                              <cfset subject = REReplaceNoCase(subject,"@href@","http://" & cdomain &"/c.cfm?" & messageid,"ALL")>

                              <!---- find and replace for href= ---->
                              <cfset subject = REReplaceNoCase(subject,"@date@",dateformat(Now(),'mm/dd/yyyy'),"ALL")>

                              <!---- find and replace for href= ---->
                              <cfset subject = REReplaceNoCase(subject,"@dateshort@",dateformat(Now(),'mm/dd/yyyy'),"ALL")>

                              <!---- find and replace for href= ---->
                              <cfset subject = REReplaceNoCase(subject,"@datelong@",dateformat(Now(),'mmmm d, yyyy'),"ALL")>

                              <!---- insert the open tracking & messageid ---->
                              <cfset subject = REReplaceNoCase(subject,"@open@","http://" & cdomain & "o.cfm?" & messageid,"ALL")>
      <!---- send the email ---->
            <cfmail
                  to="#getrecipients.email#"
                  from="#frmLine# <#messageid#@#cdomain#>"
                  subject="#subject#"
                  SpoolEnable="Yes">
                  <cfmailpart type="TEXT">
                  #templateTEXT#
                  </cfmailpart>
                  <cfmailpart type="HTML">
                  #templateHTML#
                  </cfmailpart>
            </cfmail>
      </cfloop>
End Time: <cfoutput>#now()#</cfoutput>
<!---- set the status ---->
<cfquery
      name="setStatus"
      datasource="mailpro">
      update campaigns
      set status = 'Completed.',
          completed = 1
      where id = #getCampaign.id#
</cfquery>
</cfif>
<cfset application.delivery="false">
<!---</cfif>
</cfif>--->
0
 
LVL 17

Expert Comment

by:anandkp
ID: 12081692
Has the mail server been configured properly in CFAdmin ?
If yes - then is the mail server that is configured responding - so as to effectively send emails.
Can u check the mail logs & see whats happening there ?
0
 

Author Comment

by:richardsimnett
ID: 12081844
anandkp,
yes the mail server verifies under the administrator, and works when sending single emails...... the server is configured to run 150 threads for mail delivery, but I have tried scaling this number up and down..... nothing fixed it... the MTA it is relaying to is PMTA by port 25 solutions.
The machine it is running on is a dual p4 xeon at 2.0ghz with 3 gigs of ram,  raid 0 for speed when spooling.

-Rick
0
 
LVL 17

Expert Comment

by:anandkp
ID: 12084727
How abt the from & to address ?
Check on the domain usuage restirictions with ur ISP [some ISP may block the use of a different domain names to avoid spamming]
how abt calling that file - without using the Scheduler ...
0
 

Author Comment

by:richardsimnett
ID: 12086648
anandkp,
different domains is not a problem in this scenario either..... they are their own ISP #1, and #2 it is doing this in my test environment which is definitely not restricted. I have also run it without the scheduler and it does the same thing.

Cheers,
Rick
0
Scale it in WD Gold

With up to ten times the workload capacity of desktop drives, WD Gold hard drives employ advanced technology to deliver among the best in reliability, capacity, power efficiency and performance.

 
LVL 2

Expert Comment

by:dldeeds
ID: 12089826
Have you turned on the debugger in CF Amin to see if anything stands out.

Have you tried the code on a reduced set of data, will it work if there were only 100 records pullled, or even 10, etc.

A couple of links that might be useful...

http://www.petefreitag.com/item/115.cfm  - example code to output memory stats while app is running.

http://www.macromedia.com/support/coldfusion/ts/documents/large_record_sets_oom.htm  - Macromedia technote about "ColdFusion MX: Returning large character data record sets causes server instability and Out of Memory errors" here is an excerpt...

"When returning large character data queries from a database, the memory print for Macromedia ColdFusion MX is twice as large as that of ColdFusion 5 and can eventually cause the server to hang with java.lang.OutOfMemoryError errors."



0
 
LVL 17

Expert Comment

by:Tacobell777
ID: 12093256
First replace
<cfset subject = REReplaceNoCase(subject,"@city@",getrecipients.city,"ALL")>

with
<cfset subject = replaceNoCase(subject,"@city@",getrecipients.city,"ALL")>
You are not using a regular expression

then instead of looping over the resultset I would use the query attribute of the cfmail tag

and as I said I would throttle the emails being send out.


I am not saying this solves any of your current problems, but it will sure help reduce the memory usage.

have you checked the mail spool? The log files etc? see if the are being held or sent out? If they are sent out maybe the firewall stops them?
0
 

Author Comment

by:richardsimnett
ID: 12110446
Tacobell777,
Ok I have made the changes you asked for.... and good point on the REReplaceNoCase thing... totally an oversight on my part.
I have eliminiated the cfloop, and made it internal to cfmail which poses some problems as far as generating the correct messageid's and subject lines, however, that aside, coldfusion still crashed with an out of memory error. I have since tried it with a smaller segment of the list. It seems to work up to 300,000 names at a time.
   a quick question however... during the test of 300,000 names that worked in the end I got an error for an invalid email address being found... How do I make coldfusion ignore the invalid addresses if found.

Cheers,
Rick
0
 
LVL 17

Expert Comment

by:Tacobell777
ID: 12110619
You can use cftry around the cfmail tag, I believe it should skip the invalid email
0
 

Author Comment

by:richardsimnett
ID: 12115752
Tacobell777,
I tried that it seems to skip the entire cfmail tag when I do that... really strange....
it looks like this:

<cftry>
     <cfcatch>
          <cfmail
             ....>
          </cfmail>
     </cfcatch>
</cftry>

All that happens is it seems to query the database... and then it quits

cheers,
Rick

0
 
LVL 17

Expert Comment

by:Tacobell777
ID: 12116354
the syntax is <cftry>
<cfmail></cfmail>
<cfcatch></cfcatch>
</cftry>

if thaqt doesnt work you'll need to filter out the bad emails
0
 

Author Comment

by:richardsimnett
ID: 12129721
Tacobell777,
Ok the <cftry><cfcatch> thing worked the way you said.. I completely overlooked the placement of the tags. Very strange results now however. The task runs and says it completed successfully. However the mail server reports only having received 30000 of the 100000 emails that were supposed to be sent. I have verified the queries, and had the cfm page even output what it is working with.... It says 100000 were processed but only 30,000 were sent out. I checked the mail.log. I have this error occurring several times (once for each execution of the page).

"Error","jrpp-1","09/22/04","22:05:22",,"Attribute validation error for tag CFMAIL."

I dont understand why it is quitting and claiming it was successful. Very strange stuff.

Any ideas?

Cheers,
Rick
0
 

Author Comment

by:richardsimnett
ID: 12342535
mrichmon,
I have not in anyway forgotten about this question. I have been waiting to hear a response to the error I am experiencing. I really hope this is not abandoned as this is a big issue for me right now. I have been waiting with my fingers crossed for the answer.

Cheers,
Rick Simnett
0
 

Author Comment

by:richardsimnett
ID: 12590490
Hello,
Well I have scrapped rendering large volumes of email with coldfusion. No matter what approach I take to doing it (i have done 8 major rewrites with no change in performance, as well as tried all sorts of high end hardware options) I have written a solution in java, using NIO functions. It performs about 2.4 million render and feeds to the transport agent an hour. Bottom line: I dont recommend doing large volumes of email in coldfusion. It by no means lives up to its claim of 1mm an hour.

I will leave points to those I feel were most helpful.

Cheers,
Rick Simnett
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
The purpose of this video is to demonstrate how to set up basic WordPress SEO. This will be demonstrated using a Windows 8 PC. The plugin used will be WordPress SEO by Yoast. Go to your WordPress login page. This will look like the following: myw…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now