subject:"\[openstack\-dev\] \[oslo\]\[oslo.db\] MySQL Cluster support"


Comments below..

On 2/3/2017 8:34 AM, Mike Bayer wrote:



On 02/03/2017 10:21 AM, Doug Hellmann wrote:

Excerpts from Mike Bayer's message of 2017-02-03 09:41:11 -0500:


On 02/02/2017 05:28 PM, Octave J. Orgeron wrote:

That refers to the total length of the row. InnoDB has a limit of 65k
and NDB is limited to 14k.

A simple example would be the volumes table in Cinder where the row
length goes beyond 14k. So in the IF logic block, I change columns 
types

that are vastly oversized such as status and attach_status, which by
default are 255 chars.



let me give you a tip on IF blocks, that they are a bit of an
anti-pattern.  If you want a column type to do one thing in one case,
and another in another case, create an object that does the thing 
you want:



some_table = Table(
 'some_table', metadata,
 Column('my_column', VARCHAR(255).with_variant(VARCHAR(50), 'ndb'))
)


I wonder if we want to do either, though. Shouldn't we try to use
the same (smaller) column size all the time? Otherwise we end up
with another incompatibility between different deployments, since
sometimes things like names might have different sizes in different
clouds.


in that case you have to do a migration which as you know these days 
means the "old" column remains for a whole release cycle and the 
application must undergo significant complexity, either at the app 
level or in triggers, to keep data between "old" and "new" columns 
simultaneously.   So one advantage to keeping this at the "create for 
NDB" level is that we don't need to get into schema migrations.


Unless we changed the value in the application and its migration files 
completely, and *didnt* migrate old applications, and just hope/ensure 
that they aren't writing larger data values.   Maybe that's possible 
though it seems a little scary.   Perhaps some kind of annotated type 
like VARCHAR(50, unmigrated=255) to note what's going on.





This is one of the things that I worried about and why I took the 
approach of doing the logic for NDB and keeping the default logic there 
based on the mysql_storage_engine setting. Perhaps it makes sense to do 
things this way first and then in a future release do the migrations of 
the column sizes and types as a second phase. Then as a third phase we 
can remove the column size and type logic changes? At that point, the 
only real change left will be the substitution of the 
"mysql_engine=InnoDB" with a value that we could abstract from somewhere 
(oslo.db or a dialect stub).


As for the foreign key, constraints, and index ordering, it's good 
practice to do things in the right order and doesn't impact any 
functionality in InnoDB. So that's actually a plus that I'll fix those 
with my patches.


Then that will just leave the logic for dealing with savepoints and 
nested operations. Then when NDB adds those features, we can drop that 
logic down the road.








I think we might want to look into creating a stub dialect called 'ndb'
that subclasses mysql+pymysql.   Treating ndb as a whole different
database means there's no longer the need for a flag in oslo.db, the
'ndb' name would instead be interpreted as a new backend - the main
thing would be ensuring all the mysql-appropriate hooks in oslo.db are
also emitted for ndb, but this also gives us a way to pick and choose
which hooks apply.   It seems like there may be enough different about
it to separate it at this level.

Not sure if people on the list are seeing that we are simultaneously
talking about getting rid of Postgresql in the efforts to support only
"one database", while at the same time adding one that is in many 
ways a

new database.


Yes, that does seem a bit ironic. That's also why I was pointing
out that we're going to want to have people lined up to support the
work before starting. The lack of help with Postresql testing
resulted in removing it from the gate, and possibly to dropping
support entirely.

For reference, the discussion in [1] led to this proposed TC
resolution [2].

[1] 
http://lists.openstack.org/pipermail/openstack-dev/2017-February/thread.html#111357

[2] https://review.openstack.org/427880






So to determine a more appropriate size, I look

through the Cinder code to find where the possible options/states are
for those columns. Then I cut it down to a more reasonable size. I'm
very careful when I cut the size of a string column to ensure that all
of the possible values can be contained.

In cases where a column is extremely large for capturing the 
outputs of

a command, I will change the type to Text or TinyText depending on the
length required. A good example of this is in the agents table of
Neutron where there is a column for configurations that has a string
length of 4096 characters, which I change to Text. Text blobs are 
stored

differently and do not count against the row length.

I've also observed differences between Kilo, Mitaka, and tip where 
even
for InnoDB some of these tables are getting wider than can

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support


Hi Doug,

Comments below..

On 2/3/2017 8:21 AM, Doug Hellmann wrote:

Excerpts from Mike Bayer's message of 2017-02-03 09:41:11 -0500:

On 02/02/2017 05:28 PM, Octave J. Orgeron wrote:

That refers to the total length of the row. InnoDB has a limit of 65k
and NDB is limited to 14k.

A simple example would be the volumes table in Cinder where the row
length goes beyond 14k. So in the IF logic block, I change columns types
that are vastly oversized such as status and attach_status, which by
default are 255 chars.


let me give you a tip on IF blocks, that they are a bit of an
anti-pattern.  If you want a column type to do one thing in one case,
and another in another case, create an object that does the thing you want:


some_table = Table(
  'some_table', metadata,
  Column('my_column', VARCHAR(255).with_variant(VARCHAR(50), 'ndb'))
)

I wonder if we want to do either, though. Shouldn't we try to use
the same (smaller) column size all the time? Otherwise we end up
with another incompatibility between different deployments, since
sometimes things like names might have different sizes in different
clouds.


I think we would all benefit from columns being sized for the actual 
content instead of blindly using strings of 255 when the only possible 
values are less than 10 chars. Also capturing entire command line 
outputs from OVS and hypervisors seems a bit of a waste to, instead of 
processing the output first and extracting the key values for storage in 
the database.


Doing this would significantly reduce the amount of code on my end. If 
we only had to change the columns sizes and types globally for InnoDB 
and NDB, that would simplify things a great deal. We could also develop 
some guidelines on how to structure tables to prevent them from going 
over 14k or any other value we deem appropriate to support multiple 
MySQL engines.





I think we might want to look into creating a stub dialect called 'ndb'
that subclasses mysql+pymysql.   Treating ndb as a whole different
database means there's no longer the need for a flag in oslo.db, the
'ndb' name would instead be interpreted as a new backend - the main
thing would be ensuring all the mysql-appropriate hooks in oslo.db are
also emitted for ndb, but this also gives us a way to pick and choose
which hooks apply.   It seems like there may be enough different about
it to separate it at this level.

Not sure if people on the list are seeing that we are simultaneously
talking about getting rid of Postgresql in the efforts to support only
"one database", while at the same time adding one that is in many ways a
new database.

Yes, that does seem a bit ironic. That's also why I was pointing
out that we're going to want to have people lined up to support the
work before starting. The lack of help with Postresql testing
resulted in removing it from the gate, and possibly to dropping
support entirely.

For reference, the discussion in [1] led to this proposed TC
resolution [2].

[1] 
http://lists.openstack.org/pipermail/openstack-dev/2017-February/thread.html#111357
[2] https://review.openstack.org/427880





So to determine a more appropriate size, I look

through the Cinder code to find where the possible options/states are
for those columns. Then I cut it down to a more reasonable size. I'm
very careful when I cut the size of a string column to ensure that all
of the possible values can be contained.

In cases where a column is extremely large for capturing the outputs of
a command, I will change the type to Text or TinyText depending on the
length required. A good example of this is in the agents table of
Neutron where there is a column for configurations that has a string
length of 4096 characters, which I change to Text. Text blobs are stored
differently and do not count against the row length.

I've also observed differences between Kilo, Mitaka, and tip where even
for InnoDB some of these tables are getting wider than can be supported.
So in the case of Cinder, some of the columns have been shifted to
separate tables to fit within 65k. I've seen the same thing in Neutron.
So I fully expect that some of the services that have table bloat will
have to cut the lengths or break the tables up over time anyways. As
that happens, it reduces the amount of work for me, which is a good thing.

