PJSIP Wizard incorrectly looks up DB / poor SQL query

Asterisk Asterisk Support
1

Hi all, I’ve noticed a poorly performing SQL query when using PJSIP realtime with MySQL (via ODBC) combined with configuring endpoints with the PJSIP wizard file.

Endpoint is

[ENDPOINT_NAME]
type = wizard
sends_auth = no
sends_registrations = no
remote_hosts = xxx.xxx.xxx.xxx
transport = transport-udp-vlan300
endpoint/direct_media = no
endpoint/allow = opus,alaw,g726,g729
endpoint/send_pai = yes
endpoint/trust_id_inbound = yes
endpoint/trust_id_outbound = yes
aor/qualify_frequency = 60

The following appears to coincide with the aor/qualify_frequency setting (ie this happens every 60 seconds. I can’t seem to get more out of the debug log even with debug=10.

[Dec 19 11:51:10] DEBUG[3629]: res_odbc.c:869 _ast_odbc_request_obj2: Reusing ODBC handle 0x7fa224031d78 from class 'asterisk'
[Dec 19 11:51:10] DEBUG[3629]: res_config_odbc.c:116 custom_prepare: Skip: 0; SQL: SELECT * FROM ps_endpoints WHERE aors LIKE ? ORDER BY aors
[Dec 19 11:51:10] DEBUG[3629]: res_config_odbc.c:132 custom_prepare: Parameter 1 ('aors LIKE') = '%ENDPOINT_NAME%'
[Dec 19 11:51:10] DEBUG[3629]: res_odbc.c:718 ast_odbc_release_obj: Releasing ODBC handle 0x7fa224031d78 into pool

I see two issues here I think

  1. The AOR is set up via the config file - Asterisk shouldn’t be looking in the DB for this one?
  2. The SQL query is poorly written for MySQL as it is unable to use indexes because the wildcard is at the start of the string. This means it needs to do a full table scan. As the ps_endpoint table grows this becomes more and more expensive. Sample slow log from this
# Time: 2017-12-19T00:57:13.681478Z
# User@Host: asterisk[asterisk] @  [yyy.yyy.yyy.20]  Id:  8651
# Schema: asterisk  Last_errno: 0  Killed: 0
# Query_time: 0.014841  Lock_time: 0.000176  Rows_sent: 0  Rows_examined: 7306  Rows_affected: 0
# Bytes_sent: 9562
SET timestamp=1513645033;
SELECT * FROM ps_endpoints WHERE aors LIKE '%ENDPOINT_NAME%' ORDER BY aors;

Instead of using LIKE %endpoint% can Asterisk be configured to use an exact match? Compare the two queries:

mysql> EXPLAIN SELECT * FROM ps_endpoints WHERE aors LIKE '%ENDPOINT_NAME%' ORDER BY aors\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ps_endpoints
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7251
     filtered: 11.11
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM ps_endpoints WHERE aors = 'ENDPOINT_NAME' ORDER BY aors\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ps_endpoints
   partitions: NULL
         type: ref
possible_keys: aors
          key: aors
      key_len: 203
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

For now I think I’ll need to set up ProxySQL or similar and rewrite the query on the wire.

2

Looks like the code for this is here

I might try and patch it to equals and see what happens

update: Changing the query to “=” and removing the ‘%%%’ sprintf part fixed it. It’s not clear to me why it does a LIKE query and then filters it afterwards.

1 Like