Space allocated to ssg database tables

[Parag.dabhade]

How to check the maximum allocated size of tables in ssg database in mysql? Also, how to check the table space details for all the tables in ssg database?

[Seiji Moriyama]

Hello,

 

All tables are stored in the /var/lib/mysql/ibdata file of MySQL on the API Gateway virtual appliance and its maximum size is defined by the innodb_data_file_path parameter in /etc/my.cnf like this:

 

innodb_data_file_path=ibdata:100M:autoextend:max:15851M

 

It means the ibdata file can grow to a limit of 15851MB.

 

According to the following knowledge article, Gateway 9.3 is misconfigured by default and the ibdata file is split up for each table. (https://comm.support.ca.com/kb/how-to-disable-innodb-file-per-table-in-mysql-after-9-3-00-gateway-upgrade/kb000071696)

 

If you are using 9.3, it seems it is better to follow this knowledge article.

 

Best regards,
Seiji

[Legacy User]

Adding to what Seiji said, one can run the below command after connecting to mysql:

 

SELECT table_schema "DB Name",
SUM(data_length + index_length) / 1024 / 1024 "DataBase size in MB",
SUM(data_free) / 1024 / 1024 "Free space in DataBase"
FROM information_schema.tables
GROUP BY table_schema DESC;

 

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;

 

The first one will output the Databases and their size.

The second one would list the Tables by size.

If one chose the database before running the second SQL, only the tables from that DB will be shown.

 

Hope this helps,

 

Kind Regards,

 

Amit.