High CPU Utilization in MySQL Database Server

Question

I have to run a query which produces very large result set (20K result and 5 columns each row).

I am doing some processing on the data and storing the result in another database table.

All this processing is done on a cron process.

My problem is that when I run the query CPU utilization of my database server reaches up to 100% until the process finishes.

Please help me in understanding best practice for this kind of scenarios.

Jordan Jambazov · Accepted Answer · 2013-01-02 10:20:48Z

1

In such cases you need to start thinking about optimization and/or vertical scaling of the MySQL instance.

Try to find a way to optimize the SQL query you're using
If possible, split the cron job in several steps that execute lighter queries.
If some of the data in the returned query set is identical on each cron run - cache it.
If frequency of script execution is not of critical importance, split the runs and limit the query sets to a smaller number. You can execute the script 4 times in 20 minutes with 5k items in the query set.
Think about migrating to a non-relational database, if possible.
Do some performance tuning of the MySQL instance.
etc.

answered Jan 2, 2013 at 10:20

Jordan Jambazov

3,6301 gold badge23 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1