[Phpwm] Multiple MySQL insert techniques

[Dave Holmes]

Not sure server load is an issue most of nix mail servers are damn good
at handling mail volumes and queueing the distribution just because you
pour in 6000 messages the server is not going to open up 6000
connections to remote servers.

Way way back we absolutely hammered sendmail to death sending out
hundreds of thousands of emails.

In short don't over complicate things by creating a queue to in effect
add items to err a queue.


The real concern would be making sure this is running from a standalone
machine and not your corporate email just in case you get black listed,
making sure you have DNS configured correctly to achieve deliverability
and above make sure your not acting as an open relay..


Chris Lovell wrote:
> As has been stated, the SQL insert statements shouldn't really be a
> worry as they can be handled with relatively few queries. The concern
> I'd have is the amount of emails you'd be throwing out - You'd
> probably want some kind of DB queue to store the messages ready to
> send and have a cron setup to periodically process a batch of them to
> stagger the server load
>
> 2009/3/25 Alex Mace <alex at hollytree.co.uk <mailto:alex at hollytree.co.uk>>
>
>     Hi Pete,
>
>     In the first instance I'd just test it and see what happens - no point
>     in refactoring your code unless there is actually going to be a
>     problem.
>
>     Is a user receiving the e-mail going to be counted automatically as
>     having read the message, or are you meaning that all 6000 users will
>     click a link and then view the message at once? I presume the former.
>
>     There's a few ways you could speed up the inserts. You could use
>     prepared statements, or you could do a batch insert like this:
>
>     INSERT INTO messages ( userId, messageId ) VALUES ( 1, 1 ), ( 2, 1 ),
>     ( 3,1 ), etc, etc
>
>     You can insert a large amount of values like that with relatively few
>     problems - just be careful of the max_packet_size in MySQL, which
>     limits the size of a query you can send. You can always break the
>     query up to stay under this size.
>
>     Another possible answer would be to insert them into a temporary table
>     in memory and then do and INSERT INTO ... SELECT to bulk insert them
>     into the messages table in a single operation. I think that should
>     remove some overhead.
>
>     It will depend on your particular circumstances. Like I said, I'd just
>     try it with what you have now. You might find the limit with your
>     current setup is 50000, or it could be 500. If your current method is
>     limited then try what I or others suggest and see what works best for
>     you.
>
>     Hope that helps,
>
>     Alex
>
>     This usually allows you to insert a large amount of data much quicker.
>     On 25 Mar 2009, at 15:13, pete graham wrote:
>
>     > Good afternoon developers,
>     >
>     > I would like your advice. I have a website which has an internal
>     > messaging system for users. The site admins have the ability to send
>     > out a message to all users on the site.
>     >
>     > The site has around 6,000 users currently and uses a MySQL Database.
>     > The admin messages go into a admin_messages table. They then get
>     > inserted into the messages table when a user logins into the
>     site (the
>     > separate messages table is required as we need to record is a
>     user has
>     > read messages).
>     >
>     > We now want to send an email notification when the admin sends a
>     > message. I'm concerned if I trigger 6,000 mysql inserts when an
>     admin
>     > sends a message it will flood the DB connection. Am I being paranoid
>     > or could this kill the site/DB?
>     >
>     > Here are the solutions I've been considering:
>     > - set up a cron job to batch insert the data into the messages
>     tables
>     > - use some form of clever sub query to insert the multiple items
>     > (could this also risk killing the db?)
>     > - leave the message insert logic as it is and modify the batch email
>     > sending to work from a join on admin_messages with the users table
>     >
>     > I'm open to other suggestions, if anybody has them. I predict
>     the user
>     > base of the site could grow to around 20,000 users.
>     >
>     > Thanks,
>     > Pete
>     >
>     > _______________________________________________
>     > Phpwm mailing list
>     > Phpwm at mailman.lug.org.uk <mailto:Phpwm at mailman.lug.org.uk>
>     > https://mailman.lug.org.uk/mailman/listinfo/phpwm
>
>
>     _______________________________________________
>     Phpwm mailing list
>     Phpwm at mailman.lug.org.uk <mailto:Phpwm at mailman.lug.org.uk>
>     https://mailman.lug.org.uk/mailman/listinfo/phpwm
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Phpwm mailing list
> Phpwm at mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/phpwm
>   
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.lug.org.uk/pipermail/phpwm/attachments/20090325/2cee2f8e/attachment.htm