Common_schema: Data size by engine and by schema

common schema

In this post I’ll show you two interesting utilities from common_schema framework: calculete the size by engine (MyIsam, InnoDB, Memory, …), and the size of each schema.

Just type this:
SELECT * FROM common_schema.data_size_per_engine;

SELECT * FROM common_schema.data_size_per_schema;

In my opinion, it could be more “teadable” for the user if the number would be in Mb, instead of bytes, but it’s my personal opinion.

data_size_per_engine

data_size_per_engine

data_size_per_schema

data_size_per_schema

An alternative query to calculate the size for each schema is:
SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024) AS total_mb,
ROUND(SUM(data_length)/1024/1024) AS data_mb,
ROUND(SUM(index_length)/1024/1024) AS index_mb
FROM INFORMATION_SCHEMA.tables
GROUP BY table_schema ;

Alternative data_size_per_schema2

Alternative data_size_per_schema2

If you consider this query as an interesting one, you should think seriously in creating your own database to collect all these querys and functions that allows you to manage your MariaDB or MySQL server, and to know the state of the server.

I hope it can be useful for you, and … have a nice day!