[Vtigercrm-developers] slow vtiger server

[Alan Lord]

The largest system I have runs vtiger and the mysql database on separate 
amazon instances. This system has around 600 users and > 30m rows in 
crmentity. The vtiger server has 30G of RAM, looks like it has 8 CPU 
cores (no idea if virtual or not, but as it is on aws I guess they are), 
and runs multiple applications - not just vtiger. In the config.inc.php 
they have allowed up to 1G of ram:

 > ini_set('memory_limit','1024M');

I have no cli access to their database server so can't provide any idea 
of specification but it will probably be pretty large as well - again it 
handles multiple application databases.


Having said all that I have several other large customers where they run 
everything on a single (virtual) machine.


What I would do *before* doing anything else is take your example 
queries from below and DESCRIBE (or EXPLAIN) them. This will show you 
how/if MySQL is using indexes. If the tables are not properly indexed it 
will have a huge impact on performance.


Giving MySQL more RAM and tuning some of its parameters might also help; 
but I am not a DBA and can't really suggest the best approach to 
tuning... I'm sure Google can help here.

Finally - check the speed of the machines storage (discs). Older HDDs 
are pretty slow - you might get a big performance improvement moving to 
SSD or NVMe SSD.

Hope that helps a bit.


Cheers

Al

On 03/08/2020 20:01, Rubén A. Estrada Orozco wrote:
> Hi everyone,
> 
> I'm having a performance issue with one of my clients. vtiger takes too 
> long to load listviews and sometimes 503 http errors are returned. 
> Apparently the server has a great workload. Before increasing resources, 
> I want to rule out there's something else that can be done.
> 
>   * The server has a centos OS, 2GB RAM, 2 virtual CPUs, 2GB swap memory.
>   * Vtgier version 7.1, PHP 7.0 fpm, Mariadb 5.5, apache 2.4.6
>   * Aprox. 50 concurrent users
>   * vtiger_crmentity table has 822 164 not deleted records and 23 380
>     deleted records (almost all of them are leads)
> 
> Upon activating the slow queries log I see that the queries getting the 
> listview of the leads module sometimes takes between 10 - 40 seconds 
> with aprox 1million rows examined:
> 
> image.png
> 
> Queries to the vtiger_modtracker table also take a while (36s in the 
> example below with 3.3 million rows examined):
> 
> image.png
> 
> This is a resources consumption graph of the server from 8am to 2pm:
> 
> image.png
> Any help will be appreciated.
> 
> Alan Lord, you previously commented that you have clientes with huge 
> databases (several million rows). It would be really helpful to get some 
> advice from you.
> 
> Thanks in advance
> Rubén
> 
> _______________________________________________
> http://www.vtiger.com/
>