The most complicated database schemas to patch up are cinder, glance,
neutron, and nova due to the size and complexity of their tables. Those
also have a lot of churn between releases where the schema changes more
often. Other services like keystone, heat, and ironic are considerably
easier to work with and have well laid out tables that don't change much.

Thanks,
Octave

On 2/2/2017 1:25 PM, Mike Bayer wrote:


On 02/02/2017 02:52 PM, Mike Bayer wrote:

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

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support


Hi Mike,

It might be easier to reduce the size of certain columns today. We have 
an enormous amount of wasted space in the tables today. Reducing them 
would make things more efficient regardless of the database engine being 
used. On the flip side, if we were using SQL Alchemy properly and not 
hard coding SQL statements, we could make the database back-end more 
agnostic. It would be a major project to fix this across all of the 
services today, but that may be the right thing to do in the long term. 
InnoDB has it's own scaling issues and at some point a larger shop 
(telco or cloud provider) will want other options.


Thanks,
Octave

On 2/3/2017 8:16 AM, Roman Podoliaka wrote:

On Fri, Feb 3, 2017 at 4:41 PM, Mike Bayer  wrote:

Not sure if people on the list are seeing that we are simultaneously talking
about getting rid of Postgresql in the efforts to support only "one
database", while at the same time adding one that is in many ways a new
database.

++

and, FWIW, moving columns between tables and changing of column types
in order to make NDB storage engine happy both seem to be a way more
intrusive than what we've had to do so far in the code of OpenStack
projects in order to support PostgreSQL.

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev



--

Oracle 
Octave J. Orgeron | Sr. Principal Architect and Software Engineer
Oracle Linux OpenStack
Mobile: +1-720-616-1550 
500 Eldorado Blvd. | Broomfield, CO 80021
Certified Oracle Enterprise Architect: Systems Infrastructure 

Green Oracle  Oracle is committed to 
developing practices and products that help protect the environment


__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support


Hi Mike,

Comments below..

Thanks,
Octave

On 2/3/2017 7:41 AM, Mike Bayer wrote:



On 02/02/2017 05:28 PM, Octave J. Orgeron wrote:

That refers to the total length of the row. InnoDB has a limit of 65k
and NDB is limited to 14k.

A simple example would be the volumes table in Cinder where the row
length goes beyond 14k. So in the IF logic block, I change columns types
that are vastly oversized such as status and attach_status, which by
default are 255 chars.



let me give you a tip on IF blocks, that they are a bit of an 
anti-pattern.  If you want a column type to do one thing in one case, 
and another in another case, create an object that does the thing you 
want:



some_table = Table(
'some_table', metadata,
Column('my_column', VARCHAR(255).with_variant(VARCHAR(50), 'ndb'))
)


I think we might want to look into creating a stub dialect called 
'ndb' that subclasses mysql+pymysql.   Treating ndb as a whole 
different database means there's no longer the need for a flag in 
oslo.db, the 'ndb' name would instead be interpreted as a new backend 
- the main thing would be ensuring all the mysql-appropriate hooks in 
oslo.db are also emitted for ndb, but this also gives us a way to pick 
and choose which hooks apply. It seems like there may be enough 
different about it to separate it at this level.


Not sure if people on the list are seeing that we are simultaneously 
talking about getting rid of Postgresql in the efforts to support only 
"one database", while at the same time adding one that is in many ways 
a new database.





This is an interesting approach as it would significantly reduce the 
amount of code in my patches today. Do you have any pointers on where 
this should be implemented as a stub? Would we have to take different 
approaches for SQL Alchemy vs. Alembic?



--

Oracle 
Octave J. Orgeron | Sr. Principal Architect and Software Engineer
Oracle Linux OpenStack
Mobile: +1-720-616-1550 
500 Eldorado Blvd. | Broomfield, CO 80021
Certified Oracle Enterprise Architect: Systems Infrastructure 

Green Oracle  Oracle is committed to 
developing practices and products that help protect the environment


__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support


Comments below..

On 2/2/2017 6:22 PM, Doug Hellmann wrote:

Yes, this is major undertaking and major driver for Oracle to setup a
3rd party CI so that we can automate regression testing against MySQL
Cluster. On the flip side, it helps solve some of the challenges with

I'm not sure we would want to gate projects on CI run outside of
our infrastructure community. We've had some bad experiences with
that in the past. What are the options for running MySQL Cluster
on nodes upstream?


It shouldn't be too bad to setup MySQL Cluster. I haven't worked with 
the CI infrastructure upstream. What would it take to get it configured?





larger deployments where an active/passive solution for MySQL DB is not
sufficient. So the pay-off is pretty big from an availability and
scale-out perspective.

But I do realize that I'll have to maintain this long-term and hopefully
get others to help out as more services are added to OpenStack.

Given the scope of the work being proposed, and the level of expertise
needed to do it, we're going to need to have more than one person
available to debug issues before we go to far ahead with it.

It might help to have an example or two of the sorts of migration
script changes you've described elsewhere. Maybe you can prepare a
sample patch for a project?

Are there tools that can look at a table definition and tell if it
meets the criteria for the cluster backend (the row size, types
used, whatever else)? Or is it something one has to do by hand?


I'm working on updating my keystone patches and will put that up for 
review early next week. I'll send the link for that. Then I'll work on 
cinder next. So those two should provide good examples of what the 
patches look like.


I've been doing things by hand, but I think a tool could be developed.


So what do you envision that I'll have to add to the oslo.db library to
make things work as you describe? What would be the best example for me
to build from?
You need a function that takes the cfg.CONF instance as an argument. It
should call register_opts() to register the option or options it uses,
and then it can return the value. Something like:

from oslo_db import options

def get_db_engine_type(conf):
 conf.register_opts(options.database_opts)
return conf.database.mysql_storage_engine


I'll work on this and submit an update to the patch for oslo.db.




Doug

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev



--

Oracle 
Octave J. Orgeron | Sr. Principal Architect and Software Engineer
Oracle Linux OpenStack
Mobile: +1-720-616-1550 
500 Eldorado Blvd. | Broomfield, CO 80021
Certified Oracle Enterprise Architect: Systems Infrastructure 

Green Oracle  Oracle is committed to 
developing practices and products that help protect the environment


__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support

2017-02-03 Thread Mike Bayer




On 02/03/2017 10:21 AM, Doug Hellmann wrote:

Excerpts from Mike Bayer's message of 2017-02-03 09:41:11 -0500:


On 02/02/2017 05:28 PM, Octave J. Orgeron wrote:

That refers to the total length of the row. InnoDB has a limit of 65k
and NDB is limited to 14k.

A simple example would be the volumes table in Cinder where the row
length goes beyond 14k. So in the IF logic block, I change columns types
that are vastly oversized such as status and attach_status, which by
default are 255 chars.



let me give you a tip on IF blocks, that they are a bit of an
anti-pattern.  If you want a column type to do one thing in one case,
and another in another case, create an object that does the thing you want:


some_table = Table(
 'some_table', metadata,
 Column('my_column', VARCHAR(255).with_variant(VARCHAR(50), 'ndb'))
)


I wonder if we want to do either, though. Shouldn't we try to use
the same (smaller) column size all the time? Otherwise we end up
with another incompatibility between different deployments, since
sometimes things like names might have different sizes in different
clouds.


in that case you have to do a migration which as you know these days 
means the "old" column remains for a whole release cycle and the 
application must undergo significant complexity, either at the app level 
or in triggers, to keep data between "old" and "new" columns 
simultaneously.   So one advantage to keeping this at the "create for 
NDB" level is that we don't need to get into schema migrations.


Unless we changed the value in the application and its migration files 
completely, and *didnt* migrate old applications, and just hope/ensure 
that they aren't writing larger data values.   Maybe that's possible 
though it seems a little scary.   Perhaps some kind of annotated type 
like VARCHAR(50, unmigrated=255) to note what's going on.








