3

I'm trying to get the space that a table using the MyRocks storage engine takes on disk and I'm getting inconsistent results between what I expect, a query on information_schema and the size as reported by the OS.

To get the size as reported by MariaDB, I'm using the following select statement:

select table_name 'Table Name',
      (data_length+index_length)/power(1024,3) 'Table Size in GB' 
from information_schema.tables where table_schema="MyTableName";

Which returns a really small number for what I'm doing, to the point where I'm doubtful that it's reporting the actual size (0.4GB for 4 000 000 rows with a lot of text).

If I run the command du -h /var/lib/mysql/#rocksdb/, I'm getting a disk size of 2.4GB, which is a bit more than what I would expect. However, if I understand correctly, MyRocks compacts data as data is inserted, so it's possible that the disk space reported by the du command is unrepresentative of the actual table size.

So, is there a reliable method to get the size of a table? Am I already using the correct way to get the table size?

Cydrick Trudel
  • 9,957
  • 8
  • 41
  • 63

1 Answers1

2

It looks like you need to use POWER(1024,3) instead of POWER(2014,3) assuming that storage engine is MyISAM:

select table_name AS `Table Name`,
      (data_length+index_length)/power(1024,3) AS `Table Size in GB` 
from information_schema.tables 
where table_schema='MyTableName';

And calculation:

(data_length+index_length)/(2014*2014*2014) = 0.4
=> 
(data_length+index_length) = 3267671497,6

3267671497,6/(1024*1024*1024) = 3.04GB

DATA_LENGTH

For MyISAM, DATA_LENGTH is the length of the data file, in bytes.

For InnoDB, DATA_LENGTH is the approximate amount of memory allocated for the clustered index, in bytes. Specifically, it is the clustered index size, in pages, multiplied by the InnoDB page size.)

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • 1
    Hi Lukasz, thanks for your reply. Unfortunately, this was a typo when I wrote the question. I was actually using 1024. Sorry for wasting your time. – Cydrick Trudel Oct 14 '18 at 11:44
  • @CydrickT You could also try `MAX_DATA_LENGTH` instead `DATA_LENGTH` – Lukasz Szozda Oct 14 '18 at 11:47
  • In what way would I be using this variable? Also note that the storage engine is MyRocks, which is relatively new. – Cydrick Trudel Oct 14 '18 at 11:49
  • 1
    I would not expect Max... to be relevant. 3x is a typical shrinkage factor for text; you are getting 6x. InnoDB tends to be 2x-3x bigger on disk than MyISAM; I don't know about MyRocks. – Rick James Oct 14 '18 at 15:56
  • Just tried it with `max_data_length` and the result is even smaller than `data_length`, so it does not make more sense to me. – Cydrick Trudel Oct 15 '18 at 11:00