MySQL preventing dual loading

Question

Right guys,

I have a MySQL database, using InnoDB on tables, every so often I have to perform a big cron job that does a large batch of queries and inserts. When I run this cron job, for the 5minutes or so that it is running, no other page is able to load. As soon as it is done, the queries are executed and the page loads.

The table that is actually having all this data added to it, isn't even being queried by the main site. It simply is that when MySQL is under a lot of work, the rest of the site is untouchable. This surely must not be right, what could be causing this to happen? CPU usage for MySQLD rockets to huge figures like 120% (!!!!!) and all MySQL queries are locked.

What could cause/fix this?

Answer 1

You could have the script delay for 1/10 second or so between each query. It will take longer but allow activity in the background.

sleep( 0.1 );

You will probably only need to do this for the writes, reads are very cheap.

Answer 2

No, that's obviously wrong. This is probably related to bad configuration. Take a look at the size of the innodb buffer pool and see if it can be increased. This sounds like a typical case of ram shortage. Healthy setups are almost never cpu bound, and certainly not when doing bulk inserts.

Answer 3

With InnoDB, other things should still be able to access the database. Are you prepared to show the schema (or relevant part of it) and the relevant parts of the application?

Maybe it's contention in hardware.

How big are the transactions which your "cron" job is using? Using tiny transactions will create a massive amount of IO needlessly.

Do your database servers have battery backed raid controllers (assuming your servers use hard drives not SSD)? If not, commits will be quite slow.

How much ram is in your database server? If possible, ensure that it is a bit bigger than your database and set innodb_buffer_pool to > data size - this will mean that read workloads come out of ram anyway, which should make them fast.

Can you reproduce the problem in a test system on production-grade hardware?

Answer 4

I think you might need to re-think how you are building up your queries. InnoDB has page-level locks, but with massive updates you can still lock down quite a bit of your queries.

Post your actual queries, and try again.. I don't think there is a generic solution for a generic question like this, so look into optimizing what you're doing today.