Open Stack

On 09/04, Michael Bayer wrote:
> On Mon, Apr 9, 2018 at 5:53 AM, Gorka Eguileor <geguileo at redhat.com> wrote:
> > On 06/04, Michael Bayer wrote:
> >> On Wed, Apr 4, 2018 at 5:00 AM, Gorka Eguileor <geguileo at redhat.com> wrote:
> >> > On 03/04, Jay Pipes wrote:
> >> >> On 04/03/2018 11:07 AM, Michael Bayer wrote:
> >> >> > The MySQL / MariaDB variants we use nowadays default to
> >> >> > innodb_file_per_table=ON and we also set this flag to ON in installer
> >> >> > tools like TripleO.     The reason we like file per table is so that
> >> >> > we don't grow an enormous ibdata file that can't be shrunk without
> >> >> > rebuilding the database.  Instead, we have lots of little .ibd
> >> >> > datafiles for each table throughout each openstack database.
> >> >> >
> >> >> > But now we have the issue that these files also can benefit from
> >> >> > periodic optimization which can shrink them and also have a beneficial
> >> >> > effect on performance.   The OPTIMIZE TABLE statement achieves this,
> >> >> > but as would be expected it itself can lock tables for potentially a
> >> >> > long time.   Googling around reveals a lot of controversy, as various
> >> >> > users and publications suggest that OPTIMIZE is never needed and would
> >> >> > have only a negligible effect on performance.   However here we seek
> >> >> > to use OPTIMIZE so that we can reclaim disk space on tables that have
> >> >> > lots of DELETE activity, such as keystone "token" and ceilometer
> >> >> > "sample".
> >> >> >
> >> >> > Questions for the group:
> >> >> >
> >> >> > 1. is OPTIMIZE table worthwhile to be run for tables where the
> >> >> > datafile has grown much larger than the number of rows we have in the
> >> >> > table?
> >> >>
> >> >> Possibly, though it's questionable to use MySQL/InnoDB for storing transient
> >> >> data that is deleted often like ceilometer samples and keystone tokens. A
> >> >> much better solution is to use RDBMS partitioning so you can simply ALTER
> >> >> TABLE .. DROP PARTITION those partitions that are no longer relevant (and
> >> >> don't even bother DELETEing individual rows) or, in the case of Ceilometer
> >> >> samples, don't use a traditional RDBMS for timeseries data at all...
> >> >>
> >> >> But since that is unfortunately already the case, yes it is probably a good
> >> >> idea to OPTIMIZE TABLE on those tables.
> >> >>
> >> >> > 2. from people's production experience how safe is it to run OPTIMIZE,
> >> >> > e.g. how long is it locking tables, etc.
> >> >>
> >> >> Is it safe? Yes.
> >> >>
> >> >> Does it lock the entire table for the duration of the operation? No. It uses
> >> >> online DDL operations:
> >> >>
> >> >> https://dev.mysql.com/doc/refman/5.7/en/innodb-file-defragmenting.html
> >> >>
> >> >> Note that OPTIMIZE TABLE is mapped to ALTER TABLE tbl_name FORCE for InnoDB
> >> >> tables.
> >> >>
> >> >> > 3. is there a heuristic we can use to measure when we might run this
> >> >> > -.e.g my plan is we measure the size in bytes of each row in a table
> >> >> > and then compare that in some ratio to the size of the corresponding
> >> >> > .ibd file, if the .ibd file is N times larger than the logical data
> >> >> > size we run OPTIMIZE ?
> >> >>
> >> >> I don't believe so, no. Most things I see recommended is to simply run
> >> >> OPTIMIZE TABLE in a cron job on each table periodically.
> >> >>
> >> >> > 4. I'd like to propose this job of scanning table datafile sizes in
> >> >> > ratio to logical data sizes, then running OPTIMIZE, be a utility
> >> >> > script that is delivered via oslo.db, and would run for all innodb
> >> >> > tables within a target MySQL/ MariaDB server generically.  That is, I
> >> >> > really *dont* want this to be a script that Keystone, Nova, Ceilometer
> >> >> > etc. are all maintaining delivering themselves.   this should be done
> >> >> > as a generic pass on a whole database (noting, again, we are only
> >> >> > running it for very specific InnoDB tables that we observe have a poor
> >> >> > logical/physical size ratio).
> >> >>
> >> >> I don't believe this should be in oslo.db. This is strictly the purview of
> >> >> deployment tools and should stay there, IMHO.
> >> >>
> >> >
> >> > Hi,
> >> >
> >> > As far as I know most projects do "soft deletes" where we just flag the
> >> > rows as deleted and don't remove them from the DB, so it's only when we
> >> > use a management tool and run the "purge" command that we actually
> >> > remove these rows.
> >> >
> >> > Since running the optimize without purging would be meaningless, I'm
> >> > wondering if we should trigger the OPTIMIZE also within the purging
> >> > code.  This way we could avoid innefective runs of the optimize command
> >> > when no purge has happened and even when we do the optimization we could
> >> > skip the ratio calculation altogether for tables where no rows have been
> >> > deleted (the ratio hasn't changed).
> >> >
> >>
> >> the issue is that this OPTIMIZE will block on Galera unless it is run
> >> on a per-individual node basis along with the changing of the
> >> wsrep_OSU_method parameter, this is way out of scope both to be
> >> redundantly hardcoded in multiple openstack projects, as well as
> >> there's no portable way for Keystone and others to get at the
> >> individual Galera node addresses.    Putting it in oslo.db would at
> >> least be a place that most of this logic can live but even then it
> >> needs to run for multiple Galera nodes and needs to have
> >> deployment-specific configuration.   *unless* we say, the OPTIMIZE
> >> here will short for a purged table, let's just let it block.
> >>
> >
> > I see... What about a hybrid solution?  Use the alter table as mentioned
> > in the comment [1] to not block the table for systems that support it,
> > and going with the RSU mode when it's not supported?
> >
>
> sure, it just depends on if we have Galera running or not, so I intend
> to detect if the current MySQL database is a Galera cluster or not by
> looking for wsrep_* variables and status.   Tripleo will know to
> deploy the script directly to each MySQL database, galera or not, on
> the local host that MySQL is running and the script will just do the
> right thing without any of the downstream apps having to know about
> it.
>

Maybe I misunderstood the comment, but it sounded that even clustered
MariaDB with Galera would be able to avoid locking the whole table with
a new enough version.

In any case your plan sounds good to me.


>
>
>
> >
> > [1] https://mariadb.com/kb/en/library/optimize-table/#comment_3191
> >
> >
> >>
> >> > Ideally the ratio calculation and optimization code would be provided by
> >> > oslo.db to reduce code duplication between projects.
> >>
> >> I was hoping to have this be part of oslo.db but there's disagreement on that :)
> >>
> >> If this can't be in oslo.db then the biggest issue facing me on this
> >> is building out a new application and getting it packaged since this
> >> feature has no home, unless I can ship it as some kind of script
> >> packaged in tripleo.
> >>
> >>
> >
> > I think the oslo.db home you proposed has the great benefit of making it
> > available in all deployments regardless of the installer, if that's not
> > possible I would go with the TripleO script before creating yet another
> > project that needs to be packaged and maintained.
> >
> > Cheers,
> > Gorka.
> >
> >
> >> >
> >> >
> >> >> > 5. for Galera this gets more tricky, as we might want to run OPTIMIZE
> >> >> > on individual nodes directly.  The script at [1] illustrates how to
> >> >> > run this on individual nodes one at a time.
> >> >> >
> >> >> > More succinctly, the Q is:
> >> >> >
> >> >> > a. OPTIMIZE, yes or no?
> >> >>
> >> >> 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.
> >> >>
> >> >> Best,
> >> >> -jay
> >> >>
> >> >> > thanks for your thoughts!
> >> >> >
> >> >> >
> >> >> >
> >> >> > [1] https://github.com/deimosfr/galera_innoptimizer
> >> >> >
> >> >> > __________________________________________________________________________
> >> >> > OpenStack Development Mailing List (not for usage questions)
> >> >> > Unsubscribe: OpenStack-dev-request at lists.openstack.org?subject:unsubscribe
> >> >> > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
> >> >> >
> >> >>
> >> >> __________________________________________________________________________
> >> >> OpenStack Development Mailing List (not for usage questions)
> >> >> Unsubscribe: OpenStack-dev-request at lists.openstack.org?subject:unsubscribe
> >> >> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
> >> >
> >> > __________________________________________________________________________
> >> > OpenStack Development Mailing List (not for usage questions)
> >> > Unsubscribe: OpenStack-dev-request at lists.openstack.org?subject:unsubscribe
> >> > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
> >>
> >> __________________________________________________________________________
> >> OpenStack Development Mailing List (not for usage questions)
> >> Unsubscribe: OpenStack-dev-request at lists.openstack.org?subject:unsubscribe
> >> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
> >
> > __________________________________________________________________________
> > OpenStack Development Mailing List (not for usage questions)
> > Unsubscribe: OpenStack-dev-request at lists.openstack.org?subject:unsubscribe
> > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
>
> __________________________________________________________________________
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: OpenStack-dev-request at lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev