Hi Jay,
Comments below..
On 7/26/2017 5:43 PM, Jay Pipes wrote:
On 07/26/2017 07:06 PM, Octave J. Orgeron wrote:
Hi Michael,
On 7/26/2017 4:28 PM, Michael Bayer wrote:
it at all.
thinking out loud
oslo_db.sqlalchemy.types.String(255, mysql_small_rowsize=64)
oslo_db.sqlalchemy.types.String(255, mysql_small_rowsize=sa.TINYTEXT)
oslo_db.sqlalchemy.types.String(255, mysql_small_rowsize=sa.TEXT)
so if you don't have mysql_small_rowsize, nothing happens.
I think the mysql_small_rowsize is a bit misleading since in one case
we are changing the size and the others the type. Perhaps:
mysql_alt_size=64
mysql_alt_type=sa.TINYTEXT
mysql_alt_type=sa.TEXT
alt standing for alternate. What do you think?
-1
I think it should be specific to NDB, since that's what the override
is for. I'd support something like:
oslo_db.sqlalchemy.types.String(255, mysql_ndb_size=64)
Octave, I understand due to the table row size limitations the desire
to reduce some column sizes for NDB. What I'm not entirely clear on is
the reason to change the column *type* specifically for NDB. There are
definitely cases where different databases have column types -- say,
PostgreSQL's INET column type -- that don't exist in other RDBMS. For
those cases, the standard approach in SQLAlchemy is to create a
sqlalchemy ColumnType concrete class that essentially translates the
CREATE TABLE statement (and type compilation/coercing) to specify the
supported column type in the RDBMS if it's supported otherwise
defaults the column type to something coerceable.
When it comes to changing the size or the type for a column for NDB,
this has to do with the difference in the table row limits. InnoDB
limits to 65k and NDB limits to 14k. You can't cross those limits in
either engine because it's used as part of the internal storage engine
and affects things like replication constraints, memory alignment, etc.
Because we are dealing with an issue of row length within the table, the
best way to work around this is to do one of the following.. change the
size of the column so that it fits, move the column to another table,
split the table up, or to change it to a different type. The reason why
this works is that TEXT types are stored as blobs in databases. All
database engines handle BLOBs differently than other types and as a
result they reduce the count against the row length. That's why I change
some of these columns to TEXT types. If you look closely through
services like Neutron, Barbican, Designate, Keystone, etc. you'll see
that they have hit the 65k limit in InnoDB on some tables and have had
to do the same thing. Realistically, any time you are storing something
like SSH keys, SSL certs, output from commands, etc. you should be using
the TEXT types anyways.
FYI, if you were talking about a large enterprise database for a bank or
retail shop, DBAs spend a lot of time designing tables and looking very
closely at the structure to ensure that they don't hit performance
problems, run out of row or table space, etc. They are extremely careful
about the usage of space. In some of the openstack projects, it's very
clear that we are wasting a lot of space and when tables get too wide,
they have to be rearranged and modified to deal with the limits and
constraints. So to put it into context for Nova, if any of the tables
are close to 65k in width, they will need to be modified or restructured
eventually.
Each database has structure limits:
https://www.postgresql.org/about/
https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
https://dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-limitations.html
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001029.html
https://docs.oracle.com/cloud/latest/db112/REFRN/limits003.htm#REFRN0043
If you dig through those, you'll see that each database has different
limits on things like columns, rows, sizes, indexes, etc. So this isn't
just an NDB constraint. If you want everything to work across InnoDB,
NDB, PostgreSQL, DB2, etc. we will have to deal with these table issues
eventually.
An example of this can be seen here for how this is done for IPv4 data
in the apiary project:
https://github.com/gmr/apiary/blob/master/apiary/types.py#L49
I'd certainly be open to doing things like this for NDB, but I'd first
need to understand why you chose to convert the column types for the
columns that you did. Any information you can provide about that would
be great.
Best,
-jay
__________________________________________________________________________
OpenStack Development Mailing List (not for usage questions)
Unsubscribe:
[email protected]?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
__________________________________________________________________________
OpenStack Development Mailing List (not for usage questions)
Unsubscribe: [email protected]?subject:unsubscribe
http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
