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?
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:
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.
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.tablesGROUP 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) idxfracFROM information_schema.TABLESORDER 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,