MySQL Bugs: #88774: Show data for index_length in information

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:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S4 (Feature request)
Version:	7.6.3	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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

Hello Daniël,

Thank you for the feature request.

Thanks,
Umesh

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
select
  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'
from
  (select
    if (doi.parent_obj_id = 0, doi.id, doi.parent_obj_id) base_table_id,
    doi.id object_id,
    doi.fq_name,
    doi.type,
    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'
  from
    ndbinfo.memory_per_fragment as mpf
    join ndbinfo.dict_obj_info doi
    on mpf.table_id = doi.id
  where
    doi.type in (1, 2, 3, 6)
  group by
    doi.id, doi.type, doi.fq_name, doi.parent_obj_id
  ) td
group by
  td.base_table_id
order by
  1,2;

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.