Movatterモバイル変換

ndb_desc provides a detailed description of one or moreNDB tables.

Usage

ndb_desc -cconnection_stringtbl_name -ddb_name [options]ndb_desc -cconnection_stringindex_name -ddb_name -ttbl_name

Additional options that can be used withndb_desc are listed later in this section.

Sample Output

MySQL table creation and population statements:

USE test;CREATE TABLE fish (    id INT NOT NULL AUTO_INCREMENT,    name VARCHAR(20) NOT NULL,    length_mm INT NOT NULL,    weight_gm INT NOT NULL,    PRIMARY KEY pk (id),    UNIQUE KEY uk (name)) ENGINE=NDB;INSERT INTO fish VALUES    (NULL, 'guppy', 35, 2), (NULL, 'tuna', 2500, 150000),    (NULL, 'shark', 3000, 110000), (NULL, 'manta ray', 1500, 50000),    (NULL, 'grouper', 900, 125000), (NULL ,'puffer', 250, 2500);

Output fromndb_desc:

$> ./ndb_desc -c localhost fish -d test -p-- fish --Version: 2Fragment type: HashMapPartitionK Value: 6Min load factor: 78Max load factor: 80Temporary table: noNumber of attributes: 4Number of primary keys: 1Length of frm data: 337Max Rows: 0Row Checksum: 1Row GCI: 1SingleUserMode: 0ForceVarPart: 1PartitionCount: 2FragmentCount: 2PartitionBalance: FOR_RP_BY_LDMExtraRowGciBits: 0ExtraRowAuthorBits: 0TableStatus: RetrievedTable options:HashMap: DEFAULT-HASHMAP-3840-2-- Attributes --id Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCRname Varchar(20;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY DYNAMIClength_mm Int NOT NULL AT=FIXED ST=MEMORY DYNAMICweight_gm Int NOT NULL AT=FIXED ST=MEMORY DYNAMIC-- Indexes --PRIMARY KEY(id) - UniqueHashIndexPRIMARY(id) - OrderedIndexuk(name) - OrderedIndexuk$unique(name) - UniqueHashIndex-- Per partition info --Partition       Row count       Commit count    Frag fixed memory       Frag varsized memory    Extent_space    Free extent_space0               2               2               32768                   32768                   0               01               4               4               32768                   32768                   0               0

Information about multiple tables can be obtained in a single invocation ofndb_desc by using their names, separated by spaces. All of the tables must be in the same database.

You can obtain additional information about a specific index using the--table (short form:-t) option and supplying the name of the index as the first argument tondb_desc, as shown here:

$> ./ndb_desc uk -d test -t fish-- uk --Version: 2Base table: fishNumber of attributes: 1Logging: 0Index type: OrderedIndexIndex status: Retrieved-- Attributes --name Varchar(20;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY-- IndexTable 10/uk --Version: 2Fragment type: FragUndefinedK Value: 6Min load factor: 78Max load factor: 80Temporary table: yesNumber of attributes: 2Number of primary keys: 1Length of frm data: 0Max Rows: 0Row Checksum: 1Row GCI: 1SingleUserMode: 2ForceVarPart: 0PartitionCount: 2FragmentCount: 2FragmentCountType: ONE_PER_LDM_PER_NODEExtraRowGciBits: 0ExtraRowAuthorBits: 0TableStatus: RetrievedTable options:-- Attributes --name Varchar(20;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORYNDB$TNODE Unsigned [64] PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY-- Indexes --PRIMARY KEY(NDB$TNODE) - UniqueHashIndex

When an index is specified in this way, the--extra-partition-info and--extra-node-info options have no effect.

TheVersion column in the output contains the table's schema object version. For information about interpreting this value, seeNDB Schema Object Versions.

Three of the table properties that can be set usingNDB_TABLE comments embedded inCREATE TABLE andALTER TABLE statements are also visible inndb_desc output. The table'sFRAGMENT_COUNT_TYPE is always shown in theFragmentCountType column.READ_ONLY andFULLY_REPLICATED, if set to 1, are shown in theTable options column. You can see this after executing the followingALTER TABLE statement in themysql client:

mysql> ALTER TABLE fish COMMENT='NDB_TABLE=READ_ONLY=1,FULLY_REPLICATED=1';1 row in set, 1 warning (0.00 sec)mysql> SHOW WARNINGS\G+---------+------+---------------------------------------------------------------------------------------------------------+| Level   | Code | Message                                                                                                 |+---------+------+---------------------------------------------------------------------------------------------------------+| Warning | 1296 | Got error 4503 'Table property is FRAGMENT_COUNT_TYPE=ONE_PER_LDM_PER_NODE but not in comment' from NDB |+---------+------+---------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

The warning is issued becauseREAD_ONLY=1 requires that the table's fragment count type is (or be set to)ONE_PER_LDM_PER_NODE_GROUP;NDB sets this automatically in such cases. You can check that theALTER TABLE statement has the desired effect usingSHOW CREATE TABLE:

mysql> SHOW CREATE TABLE fish\G*************************** 1. row ***************************       Table: fishCreate Table: CREATE TABLE `fish` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(20) NOT NULL,  `length_mm` int(11) NOT NULL,  `weight_gm` int(11) NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `uk` (`name`)) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciCOMMENT='NDB_TABLE=READ_BACKUP=1,FULLY_REPLICATED=1'1 row in set (0.01 sec)

BecauseFRAGMENT_COUNT_TYPE was not set explicitly, its value is not shown in the comment text printed bySHOW CREATE TABLE.ndb_desc, however, displays the updated value for this attribute. TheTable options column shows the binary properties just enabled. You can see this in the output shown here (emphasized text):

$> ./ndb_desc -c localhost fish -d test -p-- fish --Version: 4Fragment type: HashMapPartitionK Value: 6Min load factor: 78Max load factor: 80Temporary table: noNumber of attributes: 4Number of primary keys: 1Length of frm data: 380Max Rows: 0Row Checksum: 1Row GCI: 1SingleUserMode: 0ForceVarPart: 1PartitionCount: 1FragmentCount: 1FragmentCountType: ONE_PER_LDM_PER_NODE_GROUPExtraRowGciBits: 0ExtraRowAuthorBits: 0TableStatus: RetrievedTable options: readbackup, fullyreplicatedHashMap: DEFAULT-HASHMAP-3840-1-- Attributes --id Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCRname Varchar(20;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY DYNAMIClength_mm Int NOT NULL AT=FIXED ST=MEMORY DYNAMICweight_gm Int NOT NULL AT=FIXED ST=MEMORY DYNAMIC-- Indexes --PRIMARY KEY(id) - UniqueHashIndexPRIMARY(id) - OrderedIndexuk(name) - OrderedIndexuk$unique(name) - UniqueHashIndex-- Per partition info --Partition       Row count       Commit count    Frag fixed memory       Frag varsized memory    Extent_space    Free extent_space

For more information about these table properties, seeSection 15.1.20.12, “Setting NDB Comment Options”.

TheExtent_space andFree extent_space columns are applicable only toNDB tables having columns on disk; for tables having only in-memory columns, these columns always contain the value0.

To illustrate their use, we modify the previous example. First, we must create the necessary Disk Data objects, as shown here:

CREATE LOGFILE GROUP lg_1    ADD UNDOFILE 'undo_1.log'    INITIAL_SIZE 16M    UNDO_BUFFER_SIZE 2M    ENGINE NDB;ALTER LOGFILE GROUP lg_1    ADD UNDOFILE 'undo_2.log'    INITIAL_SIZE 12M    ENGINE NDB;CREATE TABLESPACE ts_1    ADD DATAFILE 'data_1.dat'    USE LOGFILE GROUP lg_1    INITIAL_SIZE 32M    ENGINE NDB;ALTER TABLESPACE ts_1    ADD DATAFILE 'data_2.dat'    INITIAL_SIZE 48M    ENGINE NDB;

(For more information on the statements just shown and the objects created by them, seeSection 25.6.11.1, “NDB Cluster Disk Data Objects”, as well asSection 15.1.16, “CREATE LOGFILE GROUP Statement”, andSection 15.1.21, “CREATE TABLESPACE Statement”.)

Now we can create and populate a version of thefish table that stores 2 of its columns on disk (deleting the previous version of the table first, if it already exists):

DROP TABLE IF EXISTS fish;CREATE TABLE fish (    id INT NOT NULL AUTO_INCREMENT,    name VARCHAR(20) NOT NULL,    length_mm INT NOT NULL,    weight_gm INT NOT NULL,    PRIMARY KEY pk (id),    UNIQUE KEY uk (name)) TABLESPACE ts_1 STORAGE DISKENGINE=NDB;INSERT INTO fish VALUES    (NULL, 'guppy', 35, 2), (NULL, 'tuna', 2500, 150000),    (NULL, 'shark', 3000, 110000), (NULL, 'manta ray', 1500, 50000),    (NULL, 'grouper', 900, 125000), (NULL ,'puffer', 250, 2500);

When run against this version of the table,ndb_desc displays the following output:

$> ./ndb_desc -c localhost fish -d test -p-- fish --Version: 1Fragment type: HashMapPartitionK Value: 6Min load factor: 78Max load factor: 80Temporary table: noNumber of attributes: 4Number of primary keys: 1Length of frm data: 1001Max Rows: 0Row Checksum: 1Row GCI: 1SingleUserMode: 0ForceVarPart: 1PartitionCount: 2FragmentCount: 2PartitionBalance: FOR_RP_BY_LDMExtraRowGciBits: 0ExtraRowAuthorBits: 0TableStatus: RetrievedTable options: readbackupHashMap: DEFAULT-HASHMAP-3840-2Tablespace id: 16Tablespace: ts_1-- Attributes --id Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCRname Varchar(80;utf8mb4_0900_ai_ci) NOT NULL AT=SHORT_VAR ST=MEMORYlength_mm Int NOT NULL AT=FIXED ST=DISKweight_gm Int NOT NULL AT=FIXED ST=DISK-- Indexes --PRIMARY KEY(id) - UniqueHashIndexPRIMARY(id) - OrderedIndexuk(name) - OrderedIndexuk$unique(name) - UniqueHashIndex-- Per partition info --Partition       Row count       Commit count    Frag fixed memory       Frag varsized memory    Extent_space    Free extent_space0               2               2               32768                   32768                   1048576         10444401               4               4               32768                   32768                   1048576         1044400

This means that 1048576 bytes are allocated from the tablespace for this table on each partition, of which 1044440 bytes remain free for additional storage. In other words, 1048576 - 1044440 = 4136 bytes per partition is currently being used to store the data from this table's disk-based columns. The number of bytes shown asFree extent_space is available for storing on-disk column data from thefish table only; for this reason, it is not visible when selecting from the Information SchemaFILES table.

Tablespace id andTablespace are also displayed for Disk Data tables.

For fully replicated tables,ndb_desc shows only the nodes holding primary partition fragment replicas; nodes with copy fragment replicas (only) are ignored. You can obtain such information, using themysql client, from thetable_distribution_status,table_fragments,table_info, andtable_replicas tables in thendbinfo database.

All options that can be used withndb_desc are shown in the following table. Additional descriptions follow the table.

Table indexes listed in the output are ordered by ID.