[TriLUG] Slow MySQL procedures (following dump - upgrade - load)

[Brian via TriLUG]

Third time's the charm, I hope?  This message (according to the 
archives) never actually made it to the list...  Trying again!

Hi list,

I recently resurrected some mysql databases after the reload I did to my 
server a couple months ago.  That involved using mysqldump to dump the 
data (before I wiped the previous installation) so that I could load it 
into the latest-and-greatest mysqld version running on the fresh server.

Well, guess what, procedures aren’t dumped by default.  Fortunately I 
kept the original mysql data files.  So, I spun up a docker container 
with mysql, copied the old data files into it, had it do a 
mysql_upgrade, and then used it to mysqldump the procedures I was 
missing, and loaded them into the production server.

Ho. Ly. Carp.  The new server runs the procedures at a SNAIL’S PACE. 
100x slower than the previous installation.  I did some digging and 
found stuff about indexes not getting used because of mismatched 
collations and stuff, but I reimported the procedures with the correct 
charsets and collations specified, and the problem’s still there.

Funny thing is, inside the docker container, the procedures run just 
like they used to.  Check it out:

Inside the container:
mysql> call full_params('15,28,121,56');

<results snipped>

4 rows in set (0.17 sec)

On the host, “production” instance:
mysql> call full_params('15,28,121,56');

<identical results snipped>

4 rows in set (19.85 sec)

20 seconds!!  Jiminey Cricket!!  I’m at a loss on how to troubleshoot 
further.  I’ve seen things suggesting making a version of the procedure 
that EXPLAINs each query, but it’s a pretty complex procedure and that 
would be a lot of additional effort.  Is that my only recourse to 
further understand the problem?

Important: The docker container is running on the same host as the 
production database, so it’s not a matter of raw horsepower.

There’s newer data in production, so I can’t just do the same thing I 
did to set up the docker container (mysql_upgrade on the original data 
files) without losing data, or going through a lengthy song-and-dance to 
preserve the new data.

Any help will be greatly appreciated...

Thanks,
-Brian