[asterisk-bugs] [JIRA] (ASTERISK-30402) app_queue: QueuePause generates an excessively large number of database queries

[Alexei Gradinari (JIRA)]

    [ https://issues.asterisk.org/jira/browse/ASTERISK-30402?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=261247#comment-261247 ] 

Alexei Gradinari commented on ASTERISK-30402:
---------------------------------------------

[~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.



> app_queue: QueuePause generates an excessively large number of database queries
> -------------------------------------------------------------------------------
>
>                 Key: ASTERISK-30402
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-30402
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: Applications/app_queue
>    Affects Versions: 18.11.0, 18.15.0
>         Environment: linux
>            Reporter: Péter Juhász
>            Severity: Major
>
> 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.  



--
This message was sent by Atlassian JIRA
(v6.2#6252)