Bug #88774 Show data for index_length in information_schema for NDB tables
Submitted: 6 Dec 2017 8:30 Modified: 9 Feb 2018 9:15
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S4 (Feature request)
Version:7.6.3 OS:Any
Assigned to: CPU Architecture:Any

[6 Dec 2017 8:30] Daniël van Eeden
index_length in information_schema.tables is 0 for NDB tables.

However ndb_index_stat shows keyBytes:421832 etc.

Would be nice to get a meaningful number in information_schema.tables.

How to repeat:
See description
[9 Feb 2018 9:15] MySQL Verification Team
Hello Daniël,

Thank you for the feature request.

[2 Mar 2018 1:03] Mauritz Sundell
Posted by developer:
As a work around one can either run ndb_desc on table and all its index tables and blob tables, or select from ndbinfo.memory_per_fragment.

Below a view over ndbinfo.memory:

create view table_info as
  substring_index(max(if(td.base_table_id = td.object_id, td.fq_name, '')), '/', 1) 'TABLE_SCHEMA',
  substring_index(max(if(td.base_table_id = td.object_id, td.fq_name, '')), '/', -1) 'TABLE_NAME',
  sum(td.data_bytes) 'DATA_LENGTH_ALL_REPLICA',
  sum(td.index_bytes) 'INDEX_LENGTH_ALL_REPLICA'
    if (doi.parent_obj_id = 0, doi.id, doi.parent_obj_id) base_table_id,
    doi.id object_id,
    sum(if (doi.type in (1,2), fixed_elem_alloc_bytes - fixed_elem_free_bytes + hash_index_alloc_bytes + var_elem_alloc_bytes - var_elem_free_bytes, 0)) 'data_bytes',
    sum(if (doi.type in (3,6), fixed_elem_alloc_bytes - fixed_elem_free_bytes + hash_index_alloc_bytes + var_elem_alloc_bytes - var_elem_free_bytes, 0)) 'index_bytes'
    ndbinfo.memory_per_fragment as mpf
    join ndbinfo.dict_obj_info doi
    on mpf.table_id = doi.id
    doi.type in (1, 2, 3, 6)
  group by
    doi.id, doi.type, doi.fq_name, doi.parent_obj_id
  ) td
group by
order by

There are a few differences between the above result and that from information_schema.TABLES:

1) The totals including all replicas is included, so DATA_LENGTH_ALL_REPLICA from view is roughly twice as big as DATA_LENGTH from information_schema

2) All mandatory memory for row data is included in DATA_LENGTH_ALL_REPLICA including memory for the PRIMARY KEY hash index.  Memory for all other indexes are summed in INDEX_LENGTH_ALL_REPLICA.

3) In information_schema.TABLES memory for blobs are not included in DATA_LENGTH, while it is included in DATA_LENGTH_ALL_REPLICA in view above.

Values in ndbinfo.dict_obj_info.type indicates SystemTable(1), UserTable(2) which is also used by blob table, UniqueHashIndex(3), OrderedIndex(6).

Currently there is no information of disk data usage per table in ndbinfo, while one can get it in MAX_DATA_LENGTH in information_schema.TABLES or via ndb_desc.