[TriLUG] Slow MySQL procedures (following dump - upgrade - load)
Brian via TriLUG
trilug at trilug.org
Mon Feb 13 13:19:13 EST 2017
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
More information about the TriLUG
mailing list