(This is a cross-post from the Engine Yard blog. The original article appears here.)
Here is a question I've actually been asked a few times:
"I am writing a batch processing script which modifies data as part of an ongoing process that is scheduled by cron. I have the ability to group a number of modifications together into a transaction, but I'm not sure what the correct number is?"
First off, I think that this question is interesting not just in the context of batch processing, but it equally applies to all parts of the application. If you are designing a high throughput system for MySQL, there are actually some potential pain points that you can design your way around.
Potential Pain Points
Here are the situations where the size of the transaction could impact performance:
Very Small …
[Read more]