[TriLUG] Slow MySQL procedures (following dump - upgrade - load)
William Sutton via TriLUG
trilug at trilug.org
Mon Feb 13 13:20:52 EST 2017
any indexes exist in the container's database that don't exist in the
host's database?
William Sutton
On Mon, 13 Feb 2017, Brian via TriLUG wrote:
> 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
> --
> This message was sent to: William <william at trilug.org>
> To unsubscribe, send a blank message to trilug-leave at trilug.org from that
> address.
> TriLUG mailing list : http://www.trilug.org/mailman/listinfo/trilug
> Unsubscribe or edit options on the web :
> http://www.trilug.org/mailman/options/trilug/william%40trilug.org
> Welcome to TriLUG: http://trilug.org/welcome
More information about the TriLUG
mailing list