1

I have a MySQL Query, which as part of a batch job, loops through about 4,000 records - running the same queries against each of the 4,000 records.

As it moves through the records, it gets progressively slower.

By the time it's at the 4,000th, it's taking up to 20 times longer than when it did for the first loop.

There's actually 10 queries which run in each loop (SELECT), but the overall trend is all getting slower as the batch job progresses.

Also interesting is comparing how long each of the 10 individual queries takes in the loop.

At the start of the batch it's uneven (e.g. one SELECT takes 1% of loop time, whilst another takes 35% of loop time for example). This is as expected, as some queries are bigger than others.

But as it progresses, these 10 queries all start to take a much more similar time, all close to 10% of loop time -- suggesting perhaps there is a delay building up and affecting statements before they execute?

On my dev environment, MySQL is running locally - Running MySQL v5.7.24 on Ubuntu 16 on AWS EC2.

On my live environment, I use AWS RDS. Running Aurora MySQL v5.6.10 on Ubuntu 16 on AWS EC2. I'm currently using Aurora Serverless, so have limited config control, but can move to RDS MySQL instance if there's config I need to access which would help.

The issue occurs in both environments.

It feels like previously executed statements are hanging around in the memory and not full freeing up resources for the next loop. But I don't know where to start looking to rectify.

EDIT:

On my dev environment (with local MySQL server), the load gets pretty high during the process (close to 100%), with RAM and SWAP not really changing (and well within limits).

On my live environment (RDS MySQL), the load and RAM are consistent, with no real change during the process (and well within limits), but the RDS hits around 60% CPU utilization

So I'm obviously peaking out the CPU - which then, in turn, results in a lag with flows on?

EDIT:

I just doubled my dev environment EC2 (t2.small to t2.medium), which doubles RAM and 1CPU -> 2CPU. It's made no difference, and I notice running htop that only one of the CPUs is maxing out -- the other is almost idle - and I've just read that the MySQL Process won't (can't) be shared across multiple cores... so that doesn't help :-)

Welcome any thoughts and advice,

Thanks

  • 2
    We really need to see the queries. And SHOW CREATE TABLE. And, can't you do all the work in a single JOIN between the two tables, rather than 1+4000 queries? – Rick James Aug 2 '19 at 19:02
1

SOLVED:

Found one statement involving a table which was missing a crucial INDEX, and it was bogging down there.

Adding the INDEX and each loop is now pretty consistent - no progressive lag.

Re query "can't you do all the work in a single JOIN between the two tables, rather than 1+4000 queries" - there's a heap more going on in each loop (sending messages etc) so it's more than just a MySQL Statement.

Cheers

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.