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

[William Sutton via TriLUG]

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