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.
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?
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,