Perl, mysql and mass-mailing

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

Hi

I'm maintaining a mailing list on my website (an opt-in mailing list, I might add!) and I have now collected roughly 1,000 names and emails.

These are all stored in a simple MySQL database, that looks something like this:

CREATE TABLE rd_news (
  id smallint(4)  DEFAULT '' NOT NULL auto_increment,
  name text    ,
  email text    ,
  PRIMARY KEY (id),
  UNIQUE id (id)
);
[download]

Now the next step for me is to just write a loop that looks through my database, and pipes the emails to sendmail one by one - and do some template work replacing template name tag with their real name.

Before I do this, however, I just wanted to seek the wisdom of the perl monks as to server resources and things I should be careful about. Is looping through 1,000 entries in a sql database, parsing template for one tag and then piping all to my sendmail program going to kill my server? If so, what is the most (more?) efficent way?

Thanks for your help!

--
Wiliam Stephens <wil@stephens.org>

Comment on Perl, mysql and mass-mailing Download Code

Replies are listed 'Best First'.
Re: Perl, mysql and mass-mailing by gav^ (Curate) on Apr 25, 2002 at 16:36 UTC
You might want to investigate Mail::Bulkmail which makes this pretty trivial to implement. gav^	[reply]
Re: Perl, mysql and mass-mailing by AidanLee (Chaplain) on Apr 25, 2002 at 16:11 UTC
it could cause some undue server load. Grabbing the entries from the database and filling out the template won't be the issue as far as I can tell. What'll really suck up system resources is sending 1000 unique emails through sendmail all at once. You may want to consider sending out the email in batches every couple of minutes till the job is done. I'd retrieve the names once from the database, store them as an array or some such in a flat file, and have a cron job wake up and send X emails every couple of minutes, removing those addresses from the list and resaving the list.	[reply]
Re: Perl, mysql and mass-mailing by Chady (Priest) on Apr 25, 2002 at 19:43 UTC
You might want to have a look at a cgi program written by one of our monks. it's called Mojo Mail. It does use batch sending or Bulk mailing using Mail::Bulkmail and it is very, very plug-and-play and easy to use. and has a lot of features.. Might worth checking out. He who asks will be a fool for five minutes, but he who doesn't ask will remain a fool for life. Chady \| http://chady.net/	[reply]
Re: Perl, mysql and mass-mailing by abstracts (Hermit) on Apr 25, 2002 at 18:19 UTC
Hello I'd follow gav^'s advice regarding using Mail::Bulkmail to send mails in bulk rather than one by one. Also, you don't need to loop through 1000 entries in an SQL database if what you mean is issuing 1000 select requests. What you need is `"select name, email from rd_news;"`, ~~dump the emails to a file, and use Mail::Bulkmail. Otherwise, you can hack Bulkmail to take a list of emails instead of reading them off from an external file.~~ Hope this helps,,, Aziz,,, Update: Mail::Bulkmail does take a list of emails Update2: Why are you using `id smallint(4) DEFAULT '' NOT NULL auto_increment`? In particular, why are you limiting the display length to 4? Shouldn't the default be 0? And shouldn't the type by unsigned?	[reply]
(cLive ;-) Re: Perl, mysql and mass-mailing by cLive ;-) (Prior) on Apr 26, 2002 at 07:32 UTC
Err, any reason you're not using majordomo? .02 cLive ;-)	[reply]