I think we might want to look into creating a stub dialect called 'ndb'
that subclasses mysql+pymysql.   Treating ndb as a whole different
database means there's no longer the need for a flag in oslo.db, the
'ndb' name would instead be interpreted as a new backend - the main
thing would be ensuring all the mysql-appropriate hooks in oslo.db are
also emitted for ndb, but this also gives us a way to pick and choose
which hooks apply.   It seems like there may be enough different about
it to separate it at this level.

Not sure if people on the list are seeing that we are simultaneously
talking about getting rid of Postgresql in the efforts to support only
"one database", while at the same time adding one that is in many ways a
new database.


Yes, that does seem a bit ironic. That's also why I was pointing
out that we're going to want to have people lined up to support the
work before starting. The lack of help with Postresql testing
resulted in removing it from the gate, and possibly to dropping
support entirely.

For reference, the discussion in [1] led to this proposed TC
resolution [2].

[1] 
http://lists.openstack.org/pipermail/openstack-dev/2017-February/thread.html#111357
[2] https://review.openstack.org/427880






So to determine a more appropriate size, I look

through the Cinder code to find where the possible options/states are
for those columns. Then I cut it down to a more reasonable size. I'm
very careful when I cut the size of a string column to ensure that all
of the possible values can be contained.

In cases where a column is extremely large for capturing the outputs of
a command, I will change the type to Text or TinyText depending on the
length required. A good example of this is in the agents table of
Neutron where there is a column for configurations that has a string
length of 4096 characters, which I change to Text. Text blobs are stored
differently and do not count against the row length.

I've also observed differences between Kilo, Mitaka, and tip where even
for InnoDB some of these tables are getting wider than can be supported.
So in the case of Cinder, some of the columns have been shifted to
separate tables to fit within 65k. I've seen the same thing in Neutron.
So I fully expect that some of the services that have table bloat will
have to cut the lengths or break the tables up over time anyways. As
that happens, it reduces the amount of work for me, which is a good thing.

The most complicated database schemas to patch up are cinder, glance,
neutron, and nova due to the size and complexity of their tables. Those
also have a lot of churn between releases where the schema changes more
often. Other services like keystone, heat, and ironic are considerably
easier to work with and have well laid out tables that don't change much.

Thanks,
Octave

On 2/2/2017 1:25 PM, Mike Bayer wrote:



On 02/02/2017 02:52 PM, Mike Bayer wrote:


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

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support

2017-02-03 Thread Doug Hellmann

Excerpts from Mike Bayer's message of 2017-02-03 09:41:11 -0500:
> 
> On 02/02/2017 05:28 PM, Octave J. Orgeron wrote:
> > That refers to the total length of the row. InnoDB has a limit of 65k
> > and NDB is limited to 14k.
> >
> > A simple example would be the volumes table in Cinder where the row
> > length goes beyond 14k. So in the IF logic block, I change columns types
> > that are vastly oversized such as status and attach_status, which by
> > default are 255 chars.
> 
> 
> let me give you a tip on IF blocks, that they are a bit of an 
> anti-pattern.  If you want a column type to do one thing in one case, 
> and another in another case, create an object that does the thing you want:
> 
> 
> some_table = Table(
>  'some_table', metadata,
>  Column('my_column', VARCHAR(255).with_variant(VARCHAR(50), 'ndb'))
> )

I wonder if we want to do either, though. Shouldn't we try to use
the same (smaller) column size all the time? Otherwise we end up
with another incompatibility between different deployments, since
sometimes things like names might have different sizes in different
clouds.

> I think we might want to look into creating a stub dialect called 'ndb' 
> that subclasses mysql+pymysql.   Treating ndb as a whole different 
> database means there's no longer the need for a flag in oslo.db, the 
> 'ndb' name would instead be interpreted as a new backend - the main 
> thing would be ensuring all the mysql-appropriate hooks in oslo.db are 
> also emitted for ndb, but this also gives us a way to pick and choose 
> which hooks apply.   It seems like there may be enough different about 
> it to separate it at this level.
> 
> Not sure if people on the list are seeing that we are simultaneously 
> talking about getting rid of Postgresql in the efforts to support only 
> "one database", while at the same time adding one that is in many ways a 
> new database.

Yes, that does seem a bit ironic. That's also why I was pointing
out that we're going to want to have people lined up to support the
work before starting. The lack of help with Postresql testing
resulted in removing it from the gate, and possibly to dropping
support entirely.

For reference, the discussion in [1] led to this proposed TC
resolution [2].

[1] 
http://lists.openstack.org/pipermail/openstack-dev/2017-February/thread.html#111357
[2] https://review.openstack.org/427880

> 
> 
> 
> 
> So to determine a more appropriate size, I look
> > through the Cinder code to find where the possible options/states are
> > for those columns. Then I cut it down to a more reasonable size. I'm
> > very careful when I cut the size of a string column to ensure that all
> > of the possible values can be contained.
> >
> > In cases where a column is extremely large for capturing the outputs of
> > a command, I will change the type to Text or TinyText depending on the
> > length required. A good example of this is in the agents table of
> > Neutron where there is a column for configurations that has a string
> > length of 4096 characters, which I change to Text. Text blobs are stored
> > differently and do not count against the row length.
> >
> > I've also observed differences between Kilo, Mitaka, and tip where even
> > for InnoDB some of these tables are getting wider than can be supported.
> > So in the case of Cinder, some of the columns have been shifted to
> > separate tables to fit within 65k. I've seen the same thing in Neutron.
> > So I fully expect that some of the services that have table bloat will
> > have to cut the lengths or break the tables up over time anyways. As
> > that happens, it reduces the amount of work for me, which is a good thing.
> >
> > The most complicated database schemas to patch up are cinder, glance,
> > neutron, and nova due to the size and complexity of their tables. Those
> > also have a lot of churn between releases where the schema changes more
> > often. Other services like keystone, heat, and ironic are considerably
> > easier to work with and have well laid out tables that don't change much.
> >
> > Thanks,
> > Octave
> >
> > On 2/2/2017 1:25 PM, Mike Bayer wrote:
> >>
> >>
> >> On 02/02/2017 02:52 PM, Mike Bayer wrote:
> >>>
> >>> 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.
> >>>
> >>
> >> correction, the phrase was "Row character length limits 65k -> 14k" -
> >> does this refer to the total size of a row?  I guess rows that store
> >> JSON or tables like keystone tokens are what you had in mind here, can
> >> you give specifics ?
> >>
> >>
> >>
> >>

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support

2017-02-03 Thread Roman Podoliaka

On Fri, Feb 3, 2017 at 4:41 PM, Mike Bayer  wrote:
> Not sure if people on the list are seeing that we are simultaneously talking
> about getting rid of Postgresql in the efforts to support only "one
> database", while at the same time adding one that is in many ways a new
> database.

++

and, FWIW, moving columns between tables and changing of column types
in order to make NDB storage engine happy both seem to be a way more
intrusive than what we've had to do so far in the code of OpenStack
projects in order to support PostgreSQL.

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support

2017-02-03 Thread Mike Bayer




On 02/02/2017 05:28 PM, Octave J. Orgeron wrote:

That refers to the total length of the row. InnoDB has a limit of 65k
and NDB is limited to 14k.

A simple example would be the volumes table in Cinder where the row
length goes beyond 14k. So in the IF logic block, I change columns types
that are vastly oversized such as status and attach_status, which by
default are 255 chars.



let me give you a tip on IF blocks, that they are a bit of an 
anti-pattern.  If you want a column type to do one thing in one case, 
and another in another case, create an object that does the thing you want:



some_table = Table(
'some_table', metadata,
Column('my_column', VARCHAR(255).with_variant(VARCHAR(50), 'ndb'))
)


