0

I have innodb_buffer_pool_size=100GB;

show table status where name='test_tb'\G                                                                          

gives

    Data_length: 4686086144
Max_data_length: 0
   Index_length: 5107564544

seems index uses 5GB memory.

ANALYZE table test_tb;
SELECT
sum(stat_value) pages,
  index_name,
  sum(stat_value) * @@innodb_page_size /1024/1024 "size(MB)"
  FROM
  mysql.innodb_index_stats
  WHERE
  table_name = 'test_tb'
  AND database_name = 'test_dbs'
  AND stat_description = 'Number of pages in the index'
  GROUP BY
  index_name;

+--------+-------------------+---------------+
| pages  | index_name        | size(MB)      |
+--------+-------------------+---------------+
| 286016 | PRIMARY           | 4469.00000000 |
| 150014 | test_key1         | 2343.96875000 |
| 161727 | test_key2         | 2526.98437500 |
+--------+-------------------+---------------+

its 8GB.

So which one is accurate?

1
  • The size of the "Primary"-index equals your data_length, the other 2 indexes will sum up to index_length. InnoDB stores all data in btree-structures (indexes).
    – Solarflare
    Commented Aug 24, 2017 at 10:34

1 Answer 1

0

Both are correct; both say the same values. (Don't divide by 1024*1024; it will be a little more obvious.)

In InnoDB, the PRIMARY KEY is "clustered" with the data. That is, they are both in the same BTree structure. The PK provides the order; the data provides the content.

In your table, the data is ~4GB, including the PK and BTree overhead; the two indexes are in two separate BTrees, adding up to about 5GB.

2
  • So the memory allocation in innodb_buffer_pool_size of this table is roughly 9GB? What' in separate index btree? for example, the test_key1 index bree. I suppose the data of the columns of test_key1, and pointer to PK?
    – Sato
    Commented Aug 25, 2017 at 0:37
  • Up to 9GB of the buffer_pool will be used for these 3 BTrees. It is a "cache", allocated 16KB at a time, so it may or may not every actually consume 9GB. The leaf nodes of a secondary index contain the PK values.
    – Rick James
    Commented Aug 25, 2017 at 4:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.