Dynamic realtime object loading on startup

[gordon_grech]

Hello everyone,

In my setup, I am loading PJSIP objects using Dynamic Realtime. Below is an extract of sorcery.conf, most other object mappings follow the same pattern;

endpoint/cache = memory_cache,object_lifetime_stale=3600,object_lifetime_maximum=7200
endpoint=config,pjsip.conf,criteria=type=endpoint
endpoint=realtime,ps_endpoints

From SQL query log files, I noticed that each time I reload Asterisk, a generic select statement is done to get all endpoints, followed by a select for each endpoint;

2021-06-04T16:31:28.753244Z 46452 Prepare SELECT * FROM ps_endpoints WHERE id LIKE ? ORDER BY id
2021-06-04T16:31:28.796128Z 46452 Execute SELECT * FROM ps_endpoints WHERE id LIKE ‘%’ ORDER BY id
2021-06-04T16:31:28.837927Z 46452 Close stmt
2021-06-04T16:31:28.879978Z 46452 Prepare SELECT * FROM ps_endpoints WHERE id LIKE ? ORDER BY id
2021-06-04T16:31:28.921308Z 46452 Execute SELECT * FROM ps_endpoints WHERE id LIKE ‘%’ ORDER BY id
2021-06-04T16:31:28.963048Z 46452 Close stmt
2021-06-04T16:31:29.030092Z 46452 Prepare SELECT * FROM ps_endpoints WHERE mailboxes != ? ORDER BY mailboxes
2021-06-04T16:31:29.074673Z 46452 Execute SELECT * FROM ps_endpoints WHERE mailboxes != ‘’ ORDER BY mailboxes
2021-06-04T16:31:29.114880Z 46452 Close stmt
2021-06-04T16:31:29.165775Z 46452 Prepare SELECT * FROM ps_endpoints WHERE id LIKE ? ORDER BY id
2021-06-04T16:31:29.206509Z 46452 Execute SELECT * FROM ps_endpoints WHERE id LIKE ‘%’ ORDER BY id
2021-06-04T16:31:29.248157Z 46452 Close stmt
2021-06-04T16:31:29.288719Z 46452 Prepare SELECT * FROM ps_endpoints WHERE id = ?
2021-06-04T16:31:29.329333Z 46452 Execute SELECT * FROM ps_endpoints WHERE id = ‘boardroom@arkafort.com’
2021-06-04T16:31:29.370251Z 46452 Close stmt

My database is about 30ms away. I have around 300 endpoints at the moment and starting up asterisk takes about 45 seconds. Im planning to have about 3500 endpoints on the server, which would result in a startup time of around 8 to 10 minutes. Not to mention concerns on the database side of things.

Is this expected behaviour or can I in some way optimize this?

Thanking you in advance for your time and consideration.

Regards,
Gordon

[jcolp]

Have you tried enabling full backend caching? That will cache the entirety and eliminate database queries except when the full refresh occurs.

[gordon_grech]

Yes I have, however I have observed the same behaviour. The same thing happens even if I disable caching entirely.

[jcolp]

Then I do not know. I am aware that such things are in production at various places, so it is possible. Nothing in your configuration stands out.

I will say though that you really really really really really really want to avoid a non-local database with any latency.

[sedwards]

I run a ‘master’ MySQL database server and each Asterisk server runs MySQL as well.

Each hour, the ‘clients’ refresh their volatile tables (3 tables, about 600 rows) and each hour the master sucks out their CDRs.

The clients are not dependent on the master for processing calls. An interruption in connectivity means data may be a little stale, but less impactful than not processing calls

[gordon_grech]

Good day. Thank you very much for the insight and ideas.

Im using Asterisk as part of a geo-distributed platform and hence the latency. However, the idea of keeping the database local is indeed the way forward.

I will be doing some tests with async replication and even with manually synchronisation and will keep this thread updated with my findings.

Nice weekend.

[gordon_grech]

Hello everyone,

So In the end we redesigned the approach we were taking to localize the database.

Instead of coupling our API database with Asterisk’s we created a messaging system using RabbitMQ to push realtime object events (create/update/delete) to various Asterisk servers in different locations. This approach makes a lot more sense than the previous as it completely decoupled our application from Asterisk thus allowing the Asterisk database to be on the same server and achieving the desired result.

Obviously the task in non-trivial as an entire service had to be written to manage the process but well worth the effort (although a lot more work is still required )