How to migrate database to partitions or optimize performance

prerequisites

• We are recommending to upgrade MySQL to the version 5.6 (see this howto Mysql_5.6) which has optimized SQL planner resulting in much faster queries using joins which are extensively used in the GUI. Besides other optimizations.
• The voipmonitor sniffer automatically enables partitions for cdr tables which allows to store millions of data partitioned per day. Each day have standalone index which means that searching will be not slower with each days. Partitioning also allows to clean old data because deleting partitions is instant and has support directly in the voipmonitor sniffer Sniffer_configuration#cleandatabase. If your database is not partitioned (which you will see if you run "MySQL> show create table cdr" and at the end you will see partitions) it cannot be converted automatically.
• Another big performance impact is when you do not use innodb_file_per_table = 1 - without this option the ib_data will be giant for all tables and partitions which is not recommended. Thus we strongly recommend to set

innodb_file_per_table = 1

which means that you have to migrate your old data to new database (see next chapter).

• Compression allows to save I/O writes and doubles space for storing data. This is only enabled if innodb_file_per_table is set to 1 and for mysql <= 5.1 innodb_file_format = barracuda (in >5.1 barracuda is default). The voipmonitor sniffer creates compressed tables automatically so you do not need to enable this feature.
• I/O disk optimization - in mysql configuration set

innodb_flush_log_at_trx_commit = 2 

Now restart the database and proceed with data migration next chapter

Data migration

Recent voipmonitor sniffer has feature which allows to automatically synchronize source voipmonitor database to another voipmonitor database including partition creation and syncing new data. This feature was developed primarily for backup purpose but it is also handy when you need to convert data to partitions on your local database. Follow next steps:

Create temporary configuration file /etc/voipmonitormigrate.conf with this content and change username and password accordingly. You can also change IP addresses to match your deployment. You can also migrate your data to another database server or another database instance. mysqlhost and mysqldb is used for destination database. database_backup_from is the original database so do not mess it up! If you are unsure you can stop the databaes server and backup your whole data (cp -a /var/lib/mysql /var/lib/mysql-backup) - note that the migration will use the same disk space like your current data so be sure you have enough space for the migration.

[general]
cdrproxy = yes
nocdr = no
sqldriver = mysql
mysqlcompress = yes
mysqlhost = 127.0.0.1
mysqlport = 3306
mysqldb = voipmonitorNEW
cdr_partition = yes
mysqltable = cdr
mysqlusername = root
mysqlpassword = rootpwd
cdr_sipport = yes
create_old_partitions_from = 2015-12-03
database_backup_from_date = 2015-12-03
database_backup_from_mysqlhost = 127.0.0.1
database_backup_from_mysqlport = 3306
database_backup_from_mysqldb = voipmonitor
database_backup_from_mysqlusername = root
database_backup_from_mysqlpassword = rootpwd
database_backup_pause = 3
database_backup_insert_threads = 3
#cleandatabase = 120
interface=lo
disable_partition_operations=no

;this section is only for those who uses matchheader and custom headers which you can omit if you do not use them. 
matchheader = myheader
custom_headers_message = Comm-Correlation-ID ; Comm-ID
custom_headers = Remote-Party-ID ; Comm-ID ; Comm-Correlation-ID; Comm-Session-Id

Run the voipmonitor sniffer

voipmonitor --config-file /etc/voipmonitormigrate.conf -k -v 1

the -k option will cause sniffer not detach from console and run in foreground.

after the migration is done (same count of CDRs inn DBs) stop both sniffers and reconfigure /etc/voipmonitor.conf to use your new database name (voipmonitorNEW) and do not forget to reconfigure the GUI -> settings -> server settigns -> database name. Run the sniffer (/etc/init.d/voipmonitor start) and check if your new database contains new CDR. Now you can drop your old database (mysql> drop database voipmonitor)

In case you need to transfer also user's settings in a GUI (Like dashboard templates, network maps, alerts settings and reports,names and password of users, etc) use under GUI->tools->backup&restore to backup the file, and then restore it in new GUI with new db.

settings migration requested

Here is the sample script for backup_and_restore_GUI_tables peridically using crontab.