Advanced MySQL optimization for Call History speed-up (MySQL partition)

From MiRTA PBX documentation
Jump to navigation Jump to search

When the number of tenants grows and the retention period for Call History data is enough long, the asteriskcdrdb/cdr table became really big. MySQL starts crawling while accessing this table, especially during table scan operation like "counts" or "selects" with not unique columns. The solution is to partition the table using the accountcode column. In this way, sequential access is way faster.

For the brave

Performing this operation on a live server table is not advised, so a special workaround needs to be put in place. However if the table is not so big and your server I/O speed is high, you can just run the following queries: 

alter table cdr drop primary key, add primary key (`ID`,`accountcode`)
alter table cdr partition by key(accountcode) partitions 1000

Replace 1000 with around 20% more partitions than your actual or planned number of tenants.

For the wise

  • take a dump of the table, omitting the table creation and lock in write
mysqldump -u root -p --no-create-info --skip-add-locks asteriskcdrdb cdr  > cdr.sql
  • take a dump of the structure for table
mysqldump -u root -p --no-data asteriskcdrdb cdr  > cdr-structure.sql
  • rename the original cdr table, we'll use it as backup. From mysql:
rename table `cdr` to `cdr_backup`
  • use the saved script cdr-structure.sql to recreate the cdr table
  • alter the primary key to include the accountcode column. From mysql:
alter table cdr drop primary key, add primary key (`ID`,`accountcode`)
  • create a table partition with around 20% more partitions than your actual or planned number of tenants. Adding more partitions than the number of tenants doesn't harm your system. If you have 500 tenants and you are still growing, it will be fine to create 1000 partitions (like in the example below)
alter table cdr partition by key(accountcode) partitions 1000

This will create 1000 tables, it can take some time.

  • Your PBX should be now able to write on the new cdr table, maybe you can be sure by reloading the cdr_adaptive_odbc.so module
  • Now you can restore the data in the system using the cdr.sql file while your PBX is normally working
Retrieved from "http://www.mirtapbx.com/manual/index.php?title=Advanced_MySQL_optimization_for_Call_History_speed-up_(MySQL_partition)&oldid=1078"