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

[Aaron Morrison via TriLUG]

Also look at the server settings. 
'mysqladmin variables' will output the running settings (same as 'show variables') and compare between both servers.

You might check the sync-binlog option as I've had this greatly impact performance. 

--am


> On Feb 13, 2017, at 13:20, William Sutton via TriLUG <trilug at trilug.org> wrote:
> 
> 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
> -- 
> This message was sent to: Aaron Morrison <ae4ko1 at gmail.com>
> 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/ae4ko1%40gmail.com
> Welcome to TriLUG: http://trilug.org/welcome