How to Monitor the Size of the ibdata File

Document ID : KB000115371
Last Modified Date : 01/10/2018
Show Technical Document Details
In order to prevent processing disruptions, there are few ways to monitor the size of the ibdata file.
1. SNMP Trapping: We have a tactical assertion that can be used. We do have customers that leverage this assertion for monitoring. 
(This assertion is provided by Support. You will need to open a ticket.)

2. Write audit logs to an external DB: This will keep some of the tables in your database from getting large, keeping the ibdata file from getting large. 

3. Series of SQL statements that audit the database. To run a SQL statement, log into the database as root, and either: 

    A. place the sql statement on a mysql command: mysql ssg -e "SELECT count(*) FROM audit_main" 
    B. launch mysql session via the command: mysql ssg 

    This will display a mysql prompt where you can type the statements directly. The statements you can use are listed below. 

    The size of the DBs in the MySQL instance: 
    SELECT table_schema, sum((data_length+index_length)/1024/1024) AS MB FROM information_schema.tables GROUP BY 1; 

    The amount of data per table: 
    SELECT table_schema AS`Database`, table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES  ORDER BY (data_length + index_length) DESC; 

    The newest and oldest audit record:
    SELECT from_unixtime(min(time)/1000) as 'Oldest Record', from_unixtime(max(time)/1000) AS 'Yongest Record' FROM audit_main; 

    The number of audit records it contains: 
    SELECT count(*) FROM audit_main; 

    The date range of these records 
    SELECT x.From, x.To, x.NumberOfAudtis FROM ( SELECT time AS intervals, from_unixtime(time/1000) AS 'From', from_unixtime(time/1000 + 2629743) AS 'To', count(*) AS 'NumberOfAudtis' FROM audit_main GROUP BY intervals div 2629743000 )x;

4. Use UIM/Nimsoft: This option is only applicable if you have already purchased UIM. If you have not, disregard this.