I think we might want to look into creating a stub dialect called 'ndb' 
that subclasses mysql+pymysql.   Treating ndb as a whole different 
database means there's no longer the need for a flag in oslo.db, the 
'ndb' name would instead be interpreted as a new backend - the main 
thing would be ensuring all the mysql-appropriate hooks in oslo.db are 
also emitted for ndb, but this also gives us a way to pick and choose 
which hooks apply.   It seems like there may be enough different about 
it to separate it at this level.


Not sure if people on the list are seeing that we are simultaneously 
talking about getting rid of Postgresql in the efforts to support only 
"one database", while at the same time adding one that is in many ways a 
new database.





So to determine a more appropriate size, I look

through the Cinder code to find where the possible options/states are
for those columns. Then I cut it down to a more reasonable size. I'm
very careful when I cut the size of a string column to ensure that all
of the possible values can be contained.

In cases where a column is extremely large for capturing the outputs of
a command, I will change the type to Text or TinyText depending on the
length required. A good example of this is in the agents table of
Neutron where there is a column for configurations that has a string
length of 4096 characters, which I change to Text. Text blobs are stored
differently and do not count against the row length.

I've also observed differences between Kilo, Mitaka, and tip where even
for InnoDB some of these tables are getting wider than can be supported.
So in the case of Cinder, some of the columns have been shifted to
separate tables to fit within 65k. I've seen the same thing in Neutron.
So I fully expect that some of the services that have table bloat will
have to cut the lengths or break the tables up over time anyways. As
that happens, it reduces the amount of work for me, which is a good thing.

The most complicated database schemas to patch up are cinder, glance,
neutron, and nova due to the size and complexity of their tables. Those
also have a lot of churn between releases where the schema changes more
often. Other services like keystone, heat, and ironic are considerably
easier to work with and have well laid out tables that don't change much.

Thanks,
Octave

On 2/2/2017 1:25 PM, Mike Bayer wrote:



On 02/02/2017 02:52 PM, Mike Bayer wrote:


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.



correction, the phrase was "Row character length limits 65k -> 14k" -
does this refer to the total size of a row?  I guess rows that store
JSON or tables like keystone tokens are what you had in mind here, can
you give specifics ?



__

OpenStack Development Mailing List (not for usage questions)
Unsubscribe:
openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev



--

Oracle 
Octave J. Orgeron | Sr. Principal Architect and Software Engineer
Oracle Linux OpenStack
Mobile: +1-720-616-1550 
500 Eldorado Blvd. | Broomfield, CO 80021
Certified Oracle Enterprise Architect: Systems Infrastructure

Green Oracle  Oracle is committed to
developing practices and products that help protect the environment



__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev



__
OpenStack Development Mailing List (not for usage questions)

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support

Excerpts from Octave J. Orgeron's message of 2017-02-02 15:08:14 -0700:
> Comments below..
> 
> On 2/2/2017 1:08 PM, Doug Hellmann wrote:
> > Excerpts from Octave J. Orgeron's message of 2017-02-02 12:16:15 -0700:
> >> Hi Doug,
> >>
> >> Comments below..
> >>
> >> Thanks,
> >> Octave
> >>
> >> On 2/2/2017 11:27 AM, Doug Hellmann wrote:
> >>> Excerpts from Octave J. Orgeron's message of 2017-02-02 09:40:23 -0700:

[snip]

> > So all existing scripts that create or modify tables will need to
> > be updated? That's going to be a lot of work. It will also be a lot
> > of work to ensure that new alter scripts are implemented using the
> > required logic, and that testing happens in the gates for all
> > projects supporting this feature to ensure there are no regressions
> > or behavioral changes in the applications as a result of the changes
> > in table definitions.
> >
> > I'll let the folks more familiar with databases in general and MySQL
> > in particular respond to some of the technical details, but I think
> > I should give you fair warning that you're taking on a very big
> > project, especially for someone new to the community.
> 
> Yes, this is major undertaking and major driver for Oracle to setup a 
> 3rd party CI so that we can automate regression testing against MySQL 
> Cluster. On the flip side, it helps solve some of the challenges with 

I'm not sure we would want to gate projects on CI run outside of
our infrastructure community. We've had some bad experiences with
that in the past. What are the options for running MySQL Cluster
on nodes upstream?

> larger deployments where an active/passive solution for MySQL DB is not 
> sufficient. So the pay-off is pretty big from an availability and 
> scale-out perspective.
> 
> But I do realize that I'll have to maintain this long-term and hopefully 
> get others to help out as more services are added to OpenStack.

Given the scope of the work being proposed, and the level of expertise
needed to do it, we're going to need to have more than one person
available to debug issues before we go to far ahead with it.

It might help to have an example or two of the sorts of migration
script changes you've described elsewhere. Maybe you can prepare a
sample patch for a project?

Are there tools that can look at a table definition and tell if it
meets the criteria for the cluster backend (the row size, types
used, whatever else)? Or is it something one has to do by hand?

> > I may not have been entirely clear. You need to add a function to
> > oslo.db to allow a user of oslo.db to read the configuration value
> > without knowing what that option name is. There are two reasons for
> > this policy:
> >
> > 1. Configuration options are supposed to be completely transparent
> > to the application developer using the library, otherwise they
> > would be parameters to the classes or functions in the library
> > instead of deployer-facing configuration options.
> >
> > oslo.config allows us to rename configuration options transparently
> > to deployers (they get a warning about the new name or location
> > for the option in the config file, but the library knows both
> > locations).
> >
> > The rename feature does not work when accessing options
> > programmatically, because we do not consider configuration options
> > to be part of the API of a library.  That means that cfg.CONF.foo.bar
> > can move to cfg.CONF.blah.bletch, and your code using it by the
> > old name will break.
> 
> This is correct. Neutron does exactly what you are describing where you 
> have to look under a neutron namespace instead of the cfg.CONF namespace 
> to find the actual configured setting from the .conf file.

The neutron migration scripts and configuration options are "owned" by
the same code, so that's fine. That's not the case with oslo.db.

> > 2. Accessing configuration options depends on having them registered,
> > and a user of the library that owns a configuration option may not
> > know which functions in the library to call to register the options.
> > As a result, they may try to use an option before it is actually
> > defined. Using an access function to read the value of an option
> > allows the library to ensure the option is registered before trying
> > to return the value.
> >
> > For those reasons, in cases where a configuration option needs to
> > be exposed outside of the library we require a function defined
> > inside the library where we can have unit tests that will break if
> > the configuration option is renamed or otherwise changed, and so
> > we can handle those changes without breaking applications consuming
> > the library.
> >
> > In this case, the migration scripts are outside of oslo.db, so they
> > will need a public function added to oslo.db to access the configuration
> > value. The function should first ensure that the new option is
> > registered, and then return the configured

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support

That refers to the total length of the row. InnoDB has a limit of 65k 
and NDB is limited to 14k.


A simple example would be the volumes table in Cinder where the row 
length goes beyond 14k. So in the IF logic block, I change columns types 
that are vastly oversized such as status and attach_status, which by 
default are 255 chars. So to determine a more appropriate size, I look 
through the Cinder code to find where the possible options/states are 
for those columns. Then I cut it down to a more reasonable size. I'm 
very careful when I cut the size of a string column to ensure that all 
of the possible values can be contained.


In cases where a column is extremely large for capturing the outputs of 
a command, I will change the type to Text or TinyText depending on the 
length required. A good example of this is in the agents table of 
Neutron where there is a column for configurations that has a string 
length of 4096 characters, which I change to Text. Text blobs are stored 
differently and do not count against the row length.


I've also observed differences between Kilo, Mitaka, and tip where even 
for InnoDB some of these tables are getting wider than can be supported. 
So in the case of Cinder, some of the columns have been shifted to 
separate tables to fit within 65k. I've seen the same thing in Neutron. 
So I fully expect that some of the services that have table bloat will 
have to cut the lengths or break the tables up over time anyways. As 
that happens, it reduces the amount of work for me, which is a good thing.


