OnCommand Insight DWH database is growing exponentially
Applies to
OnCommand Insight DataWare house (OCI DWH) 7.3.11
Answer
Concerns:
- Is OCI DWH, run any maintenance jobs to trim the database automatically?
- Yes, please see "Maintenance Jobs Configuration in OCI DWH server"
- Yes, please see "Maintenance Jobs Configuration in OCI DWH server"
- How to find out if OCI DWH database is growing exponentially?
- If "Step 1" above is configured correctly and running on time then we do not need to be concerned about the database and its size.
- In case we are curious about the OCI DWH database and its tables sizes. Here are the two commands that can run against the OCI DWH MySQL database.
- You need a MySQL "root" user password to login, If you do not know the password please contact "NetApp Support" for assistance.
- OCI DWH Databases Size
SELECT table_schema as "Database Name", SUM(ROUND((data_length + index_length)/1024/1024,2)) "Database Size in GiB" FROM information_schema.TABLES WHERE table_schema IN ("dwh_capacity","dwh_capacity_efficiency","dwh_capacity_staging","dwh_cloud_cost","dwh_cloud_cost_staging","dwh_custom","dwh_dimensions","dwh_fs_util","dwh_inventory_staging","dwh_inventory_transient","dwh_management","dwh_performance","dwh_performance_staging","dwh_ports","dwh_reports","mysql","host_data","management","sanscreen","scrub","serviceassurance","staging","workbench")
GROUP BY table_schema;
- OCI DWH Table Names and Table Rows
SELECT table_schema as "DATABASE", table_name, table_rows FROM information_schema.TABLES WHERE table_schema IN ("dwh_capacity","dwh_capacity_efficiency","dwh_capacity_staging","dwh_cloud_cost","dwh_cloud_cost_staging","dwh_custom","dwh_dimensions","dwh_fs_util","dwh_inventory_staging","dwh_inventory_transient","dwh_management","dwh_performance","dwh_performance_staging","dwh_ports","dwh_reports","mysql","host_data","management","sanscreen","scrub","serviceassurance","staging","workbench") AND table_rows > 100000 ORDER BY table_rows DESC;
- You need a MySQL "root" user password to login, If you do not know the password please contact "NetApp Support" for assistance.
- If "Step 1" above is configured correctly and running on time then we do not need to be concerned about the database and its size.
- How to manually trim OCI DWH database manually.
CAUTION: Do not make any changes until and unless it is instructed by NetApp Engineer
CAUTION: Always create a full backup of OCI DWH server or take a VMware snapshot before making any changes
- There are times when we need to manually trim the database and its table sizes
- The OCI DWH database size grows when "Maintenance Jobs" was not enabled or turned off as explained in step 1 above.
- Default configuration was changed on "PerformanceConfig" in OCI DWH server. Recommendation is not not change any default configuration
- How to check "PerformanceConfig" is changed in your OCI DWH server
- Log on OCI DWH configuration portal s "oadmin" [https://localhost/dwh]
- Go to Troubleshooting > Configuration > PerformanceConfig
- Default "PerformanceConfig" screenshot in OCI DWH 7.3.11
- Take an example of OCI DWH database that needs to be trim or reduce the database size using the following procedure
- You need a MySQL "root" user password to login, If you do not know the password please contact "NetApp Support" for assistance.
- First look up the size of the databases using the MySQL command mention in step 2
- Second look up the row size of the tables that belongs to "dwh_performance" database, the MySQL command is part of step 2 above
- NOTE: we will focus on trimmimg "qtree_hourly_performance_fact" table, all other tables sizes looks good.
- NOTE: Command and information collected above so far are non-destructive commands, now to trim the tables and database in OCI DWH database will be destructive and require you to have Full backup or VMware snapshot taken before running any other commands.
- NOTE: If you are reading this line and you have seen the same / similar situation in your OCI DWH MySQL database then please contact "NetApp Support" for help. Please make sure to reference this KB and the screenshots you gather from your OCI DWH database.
- NOTE: we will focus on trimmimg "qtree_hourly_performance_fact" table, all other tables sizes looks good.
- You need a MySQL "root" user password to login, If you do not know the password please contact "NetApp Support" for assistance.
- There are times when we need to manually trim the database and its table sizes
Additional Information
additionalInformation_text