1

Dears,

I have got an Opensips server which make queries to an mysql server. I need to optimice these queries at maximum.

One way could be: using mysql table with engine=memory and index=hash, but ¿how to load these type of table at the init of the opensips server?

Another way: ¿is there a function in Opensips server to cache data of previous querys in order to recover them later?

Kind regards, Tentenpie

Liviu Chircu
  • 1,000
  • 3
  • 11
  • 24
Tentenpie
  • 11
  • 2
  • 1
    If you are dealing with a table containing international prefix numbers, I assume there are no more than a couple of hundred entries. Why don't you load the hash table at program initialization? – JPG May 27 '19 at 15:06
  • Thanks for your response. Sorry, i don't know how to do that. – Tentenpie May 27 '19 at 15:19
  • Show the query you are running and the table structure(s). Also, what indexes do you have? – The Impaler May 27 '19 at 15:22
  • CREATE TABLE IF NOT EXISTS PrefixPerCountry ( Country CHAR(255), C_Prefix CHAR(255) NOT NULL, Prefix CHAR(255) NOT NULL, PRIMARY KEY (C_Prefix) ) ENGINE=MEMORY; – Tentenpie May 27 '19 at 15:42
  • CREATE UNIQUE INDEX idx_I_Prefixes ON PrefixPerCountry (C_Prefix) USING HASH; – Tentenpie May 27 '19 at 15:44
  • Well, let me clarify: ¿how to init that table type 'Memory/hash' in my Opensips server ? – Tentenpie May 29 '19 at 09:33

1 Answers1

0

Starting with version 2.2, OpenSIPS offers the sql_cacher module. You can use it in order to cache MySQL tables and achieve direct in-memory lookups for the keys of your choosing through the $sql_cached_value variable.

Cached tables are periodically refreshed according to the reload_interval setting of the module.

If you consider sql_cacher to be too complex for your needs, then you can still maintain a high traffic throughput for your SIP proxy while also doing blocking MySQL queries by making all of these queries asynchronous, using the async(avp_db_query()) construct.

Liviu Chircu
  • 1,000
  • 3
  • 11
  • 24
  • Thanks Liviu for your response. I am going to have a look to your very well linked docs in opensips and try a code for the purpose. Regards, – Tentenpie May 30 '19 at 06:59