Throttling mysql command line

[Alex Dean]

On Jan 18, 2012, at 11:58 AM, Eric Cope wrote:

> Hello all,
> I have a nightly script that executes a mysql batch file that takes
> approximately 8 minutes to run. Unfortunately it makes my web server
> unresponsive for all 8 minutes. I've tried ionice and nice to lower
> the priority, but it still seems to consume all resources. The weird
> thing is "top" and "iostat" look like nothing is running. Any ideas?
> 
> Its mysql 5.1.x

What process are you nice-ing?  If your script just fires off a bunch of SQL queries, it's the MySQL server which is going to end up eating all your resources.  Nicing the script which is issuing the queries probably won't help much.

Try using EXPLAIN to see if you can make your queries more efficient.  After that, look into setting up MySQL replication.  You can run your reporting queries against a slave database, and keep that load off the master.

alex