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
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 <http://www.oracle.com/>
Octave J. Orgeron | Sr. Principal Architect and Software Engineer
Oracle Linux OpenStack
Mobile: +1-720-616-1550 <tel:+17206161550>
500 Eldorado Blvd. | Broomfield, CO 80021
Certified Oracle Enterprise Architect: Systems Infrastructure
<http://www.oracle.com/us/solutions/enterprise-architecture/index.html>
Green Oracle <http://www.oracle.com/commitment> 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)
Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
--
Oracle <http://www.oracle.com/>
Octave J. Orgeron | Sr. Principal Architect and Software Engineer
Oracle Linux OpenStack
Mobile: +1-720-616-1550 <tel:+17206161550>
500 Eldorado Blvd. | Broomfield, CO 80021
Certified Oracle Enterprise Architect: Systems Infrastructure
<http://www.oracle.com/us/solutions/enterprise-architecture/index.html>
Green Oracle <http://www.oracle.com/commitment> 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