How to Monitor the Size of the ibdata File
Article ID: 115371
Updated On:
Products
STARTER PACK-7
CA Rapid App Security
CA API Gateway
Issue/Introduction
In order to prevent processing disruptions, there are few ways to monitor the size of the ibdata file.
Environment
Release:
Component: APIESM
Component: APIESM
Resolution
1. SNMP Monitoring: Out of the box the API Gateway has o/s SNMP installed as well as NET-SNMP. The MIB2 and HR mibs are available. The SNMP Polling will not work from a remote source since the API Gateway has SNMP bound by default to 127.0.0.1 (loopback only) in order to bind this to listen to all, please follow KB:
https://comm.support.ca.com/kb/Enabling-SNMP-Monitoring-in-the-CA-API-Gateway/KB000010937
This will allow a SNMP Manager For example: Spectrum, UIM, Nimsoft, or other 3rd party to Poll the SNMP statistics of the Gateway host.
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. Create a policy to monitor the ssg/ping page. You can use a HTTP Route to get the data from the inbuilt /ssg/ping page and regular expressions to extrapolate the required data you want monitored. You can use compare expressions and any inbuilt gateway function to email/snmp trap etc... And you can use a Gateway Scheduled Task if available in you gateway version to automate running of this Monitoring policy.
https://comm.support.ca.com/kb/Enabling-SNMP-Monitoring-in-the-CA-API-Gateway/KB000010937
This will allow a SNMP Manager For example: Spectrum, UIM, Nimsoft, or other 3rd party to Poll the SNMP statistics of the Gateway host.
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. Create a policy to monitor the ssg/ping page. You can use a HTTP Route to get the data from the inbuilt /ssg/ping page and regular expressions to extrapolate the required data you want monitored. You can use compare expressions and any inbuilt gateway function to email/snmp trap etc... And you can use a Gateway Scheduled Task if available in you gateway version to automate running of this Monitoring policy.
Feedback
Yes
No
Powered by
