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

[Brian via TriLUG]

So, I've done some more digging, and have more info.  First, a bit of 
detail on the procedure itself, as it's important to the discussion.

The procedure creates a sparse table of parameter values for a list of 
electronic components.  The parameters are all stored in one table 
containing name, value, and component ID.  The procedure does the following:

   1) Creates a temporary table with a column for every distinct name
      in the parameter table.
   2) Loops through the provided list of component IDs and adds a row to
      the temporary table for each, filling in whichever columns have
      values.
   3) Loops through the temporary table's columns, dropping any that
      have no values on any rows
   4) Adds a couple fields to the table for additional info the user
      will want (e.g. part description, on-hand quantity, location)
   5) Finishes with a SELECT * on the temporary table

I have discovered that the awful performance is the table-altering (the 
dropping and adding of columns).  A single ALTER TABLE seems to take a 
minimum of 0.5 seconds.  For one component with few parameters (meaning 
most of the columns in the temp table wind up being dropped), that 
really adds up.

I've made some adjustments that have improved the overall execution time 
of the procedure; namely, the temporary table is created with ENGINE = 
MEMORY while the sparse population is going on, then copied at the end 
into an InnoDB temporary table (because I need a TEXT column, which 
isn't supported by the MEMORY engine).

This suggests that too much is happening on disk for some reason, as the 
performance skyrockets when I specifically move things into RAM.  What I 
don't know is why the host instance is doing disk activity that the 
Docker container (i.e. the previous installation) isn't.  Smells a 
little like some kind of caching issue, perhaps?  A cache for disk ops 
that is too small on the host instance?  Or perhaps some filesystem 
parameter that has a different default?

It's exactly the same machine, just a fresh installation, so there's no 
hardware changes...

Cheers,
-B

On 02/14/2017 01:23 PM, Aaron Morrison wrote:
> 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