Open Stack

On 04/03/2018 11:51 AM, Michael Bayer wrote:
> On Tue, Apr 3, 2018 at 11:41 AM, Jay Pipes <jaypipes at gmail.com> wrote:
>> On 04/03/2018 11:07 AM, Michael Bayer wrote:
>>>
>>
>> Yes.
>>
>>> b. oslo.db script to run generically, yes or no?
>>
>>
>> No. Just have Triple-O install galera_innoptimizer and run it in a cron job.
> 
> OK, here are the issues I have with galera_innoptimizer:
> 
> 1. only runs on Galera.    This should work on a non-galera db as well

To recap what we just discussed on IRC... it's not necessary to do this 
for non-galera DBs because non-galera DBs don't use manual locking for 
OPTIMIZE TABLE (MySQL 5.7 online DDL changes ensure OPTIMIZE TABLE for 
InnoDB is a non-locking operation).

Galera enforces a strict ordering with its total order isolation mode by 
default for DDL operations, which is what the galera_innoptimizer thing 
is doing: turning off that total order isolation temporarily and 
executing optimize table, then turning on total order isolation again.

> 2. hardcoded to MySQLdb / mysqlclient.   We don't install that driver anymore.
> 
> 3. is just running OPTIMIZE on every table across the board, and at
> best you can give it a list of tables.  I was hoping to not add more
> hardcoded cross-dependencies to tripleo, as this means individual
> projects would need to affect how the script is run which means we
> have to again start shipping individual per-app crons that require
> eternal babysitting.

I have no issues with you creating a better tool :) Just not in oslo.db...

> What failures do you foresee if I tried to make it compare the logical
> data size to the physical file size?  since I'm going here for file
> size optimization only.   or just too complicated / brittle ?

Yeah, you are prematurely optimizing (pun intended). No need. Just run 
OPTIMIZE TABLE every day on all tables in a cron job. With modern MySQL, 
there's really not an issue with that.

Best,
-jay