<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi Doug,<br>
<br>
Comments below..<br>
<br>
Thanks,<br>
Octave<br>
<br>
<div class="moz-cite-prefix">On 2/2/2017 12:52 PM, Mike Bayer wrote:<br>
</div>
<blockquote
cite="mid:35f67f9a-9901-f507-3295-e801081c846f@redhat.com"
type="cite">
<br>
<br>
On 02/02/2017 02:16 PM, Octave J. Orgeron wrote:
<br>
<blockquote type="cite">Hi Doug,
<br>
<br>
Comments below..
<br>
<br>
Thanks,
<br>
Octave
<br>
<br>
On 2/2/2017 11:27 AM, Doug Hellmann wrote:
<br>
<blockquote type="cite">It sounds like part of the plan is to
use the configuration setting
<br>
to control how the migration scripts create tables. How will
that
<br>
work? Does each migration need custom logic, or can we build
helpers
<br>
into oslo.db somehow? Or will the option be passed to the
database
<br>
to change its behavior transparently?
<br>
</blockquote>
<br>
These are good questions. For each service, when the db sync or
db
<br>
manage operation is done it will call into SQL Alchemy or
Alembic
<br>
depending on the methods used by the given service. For example,
most
<br>
use SQL Alchemy, but there are services like Ironic and Neutron
that use
<br>
Alembic. It is within these scripts under the
<service>/db/* hierarchy
<br>
that the logic exist today to configure the database schema for
any
<br>
given service. Both approaches will look at the schema version
in the
<br>
database to determine where to start the create, upgrade, heal,
etc.
<br>
operations. What my patches do is that in the scripts where a
table
<br>
needs to be modified, there will be custom IF/THEN logic to
check the
<br>
cfg.CONF.database.mysql_storage_engine setting to make the
required
<br>
modifications. There are also use cases where the api.py or
model(s).py
<br>
under the <service>/db/ hierarchy needs to look at this
setting as well
<br>
for API and CLI operations where mysql_engine is auto-inserted
into DB
<br>
operations. In those use cases, I replace the hard coded
"InnoDB" with
<br>
the mysql_storage_engine variable.
<br>
</blockquote>
<br>
can you please clarify "replace the hard coded "InnoDB" " ? Are
you proposing to send reviews for patches against all occurrences
of "InnoDB" in files like
<a class="moz-txt-link-freetext" href="https://github.com/openstack/nova/blob/master/nova/db/sqlalchemy/migrate_repo/versions/216_havana.py">https://github.com/openstack/nova/blob/master/nova/db/sqlalchemy/migrate_repo/versions/216_havana.py</a>
? The "InnoDB" keyword is hardcoded in hundreds of migration
files across all openstack projects that use MySQL. Are all of
these going to be patched with some kind of conditional?
<br>
</blockquote>
<br>
Yes, that is the plan to patch each of the scripts that has these
and any other issues that need to be addressed.<br>
<br>
<blockquote
cite="mid:35f67f9a-9901-f507-3295-e801081c846f@redhat.com"
type="cite">
<br>
<br>
<blockquote type="cite">
<br>
It would be interesting if we could develop some helpers to
automate
<br>
this, but it would probably have to be at the SQL Alchemy or
Alembic
<br>
levels.
<br>
</blockquote>
<br>
not really, you can build a hook that intercepts operations like
CreateTable, or that intercepts SQL as it is emitted over a
connection, in order to modify these values on the fly. But that
is a specific kind of approach with it's own set of surprises.
Alternatively you can make an alternate SQLAlchemy dialect that no
longer recognizes "mysql_*" as the prefix for these arguments.
There's ways to do this part.
<br>
<br>
But more critically I noticed you referred to altering the names
of columns to suit NDB. How will this be accomplished? Changing
a column name in an openstack application is no longer trivial,
because online upgrades must be supported for applications like
Nova and Neutron. A column name can't just change to a new name,
both columns have to exist and logic must be added to keep these
columns synchronized.
<br>
</blockquote>
<br>
Putting the hooks into SQL Alchemy dialect would only solve things
like the mysql_engine=, savepoints, and nested operations. It
won't solve for the row length issues or be able to determine which
ones to target since we don't have some method of specifying the
potential lengths of contents. We also have to consider that Alembic
doesn't have the same capabilities as SQL Alchemy, so if we invest
in making enhancements there we still have Neutron and Ironic that
wouldn't be able to benefit. I think being consistent is important
as well here.<br>
<br>
The patches don't change the names of columns, they only change the
size or type. There is only a single occurrence that I've seen where
a column name causes problems because it's using a reserved name in
the SQL. I have a patch for that issue, which I believe is in Heat
if I remember correctly.<br>
<br>
<blockquote
cite="mid:35f67f9a-9901-f507-3295-e801081c846f@redhat.com"
type="cite">
<br>
Unfortunately, throughout all of the OpenStack services today we
<br>
<blockquote type="cite">are hard coding things like mysql_engine,
using InnoDB specific features
<br>
(savepoints, nested operations, etc.), and not following the
strict SQL
<br>
orders for modifying table elements (foreign keys, constraints,
and
<br>
indexes).
<br>
</blockquote>
<br>
Savepoints aren't InnoDB specific, they are a standard SQL feature
and also their use is not widespread right now. I'm not sure
what you mean by "the strict SQL orders", we use ALTER TABLE as is
standard in MySQL for this and it's behind an abstraction layer
that supports other databases such as Postgresql.
<br>
</blockquote>
<br>
Savepoints are not implemented yet in MySQL Cluster, but it's on the
roadmap. As for the SQL ordering, what I'm talking about is the way
some services will drop or modify foreign keys, constraints, or
indexes in the wrong operation order. These have to be unfurled in
the correct order and put back in the right order. InnoDB does not
enforce this, but NDB does. So this isn't about doing an ALTER TABLE
on a column. Some services like Keystone don't suffer from these
problems as they are more careful about operation orders. Other
services like Glance ignore the operation ordering and that's where
more work is required to fix things.<br>
<br>
<blockquote
cite="mid:35f67f9a-9901-f507-3295-e801081c846f@redhat.com"
type="cite">
<br>
<br>
<blockquote type="cite">
<br>
* Many of the SQL Alchemy and Alembic scripts only import the
minimal
<br>
set of python modules. If we imported others, we would also
have to
<br>
initialize those name spaces which means a lot more code :(
<br>
</blockquote>
<br>
I'm not sure what this means, can you clarify ?
<br>
</blockquote>
<br>
When the scripts run, they don't import the whole namespace for a
given service. Most of them will just import the modules for the SQL
Alchemy or Alembic namespaces with the cfg.CONF.database.* from
oslo.*. If we wanted to inject the setting elsewhere, like within
the service namespace, we have to import those modules and
initialize the full namespace. This means considerably more lines of
code and context issues to resolve. Instead, by using oslo.db under
the cfg.CONF.database, which already gets imported for SQL Alchemy
and Alembic operations, we can quickly check for
mysql_storage_engine.<br>
<br>
Does that make it clear?<br>
<br>
<blockquote
cite="mid:35f67f9a-9901-f507-3295-e801081c846f@redhat.com"
type="cite">
<br>
<br>
* Reduces the amount of overhead required to make these
changes.
<br>
<br>
What sort of "overhead", do you mean code complexity, performance
?
<br>
<br>
</blockquote>
<br>
The overhead would be importing another module from the service and
initializing the namespace for each SQL Alchemy or Alembic script.
Again, I don't want to re-architect the way those processes work
today as that would be a major undertaking. By using the same
methods they use today to get to the cfg.CONF.database namespace, I
cut down on the coding overhead and the performance overhead of
multiple scripts importing modules and initializing namespaces. It
would increase the amount of reads against the *.conf files and all
of the modules required.<br>
<br>
<blockquote
cite="mid:35f67f9a-9901-f507-3295-e801081c846f@redhat.com"
type="cite">
<br>
<br>
<blockquote type="cite">
<br>
<br>
<blockquote type="cite">
<br>
Keep in mind that we do not encourage code outside of
libraries to
<br>
rely on configuration settings defined within libraries,
because
<br>
that limits our ability to change the names and locations of
the
<br>
configuration variables. If migration scripts need to access
the
<br>
configuration setting we will need to add some sort of public
API
<br>
to oslo.db to query the value. The function can simply return
the
<br>
configured value.
<br>
</blockquote>
<br>
Configuration parameters within any given service will make use
of a
<br>
large namespace that pulls in things from oslo and the .conf
files for a
<br>
given service. So even when an API, CLI, or DB related call is
made,
<br>
these namespaces are key for things to work. In the case of the
SQL
<br>
Alchemy and Alembic scripts, they also make use of this
namespace with
<br>
oslo, oslo.db, etc. to figure out how to connect to the database
and
<br>
other database settings. I don't think we need a public API for
these
<br>
kinds of calls as the community already makes use of the
libraries to
<br>
build the namespace. My oslo.db setting and patches for each
service
<br>
just make use of the cfg.CONF.database namespace to determine
the
<br>
correct behavior to execute.
<br>
<br>
<blockquote type="cite">
<br>
What other behaviors are likely to be changed by the new
option?
<br>
Will application runtime behavior need to know about the
storage
<br>
engine?
<br>
</blockquote>
<br>
The changes will be transparent to the application runtime
behavior. The
<br>
APIs and CLI tools call into the <service>/db/api.py as
the entry point
<br>
for database calls. Behind this you usually have a models.py
that is
<br>
aware of the database schema to understand the layout of things.
So the
<br>
underlining structure is abstracted away from the run-time.
These entry
<br>
points sometimes do require minor modifications to handle any
hard coded
<br>
issues or intercept functions like savepoints and nested
operations.
<br>
Again I use the cfg.CONF.database namespace to check for the
appropriate
<br>
behavior and implement IF/THEN logic to do the right thing.
<br>
<br>
Some of my design objectives for all of these patches are:
<br>
<br>
* Zero impact on OpenStack functionality and usability (API,
CLI, user
<br>
experience)
<br>
* No loss in database structure. Consistent foreign keys,
constraints,
<br>
indexes, etc.
<br>
* Minimal impact on column size and/or types to fit within NDB
table
<br>
row limits. Many columns are over-sized today.
<br>
* Validate functionality of APIs, service processes, and CLI.
Tempest
<br>
is our friend :)
<br>
* Zero impact for users not using MySQL Cluster (NDB).
<br>
<br>
<br>
<blockquote type="cite">
<br>
Doug
<br>
<br>
<blockquote type="cite">Thanks,
<br>
Octave
<br>
<br>
On 2/2/2017 6:46 AM, Doug Hellmann wrote:
<br>
<blockquote type="cite">Excerpts from Octave J. Orgeron's
message of 2017-02-01 20:33:38 -0700:
<br>
<blockquote type="cite">Hi Folks,
<br>
<br>
I'm working on adding support for MySQL Cluster to the
core OpenStack
<br>
services. This will enable the community to benefit from
an
<br>
active/active, auto-sharding, and scale-out MySQL
database. My approach
<br>
is to have a single configuration setting in each core
OpenStack service
<br>
in the oslo.db configuration section called
mysql_storage_engine that
<br>
will enable the logic in the SQL Alchemy or Alembic
upgrade scripts to
<br>
handle the differences between InnoDB and NDB storage
engines
<br>
respectively. When enabled, this logic will make the
required table
<br>
schema changes around:
<br>
<br>
* Row character length limits 65k -> 14k
<br>
* Proper SQL ordering of foreign key, constraints,
and index operations
<br>
* Interception of savepoint and nested operations
<br>
<br>
By default this functionality will not be enabled and
will have no
<br>
impact on the default InnoDB functionality. These
changes have been
<br>
tested on Kilo and Mitaka in previous releases of our
OpenStack
<br>
distributions with Tempest. I'm working on updating
these patches for
<br>
upstream consumption. We are also working on a 3rd party
CI for
<br>
regression testing against MySQL Cluster for the
community.
<br>
<br>
The first change set is for oslo.db and can be reviewed
at:
<br>
<br>
<a class="moz-txt-link-freetext" href="https://review.openstack.org/427970">https://review.openstack.org/427970</a>
<br>
<br>
Thanks,
<br>
Octave
<br>
<br>
</blockquote>
Is it possible to detect the storage engine at runtime,
instead of
<br>
having the operator configure it?
<br>
<br>
Doug
<br>
<br>
__________________________________________________________________________
<br>
OpenStack Development Mailing List (not for usage
questions)
<br>
Unsubscribe:
<a class="moz-txt-link-abbreviated" href="mailto:OpenStack-dev-request@lists.openstack.org?subject:unsubscribe">OpenStack-dev-request@lists.openstack.org?subject:unsubscribe</a>
<br>
<a class="moz-txt-link-freetext" href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a>
<br>
</blockquote>
</blockquote>
__________________________________________________________________________
<br>
OpenStack Development Mailing List (not for usage questions)
<br>
Unsubscribe:
<a class="moz-txt-link-abbreviated" href="mailto:OpenStack-dev-request@lists.openstack.org?subject:unsubscribe">OpenStack-dev-request@lists.openstack.org?subject:unsubscribe</a>
<br>
<a class="moz-txt-link-freetext" href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a>
<br>
</blockquote>
<br>
--
<br>
<br>
Oracle <a class="moz-txt-link-rfc2396E" href="http://www.oracle.com/"><http://www.oracle.com/></a>
<br>
Octave J. Orgeron | Sr. Principal Architect and Software
Engineer
<br>
Oracle Linux OpenStack
<br>
Mobile: +1-720-616-1550 <tel:+17206161550>
<br>
500 Eldorado Blvd. | Broomfield, CO 80021
<br>
Certified Oracle Enterprise Architect: Systems Infrastructure
<br>
<a class="moz-txt-link-rfc2396E" href="http://www.oracle.com/us/solutions/enterprise-architecture/index.html"><http://www.oracle.com/us/solutions/enterprise-architecture/index.html></a>
<br>
Green Oracle <a class="moz-txt-link-rfc2396E" href="http://www.oracle.com/commitment"><http://www.oracle.com/commitment></a> Oracle is
committed to
<br>
developing practices and products that help protect the
environment
<br>
<br>
<br>
<br>
__________________________________________________________________________
<br>
OpenStack Development Mailing List (not for usage questions)
<br>
Unsubscribe:
<a class="moz-txt-link-abbreviated" href="mailto:OpenStack-dev-request@lists.openstack.org?subject:unsubscribe">OpenStack-dev-request@lists.openstack.org?subject:unsubscribe</a>
<br>
<a class="moz-txt-link-freetext" href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a>
<br>
<br>
</blockquote>
<br>
__________________________________________________________________________
<br>
OpenStack Development Mailing List (not for usage questions)
<br>
Unsubscribe:
<a class="moz-txt-link-abbreviated" href="mailto:OpenStack-dev-request@lists.openstack.org?subject:unsubscribe">OpenStack-dev-request@lists.openstack.org?subject:unsubscribe</a>
<br>
<a class="moz-txt-link-freetext" href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a>
<br>
<br>
</blockquote>
<br>
<div class="moz-signature">-- <br>
<br>
<p>
<a href="http://www.oracle.com/" target="_blank"><img
src="cid:part1.07000609.01090902@oracle.com" alt="Oracle"
border="0" height="26" width="114"></a><br>
<font color="#666666" size="2" face="Verdana, Arial, Helvetica,
sans-serif">Octave J. Orgeron | Sr. Principal Architect and
Software Engineer<br>
<font color="#ff0000">Oracle</font> Linux OpenStack<br>
Mobile: <a href="tel:+17206161550">+1-720-616-1550</a><br>
500 Eldorado Blvd. | Broomfield, CO 80021<br>
<a
href="http://www.oracle.com/us/solutions/enterprise-architecture/index.html"><img
src="cid:part4.02050705.04040403@oracle.com"
alt="Certified Oracle Enterprise Architect: Systems
Infrastructure" border="0" height="42" width="182"></a><br>
<a href="http://www.oracle.com/commitment" target="_blank"><img
src="cid:part6.07080206.07010207@oracle.com" alt="Green
Oracle" align="absmiddle" border="0" height="28"
width="44"></a>
<font color="#4B7D42" size="1" face="Verdana, Arial,
Helvetica, sans-serif">Oracle is committed to developing
practices and products that help protect the environment</font>
</font></p>
<font color="#666666" size="2" face="Verdana, Arial, Helvetica,
sans-serif">
</font></div>
</body>
</html>