This entry serves as the main documentation for BCMS Data Archiving. Upon completion, it should have the following benefits:
1. Quicker backups.
2. Quicker data transfers/uploads.
3. Faster report generation.
4. Faster queries.
Now for the policies:
1. Following the method used for archiving the artran table, we will also archive these tables alongside artran_history to a new database called "history":
a. consumer_bill - Monthly (leave the previous month's data) - indexed on account_no_id, bill_no, bill_no_id
b. consumer_bill_details - Monthly (leave the previous month's data) - indexed on bill_no_id
c. bill_detailed_summarized - Monthly (leave the previous month's data) - indexed on account_no_id, bill_no
d. meter_reading - Monthly (leave the previous month's data)? - indexed on meter_reading_id, account_no_id, billing_month, actual_billing_month
e. payment - Monthly (leave the previous month's data) - indexed on account_no_id, ornumber, payment_date+teller_id
f. payment_details - Monthly (leave the previous month's data) - indexed on payment_detail_id, payment_id
g. payment_details_vat - Monthly (leave the previous month's data) - indexed on id, payment_detail_id
h. sql_logs, transaction_logs - Monthly (purge) - indexed on user_name, date?
2. All historical tables should be MyISAM.
3. Convert bill_detailed_summarized to MyISAM.
4. Update reports.
5. Immediately make a backup of the history database after archiving.
Monday, February 11, 2008
Subscribe to:
Post Comments (Atom)
1 comment:
Hmm... we might run into FS limit problems if we use MyISAM format... let's just use InnoDB for the historical tables.
Post a Comment