How do I find the size of a Database?

Ryan Lecha -

In ClustrixDB the sizes of all tables is contained in the system.table_sizes table.

mysql> desc system.table_sizes;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| Database | varchar(256) | NO   |     | NULL    |       |
| Table    | varchar(256) | NO   |     | NULL    |       |
| Bytes    | double       | NO   |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

To get the size of a single database use this query

SELECT Database, SUM(bytes)/1024/1024/1204 AS 'Size in GB' FROM system.table_sizes WHERE `Database`='<db_name>';

Similarly, you can get the size of a table with the following query:

SELECT `Table`, SUM(bytes) /1024/1024/1024 as 'Size in GB' FROM system.table_sizes WHERE `Database`='<db_name>' AND `Table`='<table_name>';

When calculating database and table sizes for Clustrix Fast Backup and Restore you should divide the above values by the number of replicas (usually 2), as the backup does not include replicas.

If you're looking at table sizes for re-slicing operations it can be helpful to know the size of the base representation and the indexes separately. You can use a query like the following to obtain this information:

select `Database`,`Table`,`Index`,Bytes/1024/1024/1024 as size_gb from system.index_sizes where `database` = '<db_name>' and `Table`='<table_name>';
Have more questions? Submit a request

4 Comments

  • 0
    Avatar
    Ivan Di Giusto

    I also found the following query extremely useful to figure out what is happening with my tables.  Lists a size of all tables in all databases, but hides all clustrix databases (don't call your databases clustrix) and retiring tables.

    SELECT Database, Table, SUM(Size)/1024/1024 AS MB FROM system.table_sizes WHERE Table NOT LIKE '%retiring%' AND Database NOT LIKE '%clustrix%' GROUP BY Database, Table ORDER BY Database, Table;

  • 0
    Avatar
    Scott Sullivan

    Ivan, that's a good suggestion, but you should also exclude the 'system' database as that is also a Clustrix construct.

  • 0
    Avatar
    Ivan Di Giusto

    Good point, Scott.

    Here is an updated query (wish I could get phpmyadmin to use it!):

    SELECT Database, Table, SUM(Size)/1024/1024 AS MB FROM system.table_sizes WHERE Table NOT LIKE '%retiring%' AND Database NOT LIKE '%clustrix%' AND Database != 'system' GROUP BY Database, Table ORDER BY Database, Table;

  • 0
    Avatar
    Todd Trimmer

    How does this compare to InnoDB's information_schema tables.data_length and tables.index_length? Is there a way to query Clustrix to see how big just a table's indexes are?

    Is system table_sizes.Bytes equivalent to only data_length?

    Edited by Todd Trimmer
Please sign in to leave a comment.
Powered by Zendesk