MySQL Forums
Forum List  »  NDB clusters

Re: Cannot Create more than 6 or 8 tables
Posted by: Mauritz Sundell
Date: March 09, 2021 10:17AM

Hi Andrew,

First, for disk tables variable length columns always take up space for the maximum sized value.

In this case the VARCHAR(1000) columns will all take roughly 3000 bytes if utf8 (utf8mb3) is in use, or roughly 4000 bytes if utf8mb4 is in use.

Second, we have the limitations that all fixed fields on a row must start within the first 8188 bytes.

After the URL_B64S column the offset will be over beyond 8188 bytes, if utf8mb4 is in use offset may be too high already after HTML column.

For more details see:
https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-disk-data-storage-requirements.html
https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-limitations-database-objects.html

Some suggestions as work arounds:

1) Guessing that B64 stands for base64 encoding those columns could be made to use ascii to make them smaller, and moving them before the HTML column may work.

URL_B64S_Length INT NULL,
URL_B64S VARCHAR(1000) CHARSET ASCII NULL,
HTML_B64S_Length INT NULL,
HTML_B64S VARCHAR(1000) CHARSET ASCII NULL ,
...
HTML VARCHAR(1000) NULL)

And if the URL is not actually stored using UTF-8 but using IDN-encoding that column could also be made ASCII.

2) Make some VARCHAR(1000) columns smaller, note the last column may keep the size since it is only the columns offset that is limited to 8188, else the full row can be 30000 bytes.

3) Use in memory tables, there will the varchar columns count as dynamic and column offset will not be limited by 8188 bytes. Memory tables are as durable as disk tables, but if you need disk tables to increase storage capacity beyond what can be stored in memory that is not an option.


Hope that helps,
Mauritz

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Cannot Create more than 6 or 8 tables
141
March 09, 2021 10:17AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.