The most complicated database schemas to patch up are cinder, glance, 
neutron, and nova due to the size and complexity of their tables. Those 
also have a lot of churn between releases where the schema changes more 
often. Other services like keystone, heat, and ironic are considerably 
easier to work with and have well laid out tables that don't change much.


Thanks,
Octave

On 2/2/2017 1:25 PM, Mike Bayer wrote:



On 02/02/2017 02:52 PM, Mike Bayer wrote:


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.



correction, the phrase was "Row character length limits 65k -> 14k" - 
does this refer to the total size of a row?  I guess rows that store 
JSON or tables like keystone tokens are what you had in mind here, can 
you give specifics ?




__ 


OpenStack Development Mailing List (not for usage questions)
Unsubscribe: 
openstack-dev-requ...@lists.openstack.org?subject:unsubscribe

http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev



--

Oracle 
Octave J. Orgeron | Sr. Principal Architect and Software Engineer
Oracle Linux OpenStack
Mobile: +1-720-616-1550 
500 Eldorado Blvd. | Broomfield, CO 80021
Certified Oracle Enterprise Architect: Systems Infrastructure 

Green Oracle  Oracle is committed to 
developing practices and products that help protect the environment


__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support


Comments below..

On 2/2/2017 1:08 PM, Doug Hellmann wrote:

Excerpts from Octave J. Orgeron's message of 2017-02-02 12:16:15 -0700:

Hi Doug,

Comments below..

Thanks,
Octave

On 2/2/2017 11:27 AM, Doug Hellmann wrote:

Excerpts from Octave J. Orgeron's message of 2017-02-02 09:40:23 -0700:

Hi Doug,

One could try to detect the default engine. However, in MySQL Cluster,
you can support multiple storage engines. Only NDB is fully clustered
and replicated, so if you accidentally set a table to be InnoDB it won't
be replicated . So it makes more sense for the operator to be explicit
on which engine they want to use.

I think this change is probably a bigger scale item than I understood
it to be when you originally contacted me off-list for advice about
how to get started. I hope I haven't steered you too far wrong, but
at least the conversation is started.

As someone (Mike?) pointed out on the review, the option by itself
doesn't do much of anything, now. Before we add it, I think we'll
want to see some more detail about how it's going used. It may be
easier to have that broader conversation here on email than on the
patch currently up for review.

Understood, it's a complicated topic since it involves gritty details in
SQL Alchemy and Alembic that are masked from end-users and operators
alike. Figuring out how to make this work did take some time on my part.


It sounds like part of the plan is to use the configuration setting
to control how the migration scripts create tables. How will that
work? Does each migration need custom logic, or can we build helpers
into oslo.db somehow? Or will the option be passed to the database
to change its behavior transparently?

