[Phpwm] Multiple MySQL insert techniques

[pete graham]

Hi everyone,

Firstly thanks to everyone that responded for your prompt replies. I
already have my batch email system setup and tested so this is not a
issue.

To clarify the internal messages are only counted as read when the
user reads the message using the websites internal messaging system.
The email notifications are just a prompt to get people to go back to
the site.

I think a similar query to the one given by Dave Holmes should work.
Also thanks to Mike Tipping for the pointer to "insert delayed" I
wasn't familiar with that feature, could be very useful.

Pete

2009/3/25 Chris Lovell <stormys at gmail.com>:
> 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
>
>
> _______________________________________________
> Phpwm mailing list
> Phpwm at mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/phpwm
>
>