When Variphy Call Analytics for CUCM CDR searches/reports appear very slow, it is often caused by a shortage of available memory on the MySQL database server.
This typically happens when the size of the database outgrows the amount of memory made available to cache it. The larger the ratio of database size to database cache grows, the more search performance will slow down.
Ideally, for every gigabyte of data of the database size, you would want to allocate and equal amount of memory to cache it, so for a 7 GB database you’d want a cache size of 7 GB (or more to accommodate growth).
This may not always be possible, but you will want to give MySQL as much memory that you can make available for caching. The rule of thumb is to reserve at least 2GB for the OS, at least 3GB for Variphy, and whatever additional memory remains should be allocated to MySQL.
The setting that controls this memory allocation is called the innodb_buffer_pool_size.
- Total system memory = 12G
- Linux OS = 2G
- Variphy = 3G
- innodb_buffer_pool_size=7G
To Increase the MySQL Cache Size On Windows Installations:
Step 1) Locate the my.ini file on the server. This is typically in a location like C:\Variphy\Data or in a subfolder of C:\ProgramData\MySQL.
Step 2) Open the my.ini configuration file with a text-editor such as Notepad, and then locate the property and value for ‘innodb_buffer_pool_size’ in the file. It will look something like this:
innodb_buffer_pool_size=256M
Step 3) In this example this example, the buffer pool is only allocated 256 MB, meaning that it can only cached that much of your 7G database in memory. To increase this cache size simply update the value to the appropriate setting. For the machine in the previous example with 12GB total, you would set the available memory value to 7G
innodb_buffer_pool_size=7G
Step 4) Save and close the file in it’s original format (not as a .txt file)
Step 5) Restart the ‘MySQL’ Windows service to have it take effect (it may also be under the name of “Variphy-MySQL”).
If the service fails to start back up, then the OS did not grant the memory setting. We’d recommend stepping the setting down slightly until it starts up – e.g. try 6GB, etc.
To Increase the MySQL Cache Size On OVA/Linux Installations:
Step 1) Open the ‘my.cnf’ configuration file with a text-editor such as vi. It will typically be located in one of the following locations:
/etc/mysql/mysql.conf.d/mysqld.cnf
/etc/mysql/my.cnf
/etc/my.cnf
Step 2) edit the file from within the proper directory with the command sudo vi FILENAME
Step 3) Locate the property and value for ‘innodb_buffer_pool_size’ in the file. It will look something like this:
innodb_buffer_pool_size=256M
Step 4) Update the value to the appropriate setting.
innodb_buffer_pool_size=7G
Step 5) Save and close the file with the command SHIFT + : x!
Step 6) Restart the ‘MySQL’ service to have it take effect.
The Terminal/shell command to do this is:
“service mysqld restart” or “/etc/init.d/mysql restart“.
It may be necessary to add the keyword “sudo” before the command and enter the password when prompted.