These are good questions. For each service, when the db sync or db
manage operation is done it will call into SQL Alchemy or Alembic
depending on the methods used by the given service. For example, most
use SQL Alchemy, but there are services like Ironic and Neutron that use
Alembic. It is within these scripts under the /db/* hierarchy
that the logic exist today to configure the database schema for any
given service. Both approaches will look at the schema version in the
database to determine where to start the create, upgrade, heal, etc.
operations. What my patches do is that in the scripts where a table
needs to be modified, there will be custom IF/THEN logic to check the
cfg.CONF.database.mysql_storage_engine setting to make the required
modifications. There are also use cases where the api.py or model(s).py
under the /db/ hierarchy needs to look at this setting as well
for API and CLI operations where mysql_engine is auto-inserted into DB
operations. In those use cases, I replace the hard coded "InnoDB" with
the mysql_storage_engine variable.

So all existing scripts that create or modify tables will need to
be updated? That's going to be a lot of work. It will also be a lot
of work to ensure that new alter scripts are implemented using the
required logic, and that testing happens in the gates for all
projects supporting this feature to ensure there are no regressions
or behavioral changes in the applications as a result of the changes
in table definitions.

I'll let the folks more familiar with databases in general and MySQL
in particular respond to some of the technical details, but I think
I should give you fair warning that you're taking on a very big
project, especially for someone new to the community.


Yes, this is major undertaking and major driver for Oracle to setup a 
3rd party CI so that we can automate regression testing against MySQL 
Cluster. On the flip side, it helps solve some of the challenges with 
larger deployments where an active/passive solution for MySQL DB is not 
sufficient. So the pay-off is pretty big from an availability and 
scale-out perspective.


But I do realize that I'll have to maintain this long-term and hopefully 
get others to help out as more services are added to OpenStack.





It would be interesting if we could develop some helpers to automate
this, but it would probably have to be at the SQL Alchemy or Alembic
levels. Unfortunately, throughout all of the OpenStack services today we
are hard coding things like mysql_engine, using InnoDB specific features
(savepoints, nested operations, etc.), and not following the strict SQL
orders for modifying table elements (foreign keys, constraints, and
indexes). That actually makes it difficult to support other MySQL
dialects or other databases out of the box. SQL Alchemy can be used to
fix some of these things if the SQL statements are all generic and we
follow strict SQL rules. But to change that would be a monumental
effort. That is why I took this approach of just adding custom logic.
There is a president for this already for Postgres and DB2 support in
some of the OpenStack services using custom logic to deal with similar
differences.

As to why we should place the configuration setting into oslo.db? Here
are a couple of logical reasons:

Oh, I'm not

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support

Hi Doug,

Comments below..

Thanks,
Octave

On 2/2/2017 12:52 PM, Mike Bayer wrote:

On 02/02/2017 02:16 PM, Octave J. Orgeron wrote:

Hi Doug,

Comments below..

Thanks,
Octave

On 2/2/2017 11:27 AM, Doug Hellmann wrote:

It sounds like part of the plan is to use the configuration setting
to control how the migration scripts create tables. How will that
work? Does each migration need custom logic, or can we build helpers
into oslo.db somehow? Or will the option be passed to the database
to change its behavior transparently?

These are good questions. For each service, when the db sync or db
manage operation is done it will call into SQL Alchemy or Alembic
depending on the methods used by the given service. For example, most
use SQL Alchemy, but there are services like Ironic and Neutron that use
Alembic. It is within these scripts under the /db/* hierarchy
that the logic exist today to configure the database schema for any
given service. Both approaches will look at the schema version in the
database to determine where to start the create, upgrade, heal, etc.
operations. What my patches do is that in the scripts where a table
needs to be modified, there will be custom IF/THEN logic to check the
cfg.CONF.database.mysql_storage_engine setting to make the required
modifications. There are also use cases where the api.py or model(s).py
under the /db/ hierarchy needs to look at this setting as well
for API and CLI operations where mysql_engine is auto-inserted into DB
operations. In those use cases, I replace the hard coded "InnoDB" with
the mysql_storage_engine variable.

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
https://github.com/openstack/nova/blob/master/nova/db/sqlalchemy/migrate_repo/versions/216_havana.py
?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?

Yes, that is the plan to patch each of the scripts that has these and
any other issues that need to be addressed.

It would be interesting if we could develop some helpers to automate
this, but it would probably have to be at the SQL Alchemy or Alembic
levels.

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.

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.

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.

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.

Unfortunately, throughout all of the OpenStack services today we

are hard coding things like mysql_engine, using InnoDB specific features
(savepoints, nested operations, etc.), and not following the strict SQL
orders for modifying table elements (foreign keys, constraints, and
indexes).

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.

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

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support

2017-02-02 Thread Mike Bayer




On 02/02/2017 02:52 PM, Mike Bayer wrote:


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.



correction, the phrase was "Row character length limits 65k -> 14k" - 
does this refer to the total size of a row?  I guess rows that store 
JSON or tables like keystone tokens are what you had in mind here, can 
you give specifics ?




__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support

Excerpts from Octave J. Orgeron's message of 2017-02-02 12:16:15 -0700:
> Hi Doug,
> 
> Comments below..
> 
> Thanks,
> Octave
> 
> On 2/2/2017 11:27 AM, Doug Hellmann wrote:
> > Excerpts from Octave J. Orgeron's message of 2017-02-02 09:40:23 -0700:
> >> Hi Doug,
> >>
> >> One could try to detect the default engine. However, in MySQL Cluster,
> >> you can support multiple storage engines. Only NDB is fully clustered
> >> and replicated, so if you accidentally set a table to be InnoDB it won't
> >> be replicated . So it makes more sense for the operator to be explicit
> >> on which engine they want to use.
> > I think this change is probably a bigger scale item than I understood
> > it to be when you originally contacted me off-list for advice about
> > how to get started. I hope I haven't steered you too far wrong, but
> > at least the conversation is started.
> >
> > As someone (Mike?) pointed out on the review, the option by itself
> > doesn't do much of anything, now. Before we add it, I think we'll
> > want to see some more detail about how it's going used. It may be
> > easier to have that broader conversation here on email than on the
> > patch currently up for review.
> 
> Understood, it's a complicated topic since it involves gritty details in 
> SQL Alchemy and Alembic that are masked from end-users and operators 
> alike. Figuring out how to make this work did take some time on my part.
> 
> >
> > It sounds like part of the plan is to use the configuration setting
> > to control how the migration scripts create tables. How will that
> > work? Does each migration need custom logic, or can we build helpers
> > into oslo.db somehow? Or will the option be passed to the database
> > to change its behavior transparently?
> 
> These are good questions. For each service, when the db sync or db 
> manage operation is done it will call into SQL Alchemy or Alembic 
> depending on the methods used by the given service. For example, most 
> use SQL Alchemy, but there are services like Ironic and Neutron that use 
> Alembic. It is within these scripts under the /db/* hierarchy 
> that the logic exist today to configure the database schema for any 
> given service. Both approaches will look at the schema version in the 
> database to determine where to start the create, upgrade, heal, etc. 
> operations. What my patches do is that in the scripts where a table 
> needs to be modified, there will be custom IF/THEN logic to check the 
> cfg.CONF.database.mysql_storage_engine setting to make the required 
> modifications. There are also use cases where the api.py or model(s).py 
> under the /db/ hierarchy needs to look at this setting as well 
> for API and CLI operations where mysql_engine is auto-inserted into DB 
> operations. In those use cases, I replace the hard coded "InnoDB" with 
> the mysql_storage_engine variable.

So all existing scripts that create or modify tables will need to
be updated? That's going to be a lot of work. It will also be a lot
of work to ensure that new alter scripts are implemented using the
required logic, and that testing happens in the gates for all
projects supporting this feature to ensure there are no regressions
or behavioral changes in the applications as a result of the changes
in table definitions.

I'll let the folks more familiar with databases in general and MySQL
in particular respond to some of the technical details, but I think
I should give you fair warning that you're taking on a very big
project, especially for someone new to the community.

> It would be interesting if we could develop some helpers to automate 
> this, but it would probably have to be at the SQL Alchemy or Alembic 
> levels. Unfortunately, throughout all of the OpenStack services today we 
> are hard coding things like mysql_engine, using InnoDB specific features 
> (savepoints, nested operations, etc.), and not following the strict SQL 
> orders for modifying table elements (foreign keys, constraints, and 
> indexes). That actually makes it difficult to support other MySQL 
> dialects or other databases out of the box. SQL Alchemy can be used to 
> fix some of these things if the SQL statements are all generic and we 
> follow strict SQL rules. But to change that would be a monumental 
> effort. That is why I took this approach of just adding custom logic. 
> There is a president for this already for Postgres and DB2 support in 
> some of the OpenStack services using custom logic to deal with similar 
> differences.
> 
> As to why we should place the configuration setting into oslo.db? Here 
> are a couple of logical reasons:

Oh, I'm not questioning putting the option in oslo.db. I think that's
clearly the right place to put it.

> 
>   * The configuration block for database settings for each service comes
> from the oslo.db namespace today under cfg.CONF.database.*. Placing
> it here makes the location consistent across all of the services.
>   * Within the SQL Alchemy and Alembic

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support

2017-02-02 Thread Mike Bayer




On 02/02/2017 02:16 PM, Octave J. Orgeron wrote:

Hi Doug,

Comments below..

Thanks,
Octave

On 2/2/2017 11:27 AM, Doug Hellmann wrote:

It sounds like part of the plan is to use the configuration setting
to control how the migration scripts create tables. How will that
work? Does each migration need custom logic, or can we build helpers
into oslo.db somehow? Or will the option be passed to the database
to change its behavior transparently?


These are good questions. For each service, when the db sync or db
manage operation is done it will call into SQL Alchemy or Alembic
depending on the methods used by the given service. For example, most
use SQL Alchemy, but there are services like Ironic and Neutron that use
Alembic. It is within these scripts under the /db/* hierarchy
that the logic exist today to configure the database schema for any
given service. Both approaches will look at the schema version in the
database to determine where to start the create, upgrade, heal, etc.
operations. What my patches do is that in the scripts where a table
needs to be modified, there will be custom IF/THEN logic to check the
cfg.CONF.database.mysql_storage_engine setting to make the required
modifications. There are also use cases where the api.py or model(s).py
under the /db/ hierarchy needs to look at this setting as well
for API and CLI operations where mysql_engine is auto-inserted into DB
operations. In those use cases, I replace the hard coded "InnoDB" with
the mysql_storage_engine variable.


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 
https://github.com/openstack/nova/blob/master/nova/db/sqlalchemy/migrate_repo/versions/216_havana.py 
?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?





It would be interesting if we could develop some helpers to automate
this, but it would probably have to be at the SQL Alchemy or Alembic
levels.


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.


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.


Unfortunately, throughout all of the OpenStack services today we

are hard coding things like mysql_engine, using InnoDB specific features
(savepoints, nested operations, etc.), and not following the strict SQL
orders for modifying table elements (foreign keys, constraints, and
indexes).


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.





  * Many of the SQL Alchemy and Alembic scripts only import the minimal
set of python modules. If we imported others, we would also have to
initialize those name spaces which means a lot more code :(


I'm not sure what this means, can you clarify ?


   * Reduces the amount of overhead required to make these changes.

What sort of "overhead", do you mean code complexity, performance ?








Keep in mind that we do not encourage code outside of libraries to
rely on configuration settings defined within libraries, because
that limits our ability to change the names and locations of the
configuration variables.  If migration scripts need to access the
configuration setting we will need to add some sort of public API
to oslo.db to query the value. The function can simply return the
configured value.


Configuration parameters within any given service will make use of a
large namespace that pulls in things from oslo and the .conf files for a
given service. So even when an API, CLI, or DB related call is made,
these namespaces are key for things to work. In the case of the SQL
Alchemy and Alembic scripts, they also make use of this namespace with
oslo, oslo.db, etc. to figure out how to connect to the database and
other database settings. I don't think we need a public API for these
kinds of calls as the community already makes use of the libraries to
build the namespace. My oslo.db setting

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support


Hi Doug,

Comments below..

Thanks,
Octave

On 2/2/2017 11:27 AM, Doug Hellmann wrote:

Excerpts from Octave J. Orgeron's message of 2017-02-02 09:40:23 -0700:

Hi Doug,

One could try to detect the default engine. However, in MySQL Cluster,
you can support multiple storage engines. Only NDB is fully clustered
and replicated, so if you accidentally set a table to be InnoDB it won't
be replicated . So it makes more sense for the operator to be explicit
on which engine they want to use.

I think this change is probably a bigger scale item than I understood
it to be when you originally contacted me off-list for advice about
how to get started. I hope I haven't steered you too far wrong, but
at least the conversation is started.

As someone (Mike?) pointed out on the review, the option by itself
doesn't do much of anything, now. Before we add it, I think we'll
want to see some more detail about how it's going used. It may be
easier to have that broader conversation here on email than on the
patch currently up for review.


Understood, it's a complicated topic since it involves gritty details in 
SQL Alchemy and Alembic that are masked from end-users and operators 
alike. Figuring out how to make this work did take some time on my part.




It sounds like part of the plan is to use the configuration setting
to control how the migration scripts create tables. How will that
work? Does each migration need custom logic, or can we build helpers
into oslo.db somehow? Or will the option be passed to the database
to change its behavior transparently?


These are good questions. For each service, when the db sync or db 
manage operation is done it will call into SQL Alchemy or Alembic 
depending on the methods used by the given service. For example, most 
use SQL Alchemy, but there are services like Ironic and Neutron that use 
Alembic. It is within these scripts under the /db/* hierarchy 
that the logic exist today to configure the database schema for any 
given service. Both approaches will look at the schema version in the 
database to determine where to start the create, upgrade, heal, etc. 
operations. What my patches do is that in the scripts where a table 
needs to be modified, there will be custom IF/THEN logic to check the 
cfg.CONF.database.mysql_storage_engine setting to make the required 
modifications. There are also use cases where the api.py or model(s).py 
under the /db/ hierarchy needs to look at this setting as well 
for API and CLI operations where mysql_engine is auto-inserted into DB 
operations. In those use cases, I replace the hard coded "InnoDB" with 
the mysql_storage_engine variable.


It would be interesting if we could develop some helpers to automate 
this, but it would probably have to be at the SQL Alchemy or Alembic 
levels. Unfortunately, throughout all of the OpenStack services today we 
are hard coding things like mysql_engine, using InnoDB specific features 
(savepoints, nested operations, etc.), and not following the strict SQL 
orders for modifying table elements (foreign keys, constraints, and 
indexes). That actually makes it difficult to support other MySQL 
dialects or other databases out of the box. SQL Alchemy can be used to 
fix some of these things if the SQL statements are all generic and we 
follow strict SQL rules. But to change that would be a monumental 
effort. That is why I took this approach of just adding custom logic. 
There is a president for this already for Postgres and DB2 support in 
some of the OpenStack services using custom logic to deal with similar 
differences.


As to why we should place the configuration setting into oslo.db? Here 
are a couple of logical reasons:


 * The configuration block for database settings for each service comes
   from the oslo.db namespace today under cfg.CONF.database.*. Placing
   it here makes the location consistent across all of the services.
 * Within the SQL Alchemy and Alembic scripts, this is one of the few
   common namespaces that are available without bringing in a larger
   number of modules across the services today.
 * Many of the SQL Alchemy and Alembic scripts only import the minimal
   set of python modules. If we imported others, we would also have to
   initialize those name spaces which means a lot more code :(
 * Reduces the amount of overhead required to make these changes.




Keep in mind that we do not encourage code outside of libraries to
rely on configuration settings defined within libraries, because
that limits our ability to change the names and locations of the
configuration variables.  If migration scripts need to access the
configuration setting we will need to add some sort of public API
to oslo.db to query the value. The function can simply return the
configured value.


Configuration parameters within any given service will make use of a 
large namespace that pulls in things from oslo and the .conf files for a 
given service. So even when an API, CLI, or DB related call is made, 
these

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support

Excerpts from Octave J. Orgeron's message of 2017-02-02 09:40:23 -0700:
> Hi Doug,
> 
> One could try to detect the default engine. However, in MySQL Cluster, 
> you can support multiple storage engines. Only NDB is fully clustered 
> and replicated, so if you accidentally set a table to be InnoDB it won't 
> be replicated . So it makes more sense for the operator to be explicit 
> on which engine they want to use.

I think this change is probably a bigger scale item than I understood
it to be when you originally contacted me off-list for advice about
how to get started. I hope I haven't steered you too far wrong, but
at least the conversation is started.

As someone (Mike?) pointed out on the review, the option by itself
doesn't do much of anything, now. Before we add it, I think we'll
want to see some more detail about how it's going used. It may be
easier to have that broader conversation here on email than on the
patch currently up for review.

It sounds like part of the plan is to use the configuration setting
to control how the migration scripts create tables. How will that
work? Does each migration need custom logic, or can we build helpers
into oslo.db somehow? Or will the option be passed to the database
to change its behavior transparently?

Keep in mind that we do not encourage code outside of libraries to
rely on configuration settings defined within libraries, because
that limits our ability to change the names and locations of the
configuration variables.  If migration scripts need to access the
configuration setting we will need to add some sort of public API
to oslo.db to query the value. The function can simply return the
configured value.

What other behaviors are likely to be changed by the new option?
Will application runtime behavior need to know about the storage
engine?

Doug

> 
> Thanks,
> Octave
> 
> On 2/2/2017 6:46 AM, Doug Hellmann wrote:
> > Excerpts from Octave J. Orgeron's message of 2017-02-01 20:33:38 -0700:
> >> Hi Folks,
> >>
> >> I'm working on adding support for MySQL Cluster to the core OpenStack
> >> services. This will enable the community to benefit from an
> >> active/active, auto-sharding, and scale-out MySQL database. My approach
> >> is to have a single configuration setting in each core OpenStack service
> >> in the oslo.db configuration section called mysql_storage_engine that
> >> will enable the logic in the SQL Alchemy or Alembic upgrade scripts to
> >> handle the differences between InnoDB and NDB storage engines
> >> respectively. When enabled, this logic will make the required table
> >> schema changes around:
> >>
> >>* Row character length limits 65k -> 14k
> >>* Proper SQL ordering of foreign key, constraints, and index operations
> >>* Interception of savepoint and nested operations
> >>
> >> By default this functionality will not be enabled and will have no
> >> impact on the default InnoDB functionality. These changes have been
> >> tested on Kilo and Mitaka in previous releases of our OpenStack
> >> distributions with Tempest. I'm working on updating these patches for
> >> upstream consumption. We are also working on a 3rd party CI for
> >> regression testing against MySQL Cluster for the community.
> >>
> >> The first change set is for oslo.db and can be reviewed at:
> >>
> >> https://review.openstack.org/427970
> >>
> >> Thanks,
> >> Octave
> >>
> > Is it possible to detect the storage engine at runtime, instead of
> > having the operator configure it?
> >
> > Doug
> >
> > __
> > OpenStack Development Mailing List (not for usage questions)
> > Unsubscribe: openstack-dev-requ...@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-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support


Hi Mike,

I've sent out another email that gives some more insight into how this 
will work for the other OpenStack services. The hook in the oslo.db 
namespace gives a global configuration point for enabling the patches 
elsewhere.


Thanks,
Octave

On 2/2/2017 9:24 AM, Mike Bayer wrote:



On 02/02/2017 10:25 AM, Monty Taylor wrote:

On 02/01/2017 09:33 PM, Octave J. Orgeron wrote:

Hi Folks,

I'm working on adding support for MySQL Cluster to the core OpenStack
services. This will enable the community to benefit from an
active/active, auto-sharding, and scale-out MySQL database. My approach
is to have a single configuration setting in each core OpenStack 
service

in the oslo.db configuration section called mysql_storage_engine that
will enable the logic in the SQL Alchemy or Alembic upgrade scripts to
handle the differences between InnoDB and NDB storage engines
respectively. When enabled, this logic will make the required table
schema changes around:

  * Row character length limits 65k -> 14k
  * Proper SQL ordering of foreign key, constraints, and index 
operations

  * Interception of savepoint and nested operations

By default this functionality will not be enabled and will have no
impact on the default InnoDB functionality. These changes have been
tested on Kilo and Mitaka in previous releases of our OpenStack
distributions with Tempest. I'm working on updating these patches for
upstream consumption. We are also working on a 3rd party CI for
regression testing against MySQL Cluster for the community.

The first change set is for oslo.db and can be reviewed at:

https://review.openstack.org/427970


Yay!

(You may not be aware, but there are several of us who used to be on the
MySQL Cluster team who are now on OpenStack. I've been wanting good NDB
support for a while. So thank you!)


as I noted on the review it would be nice to have some specifics of 
how this is to be accomplished as the code review posted doesn't show 
anything of how this would work.








__ 


OpenStack Development Mailing List (not for usage questions)
Unsubscribe: 
openstack-dev-requ...@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-requ...@lists.openstack.org?subject:unsubscribe

http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev



--

Oracle 
Octave J. Orgeron | Sr. Principal Architect and Software Engineer
Oracle Linux OpenStack
Mobile: +1-720-616-1550 
500 Eldorado Blvd. | Broomfield, CO 80021
Certified Oracle Enterprise Architect: Systems Infrastructure 

Green Oracle  Oracle is committed to 
developing practices and products that help protect the environment


__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support


Hi Monty,

Thank you for the feedback. I'm excited about getting these patches 
upstream as everyone will be able to benefit from them.


Thanks,
Octave

On 2/2/2017 8:25 AM, Monty Taylor wrote:

On 02/01/2017 09:33 PM, Octave J. Orgeron wrote:

Hi Folks,

I'm working on adding support for MySQL Cluster to the core OpenStack
services. This will enable the community to benefit from an
active/active, auto-sharding, and scale-out MySQL database. My approach
is to have a single configuration setting in each core OpenStack service
in the oslo.db configuration section called mysql_storage_engine that
will enable the logic in the SQL Alchemy or Alembic upgrade scripts to
handle the differences between InnoDB and NDB storage engines
respectively. When enabled, this logic will make the required table
schema changes around:

   * Row character length limits 65k -> 14k
   * Proper SQL ordering of foreign key, constraints, and index operations
   * Interception of savepoint and nested operations

By default this functionality will not be enabled and will have no
impact on the default InnoDB functionality. These changes have been
tested on Kilo and Mitaka in previous releases of our OpenStack
distributions with Tempest. I'm working on updating these patches for
upstream consumption. We are also working on a 3rd party CI for
regression testing against MySQL Cluster for the community.

The first change set is for oslo.db and can be reviewed at:

https://review.openstack.org/427970

Yay!

(You may not be aware, but there are several of us who used to be on the
MySQL Cluster team who are now on OpenStack. I've been wanting good NDB
support for a while. So thank you!)


__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev


--

Oracle 
Octave J. Orgeron | Sr. Principal Architect and Software Engineer
Oracle Linux OpenStack
Mobile: +1-720-616-1550 
500 Eldorado Blvd. | Broomfield, CO 80021
Certified Oracle Enterprise Architect: Systems Infrastructure 

Green Oracle  Oracle is committed to 
developing practices and products that help protect the environment


__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support


Hi Doug,

One could try to detect the default engine. However, in MySQL Cluster, 
you can support multiple storage engines. Only NDB is fully clustered 
and replicated, so if you accidentally set a table to be InnoDB it won't 
be replicated . So it makes more sense for the operator to be explicit 
on which engine they want to use.


Thanks,
Octave

On 2/2/2017 6:46 AM, Doug Hellmann wrote:

Excerpts from Octave J. Orgeron's message of 2017-02-01 20:33:38 -0700:

Hi Folks,

I'm working on adding support for MySQL Cluster to the core OpenStack
services. This will enable the community to benefit from an
active/active, auto-sharding, and scale-out MySQL database. My approach
is to have a single configuration setting in each core OpenStack service
in the oslo.db configuration section called mysql_storage_engine that
will enable the logic in the SQL Alchemy or Alembic upgrade scripts to
handle the differences between InnoDB and NDB storage engines
respectively. When enabled, this logic will make the required table
schema changes around:

   * Row character length limits 65k -> 14k
   * Proper SQL ordering of foreign key, constraints, and index operations
   * Interception of savepoint and nested operations

By default this functionality will not be enabled and will have no
impact on the default InnoDB functionality. These changes have been
tested on Kilo and Mitaka in previous releases of our OpenStack
distributions with Tempest. I'm working on updating these patches for
upstream consumption. We are also working on a 3rd party CI for
regression testing against MySQL Cluster for the community.

The first change set is for oslo.db and can be reviewed at:

https://review.openstack.org/427970

Thanks,
Octave


Is it possible to detect the storage engine at runtime, instead of
having the operator configure it?

Doug

__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev


--

Oracle 
Octave J. Orgeron | Sr. Principal Architect and Software Engineer
Oracle Linux OpenStack
Mobile: +1-720-616-1550 
500 Eldorado Blvd. | Broomfield, CO 80021
Certified Oracle Enterprise Architect: Systems Infrastructure 

Green Oracle  Oracle is committed to 
developing practices and products that help protect the environment


__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support

2017-02-02 Thread Mike Bayer




On 02/02/2017 10:25 AM, Monty Taylor wrote:

On 02/01/2017 09:33 PM, Octave J. Orgeron wrote:

Hi Folks,

I'm working on adding support for MySQL Cluster to the core OpenStack
services. This will enable the community to benefit from an
active/active, auto-sharding, and scale-out MySQL database. My approach
is to have a single configuration setting in each core OpenStack service
in the oslo.db configuration section called mysql_storage_engine that
will enable the logic in the SQL Alchemy or Alembic upgrade scripts to
handle the differences between InnoDB and NDB storage engines
respectively. When enabled, this logic will make the required table
schema changes around:

  * Row character length limits 65k -> 14k
  * Proper SQL ordering of foreign key, constraints, and index operations
  * Interception of savepoint and nested operations

By default this functionality will not be enabled and will have no
impact on the default InnoDB functionality. These changes have been
tested on Kilo and Mitaka in previous releases of our OpenStack
distributions with Tempest. I'm working on updating these patches for
upstream consumption. We are also working on a 3rd party CI for
regression testing against MySQL Cluster for the community.

The first change set is for oslo.db and can be reviewed at:

https://review.openstack.org/427970


Yay!

(You may not be aware, but there are several of us who used to be on the
MySQL Cluster team who are now on OpenStack. I've been wanting good NDB
support for a while. So thank you!)


as I noted on the review it would be nice to have some specifics of how 
this is to be accomplished as the code review posted doesn't show 
anything of how this would work.








__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@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-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support

2017-02-02 Thread Monty Taylor

On 02/01/2017 09:33 PM, Octave J. Orgeron wrote:
> Hi Folks,
> 
> I'm working on adding support for MySQL Cluster to the core OpenStack
> services. This will enable the community to benefit from an
> active/active, auto-sharding, and scale-out MySQL database. My approach
> is to have a single configuration setting in each core OpenStack service
> in the oslo.db configuration section called mysql_storage_engine that
> will enable the logic in the SQL Alchemy or Alembic upgrade scripts to
> handle the differences between InnoDB and NDB storage engines
> respectively. When enabled, this logic will make the required table
> schema changes around:
> 
>   * Row character length limits 65k -> 14k
>   * Proper SQL ordering of foreign key, constraints, and index operations
>   * Interception of savepoint and nested operations
> 
> By default this functionality will not be enabled and will have no
> impact on the default InnoDB functionality. These changes have been
> tested on Kilo and Mitaka in previous releases of our OpenStack
> distributions with Tempest. I'm working on updating these patches for
> upstream consumption. We are also working on a 3rd party CI for
> regression testing against MySQL Cluster for the community.
> 
> The first change set is for oslo.db and can be reviewed at:
> 
> https://review.openstack.org/427970

Yay!

(You may not be aware, but there are several of us who used to be on the
MySQL Cluster team who are now on OpenStack. I've been wanting good NDB
support for a while. So thank you!)


__
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

Re: [openstack-dev] [oslo][oslo.db] MySQL Cluster support