Layer7 API Management

Expand all | Collapse all

Space allocated to ssg database tables

Jump to Best Answer
  • 1.  Space allocated to ssg database tables

    Posted 08-29-2018 09:31 AM

    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?



  • 2.  Re: Space allocated to ssg database tables
    Best Answer

    Posted 08-30-2018 10:32 PM

    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



  • 3.  Re: Space allocated to ssg database tables

    Posted 08-31-2018 03:00 AM

    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.