I have a table CDR which contains 25 crore data and generally it contains 5 million records on daily basis then ho we opt for best strategy to handle the cdr.
what should be the backup plan
what should be the table optimization
what should be best strategy which is used for data migration and data filtration
give complete analaysis as DBA.
CREATE TABLE 'cdr' (
'id_call' int(11) NOT NULL AUTO_INCREMENT,
'direction' varchar(30) DEFAULT NULL,
'presence_data' varchar(255) DEFAULT NULL,
'presence_id' varchar(50) DEFAULT NULL,
'lead_id' int(11) DEFAULT NULL,
'upload_id' int(11) DEFAULT NULL,
'id_campaign' int(11) DEFAULT NULL,
'message' varchar(240) DEFAULT NULL,
'caller_id_number' varchar(45) DEFAULT NULL,
'answered_number' varchar(50) DEFAULT NULL,
'dialed_number' varchar(45) DEFAULT NULL,
'hangup_cause' varchar(240) DEFAULT NULL,
'hangup_cause_q850' int(4) DEFAULT NULL,
'start_epoch' int(11) DEFAULT NULL,
'answer_epoch' int(11) DEFAULT NULL,
'progress_media_epoch' int(11) DEFAULT NULL,
'end_epoch' int(11) DEFAULT NULL,
'duration' int(4) DEFAULT NULL,
'billsec' int(4) DEFAULT NULL,
'key_act' varchar(15) DEFAULT NULL,
'key_press' varchar(2) NOT NULL,
'progresssec' int(3) DEFAULT NULL,
'answersec' int(3) DEFAULT NULL,
'progress_mediamsec' int(5) DEFAULT NULL,
'iduser' int(11) DEFAULT NULL,
'cost' double(6,4) DEFAULT NULL,
'progress_media' int(11) DEFAULT NULL,
'rate' double(6,4) DEFAULT NULL,
'accountcode' int(11) DEFAULT NULL,
'last_app' varchar(255) DEFAULT NULL,
'last_arg' text DEFAULT NULL,
'ast_hangupcause' varchar(255) DEFAULT NULL,
'ast_hangupcausecode' varchar(29) DEFAULT NULL,
'last_bridge_hangup_cause' varchar(50) DEFAULT NULL,
'last_sent_callee_id_number' varchar(20) DEFAULT NULL,
'current_application_data' text DEFAULT NULL,
'current_application' varchar(50) DEFAULT NULL,
'uuid' varchar(100) DEFAULT NULL,
'start_stamp' datetime DEFAULT NULL,
'progress_media_stamp' datetime DEFAULT NULL,
'answer_stamp' datetime DEFAULT NULL,
'end_stamp' datetime DEFAULT NULL,
'ivr_menu_status' varchar(20) DEFAULT NULL,
'playback_ms' int(11) DEFAULT NULL,
'failure_status' varchar(100) DEFAULT NULL,
'failure_phrase' varchar(100) DEFAULT NULL,
'member_uuid' varchar(100) DEFAULT NULL,
'session_uuid' varchar(100) DEFAULT NULL,
'agent' varchar(100) DEFAULT NULL,
'queue' varchar(100) DEFAULT NULL,
'queue_answered_epoch' int(11) DEFAULT 0,
'queue_terminate_epoch' int(11) DEFAULT 0,
'queue_join_epoch' int(11) DEFAULT 0,
'queue_cancelled_epoch' int(11) DEFAULT 0,
'agent_bridge' varchar(8) DEFAULT '0',
'cc_record_filename' text DEFAULT NULL,
'amd' int(11) DEFAULT 0,
'camp_type' int(11) DEFAULT NULL,
'transfer_type' text DEFAULT NULL,
'u_status' int(11) DEFAULT 0,
KEY 'id_call' ('id_call'),
KEY 'upload_id' ('upload_id'),
KEY 'lead_id' ('lead_id'),
KEY 'presence_id' ('presence_id'),
KEY 'hangup_cause_q850' ('hangup_cause_q850'),
KEY 'start_epoch' ('start_epoch')
) ENGINE=InnoDB AUTO_INCREMENT=2856992 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
this is the structure of our cdr table
Tuhin PaulPosted Mar 21, 2023, 5:38 PM
Partitioning the table can help to improve query performance and reduce maintenance overhead. By dividing the table into smaller partitions, you can manage data more efficiently, and optimize queries that only need to access a subset of data. Partitioning can also help with data archiving and backup. Proper indexing is crucial for good performance on large tables. You should analyze your queries and create indexes on columns that are frequently used in WHERE, JOIN, or ORDER BY clauses. Consider using a clustered index on the primary key column. To reduce the size of the table and improve performance, you can consider archiving old data to a separate table or database. This can also help with backups and disaster recovery.
Tuhin PaulPosted Mar 21, 2023, 5:37 PM
Keep in mind that handling a table with such a large amount of data requires careful planning and execution to ensure that the database is optimized for performance and scalability.