Summary:	ASTERISK-30402: app_queue: QueuePause generates an excessively large number of database queries
Reporter:	Péter Juhász (peter.juhasz)	Labels:
Date Opened:	2023-01-24 08:13:04.000-0600	Date Closed:
Priority:	Major	Regression?	Yes
Status:	Open/New	Components:	Applications/app_queue
Versions:	18.11.0 18.15.0	Frequency of Occurrence	Constant
Related Issues:	is caused by ASTERISK-18416 [patch] Realtime queue agents unavailable via AMI before a call event. is caused by ASTERISK-27597 AMI Queuestatus not working (with realtime queue) is caused by ASTERISK-29873 [patch] Queue Realtime load
Environment:	linux	Attachments:
Description:	We've noticed that Asterisk generates a very large number of database queries that match the pattern `SELECT * FROM queues WHERE name = 'foo' LIMIT 1`. We were able to trace this to the QueuePause AMI command, which is used by our dialer application: every time asterisk receives this command without a queue name, it iterates over all queries in alphabetical order, and issues a single-queue select query for each one. Apparently this behavior was introduced by this commit: https://gerrit.asterisk.org/c/asterisk/+/17987 Now the intent behind that change is right and proper: if you have realtime queues, you'd want to be sure that you have the latest version of them in memory. However, the implementation is problematic, because it generates one query per queue (thus, one database round-trip), for all queues, for every QueuePause command. This clearly doesn't scale well, and gets worse when the number of queues increases. It got so bad that for one our customers Asterisk generated 600k such queries per hour, which actually accounted for the majority of database traffic for that customer. A more proper solution would be issuing a single `select * from queues` query and then processing its output.
Comments:	By: Asterisk Team (asteriskteam) 2023-01-24 08:13:05.436-0600 Thanks for creating a report! The issue has entered the triage process. That means the issue will wait in this status until a Bug Marshal has an opportunity to review the issue. Once the issue has been reviewed you will receive comments regarding the next steps towards resolution. Please note that log messages and other files should not be sent to the Sangoma Asterisk Team unless explicitly asked for. All files should be placed on this issue in a sanitized fashion as needed. A good first step is for you to review the [Asterisk Issue Guidelines|https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines] if you haven't already. The guidelines detail what is expected from an Asterisk issue report. Then, if you are submitting a patch, please review the [Patch Contribution Process|https://wiki.asterisk.org/wiki/display/AST/Patch+Contribution+Process]. Please note that once your issue enters an open state it has been accepted. As Asterisk is an open source project there is no guarantee or timeframe on when your issue will be looked into. If you need expedient resolution you will need to find and pay a suitable developer. Asking for an update on your issue will not yield any progress on it and will not result in a response. All updates are posted to the issue when they occur. Please note that by submitting data, code, or documentation to Sangoma through JIRA, you accept the Terms of Use present at [https://www.asterisk.org/terms-of-use/|https://www.asterisk.org/terms-of-use/]. By: Michael Bradeen (mbradeen) 2023-01-25 12:51:21.463-0600 Hi [~alexei gradinari] any input on the request mentioned here? By: Alexei Gradinari (alexei gradinari) 2023-01-25 15:41:17.053-0600 [~peter.juhasz], The "realtime" is very ancient in module app_queue, better to replace "realtime" into sorcery, but this is a huge amount of work. My patch resolved the issue which I was facing. In my case an application sends AMI actions QueuePause with queue name and the asterisk issues only a single-queue SELECT. In your case to avoid large number of database queries you should: - ether change your application to send AMI actions QueuePause with the queue name the pause interface is a member of; - or provide a patch which optimizes the DB requests. BTW, the asterisk also issues SELECT on queue_members table per queue, I don't know you can avoid this. By: Péter Juhász (peter.juhasz) 2023-01-26 12:07:05.772-0600 For now, we've patched out the offending `load_realtime_queues` call from the function in our build. That's how it worked before 18.11, seemingly without problems. But I realize that for other use cases (like yours) reloading the queues is the right thing to do, and with a lot of queues this does cause problems like those I've reported in this issue.