<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi Jay,<br>
<br>
Comments below..<br>
<br>
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.
<br>
<br>
Thanks,<br>
Octave<br>
<br>
<div class="moz-cite-prefix">On 7/27/2017 11:49 AM, Jay Pipes wrote:<br>
</div>
<blockquote
cite="mid:ee0bd5d4-0e92-d2dd-4fb0-b3c2b497dba9@gmail.com"
type="cite">I guess we're really getting into the weeds here.
<br>
<br>
On 07/27/2017 12:51 PM, Octave J. Orgeron wrote:
<br>
<blockquote type="cite">Hi Jay,
<br>
<br>
Comments below..
<br>
<br>
On 7/26/2017 5:43 PM, Jay Pipes wrote:
<br>
<blockquote type="cite">On 07/26/2017 07:06 PM, Octave J.
Orgeron wrote:
<br>
<blockquote type="cite">Hi Michael,
<br>
<br>
On 7/26/2017 4:28 PM, Michael Bayer wrote:
<br>
<blockquote type="cite">
<br>
it at all.
<br>
thinking out loud
<br>
<br>
oslo_db.sqlalchemy.types.String(255,
mysql_small_rowsize=64)
<br>
oslo_db.sqlalchemy.types.String(255,
mysql_small_rowsize=sa.TINYTEXT)
<br>
oslo_db.sqlalchemy.types.String(255,
mysql_small_rowsize=sa.TEXT)
<br>
<br>
<br>
so if you don't have mysql_small_rowsize, nothing
happens.
<br>
<br>
</blockquote>
<br>
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:
<br>
<br>
mysql_alt_size=64
<br>
mysql_alt_type=sa.TINYTEXT
<br>
mysql_alt_type=sa.TEXT
<br>
<br>
alt standing for alternate. What do you think?
<br>
</blockquote>
<br>
-1
<br>
<br>
I think it should be specific to NDB, since that's what the
override is for. I'd support something like:
<br>
<br>
oslo_db.sqlalchemy.types.String(255, mysql_ndb_size=64)
<br>
<br>
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.
<br>
</blockquote>
<br>
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.
<br>
</blockquote>
<br>
Yes, I'm aware of those constraints, though you are incorrect
about InnoDB.
<br>
<br>
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.
<br>
</blockquote>
<br>
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:<br>
<br>
<a class="moz-txt-link-freetext" href="https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html#row-size-limits">https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html#row-size-limits</a><br>
<br>
<blockquote
cite="mid:ee0bd5d4-0e92-d2dd-4fb0-b3c2b497dba9@gmail.com"
type="cite">
<br>
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]
<br>
<br>
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.
<br>
</blockquote>
<br>
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.<br>
<br>
<blockquote
cite="mid:ee0bd5d4-0e92-d2dd-4fb0-b3c2b497dba9@gmail.com"
type="cite">
<br>
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.
<br>
<br>
This is the big difference between the two systems and why you are
changing some columns to the TEXT type.
<br>
<br>
[1] mysql> use test
<br>
Database changed
<br>
mysql> show variables like 'innodb_page_size';
<br>
+------------------+-------+
<br>
| Variable_name | Value |
<br>
+------------------+-------+
<br>
| innodb_page_size | 16384 |
<br>
+------------------+-------+
<br>
1 row in set (0.02 sec)
<br>
<br>
mysql> create table t1 (a VARCHAR(9000));
<br>
Query OK, 0 rows affected (0.02 sec)
<br>
</blockquote>
<br>
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:<br>
<br>
<a class="moz-txt-link-freetext" href="https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html#row-size-limits">https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html#row-size-limits</a><br>
<br>
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),<br>
c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),<br>
f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER
SET latin1;<br>
ERROR 1118 (42000): Row size too large. The maximum row size for the
used <br>
table type, not counting BLOBs, is 65535. This includes storage
overhead, <br>
check the manual. <b>You have to change some columns to TEXT or
BLOBs</b><br>
<br>
You have to love the error message above, because it tells you how
to fix this problem :)<br>
Here is the example from the documentation on how to fix this by
using TEXT:<br>
<br>
mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),<br>
c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),<br>
f VARCHAR(10000), g <b>TEXT(6000)</b>) ENGINE=InnoDB
CHARACTER SET latin1;<br>
Query OK, 0 rows affected (0.02 sec)<br>
<br>
So regardless of the engine being used, you have to be mindful of
the length of the rows in your tables :)<br>
<br>
<br>
<blockquote
cite="mid:ee0bd5d4-0e92-d2dd-4fb0-b3c2b497dba9@gmail.com"
type="cite">
<br>
[2] This is the reason NDB isn't listed under "Alternative Storage
Engines" in the MySQL documentation...
<br>
</blockquote>
<br>
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.<br>
<br>
<blockquote
cite="mid:ee0bd5d4-0e92-d2dd-4fb0-b3c2b497dba9@gmail.com"
type="cite">
<br>
<blockquote type="cite">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.
<br>
</blockquote>
<br>
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.
<br>
</blockquote>
<br>
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.<br>
<br>
<blockquote
cite="mid:ee0bd5d4-0e92-d2dd-4fb0-b3c2b497dba9@gmail.com"
type="cite">
<br>
<blockquote type="cite">All database engines handle BLOBs
differently than other types and as
<br>
a result they reduce the count against the row length. That's
why I
<br>
change some of these columns to TEXT types.
<br>
</blockquote>
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.
<br>
</blockquote>
<br>
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.<br>
<br>
<blockquote
cite="mid:ee0bd5d4-0e92-d2dd-4fb0-b3c2b497dba9@gmail.com"
type="cite">
<br>
<blockquote type="cite">If you look closely through services like
Neutron, Barbican,
<br>
Designate, Keystone, etc. you'll see that they have hit the 65k
limit
<br>
in InnoDB on some tables and have had to do the same thing.
<br>
Realistically, any time you are storing something like SSH keys,
SSL
<br>
certs, output from commands, etc. you should be using the TEXT
types
<br>
anyways.
<br>
</blockquote>
<br>
No disagreement from me at all here. However, see above point
about the public REST API and the constraints it places on us.<br>
</blockquote>
<br>
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.<br>
<blockquote
cite="mid:ee0bd5d4-0e92-d2dd-4fb0-b3c2b497dba9@gmail.com"
type="cite">
<br>
<blockquote type="cite">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.
<br>
</blockquote>
<br>
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.
<br>
</blockquote>
<br>
Thanks! I think over time, you'll see more of that. <br>
<br>
<blockquote
cite="mid:ee0bd5d4-0e92-d2dd-4fb0-b3c2b497dba9@gmail.com"
type="cite">
<br>
<blockquote type="cite">Each database has structure limits:
<br>
<br>
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/about/">https://www.postgresql.org/about/</a>
<br>
<a class="moz-txt-link-freetext" href="https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html">https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html</a>
<br>
<a class="moz-txt-link-freetext" href="https://dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-limitations.html">https://dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-limitations.html</a>
<br>
<a class="moz-txt-link-freetext" href="https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001029.html">https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001029.html</a>
<br>
<a class="moz-txt-link-freetext" href="https://docs.oracle.com/cloud/latest/db112/REFRN/limits003.htm#REFRN0043">https://docs.oracle.com/cloud/latest/db112/REFRN/limits003.htm#REFRN0043</a>
<br>
<br>
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.
<br>
</blockquote>
<br>
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:
<br>
<br>
a) Making NDB-only/NDB-specific code modules in oslo.db
<br>
b) Embedding tribal knowledge about NDB's internal data structure
restrictions in a format that only experts will be able to
understand
<br>
<br>
Nobody is against NDB. We're just trying to find an elegant
solution that is maintainable long-term.
<br>
<br>
Mike's got a good start on the above-mentioned solution here:
<br>
<br>
<a class="moz-txt-link-freetext" href="https://review.openstack.org/#/c/487902/">https://review.openstack.org/#/c/487902/</a>
<br>
<br>
I'll be reviewing it later on this evening. Hope to see your
review on it as well.
<br>
</blockquote>
<br>
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.<br>
<blockquote
cite="mid:ee0bd5d4-0e92-d2dd-4fb0-b3c2b497dba9@gmail.com"
type="cite">
<br>
Best,
<br>
-jay
<br>
<br>
__________________________________________________________________________
<br>
OpenStack Development Mailing List (not for usage questions)
<br>
Unsubscribe:
<a class="moz-txt-link-abbreviated" href="mailto:OpenStack-dev-request@lists.openstack.org?subject:unsubscribe">OpenStack-dev-request@lists.openstack.org?subject:unsubscribe</a>
<br>
<a class="moz-txt-link-freetext" href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev">http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev</a>
<br>
</blockquote>
<br>
<div class="moz-signature"><br>
<font color="#666666" size="2" face="Verdana, Arial, Helvetica,
sans-serif"><font color="#666666" size="2" face="Verdana, Arial,
Helvetica, sans-serif"></font></font></div>
</body>
</html>