Optimize CDR Database in MySQL

Sooner or later, but the base CDR grows to a considerable size. There are several fairly simple ways to optimize it.

1. Update MySQL. Each new version is faster than the previous one. The issue of updating is beyond the scope of the topic, but I remind you - when jumping “through the version”, it is optimal to unload the entire base and reload onto a clean installation, otherwise surprises are possible.

2. Optimize InnoDB. In the my.cnf config, I recommend the following settings:
')

innodb_buffer_pool_size = 256M ;    ,   RAM  . innodb_file_format = Barracuda ;    .  . innodb_buffer_pool_instances = 1 ;          ,  . innodb_change_buffer_max_size=10 ;  CDR      .     . innodb_flush_log_at_trx_commit=2 ;             . 

3. Change the engine to InnoDB

 ALTER TABLE cdr ENGINE=InnoDB; 

4. Disable performance schema if you are not using it (my.cnf)

 performance_schema=OFF 

5. Enable query caching (my.cnf)

 query_cache_type=1 query_cache_size=32M 

6. Partition the table by creating 12 sections, one for the month of the year.

 ALTER TABLE cdr PARTITION BY HASH (month(calldate)) PARTITIONS 12; 

7. Change to compressed format if you have a mechanical one, not an SSD disk. This is what the Barracuda format is for.

 ALTER TABLE cdr ROW_FORMAT=COMPRESSED; 

Recommendations are relevant for MySQL 5.6