Open Stack

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: 
> OpenStack-dev-request at lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev