Hi Guys,
I was having an unusual scenario in one of my projects. The data was growing by leaps and bounds- day by day. One year after application went live now .. we have problem running reports ... CPU utilization is becoming very high .. resulting in freezing of Application on Weblogic Servers .... Load on database server is also getting huge ... . Unfortunately initial designers of application didn't have hindsight of putting Archiving-Purging logic in application . By the time I took over, we were into other trouble shooting that this was put to back burner till now ...
Things have got hot now .. we have to work on this Database growth analysis:
This is how we did it ...
1. We decided that we will capture the table record counts & table size of all Tables daily at a pre-defined time for a month.
2. We started getting these table records counts & table size in form of xls files.
3. Now we know that there will almost 30 days xls , each with details of 500 odd tables.
4. How do we analyze this data - will putting in a database table help us ?
5. Or the xls itself can be used for data manipulation. But this will be highly manual task. Can't waste time to do comparsion of each table for all 30 days , find growth every day .. then find average growth ..
6. Then we struck upon a idea - use xls , jdbc & sql-query from Java ...
7. We know SQL's are very easy to build & execute ... so why not use SQL to do this manipulation in xls.
8. There is way to connect to xls using Jdbc-Odbc bridge connection in windows.
9. Assume the xls we have is of name"qa.xls". Each worksheets is having name "qa1" , "qa2" , "qa3" ... "qa30" & have data for 30 days respectively.
10 . Each worksheet will have header & data as :
SCHEMA TABLENAME ROWCOUNT SPACEUSED
11. Now we need to create ODBC data source through Control Panel , use "Microsoft Excel Driver".
12. Give name of "Data Source Name" as "qa-list". And select the xls file "qa.xls".
13.Odbc-Jdbc microsoft excel setup is complete now.
14. Now we can programatically call the xls worksheet as database tables . Using joins we are able to achieve the daily growth , average growth etc ... for any specific time period.
15. Refer my other post "You can use JDBC/SQL Query in Java for Excel files" to see the compelete program listing.
16. Output from this an xls sheet with all required data growth statistics.
17. Utimately from daily data for a period of time we are able to derive a equation from xls .. which will predict what data will be available at time in future.
18. This analysis has helped us to plan for our server disc space capacity planning. Think on Archiving strategy on out growing tables ..
I was having an unusual scenario in one of my projects. The data was growing by leaps and bounds- day by day. One year after application went live now .. we have problem running reports ... CPU utilization is becoming very high .. resulting in freezing of Application on Weblogic Servers .... Load on database server is also getting huge ... . Unfortunately initial designers of application didn't have hindsight of putting Archiving-Purging logic in application . By the time I took over, we were into other trouble shooting that this was put to back burner till now ...
Things have got hot now .. we have to work on this Database growth analysis:
This is how we did it ...
1. We decided that we will capture the table record counts & table size of all Tables daily at a pre-defined time for a month.
2. We started getting these table records counts & table size in form of xls files.
3. Now we know that there will almost 30 days xls , each with details of 500 odd tables.
4. How do we analyze this data - will putting in a database table help us ?
5. Or the xls itself can be used for data manipulation. But this will be highly manual task. Can't waste time to do comparsion of each table for all 30 days , find growth every day .. then find average growth ..
6. Then we struck upon a idea - use xls , jdbc & sql-query from Java ...
7. We know SQL's are very easy to build & execute ... so why not use SQL to do this manipulation in xls.
8. There is way to connect to xls using Jdbc-Odbc bridge connection in windows.
9. Assume the xls we have is of name"qa.xls". Each worksheets is having name "qa1" , "qa2" , "qa3" ... "qa30" & have data for 30 days respectively.
10 . Each worksheet will have header & data as :
SCHEMA TABLENAME ROWCOUNT SPACEUSED
11. Now we need to create ODBC data source through Control Panel , use "Microsoft Excel Driver".
12. Give name of "Data Source Name" as "qa-list". And select the xls file "qa.xls".
13.Odbc-Jdbc microsoft excel setup is complete now.
14. Now we can programatically call the xls worksheet as database tables . Using joins we are able to achieve the daily growth , average growth etc ... for any specific time period.
15. Refer my other post "You can use JDBC/SQL Query in Java for Excel files" to see the compelete program listing.
16. Output from this an xls sheet with all required data growth statistics.
17. Utimately from daily data for a period of time we are able to derive a equation from xls .. which will predict what data will be available at time in future.
18. This analysis has helped us to plan for our server disc space capacity planning. Think on Archiving strategy on out growing tables ..
No comments:
Post a Comment