MariaDB stores large object (LOB) data (e.g. varchar(max), text, image columns) in the so-called separate allocation units.

The query below can help you find how much space is allocated for LOB data.

Query

select case when col.data_type in ('blob', 'mediumblob', 'longblob',
            'text', 'mediumtext', 'longtext') then 'LOB data'
        else 'Regular data' end as allocation_type,
    round(sum(data_length + index_length) /
        power(1024, 2), 2) as used_mb,
    round(sum(data_length + index_length + data_free) /
        power(1024, 2), 2) as allocated_mb
from information_schema.tables as tab
    inner join information_schema.columns as col 
        on col.table_schema = tab.table_schema
            and col.table_name = tab.table_name
where tab.table_schema = 'your database name'
    and tab.table_type = 'BASE TABLE'
group by 1
order by 1;

Columns

• allocation_type - type of space allcation:
  • LOB data - space allocated for LOB data columns
  • Regular data - space allocated for other columns
• used_mb - space used in MB
• allocated_mb - space allocated in MB

Rows

• One row: represents one type of allocation: LOB/regular
• Scope of rows: query returns two rows

Sample results

Space allocation for LOB and other data types in the Sakila database (schema):