uCRM - Performance/Database cleanup [ Edited ]

[sbyrd]

I have uCRM installed on a VMware system with uCRM setup with 2 processors and 8GB or ram.  I now have 1050 clients and 300 client leads in the system and I am finding performance is starting to struggle when opening/closing tickets and jobs.

I ssh'd into the server and ran top and saw that while opening jobs or chaning their status the 'postgres' process hits 70-99% CPU usage.  Is there anyway to improve this?  Would vacuuming the db help things out and how would I do that?

I will also clarify it seems to be much worse when opening jobs and tickets and or modifying data in them or their status.  Editing Client data seems to run better.

VMHost specs are as follows

HP Proliant DL360 G6

Intel Xeon X5550 @2.67ghz 

8 Logical Processors

144GB RAM

ESXI 6.0

Only others servers running on this host are Mtik Dude (CHR) and Aircontrol 2 (Windows 10), and UNMS (only one AP and a few CPEs for testing).

[sbyrd]

@UBNT-Petr - Any thoughts on how to improve my performance?

[oskaratk]

I am coming more from  a database and software developing background.

We are dealing with different database, SAP-ADS, MS-SQL, etc. Some of those databases have billions of records and a couple thousand tables.

The best performance we see is still on a dedicated machine and not on a host system with virtualization or cloud setup

Might be worth to put UCRM on it's own Linux box

Just my 2c

Oskar

[UBNT-Petr]

@sbyrd 1k clients should be totally fine on your VM. We are not talking about billions of records and thousands of tables, so VM should be ok.

However, we found some performance issues in some UCRM views/modals/actions which might be slow, and it should be solved by our minor fixes, no need to boost your HW. One of these fixes is already prepared for 2.14.5.

Please, @sbyrd feel free to PM me a video or description of other locations which seems slow to you.

[UBNT-Jachym]

We added some preformance fixes for these pages into the 2.14.5 release. I'm not entirely sure if it will help with your case so please try it and let us know.

[jpanoch]

hello,

we are now working with api extensively (to add needed data from our previous systems after first import),

and we see very slow performance too. Just for fun - about 1700 clients imported and to do for all of them:

1) fix gps for client address and first of his service address

2) add one client device for clients's first service (created by import)

3) add two user defined tags

took 150 minutes (about 5.3 seconds to update one client).

i can create a detailed log of api calls duration, if it helps.

hw: 2x3ghz xeon, 8gb ram, 64gb ssd

it looks like, the most cputime consumes ucrm_web_appp_1 when i observe 'docker stat'.

Bye

Jan

[UBNT-Petr]

@jpanoch thanks for reporting, we will investigate this.

[jpanoch]

---

@UBNT-Petr wrote:
@jpanochthanks for reporting, we will investigate this.

---

i can add even here a piece of my api request log:

date            request duration request method and url
-------------------------------------------------------
20190116-111250 0,08210277557373 GET clients?userIdent=xxkxkd
20190116-111251 0,62469506263733 PATCH clients/873834/geocode
20190116-111251 0,091971874237061 GET clients/services?clientId=873834
20190116-111251 0,11855101585388 PATCH clients/services/7745/geocode
20190116-111251 0,08871603012085 GET clients/services?clientId=873834
20190116-111251 0,209233045578 PATCH clients/services/7745
20190116-111251 0,096760034561157 GET clients/services?clientId=873834
20190116-111251 0,067715883255005 POST clients/services/7745/service-devices
20190116-111251 0,083785057067871 GET clients/873834
20190116-111253 1,2651269435883 PATCH clients/873834/remove-tag/1
20190116-111254 1,2401759624481 PATCH clients/873834/add-tag/1
20190116-111254 0,077000141143799 GET clients/873834
20190116-111255 0,82782697677612 PATCH clients/873834/remove-tag/25
20190116-111256 0,83112502098083 PATCH clients/873834/add-tag/25

[sergiov87]

Just wanted to chime in myself, i have another ticket open with a similar issue. 

The last update seemed to have fixed some stuff, but we also have a slowness in response, this is happening when we assign tickets for us mainly.

https://community.ubnt.com/t5/UCRM/Error-when-assigning-tickets/m-p/2635770#M13521

[jpanoch]

---

@jpanoch wrote:

---

@UBNT-Petr wrote:
@jpanochthanks for reporting, we will investigate this.

---

i can add even here a piece of my api request log:

date            request duration request method and url
-------------------------------------------------------
20190116-111250 0,08210277557373 GET clients?userIdent=xxkxkd
20190116-111251 0,62469506263733 PATCH clients/873834/geocode
20190116-111251 0,091971874237061 GET clients/services?clientId=873834
20190116-111251 0,11855101585388 PATCH clients/services/7745/geocode
20190116-111251 0,08871603012085 GET clients/services?clientId=873834
20190116-111251 0,209233045578 PATCH clients/services/7745
20190116-111251 0,096760034561157 GET clients/services?clientId=873834
20190116-111251 0,067715883255005 POST clients/services/7745/service-devices
20190116-111251 0,083785057067871 GET clients/873834
20190116-111253 1,2651269435883 PATCH clients/873834/remove-tag/1
20190116-111254 1,2401759624481 PATCH clients/873834/add-tag/1
20190116-111254 0,077000141143799 GET clients/873834
20190116-111255 0,82782697677612 PATCH clients/873834/remove-tag/25
20190116-111256 0,83112502098083 PATCH clients/873834/add-tag/25

it looks like anything with removing and adding tags is very slow, so i rewrote my logic and instead of remove tag and then add tag i'm checking if it is not the same tag and if yes, i do not call api at all (because not needed, the tag is allready set).

---

[UBNT-Jachym]

@jpanoch Considering you're doing 14 API requests for one client, 5.3 seconds is not all that surprising. While some of the requests do seem to take longer than they should (sadly we were unable to reproduce it), the main problem in my opinion is that you're making them sequentially instead of concurrently.