[Phpwm] Multiple MySQL insert techniques

[Chris Lovell]

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>

> 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
> > 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/d5f68b0f/attachment.htm