Announcement

Collapse
No announcement yet.

VOIP performances and MySQL load

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • VOIP performances and MySQL load

    Our Espo installation is quite big: 120.000 accounts, 150.000 contacts, 16.000 leads, 86.000 phone numbers. We have almost 90 CRM users with a linked VOIP profile, and a lot of them works for phone marketing. The load on MySQL was very heavy, and after some investigation I've found tha problem: the plugin do a lot of query in the form of:

    format_number(phone_number.name) LIKE "%123456789" or
    format_number(phone_number.name) IN ("123456789", "12345", "1234567", "12345678") There is no way that MySQL can use indexes on a field passed through format_number! Given that the plugin activates also on internals calls (e.g. when phne #123 calls #456 in another room), MySQL works like crazy.

    Now I'm working on a workaround, overriding plugin classess through \Espo\Custom\* classes, but I'd like to see this approach used natively by the plugin. Basically I've added another column to the `phone_number` table, called `reverse`. Here I write the reversed version of a clean number, so one table's row could be:

    id name deleted type reverse
    xxxxx +39 (01) 234567 0 Office 7654321093


    With this column almost every query can be simplified. The first example become:

    phone_number.reverse LIKE '987654321%'

    And this is good, because a LIKE search that "begins with" can use indexes.

    The second one become way better, because we can search by the reverse of the shortest number version:

    phone_number.reverse LIKE '54321%'

    Pay attention that queries with ORs or INs often cannot use indexes, so I prefer to get rid of them.

    Our server is a 4 core (originally had 2 cores, we increased for performances reasons when we started using VOIP plugin), and under a standard load we improved from a load of 3.5 with MySQL using 350% of CPU to a way more sane 0.7 and isolated busts at 120%.

    I've got the authorization by my company to share the code of my modifications, if can be useful for the developers.

  • #2
    Thanks for your thoughts.
    Job Offers and Requests

    Comment


    • #3
      We are facing the exact same issue. Can you share your solution so we can try it on our installation too?

      Comment

      Working...
      X