CODEWITHSUNDEEP
springhibernatespring-bootspring-dataspring-data-jpa
ytterrr
ytterrr

Reputation: 3246

Size of table in Spring

Is there any simple way to estimate size of given table in bytes using Spring + Hibernate? Previously with JDBC + MySQL I've used scripts like this one:

SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  10;

Upvotes: 0

Views: 2113

Answers (1)

manish
manish

Reputation: 20135

This should be doable in a couple of ways depending on whether you need just one piece of information or all of the information from the sample script you have linked to. Getting a single piece of information is easy:

@Query(value = "SELECT ROUND(table_rows / 1000000, 2) FROM information_schema.TABLES WHERE table_name=?1", nativeQuery = true)
Long findTableRows(String tableName);

If you need access to all of that information in the linked script, you can do the following:

Step 1. Create an entity class to hold that information:

@Entity
@Table(name = "table_stats")
public class TableStats {
  @Column
  private String tableName;
  @Column
  private Long rows;
  @Column
  private Double data;
  @Column
  private Double size;
  @Column
  private Double indexFraction;
}

Step 2: (Optionally) Create a database view or table with the name table_stats and populate it with data using the linked script. If a table is created (which will give better runtime performance), create associated scheduled jobs to refresh the table periodically, at sensible intervals. If a view is created, the data retrieved will always be current (although performance will be slow).

Step 3: Create a repository for TableStats.

Step 4a: If the view or table was created on step 2 above, put a regular method TableInfo findByTableName(String name) on the repository.

Step 4b: If the view or table was not created on step 2 above, put a native query method as shown before on the repository.

The only challenge with using a native query is that the solution will only work on MySQL and not with any other database. So, if you use different databases, say, between development, integration, test, staging and production, this approach will only work on environment that use MySQL and will fail on all others. The other approach of creating a view (provided the underlying database supports creation of views) or table will work on all databases (subject to periodic refreshes of the information).

Upvotes: 4

Related Questions