Add extension-user infinite time |

[VoipMC]

Greetings

there is a bug that has never been corrected and that continues to create problems, this happens on all installed PABXs (about 10) both with version 15 or 16. In practice, if a pjsip extension is added and a new one is created user (User Directory) the CPU runs at 75-100% for several minutes, even 30! if instead you don’t create the user but only the pjsip extension, no problem. This happens randomly when a certain number of users is reached, for example 50 and more (at the moment we have over 1000) even eliminating users from a group is a titanic task. I think some query in mysql is not optimized or without index

[VoipMC]

Is there anyone on staff who can answer? the system is poorly designed and even with paid assistance it is impossible to fix. Why pay for a design defect? in other messages it is recommended not to create the user but only the extension, but if I don’t create the user how do I use Sangoma Talk? At this point, why is a non-functioning system on sale? by enabling the mysql log you can see that just to update the freepbx user sends thousands of queries!!! all this makes the system unusable! Can anyone from Sangoma staff give an answer??

		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1937’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1938’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1939’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1940’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1941’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1942’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1943’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1944’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1945’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1946’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1947’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1948’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1949’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1950’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1951’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1952’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1953’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1954’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1955’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1956’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1957’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1958’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1959’ LIMIT 1
		633 Query	SELECT * FROM userman_users WHERE default_extension = ‘1960’ LIMIT 1

[dicko]

mysql asterisk -e "describe  userman_users"

mysql asterisk -e "CREATE INDEX whatever ON userman_users (default_extension)"

mysql asterisk -e "describe  userman_users"

[VoipMC]

[root@asterisk]# mysql asterisk -e “describe userman_users”
±------------------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------------------±-------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| auth | varchar(150) | YES | | freepbx | |
| authid | varchar(750) | YES | | NULL | |
| username | varchar(150) | YES | MUL | NULL | |
| description | varchar(255) | YES | | NULL | |
| password | varchar(255) | YES | | NULL | |
| default_extension | varchar(45) | NO | | none | |
| primary_group | int(11) | YES | | NULL | |
| permissions | longblob | YES | | NULL | |
| fname | varchar(100) | YES | | NULL | |
| lname | varchar(100) | YES | | NULL | |
| displayname | varchar(200) | YES | | NULL | |
| title | varchar(100) | YES | | NULL | |
| company | varchar(100) | YES | | NULL | |
| department | varchar(100) | YES | | NULL | |
| language | varchar(100) | YES | | NULL | |
| timezone | varchar(100) | YES | | NULL | |
| dateformat | varchar(100) | YES | | NULL | |
| timeformat | varchar(100) | YES | | NULL | |
| datetimeformat | varchar(100) | YES | | NULL | |
| email | longtext | YES | | NULL | |
| cell | varchar(100) | YES | | NULL | |
| work | varchar(100) | YES | | NULL | |
| home | varchar(100) | YES | | NULL | |
| fax | varchar(100) | YES | | NULL | |
±------------------±-------------±-----±----±--------±---------------+
[root@asterisk]# mysql asterisk -e “CREATE INDEX whatever ON userman_users (default_extension )”
[root@asterisk]# mysql asterisk -e “describe userman_users”
±------------------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------------------±-------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| auth | varchar(150) | YES | | freepbx | |
| authid | varchar(750) | YES | | NULL | |
| username | varchar(150) | YES | MUL | NULL | |
| description | varchar(255) | YES | | NULL | |
| password | varchar(255) | YES | | NULL | |
| default_extension | varchar(45) | NO | MUL | none | |
| primary_group | int(11) | YES | | NULL | |
| permissions | longblob | YES | | NULL | |
| fname | varchar(100) | YES | | NULL | |
| lname | varchar(100) | YES | | NULL | |
| displayname | varchar(200) | YES | | NULL | |
| title | varchar(100) | YES | | NULL | |
| company | varchar(100) | YES | | NULL | |
| department | varchar(100) | YES | | NULL | |
| language | varchar(100) | YES | | NULL | |
| timezone | varchar(100) | YES | | NULL | |
| dateformat | varchar(100) | YES | | NULL | |
| timeformat | varchar(100) | YES | | NULL | |
| datetimeformat | varchar(100) | YES | | NULL | |
| email | longtext | YES | | NULL | |
| cell | varchar(100) | YES | | NULL | |
| work | varchar(100) | YES | | NULL | |
| home | varchar(100) | YES | | NULL | |
| fax | varchar(100) | YES | | NULL | |
±------------------±-------------±-----±----±--------±---------------+

