If your Vtiger CRM is slow, you have a big database with thousands of records, or you have added more than a few custom modules to your CRM, then there is a good chance you have one or two improvement opportunities in you Vtiger MySQL Database server.
As part of our Vtiger Manage Hosting service, we usually optimize our client’s database, not just because we want the best CRM experience for them but also let us use our hardware more efficiently
In this quick tutorial, I will show you three quick wins you could implement right now to improve your vtiger MySQL database performance. Your user will love the improvement.
Use SSD Hosting
If you are taking just one thing from this tutorial, this should be it. Just changing your hosting to one that uses SSD Disk instead of hard optical drivers will be a huge performance increase.
There are several studies online that show that switching to SSD disks will improve your MySQL performance between 5x and 30x. Considering that each time you View, Search, Edit a record in vtiger you hit the database server, and this improvement will go directly to your users.
Find slow and problematic queries in Vtiger MySQL
MySQL server came with a feature called “Slow Query Log.”
The MySQL slow query log is a file where the SQL servers register slow or potentially problematic queries. This logging functionality is turned off by default.
The queries that are logged are queries that take longer than a specified amount of time to execute or queries that do not correctly hit indexes.
To enable the slow query log in MySQL, follow these steps:
Log into your server using SSH.
At the command line, type the following command:
mysql -u root -p
Type the MySQL root password.
To enable the slow query log, type the following command at the mysql> prompt:
SET GLOBAL slow_query_log = 'ON';
There are additional options that you can set for the slow query log:
By default, when the slow query log is enabled, it logs any query that takes longer than 10 seconds to run. To change this interval, type the following command, replacing X with the time in seconds:
SET GLOBAL long_query_time = X;
Good timing for Vtiger MySQL Database I think it’s 2 seconds. In an extensive database probably the home widget will take longer than that. Any other query could potentially be improved
By default, the slow query log file is located at /var/lib/mysql/hostname-slow.log. To change the log path or filename, type the following command, replacing the path with the path to the file, and filename with the name of the log file name:
SET GLOBAL slow_query_log_file = '/path/filename';
Continue to monitor the slow query log file to see which queries take a long time to run.
When you are done troubleshooting, disable the slow query log. To do this, rerun the MySQL program, and then type the following command:
SET GLOBAL slow_query_log = 'OFF';
You should only enable the slow query log for as long as it is necessary to troubleshoot performance issues.
Now that you have identified the slow or problematic queries you will need to fix and improve them
Improving Slow Queries
MySQL and MariaDB provide a useful EXPLAIN command which can analyze your queries and detect potential performance issues:
- EXPLAIN describes how a SELECT will be processed including information about JOINS.
- EXPLAIN EXTENDED provides additional information and estimates the number of table rows that are filtered by the condition.
There is a great tutorial about using EXPLAIN to improve your queries in here. You should follow that tutorial
My recommendation is to use EXPLAIN to add any missing index you have identified using the step 2 of this tutorial.
Following this exact step, we manage to reduce one client query from 90 seconds to just 2.5 seconds!