Open Stack

Hi Jay,

Comments below..

I apologize for being a bit verbose, but I know some folks on the list 
are not familiar with NDB, so I do go into some details below.

Thanks,
Octave

On 7/27/2017 11:49 AM, Jay Pipes wrote:
> I guess we're really getting into the weeds here.
>
> On 07/27/2017 12:51 PM, Octave J. Orgeron wrote:
>> 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.
>
> Yes, I'm aware of those constraints, though you are incorrect about 
> InnoDB.
>
> InnoDB's row size limit is actually not 65K. It is dependent on the 
> innodb_page_size value. At the default innodb_page_size value of 16KB, 
> the max row size is 8KB. It is MySQL, not InnoDB, that places a max 
> row size of 64KB which limits row size when innodb_page_size is set to 
> a large value.

The row limit and the page size value are definitely related and if the 
page size isn't configured, you can run into the limit faster:

https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html#row-size-limits

>
> However, it is important to point out that InnoDB's max row size 
> *doesn't* include the size of BLOB-based *data*, only a pointer to 
> that data on disk. *Nor* does InnoDB count VARCHAR/VARBINARY columns' 
> size in its maximum row size calculations. A VARCHAR(9000) column in 
> InnoDB is perfectly acceptable. [1]
>
> NDB, on the other hand, isn't a MySQL storage engine in the way that 
> InnoDB is [2]. :) It's a completely different database system that is 
> designed for in-memory-only use, though support for TEXT and BLOB 
> columns in disk-backed cluster nodes is supported now.

By default these days, NDB will back-end everything to disk. You are 
right that it's in-memory first and sync'd across nodes. NDB storage 
nodes that own the data bits for a given data blob then sync that to 
disk. It's a two phase commit model. Very different from the InnoDB model.

>
> NDB always stores the first 256 bytes of the BLOB/TEXT data plus an 
> 8-byte pointer to disk table data. This is in contrast to InnoDB which 
> just stores a pointer to the data [2]. VARCHAR/VARBINARY data in 
> InnoDB is different. While InnoDB will try to fit some amount of the 
> VARCHAR data in the row itself before automatically overflowing the 
> data to a pointer to a separate data page, NDB, on the other hand, 
> treats VARCHAR/VARBINARY is fixed-size columns.
>
> This is the big difference between the two systems and why you are 
> changing some columns to the TEXT type.
>
> [1] mysql> use test
> Database changed
> mysql> show variables like 'innodb_page_size';
> +------------------+-------+
> | Variable_name    | Value |
> +------------------+-------+
> | innodb_page_size | 16384 |
> +------------------+-------+
> 1 row in set (0.02 sec)
>
> mysql> create table t1 (a VARCHAR(9000));
> Query OK, 0 rows affected (0.02 sec)

If you look in the link below, you'll see that if you go over the 65k 
limit with InnoDB, you'll run into the same problem as NDB:

https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html#row-size-limits

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
        c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
        f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET 
latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. *You have to change some columns to TEXT or BLOBs*

You have to love the error message above, because it tells you how to 
fix this problem :)
Here is the example from the documentation on how to fix this by using TEXT:

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
        c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
        f VARCHAR(10000), g *TEXT(6000)*) ENGINE=InnoDB CHARACTER SET 
latin1;
Query OK, 0 rows affected (0.02 sec)

So regardless of the engine being used, you have to be mindful of the 
length of the rows in your tables :)


>
> [2] This is the reason NDB isn't listed under "Alternative Storage 
> Engines" in the MySQL documentation...

Actually the reason is that the binaries for MySQL InnoDB and MySQL 
Cluster (NDB) are different because of the underlining architectures. 
You can't drop the NDB engine components into your MySQL directory and 
expect the normal MySQL binaries to know what to do. They both speak the 
same MySQL dialect, but use different engines, replication models, 
processes, etc. For MySQL Cluster, the MySQL dialect is nothing more 
than an API layer that translates MySQL dialect to NDB storage calls. On 
the flip side, the MySQL API layer can also deal with InnoDB tables, but 
they are not replicated or clustered under MySQL Cluster because it's a 
different architecture.

>
>> 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.
>
> Please see my earlier response about the REST API -- at least in Nova 
> -- unfortunately exposing certain input field length limitations. It's 
> not possible to reduce certain column sizes without a corresponding 
> microversion bump in the public API.

You can modify the models and api modules to deal with these changes. 
Something that is also included in my patches thus far. The 
microversioning is the only missing component here and I can work on 
addressing that.

>
>> 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.
> As mentioned above, you are changing the column type because of the 
> fact that NDB treats VARCHAR as fixed-length CHAR fields and counts 
> the max VARCHAR size against the total row size, unlike InnoDB. It's 
> an important distinction.

VARCHARs clearly impact the total row size in InnoDB, if you look at the 
documentation and examples above. How the underlining storage engines 
deal with the data layout in memory and on disk is totally different 
between the two. But that is no different than comparing InnoDB with 
MYISAM.. apples and oranges.

>
>> 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.
>
> No disagreement from me at all here. However, see above point about 
> the public REST API and the constraints it places on us.

Again, we can address these with patches. Just like when any other 
project changes their database structure, they may have to change the 
REST APIs to line up as well. A slight pain, but not insurmountable.
>
>> 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.
>
> I agree that Nova's database schema (and other project's schemas) is 
> less than optimal in a number of places. I very much look forward to 
> your contributions to Nova to optimize our DB schema -- outside of any 
> NDB-specific things.

Thanks! I think over time, you'll see more of that.

>
>> 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.
>
> Octave, nobody is disagreeing with you on the fact that different DBs 
> have different storage restrictions. What we are trying to do is come 
> up with a solution to supporting NDB without:
>
> a) Making NDB-only/NDB-specific code modules in oslo.db
> b) Embedding tribal knowledge about NDB's internal data structure 
> restrictions in a format that only experts will be able to understand
>
> Nobody is against NDB. We're just trying to find an elegant solution 
> that is maintainable long-term.
>
> Mike's got a good start on the above-mentioned solution here:
>
> https://review.openstack.org/#/c/487902/
>
> I'll be reviewing it later on this evening. Hope to see your review on 
> it as well.

Thanks! I agree that making things easy is important. I do like the 
overrides as they are in-line and easy to understand, but I'll need to 
test it to make sure nothing breaks against the patches I have. So it'll 
take some work to test things out.
>
> 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


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack-dev/attachments/20170727/fbf4835e/attachment.html>