[asterisk-bugs] [JIRA] (ASTERISK-26061) [patch] res_pjsip: improve realtime performance - remove updating all endpoints status on startup

[Richard Mudgett (JIRA)]

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

Richard Mudgett edited comment on ASTERISK-26061 at 7/7/16 1:12 PM:
--------------------------------------------------------------------

So I cleaned up the DB to around 7400 rows in ps_endpoints and ps_aors.

Immediately at startup, here's what I have:
{noformat}
[2016-07-07 11:42:59] DEBUG[30851] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_endpoint_id_ips WHERE id LIKE ? ORDER BY id
[2016-07-07 11:42:59] DEBUG[30851] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_endpoints WHERE id = ?
[2016-07-07 11:43:00] DEBUG[28579] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_endpoints WHERE aors LIKE ? ORDER BY aors
[2016-07-07 11:43:02] DEBUG[28579] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_endpoints WHERE aors LIKE ? ORDER BY aors
[2016-07-07 11:43:06] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE contact != ? AND qualify_frequency <= ? ORDER BY contact
[2016-07-07 11:43:06] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE expiration_time > ? AND qualify_frequency <= ? ORDER BY expiration_time
[2016-07-07 11:43:06] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE qualify_frequency > ? ORDER BY qualify_frequency
[2016-07-07 11:43:07] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
{noformat}
Beyond that, here's a rough pattern what I get 
{noformat}
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32267] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_endpoints WHERE aors LIKE ? ORDER BY aors
{noformat}

After Asterisk Ready, here are the total counts:
{noformat}
[root at media-3:/var/log/asterisk#]cat debug | grep "SQL: SELECT \* FROM ps_aors"  | wc -l
5887
[root at media-3:/var/log/asterisk#]cat debug | grep "SQL: SELECT \* FROM ps_contacts"  | wc -l
5915
{noformat}



was (Author: n8ideas):
So I cleaned up the DB to around 7400 rows in ps_endpoints and ps_aors.

Immediately at startup, here's what I have:

[2016-07-07 11:42:59] DEBUG[30851] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_endpoint_id_ips WHERE id LIKE ? ORDER BY id
[2016-07-07 11:42:59] DEBUG[30851] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_endpoints WHERE id = ?
[2016-07-07 11:43:00] DEBUG[28579] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_endpoints WHERE aors LIKE ? ORDER BY aors
[2016-07-07 11:43:02] DEBUG[28579] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_endpoints WHERE aors LIKE ? ORDER BY aors
[2016-07-07 11:43:06] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE contact != ? AND qualify_frequency <= ? ORDER BY contact
[2016-07-07 11:43:06] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE expiration_time > ? AND qualify_frequency <= ? ORDER BY expiration_time
[2016-07-07 11:43:06] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE qualify_frequency > ? ORDER BY qualify_frequency
[2016-07-07 11:43:07] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id

Beyond that, here's a rough pattern what I get 

[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:23] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_aors WHERE id = ?
[2016-07-07 11:46:24] DEBUG[32233] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_contacts WHERE id LIKE ? ORDER BY id
[2016-07-07 11:46:24] DEBUG[32267] res_config_odbc.c: Skip: 0; SQL: SELECT * FROM ps_endpoints WHERE aors LIKE ? ORDER BY aors

After Asterisk Ready, here are the total counts:

[root at media-3:/var/log/asterisk#]cat debug | grep "SQL: SELECT \* FROM ps_aors"  | wc -l
5887
[root at media-3:/var/log/asterisk#]cat debug | grep "SQL: SELECT \* FROM ps_contacts"  | wc -l
5915


> [patch] res_pjsip: improve realtime performance - remove updating all endpoints status on startup
> -------------------------------------------------------------------------------------------------
>
>                 Key: ASTERISK-26061
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-26061
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: Resources/res_pjsip
>    Affects Versions: 13.9.1
>            Reporter: Alexei Gradinari
>
> The patch removes updating all Endpoints' status on startup.
> Instead, only non-qualified aors with static contact
> and non-qualified non-expired contacts are retrieved from the realtime to
> update the endpoint status to ONLINE.
> The endpoint name was added to the contact object to simply find the endpoint
> that created this contact.
> The status of endpoints with qualified aors will be updated by 'qualify'
> functions.



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