[VoipMC]

create new user…mysql CPU 100%

[dicko]

Then it’s not this missing index

[VoipMC]

no, This is a design issue that was never resolved in version 15 or 16. Cannot use groups or users.

[dicko]

I can but none of my PBI have 1000 endpoints, take it to Sangoma

[VoipMC]

the problem is not having 1000 extensions, I have old pabx even with astersik 1.4 and 1.6 and freepbx 2 (naturally not connected to the internet) with 10,000 sip & iax users and there are no problems. Even with freepbx 15 or 16 and asterisk 18 there are no problems as long as you use the sip extensions, even more than 1000. But as soon as the user manager and groups are brought into play, even just creating 50 users basically takes a biblical amount of time , and from the tests I have carried out this happens on every freepbx both in version 15 or 16 (I have more than 10 in production, all with the same problem) machines with 8-16 CPUs, 32-64 GB of RAM SSD disks and 10 Gbps of internet bandwidth up\down. Support doesn’t solve anything, even using paid tickets. I’m surprised no one on this forum is complaining about the same problem.

[dobrosavljevic (Igor Dobrosavljevic)]

@VoipMC

even just creating 50 users basically takes a biblical amount of time

Never has for me.

[VoipMC]

Good for you, but the problem remains. After another investigation, the question again moved to MySql. As I was saying, I have old versions of Asterisk even with 10,000 sip extensions and no problem, but freepbx 15 and 16 make massive use of Mysql queries that are not fully optimized, recursive queries even for the various connected softphones (think of the recent change from replicate_cdr to trasient_cdr) and this also blocks very bulky systems from a hardware point of view.

Example mysql bug

Sangoma Phone Desktop - BUG

Hi I use Sangoma Phoma Desktop with about 1000 licenses, currently at version 3.9.3 but I think there are still too many bugs. For example By activating the queue display option as described here https://wiki.freepbx.org/display/CONNECT/Sangoma+Phone+Desktop+Client+Queues there is a drawback, i.e. an agent in queue (queue 905) is named with the name of another queue. The agent is 450 but appears with the queue name 909! [image] but in freepbx settings i have [image] and as proof [imag…

and again

Slow Gui when extensions and user are created

I have a problem with freepbx the latest release 16. If I add a pjsip extension everything is ok, the system is responsive. IF I add a pjsip extension and at the same time create a user in the default group, the system takes several minutes (!!) the cpu rises to 40% IF I add a pjsip extension without user and then later I modify the extension creating a new user there are no problem, BUT if the user is added to the default group … the problem returns It seems that the problem is when you crea…

However…The moral is that if you have 1000 pjsip extensions and as many users (both local and for example from active directory) and these are inserted as dynamic agents the system goes to complete block both for Sangoma Connect and in case you want to add a new user, at best sangoma Connect also takes 2 minutes to alert itself and it also takes 45 seconds to add or update a user.

I have 31 queues, and inserted all the dynamic agents that can log in (about 600 out of 1300) as soon as this operation is performed for all the queues or in any case for a certain number, it is not possible to add users unless it takes a very, very long time .

The reason is that the system writes all this information into the sangomartapi_call_queue_members table and as can be seen with almost 20,000 rows (but if they were even just half as many it would change little) each time a query of the type

SELECT cqm.*, u.default_extension AS extension, u.fname AS first_name, u.lname AS last_name, u.displayname AS display_name
FROM sangomartapi_call_queue_members AS cqm LEFT JOIN userman_users AS u ON cqm.person_account_id = u.id WHERE cqm.call_queue_account_id = 200

and missing index on call_queue_account_id and persona_account_id
the query reads the entire table every time !
imagine adding a new user and each time scanning a 20,000 row table for just 1 insert

other queries are

SELECT * FROM userman_users WHERE default_extension = ‘923’ LIMIT 1

with no index on default_extension

and SELECT DATA FROM queues_details WHERE id = 918 AND keyword = ‘member’ ORDER BY flags, also here no index is used

so if use dynamic member with many trains the user and group system is unusable

At the same time, Sanogma Connect is unusable and upon opening it reads the indicated tables to understand which users have logged in in the queue, imagine even just 100 operators starting thousands of queries on a non-indexed table…sanogma connect uses until 2 minutes to start

This is partly solved by eliminating dynamic agents from queues and using Agent Regex Filter
It’s a shame, however, that in this case Sangoma Connect, not seeing any data in the angomartapi_call_queue_members` tab, cannot show who is logged in or on pause