how to speed the mail process?

pysome has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks: I came across a boring problem. Pls help me to solve it,Any suggestion will be appreciated
The issues process is as following:

My fellow prepared some data at front-end.He insert our customers' info into DB (say :msg_DB)
. The frequency is about 30 items per minute.

Then i need to take the data that he insertd into msg_DB, compose of them with customized HTML template.
Subsequently,I send the data to our customers by SMTP server (At the same time,i mark the corresponding costomers' info in msg_DB to "already sent").

Now the problem is my program is slower than my fellow insert into the msg_DB . As a result,our customers will wait for a long time to recive their mail! My program is a deamon.The Pseudocode is :

    while (1) {
        $sth = $dbh->prepare(qq{select * from customers where mark=0})
+;
        $sth->execute() or die "$DBI::errstr\n";
        while ($sth->fetch) {
            my $content = construct_the_customers_template();
            send_customer_mail($coutnet);
            mark_the_flag_in_msgdb();  # set the mark field to 1 ,repr
+esent has sent!
        }
    }
[download]

What's about this method? Whether or not i need use multi-thread process to speed my program ? Pls make suggestions to me:)

Regards,
pysome

Comment on how to speed the mail process? Download Code

Replies are listed 'Best First'.
Re: how to speed the mail process? by grinder (Bishop) on Aug 27, 2007 at 12:36 UTC
It's probably a safe bet to say that the slowness is the sending of the mail, not the database access. Whack a `use Time::HiRes;` ...at the top of the program and then instrument the time it takes to send the message: `my $begin = time; send_customer_mail($coutnet); my $delta = time - $begin; warn "Well that took $delta seconds\n";` [download] I wager you're seeing significant times being eaten right there. Be that as it may, two messages per second is stunningly slow. Are you trying to connect to the remote MX directly from your script? I would inject the messages to a local MTA and let it deal with the delivery. I use Postfix at $work, and my untuned configuration will let me queue several hundred messages per second, if not more. And there are other MTAs like qpsmtpd (written in Perl, natch), which are faster still. Another consideration to keep in mind is that just because you sent a message, doesn't mean the recipient received it... you need to check your outbound SMTP queue and check that it's been delivered (and not rejected) to the remote destination (but even then it may get dropped on the floor, filed as spam, or bounced back to you for any number of legitimate reasons). • another intruder with the mooring in the heart of the Perl	[reply] [d/l] [select]
Re: how to speed the mail process? by moritz (Cardinal) on Aug 27, 2007 at 12:18 UTC
You should profile your code to see which parts are slow. For a nice introduction read this article We could suggest some possible performance tweeks that you likely don't use yet, but without knowing the code that's rather inefficient. A small improvement could be to `prepare` the query outside the loop, you only have to do that once. Perl 6 in German -- Difficult Sudoku	[reply] [d/l]
Re: how to speed the mail process? by derby (Abbot) on Aug 27, 2007 at 12:47 UTC
Sending email from your app directly to a user (ie, connecting to their smtp server) is always going to be a bottleneck. Your best bet is to set up a sendmail (or postfix) server of your own and queue the email into that. By off-loading the mail delivery to a sendmail/postfix server, you free your app from the normal email blocking situations (dns lookups/timeouts, address verification, retries, etc). You can also tweak the server to send email how you want (right away, every X minutes, retry X number of times, etc). -derby	[reply]
Re: how to speed the mail process? by fmerges (Chaplain) on Aug 27, 2007 at 13:13 UTC
Hi, Resuming... Can you provide with more code, basically important stuff is not shown, like construct_the_customers_template, sned_customer_mail, mark_the_flag_in_msgdb. If you want to profile where it's spending time, you have a lot of options like Devel::DProf, or for timing, a nice one Benchmark::Stopwatch. I think the db stuff can be improved a lil'bit, not only in speed also in quality, see statement handler, mark_the_flag can also be an already prepared statement... Mail stuff, as someone pointed out already, I would decouple it from your main script, by queuing it into a local SMTP server. About what happen inside of ...customers_template function, I hope you're using a fast templating system, or one at all, and not some home made not optimized at all ;-) Regards, fmerges at irc.freenode.net	[reply]
Re: how to speed the mail process? by ides (Deacon) on Aug 27, 2007 at 14:07 UTC
Definitely take the advice of profiling your code and also make sure to profile your SQL query. For example if you don't have an index ( preferably a partial index ) on the mark column... but there is one obvious performance improvement I can suggest. Move the `$dbh->prepare()` outside of the outer most while. Because the query isn't changing, there is no need to re-prepare it on every call. Another option to consider, if you're using a database that support transactions, is to fork say 30 of these daemons and change your logic to be along the lines of (in pseudo-code): BEGIN TRANSACTION SELECT FOR UPDATE * FROM customers WHERE mark=0 LIMIT 1 mark_the_flag_in_msgdb() send your message COMMIT TRANSACTION This way you can be processing more at any given point.... But what you should really do is move to a messaging oriented system where the front end sends a message to something like Apache's ActiveMQ. The daemon would then pick up messages to be handled, send the message, write the data to the db, and move on to the next message... Hope this helps! Frank Wiles <frank@revsys.com> www.revsys.com	[reply] [d/l]
Re: how to speed the mail process? by rir (Vicar) on Aug 27, 2007 at 16:19 UTC
Like others, I suspect that your code is waiting for your mail transfer agent; I'd try mailing everything to a local account or appending to a local file just to see how timing is effected. Yes, I know it's pseudocode, but: Your `prepare` statement should be outside the loop. It is good to avoid `select *` in code as the schema may change underneath you. You may want to throw a `sleep SOME_INTERVAL` into your loop to ease the load on your database or add an option to let the database admin tune as load warrants. Do you have passes through the database that come up empty? Setting `mark` should be contingent on the success of `send_customer_mail` in handing off the message to a MTA. Be well, rir	[reply] [d/l] [select]