Hi all, I just found the problem ;-)

The column in ported table was BIGINT but sqlops and avops take only string or int. I changed the colunm id table from BIGINT to INT and everything works fine! kamailio Rock's!!

Alex

On Mon, Dec 21, 2009 at 8:17 PM, alex pappas rebel.pappas@gmail.com wrote:

Hi Alex,

I tried :

if(!is_avp_set("$avp(s:lnp_prefix)")){ xlog("alx ------- The new rU $rU with prefix $avp(s:lnp_prefix) return code:$retcode ----------"); }

and it looks like is doing to right thing BUT why I don't see the avp value? I need this value to added in the front of rU as portability prefix..

Any comments about the sqlops ?

Thank

Alex

On Mon, Dec 21, 2009 at 6:37 PM, Alex Balashov abalashov@evaristesys.comwrote:

...

Alex,

Try: if(is_avp_set("$avp(s:lnp_prefix)"))

-- Sent from mobile device

On Dec 21, 2009, at 11:19 AM, alex pappas rebel.pappas@gmail.com wrote:

Hi all,

...

I'm trying to make an sql query through my kamailio script in order to check for number portability. i have a table with 2 col (phonenumber,prefix). I'm trying both modules avpops and sqlops but I'm not able to retreive any data.

modparam("avpops", "db_url","mysql://openser:openserrw@localhost /openser")

avp_db_query("SELECT prefix FROM ported WHERE phonenumber='$rU'","$avp(s:lnp_prefix)") ;The query run perfectly in command line.

The return code is always 1 but when I print $avp(s:lnp_prefix) I get no result.

I have 2 questions :

1. Which is the best way to check portability in my case
2. What I'm doing wrong and I'm not getting any result from the query.

Thank you ALL

Alex

---

Kamailio (OpenSER) - Users mailing list Users@lists.kamailio.org http://lists.kamailio.org/cgi-bin/mailman/listinfo/users http://lists.openser-project.org/cgi-bin/mailman/listinfo/users

Alex, What do you suggest for faster sql queries with mysqlp : avpops or sqlops ?

Cheers Alex

On Mon, Dec 21, 2009 at 9:55 PM, Alex Balashov abalashov@evaristesys.comwrote:

On 12/21/2009 02:51 PM, alex pappas wrote:

The column in ported table was BIGINT but sqlops and avops take only

...

string or int. I changed the colunm id table from BIGINT to INT and everything works fine! kamailio Rock's!!

Ah, yes, I have commonly run into this one as well. Yes, the database interface layers generally support only canonical scalar types - basic integer and varchar, as well as types that offer ready-made convertabiliy in a textual evaluation context (e.g. PostgreSQL boolean type, 't' or 'f').

No bigints unless you cast down to int. :)

-- Alex Balashov - Principal Evariste Systems Web : http://www.evaristesys.com/ Tel : (+1) (678) 954-0670 Direct : (+1) (678) 954-0671

On Mon, December 21, 2009 9:27 pm, Alex Balashov wrote:

[..] In short, I don't think sqlops is materially faster, but it is a more modern and sophisticated way to talk to your database and you should use it for that reason.

Hey Alex,

as AVPs are also more or less just a special kind of pseudo-variable, like the exported interface from the sqlops module there should be indeed no notable speed difference, especially on today fast systems. But indeed sqlops have the more appropriate interface and I would recommend it for new deployments, for the reasons you summarized.

Best regards,

Henning

On Mon, December 21, 2009 9:50 pm, Alex Balashov wrote:

[..] Having said that, AVPs are still very useful and necessary because they persist across the lifetime of a transaction, so you can access them in subsequent reply and failure routes. This is not true of user pseudovariables ($var(...)), nor, as far as I know, of any other class of pseudovariable at this point except special transaction-persistent ones exported by modules.

As a result, it is often necessary for me to take data from $dbr (sqlops) and load it into arrays of AVPs, such as for example when doing custom fail-over in failure_route that uses logic for which dispatcher is not suitable. I store a set of gateways in an AVP array along with another AVP value indicating the current array index and iterate over them in failure_route, and so on.

Daniel taught me how to use AVP arrays once, although I noticed this is not mentioned anywhere in the avpops documentation. It should be published somewhere, as it is one of the most useful programmatic features of Kamailio.

Henning/Daniel, does Kamailio 3/SR offer any kind of better substitute for what I am doing with AVPs as described above? In other words, are there any transaction-persistent pseudovariables and/or namespace containers that are easier to deal with syntactically, and provide more advanced data structure primitives like arrays? I am aware of htable, but I need something local to a transaction, not global.

There are the $var PVs you already mentioned, and then also the $shv that are shared variables. But they are (process) global, and not transactional. Daniel proposed and developed the new xavp type for sr which are basically extended AVPs. More informations can be found here: http://sip-router.org/wiki/devel/xavp

Regards,

Henning

On Tuesday 22 December 2009 12:25:52 alex pappas wrote:

Hi all,

Concerning portability again :-)

Is there any way I can add in the memory all the numbers with portability? (2.000.000 rows)? And also reload these data a few times a day and access this data from the Kamailio script?

Cheers Alex

You could setup the PDB module, that does exactly that ... number portability management

On Tuesday 22 December 2009 13:06:44 alex pappas wrote:

Hi,

I use kamailio 1.5 and there is no such a module in that version

AFAIK you could get the pdb module from kamailio 3.0 and compile it on 1.5 ... there are no strange dependencies ..

Hi all,

I will check that. In my company already are using a oracle database for portability for other systems, so I was thinking maybe I can try and query that DB with sqlops. I tried testing with mysql but after 30 CPS i had very bad stats. (sipp test)

Cheers Alex

On Tue, Dec 22, 2009 at 4:07 PM, Henning Westerholt < henning.westerholt@1und1.de> wrote:

On Tue, December 22, 2009 2:41 pm, Raúl Alexis Betancor Santana wrote:

...

...

I use kamailio 1.5 and there is no such a module in that version

AFAIK you could get the pdb module from kamailio 3.0 and compile it on

1.5

...

... there are no strange dependencies ..

Indeed the module should work more or less unchanged in kamailio 1.5. The only thing that probably make problems are some of the sr specific defines, but you could probably just remove them.

But if you use "only" 2.000.000 rows you should be able to use just a normal database, if the machine has enough RAM it should be also more then fast enough. The pdb module works with a dedicated pdb server, which supports advanced optimizations and is especially optimized for the portability lookup task, but its of course a bit more difficult to setup.

Regards,

Henning

Henning hi,

In the server I'm running Kamailio I have a good CPU(4 cores) and 2G memory but for reading or writing to the disk i have a big problem.Is a blade server with very bad disk which they do also software raid 1. Even if I try to write in the log file of kamailio I get such a high load that I cannot pass the 10 CPS. This is the reason on why I try through a network connection. Apparently i found out that also the second server that I'm writng in mysql is the same blade so now I will try to get data from the Oracle which is a good server. In testing I had with sipp and without reading or writing to disk I had 1.000CPS and 300.000 calls without any problem.

Thanks fo rhelping me

Cheers Alex

On Tue, Dec 22, 2009 at 9:46 PM, Henning Westerholt < henning.westerholt@1und1.de> wrote:

On Tue, December 22, 2009 3:33 pm, alex pappas wrote:

...

I will check that. In my company already are using a oracle database for portability for other systems, so I was thinking maybe I can try and query that DB with sqlops. I tried testing with mysql but after 30 CPS i had very bad

stats. > (sipp test)

Hey Alex,

well, 30 CPS are not that much. This looks more like a configuration problem or a slow network connection to the DB server. You could elevate this by using a local read-only replication copy on the localhost, which is easy to setup with mysql.

If you want to query directly the oracle DB you could use the db_unixodbc module via ODBC, or giving the db_oracle module a try.

Regards,

Henning

On 12/24/2009 06:59 AM, Henning Westerholt wrote:

On Wed, December 23, 2009 9:52 am, alex pappas wrote:

...

In the server I'm running Kamailio I have a good CPU(4 cores) and 2G memory but for reading or writing to the disk i have a big problem.Is a blade server with very bad disk which they do also software raid 1. Even if I try to write in the log file of kamailio I get such a high load that I cannot pass the 10 CPS.

Hey Alex,

ok, this is indeed a really bad disk. :) Regarding the slow logging, you could check the syslog configuration if its configured to write asynchronously (there should be a "-" in front of the log statement in syslog.conf). And also check if you perhaps not use a to high log level in the kamailio cfg.

Oh, so that's what the '-' does. Thanks Henning, one learns something new every day from you. :-)

Alex - this server is not a Dell Poweredge 2850/2950 with a PERC 5i, is it? I have run into a bug on a few of these where I/O causes enormous blocking/waiting, and discovered it by turning off syslogging and watching the 'waiting' portion of the load to go down. It was necessary to replace the defective PERC to fix the problem.

Hello,

On 12/22/09 1:25 PM, alex pappas wrote:

Hi all,

Concerning portability again :-)

Is there any way I can add in the memory all the numbers with portability? (2.000.000 rows)? And also reload these data a few times a day and access this data from the Kamailio script?

while I advise usage of sqlops/sql_query() instead of avpops/avp_db_query(), if you want to load such data in memory should be in shared memory. For that look at htable or maybe you can re-use pdt.

Cheers, Daniel

Cheers Alex

On Tue, Dec 22, 2009 at 12:56 AM, Henning Westerholt <henning.westerholt@1und1.de mailto:henning.westerholt@1und1.de> wrote:

On Mon, December 21, 2009 9:50 pm, Alex Balashov wrote:
> [..]
> Having said that, AVPs are still very useful and necessary
because they
> persist across the lifetime of a transaction, so you can access
them in
> subsequent reply and failure routes.  This is not true of user
> pseudovariables ($var(...)), nor, as far as I know, of any other
class
> of pseudovariable at this point except special
transaction-persistent
> ones exported by modules.
>
> As a result, it is often necessary for me to take data from $dbr
> (sqlops) and load it into arrays of AVPs, such as for example
when doing
> custom fail-over in failure_route that uses logic for which
dispatcher
> is not suitable.  I store a set of gateways in an AVP array
along with
> another AVP value indicating the current array index and iterate
over
> them in failure_route, and so on.
>
> Daniel taught me how to use AVP arrays once, although I noticed
this is
> not mentioned anywhere in the avpops documentation.  It should be
> published somewhere, as it is one of the most useful programmatic
> features of Kamailio.
>
> Henning/Daniel, does Kamailio 3/SR offer any kind of better
substitute
> for what I am doing with AVPs as described above?  In other
words, are
> there any transaction-persistent pseudovariables and/or namespace
> containers that are easier to deal with syntactically, and
provide more
> advanced data structure primitives like arrays?  I am aware of
htable,
> but I need something local to a transaction, not global.

There are the $var PVs you already mentioned, and then also the
$shv that
are shared variables. But they are (process) global, and not
transactional. Daniel proposed and developed the new xavp type for sr
which are basically extended AVPs. More informations can be found
here:
http://sip-router.org/wiki/devel/xavp

Regards,

Henning

---

Kamailio (OpenSER) - Users mailing list Users@lists.kamailio.org http://lists.kamailio.org/cgi-bin/mailman/listinfo/users http://lists.openser-project.org/cgi-bin/mailman/listinfo/users

Hi all,

It is possible to have some help in defining the hash table?

I'm giving some parameters but some of them i don't really understand them. my configuration is:

# --------- Htable --------- modparam("htable", "htable", "a=>dbtable=ported1;") modparam("htable", "db_url", "mysql://pskoul:pskoul123@localhost/openser") modparam("htable", "key_name_column", "phonenumber") modparam("htable", "key_value", "prefix") modparam("htable", "fetch_rows", 1000)

The table I want to htable is a 2 cols table (phonenumber,prefix) In mysql they are defined as varchar(10). All the phonenumber values are unique so the key in htable is phonenumber with value in prefix.

Thanks All,

Alex

On Wed, Dec 23, 2009 at 1:04 PM, alex pappas rebel.pappas@gmail.com wrote:

Thank you Daniel,

I will try first oracle db and then htable which is also very interesting. When I'm done I will get back with the results.

Cheers Alex

On Wed, Dec 23, 2009 at 11:44 AM, Daniel-Constantin Mierla < miconda@gmail.com> wrote:

...

Hello,

On 12/22/09 1:25 PM, alex pappas wrote:

Hi all,

Concerning portability again :-)

Is there any way I can add in the memory all the numbers with portability? (2.000.000 rows)? And also reload these data a few times a day and access this data from the Kamailio script?

while I advise usage of sqlops/sql_query() instead of avpops/avp_db_query(), if you want to load such data in memory should be in shared memory. For that look at htable or maybe you can re-use pdt.

Cheers, Daniel

Cheers Alex

On Tue, Dec 22, 2009 at 12:56 AM, Henning Westerholt < henning.westerholt@1und1.de> wrote:

...

On Mon, December 21, 2009 9:50 pm, Alex Balashov wrote:

...

[..] Having said that, AVPs are still very useful and necessary because they persist across the lifetime of a transaction, so you can access them in subsequent reply and failure routes. This is not true of user pseudovariables ($var(...)), nor, as far as I know, of any other class of pseudovariable at this point except special transaction-persistent ones exported by modules.

As a result, it is often necessary for me to take data from $dbr (sqlops) and load it into arrays of AVPs, such as for example when

doing

...

custom fail-over in failure_route that uses logic for which dispatcher is not suitable. I store a set of gateways in an AVP array along with another AVP value indicating the current array index and iterate over them in failure_route, and so on.

Daniel taught me how to use AVP arrays once, although I noticed this is not mentioned anywhere in the avpops documentation. It should be published somewhere, as it is one of the most useful programmatic features of Kamailio.

Henning/Daniel, does Kamailio 3/SR offer any kind of better substitute for what I am doing with AVPs as described above? In other words, are there any transaction-persistent pseudovariables and/or namespace containers that are easier to deal with syntactically, and provide more advanced data structure primitives like arrays? I am aware of htable, but I need something local to a transaction, not global.

There are the $var PVs you already mentioned, and then also the $shv that are shared variables. But they are (process) global, and not transactional. Daniel proposed and developed the new xavp type for sr which are basically extended AVPs. More informations can be found here: http://sip-router.org/wiki/devel/xavp

Regards,

Henning

---

Kamailio (OpenSER) - Users mailing list Users@lists.kamailio.orghttp://lists.kamailio.org/cgi-bin/mailman/listinfo/usershttp://lists.openser...

-- Daniel-Constantin Mierla

• http://www.asipto.com/

Hi Daniel,

My problem is how to define the hash table in Kamailio.cfg. Especially I don't understand how to define : key_name_column: here I understand that is the name of the key = fist col (phonenumber) key_type_column == string ? value_type_column == string ? key_value_column == second col(prefix)?

I get the following errors:

ERROR:db_mysql:db_mysql_submit_query: driver error on query: Unknown column 'key_type' in 'field list' ERROR:core:db_do_query: error while submitting query ERROR:htable:ht_db_load_table: Error while querying db ERROR:core:init_mod: failed to initialize module htable ERROR:core:main: error while initializing modules

Thank you

Alex

On Wed, Dec 23, 2009 at 6:43 PM, Daniel-Constantin Mierla <miconda@gmail.com

wrote:

Hello,

On 12/23/09 4:28 PM, alex pappas wrote:

Hi all,

It is possible to have some help in defining the hash table?

I'm giving some parameters but some of them i don't really understand them. my configuration is:

# --------- Htable --------- modparam("htable", "htable", "a=>dbtable=ported1;") modparam("htable", "db_url", "mysql://pskoul:pskoul123@localhost/openser") modparam("htable", "key_name_column", "phonenumber") modparam("htable", "key_value", "prefix") modparam("htable", "fetch_rows", 1000)

The table I want to htable is a 2 cols table (phonenumber,prefix) In mysql they are defined as varchar(10). All the phonenumber values are unique so the key in htable is phonenumber with value in prefix.

what don't you understand? I do not know what to explain. Is there any error you get? At least the parameter key_value is wrong, should be key_value_column.

Cheers, Daniel

Thanks All,

Alex

On Wed, Dec 23, 2009 at 1:04 PM, alex pappas rebel.pappas@gmail.comwrote:

...

Thank you Daniel,

I will try first oracle db and then htable which is also very interesting. When I'm done I will get back with the results.

Cheers Alex

On Wed, Dec 23, 2009 at 11:44 AM, Daniel-Constantin Mierla < miconda@gmail.com> wrote:

...

Hello,

On 12/22/09 1:25 PM, alex pappas wrote:

Hi all,

Concerning portability again :-)

Is there any way I can add in the memory all the numbers with portability? (2.000.000 rows)? And also reload these data a few times a day and access this data from the Kamailio script?

while I advise usage of sqlops/sql_query() instead of avpops/avp_db_query(), if you want to load such data in memory should be in shared memory. For that look at htable or maybe you can re-use pdt.

Cheers, Daniel

Cheers Alex

On Tue, Dec 22, 2009 at 12:56 AM, Henning Westerholt < henning.westerholt@1und1.de> wrote:

...

On Mon, December 21, 2009 9:50 pm, Alex Balashov wrote:

...

[..] Having said that, AVPs are still very useful and necessary because

they

...

persist across the lifetime of a transaction, so you can access them

in

...

subsequent reply and failure routes. This is not true of user pseudovariables ($var(...)), nor, as far as I know, of any other class of pseudovariable at this point except special transaction-persistent ones exported by modules.

As a result, it is often necessary for me to take data from $dbr (sqlops) and load it into arrays of AVPs, such as for example when

doing

...

custom fail-over in failure_route that uses logic for which dispatcher is not suitable. I store a set of gateways in an AVP array along with another AVP value indicating the current array index and iterate over them in failure_route, and so on.

Daniel taught me how to use AVP arrays once, although I noticed this

is

...

not mentioned anywhere in the avpops documentation. It should be published somewhere, as it is one of the most useful programmatic features of Kamailio.

Henning/Daniel, does Kamailio 3/SR offer any kind of better substitute for what I am doing with AVPs as described above? In other words, are there any transaction-persistent pseudovariables and/or namespace containers that are easier to deal with syntactically, and provide

more

...

advanced data structure primitives like arrays? I am aware of htable, but I need something local to a transaction, not global.

There are the $var PVs you already mentioned, and then also the $shv that are shared variables. But they are (process) global, and not transactional. Daniel proposed and developed the new xavp type for sr which are basically extended AVPs. More informations can be found here: http://sip-router.org/wiki/devel/xavp

Regards,

Henning

---

Kamailio (OpenSER) - Users mailing list Users@lists.kamailio.orghttp://lists.kamailio.org/cgi-bin/mailman/listinfo/usershttp://lists.openser...

-- Daniel-Constantin Mierla

• http://www.asipto.com/

---

Kamailio (OpenSER) - Users mailing listUsers@lists.kamailio.orghttp://lists.kamailio.org/cgi-bin/mailman/listinfo/usershttp://lists.openser...

-- Daniel-Constantin Mierla

• http://www.asipto.com/

Thank you Daniel,

Cheers Alex

On Thu, Dec 24, 2009 at 4:05 PM, Daniel-Constantin Mierla <miconda@gmail.com

wrote:

Hi Alex,

the default table structure for htable is defined by sql:

CREATE TABLE htable ( id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, key_name VARCHAR(64) DEFAULT '' NOT NULL, key_type INT DEFAULT 0 NOT NULL, value_type INT DEFAULT 0 NOT NULL, key_value VARCHAR(128) DEFAULT '' NOT NULL ) ENGINE=MyISAM;

If you map over another table, then you must have at least four columns there (id is for generic purpose). See here descriptions of the columns: http://kamailio.org/docs/modules/3.0.x/modules_k/htable.html#id2529061

Cheers, Daniel

On 12/23/09 6:13 PM, alex pappas wrote:

Hi Daniel,

My problem is how to define the hash table in Kamailio.cfg. Especially I don't understand how to define : key_name_column: here I understand that is the name of the key = fist col (phonenumber) key_type_column == string ? value_type_column == string ? key_value_column == second col(prefix)?

I get the following errors:

ERROR:db_mysql:db_mysql_submit_query: driver error on query: Unknown column 'key_type' in 'field list' ERROR:core:db_do_query: error while submitting query ERROR:htable:ht_db_load_table: Error while querying db ERROR:core:init_mod: failed to initialize module htable ERROR:core:main: error while initializing modules

Thank you

Alex

On Wed, Dec 23, 2009 at 6:43 PM, Daniel-Constantin Mierla < miconda@gmail.com> wrote:

...

Hello,

On 12/23/09 4:28 PM, alex pappas wrote:

Hi all,

It is possible to have some help in defining the hash table?

I'm giving some parameters but some of them i don't really understand them. my configuration is:

# --------- Htable --------- modparam("htable", "htable", "a=>dbtable=ported1;") modparam("htable", "db_url", "mysql://pskoul:pskoul123@localhost /openser") modparam("htable", "key_name_column", "phonenumber") modparam("htable", "key_value", "prefix") modparam("htable", "fetch_rows", 1000)

The table I want to htable is a 2 cols table (phonenumber,prefix) In mysql they are defined as varchar(10). All the phonenumber values are unique so the key in htable is phonenumber with value in prefix.

what don't you understand? I do not know what to explain. Is there any error you get? At least the parameter key_value is wrong, should be key_value_column.

Cheers, Daniel

Thanks All,

Alex

On Wed, Dec 23, 2009 at 1:04 PM, alex pappas rebel.pappas@gmail.comwrote:

...

Thank you Daniel,

I will try first oracle db and then htable which is also very interesting. When I'm done I will get back with the results.

Cheers Alex

On Wed, Dec 23, 2009 at 11:44 AM, Daniel-Constantin Mierla < miconda@gmail.com> wrote:

...

Hello,

On 12/22/09 1:25 PM, alex pappas wrote:

Hi all,

Concerning portability again :-)

Is there any way I can add in the memory all the numbers with portability? (2.000.000 rows)? And also reload these data a few times a day and access this data from the Kamailio script?

while I advise usage of sqlops/sql_query() instead of avpops/avp_db_query(), if you want to load such data in memory should be in shared memory. For that look at htable or maybe you can re-use pdt.

Cheers, Daniel

Cheers Alex

On Tue, Dec 22, 2009 at 12:56 AM, Henning Westerholt < henning.westerholt@1und1.de> wrote:

...

On Mon, December 21, 2009 9:50 pm, Alex Balashov wrote:

...

[..] Having said that, AVPs are still very useful and necessary because

they

...

persist across the lifetime of a transaction, so you can access them

in

...

subsequent reply and failure routes. This is not true of user pseudovariables ($var(...)), nor, as far as I know, of any other

class

...

of pseudovariable at this point except special transaction-persistent ones exported by modules.

As a result, it is often necessary for me to take data from $dbr (sqlops) and load it into arrays of AVPs, such as for example when

doing

...

custom fail-over in failure_route that uses logic for which

dispatcher

...

is not suitable. I store a set of gateways in an AVP array along

with

...

another AVP value indicating the current array index and iterate over them in failure_route, and so on.

Daniel taught me how to use AVP arrays once, although I noticed this

is

...

not mentioned anywhere in the avpops documentation. It should be published somewhere, as it is one of the most useful programmatic features of Kamailio.

Henning/Daniel, does Kamailio 3/SR offer any kind of better

substitute

...

for what I am doing with AVPs as described above? In other words,

are

...

there any transaction-persistent pseudovariables and/or namespace containers that are easier to deal with syntactically, and provide

more

...

advanced data structure primitives like arrays? I am aware of

htable,

...

but I need something local to a transaction, not global.

There are the $var PVs you already mentioned, and then also the $shv that are shared variables. But they are (process) global, and not transactional. Daniel proposed and developed the new xavp type for sr which are basically extended AVPs. More informations can be found here: http://sip-router.org/wiki/devel/xavp

Regards,

Henning

---

Kamailio (OpenSER) - Users mailing list Users@lists.kamailio.orghttp://lists.kamailio.org/cgi-bin/mailman/listinfo/usershttp://lists.openser...

-- Daniel-Constantin Mierla

• http://www.asipto.com/

---

Kamailio (OpenSER) - Users mailing listUsers@lists.kamailio.orghttp://lists.kamailio.org/cgi-bin/mailman/listinfo/usershttp://lists.openser...

-- Daniel-Constantin Mierla

• http://www.asipto.com/

---

Kamailio (OpenSER) - Users mailing listUsers@lists.kamailio.orghttp://lists.kamailio.org/cgi-bin/mailman/listinfo/usershttp://lists.openser...

-- Daniel-Constantin Mierla

• http://www.asipto.com/

Hi Alex,

On 12/24/09 6:18 PM, alex pappas wrote:

Daniel,

The reason that I was NOT understanding how to implement the htable is that even I read the all internet ;-) I did not see the already setup table Htable in my mysql server which was already made from the installation and that's why the value type and key types did not make sense!! now i fill a bit stupid :-(

I apologize for stilling you time.

no need for that! If I have no time I do not answer. Sometime even obvious things are not seen, happens to me, this is life ...

Merry Christmas! Daniel

PS. or do you celebrate Christmas in January, using the old calendar date?

Cheers Alex

On Thu, Dec 24, 2009 at 4:36 PM, alex pappas <rebel.pappas@gmail.com mailto:rebel.pappas@gmail.com> wrote:

Thank you Daniel,

Cheers
Alex


On Thu, Dec 24, 2009 at 4:05 PM, Daniel-Constantin Mierla
<miconda@gmail.com <mailto:miconda@gmail.com>> wrote:

    Hi Alex,

    the default table structure for htable is defined by sql:

    CREATE TABLE htable (
        id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
        key_name VARCHAR(64) DEFAULT '' NOT NULL,
        key_type INT DEFAULT 0 NOT NULL,
        value_type INT DEFAULT 0 NOT NULL,
        key_value VARCHAR(128) DEFAULT '' NOT NULL
    ) ENGINE=MyISAM;

    If you map over another table, then you must have at least
    four columns there (id is for generic purpose). See here
    descriptions of the columns:
    http://kamailio.org/docs/modules/3.0.x/modules_k/htable.html#id2529061

    Cheers,
    Daniel


    On 12/23/09 6:13 PM, alex pappas wrote:

...

    Hi Daniel,

    My problem is how to define the hash table in Kamailio.cfg.
    Especially I don't understand how to define :


          |key_name_column: here I understand that is the name of
          the key = fist col (phonenumber)
          |


          |key_type_column == string ?
          |


          |value_type_column == string ?
          |


          |key_value_column == second col(prefix)?
          |



    I get the following errors:

    ERROR:db_mysql:db_mysql_submit_query: driver error on query:
    Unknown column 'key_type' in 'field list'
    ERROR:core:db_do_query: error while submitting query
    ERROR:htable:ht_db_load_table: Error while querying db
    ERROR:core:init_mod: failed to initialize module htable
    ERROR:core:main: error while initializing modules

    Thank you

    Alex



    On Wed, Dec 23, 2009 at 6:43 PM, Daniel-Constantin Mierla
    <miconda@gmail.com <mailto:miconda@gmail.com>> wrote:

        Hello,


        On 12/23/09 4:28 PM, alex pappas wrote:

...

        Hi all,

        It is possible to have some help in defining the hash table?

        I'm giving some parameters but some of them i don't
        really understand them. my configuration is:

        # --------- Htable ---------
        modparam("htable", "htable", "a=>dbtable=ported1;")
        modparam("htable", "db_url",
        "mysql://pskoul:pskoul123@localhost/openser")
        modparam("htable", "key_name_column", "phonenumber")
        modparam("htable", "key_value", "prefix")
        modparam("htable", "fetch_rows", 1000)

        The table I want to htable is a 2 cols table
        (phonenumber,prefix) In mysql they are defined as
        varchar(10). All the phonenumber values are unique so
        the key in htable is phonenumber with value in prefix.

        what don't you understand? I do not know what to explain.
        Is there any error you get? At least the parameter
        key_value is wrong, should be key_value_column.

        Cheers,
        Daniel

...

        Thanks All,

        Alex



        On Wed, Dec 23, 2009 at 1:04 PM, alex pappas
        <rebel.pappas@gmail.com <mailto:rebel.pappas@gmail.com>>
        wrote:

            Thank you Daniel,

            I will try first oracle db and then htable which is
            also very interesting. When I'm done I will get back
            with the results.

            Cheers
            Alex




            On Wed, Dec 23, 2009 at 11:44 AM, Daniel-Constantin
            Mierla <miconda@gmail.com
            <mailto:miconda@gmail.com>> wrote:

                Hello,


                On 12/22/09 1:25 PM, alex pappas wrote:

...

                Hi all,

                Concerning portability again :-)

                Is there any way I can add in the memory all
                the numbers with portability? (2.000.000 rows)?
                And also reload these data a few times a day
                and access this data from the  Kamailio script?

                while I advise usage of sqlops/sql_query()
                instead of avpops/avp_db_query(), if you want to
                load such data in memory should be in shared
                memory. For that look at htable or maybe you can
                re-use pdt.

                Cheers,
                Daniel

...

                 Cheers
                Alex



                On Tue, Dec 22, 2009 at 12:56 AM, Henning
                Westerholt <henning.westerholt@1und1.de
                <mailto:henning.westerholt@1und1.de>> wrote:

                    On Mon, December 21, 2009 9:50 pm, Alex
                    Balashov wrote:
                    > [..]
                    > Having said that, AVPs are still very
                    useful and necessary because they
                    > persist across the lifetime of a
                    transaction, so you can access them in
                    > subsequent reply and failure routes.
                     This is not true of user
                    > pseudovariables ($var(...)), nor, as far
                    as I know, of any other class
                    > of pseudovariable at this point except
                    special transaction-persistent
                    > ones exported by modules.
                    >
                    > As a result, it is often necessary for me
                    to take data from $dbr
                    > (sqlops) and load it into arrays of AVPs,
                    such as for example when doing
                    > custom fail-over in failure_route that
                    uses logic for which dispatcher
                    > is not suitable.  I store a set of
                    gateways in an AVP array along with
                    > another AVP value indicating the current
                    array index and iterate over
                    > them in failure_route, and so on.
                    >
                    > Daniel taught me how to use AVP arrays
                    once, although I noticed this is
                    > not mentioned anywhere in the avpops
                    documentation.  It should be
                    > published somewhere, as it is one of the
                    most useful programmatic
                    > features of Kamailio.
                    >
                    > Henning/Daniel, does Kamailio 3/SR offer
                    any kind of better substitute
                    > for what I am doing with AVPs as
                    described above?  In other words, are
                    > there any transaction-persistent
                    pseudovariables and/or namespace
                    > containers that are easier to deal with
                    syntactically, and provide more
                    > advanced data structure primitives like
                    arrays?  I am aware of htable,
                    > but I need something local to a
                    transaction, not global.

                    There are the $var PVs you already
                    mentioned, and then also the $shv that
                    are shared variables. But they are
                    (process) global, and not
                    transactional. Daniel proposed and
                    developed the new xavp type for sr
                    which are basically extended AVPs. More
                    informations can be found here:
                    http://sip-router.org/wiki/devel/xavp

                    Regards,

                    Henning



                _______________________________________________
                Kamailio (OpenSER) - Users mailing list
                Users@lists.kamailio.org
                <mailto:Users@lists.kamailio.org>
                http://lists.kamailio.org/cgi-bin/mailman/listinfo/users
                http://lists.openser-project.org/cgi-bin/mailman/listinfo/users

                -- 
                Daniel-Constantin Mierla
                *http://www.asipto.com/





        _______________________________________________
        Kamailio (OpenSER) - Users mailing list
        Users@lists.kamailio.org  <mailto:Users@lists.kamailio.org>
        http://lists.kamailio.org/cgi-bin/mailman/listinfo/users
        http://lists.openser-project.org/cgi-bin/mailman/listinfo/users

        -- 
        Daniel-Constantin Mierla
        *http://www.asipto.com/




    _______________________________________________
    Kamailio (OpenSER) - Users mailing list
    Users@lists.kamailio.org  <mailto:Users@lists.kamailio.org>
    http://lists.kamailio.org/cgi-bin/mailman/listinfo/users
    http://lists.openser-project.org/cgi-bin/mailman/listinfo/users

    -- 
    Daniel-Constantin Mierla
    *http://www.asipto.com/

Hello,

On 12/29/09 3:20 PM, alex pappas wrote:

Dear friends,

Happy holidays :-)

I'm trying to add the portability database in an htable hash table. When I have 200.000 rows I can add these rows to the hash table in memory but when they exid that number then i usually get the error :

ERROR:db_mysql:db_mysql_submit_query: driver error on query: Lost connection to MySQL server

mysql module has option to auto-reconnect which is on by default. If the connection is lost then something happens with the mysql server, do you get something in the syslog for mysql daemon?

From what i found till now the htable is siting in shared memory.(corect me if I'm wrong). I'm starting kamailio with private memory 256 and shared memory 2048.

I also did and some fine tuning in mysql server(local):

query_cache_type = 1 query_cache_limit = 1024M query_cache_size = 1024M key_buffer = 64M table_cache = 1024 sort_buffer=4M read_buffer_size=1M max_allowed_packet = 1536M thread_stack = 128K thread_cache_size = 8 And i cached the table by running to the server the command SELECT SQL_CACHE * from htable;

After this fine tuning I don't get any error when I run kamailio BUT when I try to receive any vaule from the htable I get NULL back.(this configuration work's when i have a few values only in the htable)

Now the question is does private memory is involve in this? Is an memory issue or something else?

Private memory is involved only when data is loaded from mysql, after that is moved in shared memory.

There is an MI command that can dump the content of a hash table (you can use kamctl for that) to check if there are some values in it.

Since you have a lot of records, make sure the size of htable is big (14 is max and recommended).

Cheers, Daniel

Thank you for your help Best wishes

Peace! Alex

On Thu, Dec 24, 2009 at 7:31 PM, Daniel-Constantin Mierla <miconda@gmail.com mailto:miconda@gmail.com> wrote:

Hi Alex,


On 12/24/09 6:18 PM, alex pappas wrote:

...

Daniel,

The reason that I was NOT understanding how to implement the
htable is that even I read the all internet ;-) I did not see the
already setup table Htable in my mysql server which was already
made from the installation and  that's why the value type  and
key types did not make sense!! now i fill a bit stupid :-(

I apologize for stilling you time.

no need for that! If I have no time I do not answer. Sometime even
obvious things are not seen, happens to me, this is life ...

Merry Christmas!
Daniel

PS. or do you celebrate Christmas in January, using the old
calendar date?

...

Cheers
Alex



On Thu, Dec 24, 2009 at 4:36 PM, alex pappas
<rebel.pappas@gmail.com <mailto:rebel.pappas@gmail.com>> wrote:

    Thank you Daniel,

    Cheers
    Alex


    On Thu, Dec 24, 2009 at 4:05 PM, Daniel-Constantin Mierla
    <miconda@gmail.com <mailto:miconda@gmail.com>> wrote:

        Hi Alex,

        the default table structure for htable is defined by sql:

        CREATE TABLE htable (
            id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
            key_name VARCHAR(64) DEFAULT '' NOT NULL,
            key_type INT DEFAULT 0 NOT NULL,
            value_type INT DEFAULT 0 NOT NULL,
            key_value VARCHAR(128) DEFAULT '' NOT NULL
        ) ENGINE=MyISAM;

        If you map over another table, then you must have at
        least four columns there (id is for generic purpose). See
        here descriptions of the columns:
        http://kamailio.org/docs/modules/3.0.x/modules_k/htable.html#id2529061

        Cheers,
        Daniel


        On 12/23/09 6:13 PM, alex pappas wrote:

...

        Hi Daniel,

        My problem is how to define the hash table in
        Kamailio.cfg. Especially I don't understand how to define :


              |key_name_column: here I understand that is the
              name of the key = fist col (phonenumber)
              |


              |key_type_column == string ?
              |


              |value_type_column == string ?
              |


              |key_value_column == second col(prefix)?
              |



        I get the following errors:

        ERROR:db_mysql:db_mysql_submit_query: driver error on
        query: Unknown column 'key_type' in 'field list'
        ERROR:core:db_do_query: error while submitting query
        ERROR:htable:ht_db_load_table: Error while querying db
        ERROR:core:init_mod: failed to initialize module htable
        ERROR:core:main: error while initializing modules

        Thank you

        Alex



        On Wed, Dec 23, 2009 at 6:43 PM, Daniel-Constantin
        Mierla <miconda@gmail.com <mailto:miconda@gmail.com>> wrote:

            Hello,


            On 12/23/09 4:28 PM, alex pappas wrote:

...

            Hi all,

            It is possible to have some help in defining the
            hash table?

            I'm giving some parameters but some of them i don't
            really understand them. my configuration is:

            # --------- Htable ---------
            modparam("htable", "htable", "a=>dbtable=ported1;")
            modparam("htable", "db_url",
            "mysql://pskoul:pskoul123@localhost/openser")
            modparam("htable", "key_name_column", "phonenumber")
            modparam("htable", "key_value", "prefix")
            modparam("htable", "fetch_rows", 1000)

            The table I want to htable is a 2 cols table
            (phonenumber,prefix) In mysql they are defined as
            varchar(10). All the phonenumber values are unique
            so the key in htable is phonenumber with value in
            prefix.

            what don't you understand? I do not know what to
            explain. Is there any error you get? At least the
            parameter key_value is wrong, should be
            key_value_column.

            Cheers,
            Daniel

...

            Thanks All,

            Alex



            On Wed, Dec 23, 2009 at 1:04 PM, alex pappas
            <rebel.pappas@gmail.com
            <mailto:rebel.pappas@gmail.com>> wrote:

                Thank you Daniel,

                I will try first oracle db and then htable
                which is also very interesting. When I'm done I
                will get back with the results.

                Cheers
                Alex




                On Wed, Dec 23, 2009 at 11:44 AM,
                Daniel-Constantin Mierla <miconda@gmail.com
                <mailto:miconda@gmail.com>> wrote:

                    Hello,


                    On 12/22/09 1:25 PM, alex pappas wrote:

...

                    Hi all,

                    Concerning portability again :-)

                    Is there any way I can add in the memory
                    all the numbers with portability?
                    (2.000.000 rows)?
                    And also reload these data a few times a
                    day and access this data from the 
                    Kamailio script?

                    while I advise usage of sqlops/sql_query()
                    instead of avpops/avp_db_query(), if you
                    want to load such data in memory should be
                    in shared memory. For that look at htable
                    or maybe you can re-use pdt.

                    Cheers,
                    Daniel

...

                     Cheers
                    Alex



                    On Tue, Dec 22, 2009 at 12:56 AM, Henning
                    Westerholt <henning.westerholt@1und1.de
                    <mailto:henning.westerholt@1und1.de>> wrote:

                        On Mon, December 21, 2009 9:50 pm,
                        Alex Balashov wrote:
                        > [..]
                        > Having said that, AVPs are still
                        very useful and necessary because they
                        > persist across the lifetime of a
                        transaction, so you can access them in
                        > subsequent reply and failure routes.
                         This is not true of user
                        > pseudovariables ($var(...)), nor, as
                        far as I know, of any other class
                        > of pseudovariable at this point
                        except special transaction-persistent
                        > ones exported by modules.
                        >
                        > As a result, it is often necessary
                        for me to take data from $dbr
                        > (sqlops) and load it into arrays of
                        AVPs, such as for example when doing
                        > custom fail-over in failure_route
                        that uses logic for which dispatcher
                        > is not suitable.  I store a set of
                        gateways in an AVP array along with
                        > another AVP value indicating the
                        current array index and iterate over
                        > them in failure_route, and so on.
                        >
                        > Daniel taught me how to use AVP
                        arrays once, although I noticed this is
                        > not mentioned anywhere in the avpops
                        documentation.  It should be
                        > published somewhere, as it is one of
                        the most useful programmatic
                        > features of Kamailio.
                        >
                        > Henning/Daniel, does Kamailio 3/SR
                        offer any kind of better substitute
                        > for what I am doing with AVPs as
                        described above?  In other words, are
                        > there any transaction-persistent
                        pseudovariables and/or namespace
                        > containers that are easier to deal
                        with syntactically, and provide more
                        > advanced data structure primitives
                        like arrays?  I am aware of htable,
                        > but I need something local to a
                        transaction, not global.

                        There are the $var PVs you already
                        mentioned, and then also the $shv that
                        are shared variables. But they are
                        (process) global, and not
                        transactional. Daniel proposed and
                        developed the new xavp type for sr
                        which are basically extended AVPs.
                        More informations can be found here:
                        http://sip-router.org/wiki/devel/xavp

                        Regards,

                        Henning



                    _______________________________________________
                    Kamailio (OpenSER) - Users mailing list
                    Users@lists.kamailio.org
                    <mailto:Users@lists.kamailio.org>
                    http://lists.kamailio.org/cgi-bin/mailman/listinfo/users
                    http://lists.openser-project.org/cgi-bin/mailman/listinfo/users

                    -- 
                    Daniel-Constantin Mierla
                    *http://www.asipto.com/





            _______________________________________________
            Kamailio (OpenSER) - Users mailing list
            Users@lists.kamailio.org  <mailto:Users@lists.kamailio.org>
            http://lists.kamailio.org/cgi-bin/mailman/listinfo/users
            http://lists.openser-project.org/cgi-bin/mailman/listinfo/users

            -- 
            Daniel-Constantin Mierla
            *http://www.asipto.com/




        _______________________________________________
        Kamailio (OpenSER) - Users mailing list
        Users@lists.kamailio.org  <mailto:Users@lists.kamailio.org>
        http://lists.kamailio.org/cgi-bin/mailman/listinfo/users
        http://lists.openser-project.org/cgi-bin/mailman/listinfo/users

        -- 
        Daniel-Constantin Mierla
        *http://www.asipto.com/

-- 
Daniel-Constantin Mierla
*http://www.asipto.com/

---

Kamailio (OpenSER) - Users mailing list Users@lists.kamailio.org http://lists.kamailio.org/cgi-bin/mailman/listinfo/users http://lists.openser-project.org/cgi-bin/mailman/listinfo/users

Hello,

On 12/30/09 4:05 PM, alex pappas wrote:

Hi,

The error I get is the following:

ERROR:db_mysql:db_mysql_submit_query: driver error on query: Lost connection to MySQL server during query ERROR:core:db_do_query: error while submitting query ERROR:htable:ht_db_load_table: Error while querying db ERROR:core:init_mod: failed to initialize module htable ERROR:core:main: error while initializing modules

I'm running kamailio with 8M private memory per process and 2048M shared memory.

When kamailio is getting the data from mysql is passing it all the data chunk in private memory and then in the shared memory?

It is loaded in chunks of rows -- you can tune it with a module parameter: http://kamailio.org/docs/modules/3.0.x/modules_k/htable.html#id2529075

Try to play with it to see if you get better results. You do not need more private memory, 8MB is ok.

Maybe the mysql server is closing due to lot of loaded data. Have you got any figure what is the number of rows when it starts losing the connection?

Cheers, Daniel

because if this is the case then I need more than 1000M in private memory.

Cheers Alex

P.S. i don't get any errors from mysql.

No, the size means 16384 unique buckets, not total capacity. It's just that the efficiency of a hash table breaks down and approaches a linear search as the size of the data vastly exceeds the capacity of the table, as a unique distribution is not possible and almost all lookups require collision list searches.

On 12/30/2009 10:53 AM, alex pappas wrote:

Hi,

I don't see where the mysql is stacking but if I read correctly in http://kamailio.org/docs/modules/1.5.x/htable.html

_/The number of entries in the table is 2^size/_ : 2^14=16384 rows data and i have 2.000.000 rows so maybe it is not possible to load them..But you know better of course.

Cheers Alex

On Wed, Dec 30, 2009 at 5:32 PM, Daniel-Constantin Mierla <miconda@gmail.com mailto:miconda@gmail.com> wrote:

Hello,

On 12/30/09 4:05 PM, alex pappas wrote:

    Hi,

    The error I get is the following:

    ERROR:db_mysql:db_mysql_submit_query: driver error on query:
    Lost connection to MySQL server during query
    ERROR:core:db_do_query: error while submitting query
    ERROR:htable:ht_db_load_table: Error while querying db
    ERROR:core:init_mod: failed to initialize module htable
    ERROR:core:main: error while initializing modules

    I'm running kamailio with 8M private memory per process and
    2048M  shared memory.

    When kamailio is getting the data from mysql is passing it all
    the data chunk in private memory and then in the shared memory?

It is loaded in chunks of rows -- you can tune it with a module
parameter:
http://kamailio.org/docs/modules/3.0.x/modules_k/htable.html#id2529075

Try to play with it to see if you get better results. You do not
need more private memory, 8MB is ok.

Maybe the mysql server is closing due to lot of loaded data. Have
you got any figure what is the number of rows when it starts losing
the connection?

Cheers,
Daniel



    because if this is the case then I need more than 1000M in
    private memory.

    Cheers
    Alex

    P.S. i don't get any errors from mysql.




--
Daniel-Constantin Mierla
* http://www.asipto.com/

---

Kamailio (OpenSER) - Users mailing list Users@lists.kamailio.org http://lists.kamailio.org/cgi-bin/mailman/listinfo/users http://lists.openser-project.org/cgi-bin/mailman/listinfo/users

Hi,

From the syslog when kamailio start ups I get the following:

Dec 31 12:35:45 YGOR /usr/local/sbin/kamailio[10281]: DBG:core:destroy_modules: shutting down module htable Dec 31 12:35:46 YGOR kamailio: DBG:core:yyparse: loading module /usr/local/lib64/kamailio/modules/htable.so Dec 31 12:35:46 YGOR kamailio: DBG:core:register_module: register_pv: htable Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: htable matches module htable Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: found <htable> in module htable [/usr/local/lib64/kamailio/modules/] Dec 31 12:35:46 YGOR kamailio: DBG:htable:ht_table_spec: htable [a] - size [14] Dec 31 12:35:46 YGOR kamailio: DBG:htable:ht_table_spec: htable [a] - dbtable [htable] Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: htable matches module htable Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: found <db_url> in module htable [/usr/local/lib64/kamailio/modules/] Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: htable matches module htable Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: found <fetch_rows> in module htable [/usr/local/lib64/kamailio/modules/] Dec 31 12:35:46 YGOR kamailio: DBG:core:find_cmd_export_t: found <sht_print>(0) in module htable [/usr/local/lib64/kamailio/modules/] Dec 31 12:35:52 YGOR /usr/local/sbin/kamailio[10371]: INFO:core:init_mod: initializing module htable Dec 31 12:35:52 YGOR /usr/local/sbin/kamailio[10371]: DBG:htable:ht_db_open_con: database connection opened successfully Dec 31 12:35:52 YGOR /usr/local/sbin/kamailio[10371]: DBG:htable:ht_db_load_tables: loading db table [htable] in ht [a] Dec 31 12:35:52 YGOR /usr/local/sbin/kamailio[10371]: DBG:htable:ht_db_load_table: =============== loading hash table [a] from database [htable] Dec 31 12:35:58 YGOR /usr/local/sbin/kamailio[10371]: DBG:htable*:ht_db_load_table: Nothing to be loaded in hash table* Dec 31 12:35:58 YGOR /usr/local/sbin/kamailio[10371]: DBG:core:init_mod: register MI for htable Dec 31 12:35:58 YGOR /usr/local/sbin/kamailio[10371]: DBG:htable:pv_parse_ht_name: htable [a] - key [$rU] Dec 31 12:35:58 YGOR /usr/local/sbin/kamailio[10371]: DBG:htable:ht_get_table: htable found [a]

In the mysql htable I see :

mysql> select count(*) from openser10.htable; +----------+ | count(*) | +----------+ | 1893255 | +----------+ 1 row in set (0.00 sec)

And ofcourse when I run the sht_print(); I get many errors since htable in memory is empty:

Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1376] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1377] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1378] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1379] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1380] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1381] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1382] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1383] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1384] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1385] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1386] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1387] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1388] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1389] -- <0>

Do you have any idea on why htable module does not load the data in the memory ?

Cheers Alex

On Wed, Dec 30, 2009 at 6:01 PM, Daniel-Constantin Mierla <miconda@gmail.com

wrote:

Hello,

On 12/30/09 4:53 PM, alex pappas wrote:

Hi,

I don't see where the mysql is stacking but if I read correctly in http://kamailio.org/docs/modules/1.5.x/htable.html

*The number of entries in the table is 2^size* : 2^14=16384 rows data and i have 2.000.000 rows so maybe it is not possible to load them..But you know better of course.

no, that is the number of slots (buckets) in the hash table, not the number of items that can be loaded -- some more technical aspects of hash tables:

http://en.wikipedia.org/wiki/Hash_table

The number of items in htable is only a matter of shared memory. The number of slots controls access speed -- higher number gives less collisions.

You said at a moment that you could start, but htable was returning only null: put a sht_print() in config to see the content of the htable (you probably need high debug level: debug=5) with the first sip request.

Cheers, Daniel

On Wed, Dec 30, 2009 at 5:32 PM, Daniel-Constantin Mierla < miconda@gmail.com> wrote:

...

Hello,

On 12/30/09 4:05 PM, alex pappas wrote:

...

Hi,

The error I get is the following:

ERROR:db_mysql:db_mysql_submit_query: driver error on query: Lost connection to MySQL server during query ERROR:core:db_do_query: error while submitting query ERROR:htable:ht_db_load_table: Error while querying db ERROR:core:init_mod: failed to initialize module htable ERROR:core:main: error while initializing modules

I'm running kamailio with 8M private memory per process and 2048M shared memory.

When kamailio is getting the data from mysql is passing it all the data chunk in private memory and then in the shared memory?

It is loaded in chunks of rows -- you can tune it with a module parameter: http://kamailio.org/docs/modules/3.0.x/modules_k/htable.html#id2529075

Try to play with it to see if you get better results. You do not need more private memory, 8MB is ok.

Maybe the mysql server is closing due to lot of loaded data. Have you got any figure what is the number of rows when it starts losing the connection?

Cheers, Daniel

...

because if this is the case then I need more than 1000M in private memory.

Cheers Alex

P.S. i don't get any errors from mysql.

-- Daniel-Constantin Mierla

• http://www.asipto.com/

-- Daniel-Constantin Mierla

• http://www.asipto.com/

Hello,

On 12/31/09 12:14 PM, alex pappas wrote:

Hi,

When I have less rows in the table then :

Dec 31 13:11:26 YGOR kamailio: DBG:core:find_cmd_export_t: found <sht_print>(0) in module htable [/usr/local/lib64/kamailio/modules/] Dec 31 13:11:33 YGOR /usr/local/sbin/kamailio[10843]: INFO:core:init_mod: initializing module htable Dec 31 13:11:33 YGOR /usr/local/sbin/kamailio[10843]: DBG:htable:ht_db_open_con: database connection opened successfully Dec 31 13:11:33 YGOR /usr/local/sbin/kamailio[10843]: DBG:htable:ht_db_load_tables: loading db table [htable] in ht [a] Dec 31 13:11:33 YGOR /usr/local/sbin/kamailio[10843]: DBG:htable:ht_db_load_table: =============== loading hash table [a] from database [htable] Dec 31 13:11:33 YGOR /usr/local/sbin/kamailio[10843]: DBG:htable:*ht_db_load_table: loaded 67 values in hash table*

this time is ok, indeed. As said in previous email, I will try too look deeper at it in the next days.

Thanks for troubleshooting and happy new year!

Daniel

Dec 31 13:11:33 YGOR /usr/local/sbin/kamailio[10843]: DBG:core:init_mod: register MI for htable Dec 31 13:11:33 YGOR /usr/local/sbin/kamailio[10843]: DBG:htable:pv_parse_ht_name: htable [a] - key [$rU] Dec 31 13:11:33 YGOR /usr/local/sbin/kamailio[10843]: DBG:htable:ht_get_table: htable found [a]

Cheers Alex

On Thu, Dec 31, 2009 at 1:02 PM, alex pappas <rebel.pappas@gmail.com mailto:rebel.pappas@gmail.com> wrote:

Hi,

From the syslog when kamailio start ups I get the following:


Dec 31 12:35:45 YGOR /usr/local/sbin/kamailio[10281]:
DBG:core:destroy_modules: shutting down module htable
Dec 31 12:35:46 YGOR kamailio: DBG:core:yyparse: loading module
/usr/local/lib64/kamailio/modules/htable.so
Dec 31 12:35:46 YGOR kamailio: DBG:core:register_module:
register_pv: htable
Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex:
htable matches module htable
Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: found
<htable> in module htable [/usr/local/lib64/kamailio/modules/]
Dec 31 12:35:46 YGOR kamailio: DBG:htable:ht_table_spec: htable
[a] - size [14]
Dec 31 12:35:46 YGOR kamailio: DBG:htable:ht_table_spec: htable
[a] - dbtable [htable]
Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex:
htable matches module htable
Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: found
<db_url> in module htable [/usr/local/lib64/kamailio/modules/]
Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex:
htable matches module htable
Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: found
<fetch_rows> in module htable [/usr/local/lib64/kamailio/modules/]
Dec 31 12:35:46 YGOR kamailio: DBG:core:find_cmd_export_t: found
<sht_print>(0) in module htable [/usr/local/lib64/kamailio/modules/]
Dec 31 12:35:52 YGOR /usr/local/sbin/kamailio[10371]:
INFO:core:init_mod: initializing module htable
Dec 31 12:35:52 YGOR /usr/local/sbin/kamailio[10371]:
DBG:htable:ht_db_open_con: database connection opened successfully
Dec 31 12:35:52 YGOR /usr/local/sbin/kamailio[10371]:
DBG:htable:ht_db_load_tables: loading db table [htable] in ht [a]
Dec 31 12:35:52 YGOR /usr/local/sbin/kamailio[10371]:
DBG:htable:ht_db_load_table: =============== loading hash table
[a] from database [htable]
Dec 31 12:35:58 YGOR /usr/local/sbin/kamailio[10371]:
DBG:htable*:ht_db_load_table: Nothing to be loaded in hash table*
Dec 31 12:35:58 YGOR /usr/local/sbin/kamailio[10371]:
DBG:core:init_mod: register MI for htable
Dec 31 12:35:58 YGOR /usr/local/sbin/kamailio[10371]:
DBG:htable:pv_parse_ht_name: htable [a] - key [$rU]
Dec 31 12:35:58 YGOR /usr/local/sbin/kamailio[10371]:
DBG:htable:ht_get_table: htable found [a]

In the mysql htable I see :


mysql> select count(*) from openser10.htable;
+----------+
| count(*) |
+----------+
|  1893255 |
+----------+
1 row in set (0.00 sec)

And ofcourse when I run the sht_print(); I get many errors since
htable in memory is empty:

Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]:
ERROR:htable:ht_dbg: htable[1376] -- <0>
Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]:
ERROR:htable:ht_dbg: htable[1377] -- <0>
Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]:
ERROR:htable:ht_dbg: htable[1378] -- <0>
Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]:
ERROR:htable:ht_dbg: htable[1379] -- <0>
Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]:
ERROR:htable:ht_dbg: htable[1380] -- <0>
Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]:
ERROR:htable:ht_dbg: htable[1381] -- <0>
Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]:
ERROR:htable:ht_dbg: htable[1382] -- <0>
Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]:
ERROR:htable:ht_dbg: htable[1383] -- <0>
Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]:
ERROR:htable:ht_dbg: htable[1384] -- <0>
Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]:
ERROR:htable:ht_dbg: htable[1385] -- <0>
Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]:
ERROR:htable:ht_dbg: htable[1386] -- <0>
Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]:
ERROR:htable:ht_dbg: htable[1387] -- <0>
Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]:
ERROR:htable:ht_dbg: htable[1388] -- <0>
Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]:
ERROR:htable:ht_dbg: htable[1389] -- <0>

Do you have any idea on why htable module does not load the data
in the memory ?

Cheers
Alex







On Wed, Dec 30, 2009 at 6:01 PM, Daniel-Constantin Mierla
<miconda@gmail.com <mailto:miconda@gmail.com>> wrote:

    Hello,


    On 12/30/09 4:53 PM, alex pappas wrote:

...

    Hi,

    I don't see where the mysql is stacking but if I read
    correctly in http://kamailio.org/docs/modules/1.5.x/htable.html

    _/The number of entries in the table is 2^size/_ : 
    2^14=16384 rows data and i have 2.000.000 rows so maybe it is
    not possible to load them..But you know better of course.

    no, that is the number of slots (buckets) in the hash table,
    not the number of items that can be loaded -- some more
    technical aspects of hash tables:

    http://en.wikipedia.org/wiki/Hash_table

    The number of items in htable is only a matter of shared
    memory. The number of slots controls access speed -- higher
    number gives less collisions.

    You said at a moment that you could start, but htable was
    returning only null: put a sht_print() in config to see the
    content of the htable (you probably need high debug level:
    debug=5) with the first sip request.

    Cheers,
    Daniel

...

    On Wed, Dec 30, 2009 at 5:32 PM, Daniel-Constantin Mierla
    <miconda@gmail.com <mailto:miconda@gmail.com>> wrote:

        Hello,

        On 12/30/09 4:05 PM, alex pappas wrote:

            Hi,

            The error I get is the following:

            ERROR:db_mysql:db_mysql_submit_query: driver error on
            query: Lost connection to MySQL server during query
            ERROR:core:db_do_query: error while submitting query
            ERROR:htable:ht_db_load_table: Error while querying db
            ERROR:core:init_mod: failed to initialize module htable
            ERROR:core:main: error while initializing modules

            I'm running kamailio with 8M private memory per
            process and 2048M  shared memory.

            When kamailio is getting the data from mysql is
            passing it all the data chunk in private memory and
            then in the shared memory?

        It is loaded in chunks of rows -- you can tune it with a
        module parameter:
        http://kamailio.org/docs/modules/3.0.x/modules_k/htable.html#id2529075

        Try to play with it to see if you get better results. You
        do not need more private memory, 8MB is ok.

        Maybe the mysql server is closing due to lot of loaded
        data. Have you got any figure what is the number of rows
        when it starts losing the connection?

        Cheers,
        Daniel



            because if this is the case then I need more than
            1000M in private memory.

            Cheers
            Alex

            P.S. i don't get any errors from mysql.




        -- 
        Daniel-Constantin Mierla
        * http://www.asipto.com/

    -- 
    Daniel-Constantin Mierla
    *http://www.asipto.com/

Hello,

On 12/31/09 12:02 PM, alex pappas wrote:

Hi,

From the syslog when kamailio start ups I get the following:

Dec 31 12:35:45 YGOR /usr/local/sbin/kamailio[10281]: DBG:core:destroy_modules: shutting down module htable Dec 31 12:35:46 YGOR kamailio: DBG:core:yyparse: loading module /usr/local/lib64/kamailio/modules/htable.so Dec 31 12:35:46 YGOR kamailio: DBG:core:register_module: register_pv: htable Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: htable matches module htable Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: found <htable> in module htable [/usr/local/lib64/kamailio/modules/] Dec 31 12:35:46 YGOR kamailio: DBG:htable:ht_table_spec: htable [a] - size [14] Dec 31 12:35:46 YGOR kamailio: DBG:htable:ht_table_spec: htable [a] - dbtable [htable] Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: htable matches module htable Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: found <db_url> in module htable [/usr/local/lib64/kamailio/modules/] Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: htable matches module htable Dec 31 12:35:46 YGOR kamailio: DBG:core:set_mod_param_regex: found <fetch_rows> in module htable [/usr/local/lib64/kamailio/modules/] Dec 31 12:35:46 YGOR kamailio: DBG:core:find_cmd_export_t: found <sht_print>(0) in module htable [/usr/local/lib64/kamailio/modules/] Dec 31 12:35:52 YGOR /usr/local/sbin/kamailio[10371]: INFO:core:init_mod: initializing module htable Dec 31 12:35:52 YGOR /usr/local/sbin/kamailio[10371]: DBG:htable:ht_db_open_con: database connection opened successfully Dec 31 12:35:52 YGOR /usr/local/sbin/kamailio[10371]: DBG:htable:ht_db_load_tables: loading db table [htable] in ht [a] Dec 31 12:35:52 YGOR /usr/local/sbin/kamailio[10371]: DBG:htable:ht_db_load_table: =============== loading hash table [a] from database [htable] Dec 31 12:35:58 YGOR /usr/local/sbin/kamailio[10371]: DBG:htable*:ht_db_load_table: Nothing to be loaded in hash table*

this is caused by a check of number of rows which seems to be 0. I will try to reproduce in the next days ...

Dec 31 12:35:58 YGOR /usr/local/sbin/kamailio[10371]: DBG:core:init_mod: register MI for htable Dec 31 12:35:58 YGOR /usr/local/sbin/kamailio[10371]: DBG:htable:pv_parse_ht_name: htable [a] - key [$rU] Dec 31 12:35:58 YGOR /usr/local/sbin/kamailio[10371]: DBG:htable:ht_get_table: htable found [a]

In the mysql htable I see :

mysql> select count(*) from openser10.htable; +----------+ | count(*) | +----------+ | 1893255 | +----------+ 1 row in set (0.00 sec)

And ofcourse when I run the sht_print(); I get many errors since htable in memory is empty:

Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1376] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1377] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1378] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1379] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1380] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1381] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1382] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1383] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1384] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1385] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1386] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1387] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1388] -- <0> Dec 31 12:52:48 YGOR /usr/local/sbin/kamailio[10375]: ERROR:htable:ht_dbg: htable[1389] -- <0>

Yes, these show empty htable slots.

Do you have any idea on why htable module does not load the data in the memory ?

Needs some troubleshooting as looks not to work from a certain amount of data.

Cheers, Daniel

Cheers Alex

On Wed, Dec 30, 2009 at 6:01 PM, Daniel-Constantin Mierla <miconda@gmail.com mailto:miconda@gmail.com> wrote:

Hello,


On 12/30/09 4:53 PM, alex pappas wrote:

...

Hi,

I don't see where the mysql is stacking but if I read correctly
in http://kamailio.org/docs/modules/1.5.x/htable.html

_/The number of entries in the table is 2^size/_ :  2^14=16384
rows data and i have 2.000.000 rows so maybe it is not possible
to load them..But you know better of course.

no, that is the number of slots (buckets) in the hash table, not
the number of items that can be loaded -- some more technical
aspects of hash tables:

http://en.wikipedia.org/wiki/Hash_table

The number of items in htable is only a matter of shared memory.
The number of slots controls access speed -- higher number gives
less collisions.

You said at a moment that you could start, but htable was
returning only null: put a sht_print() in config to see the
content of the htable (you probably need high debug level:
debug=5) with the first sip request.

Cheers,
Daniel

...

On Wed, Dec 30, 2009 at 5:32 PM, Daniel-Constantin Mierla
<miconda@gmail.com <mailto:miconda@gmail.com>> wrote:

    Hello,

    On 12/30/09 4:05 PM, alex pappas wrote:

        Hi,

        The error I get is the following:

        ERROR:db_mysql:db_mysql_submit_query: driver error on
        query: Lost connection to MySQL server during query
        ERROR:core:db_do_query: error while submitting query
        ERROR:htable:ht_db_load_table: Error while querying db
        ERROR:core:init_mod: failed to initialize module htable
        ERROR:core:main: error while initializing modules

        I'm running kamailio with 8M private memory per process
        and 2048M  shared memory.

        When kamailio is getting the data from mysql is passing
        it all the data chunk in private memory and then in the
        shared memory?

    It is loaded in chunks of rows -- you can tune it with a
    module parameter:
    http://kamailio.org/docs/modules/3.0.x/modules_k/htable.html#id2529075

    Try to play with it to see if you get better results. You do
    not need more private memory, 8MB is ok.

    Maybe the mysql server is closing due to lot of loaded data.
    Have you got any figure what is the number of rows when it
    starts losing the connection?

    Cheers,
    Daniel



        because if this is the case then I need more than 1000M
        in private memory.

        Cheers
        Alex

        P.S. i don't get any errors from mysql.




    -- 
    Daniel-Constantin Mierla
    * http://www.asipto.com/

-- 
Daniel-Constantin Mierla
*http://www.asipto.com/

On Mon, December 21, 2009 5:37 pm, Alex Balashov wrote:

Try: if(is_avp_set("$avp(s:lnp_prefix)"))

Hey Alex,

also possible is to use it: if (! $avp(s:lnp_prefix)) which is shorter and don't rely on the avpops module to test for null. (In sr the "defined" operator can be used for the same purpose.

Best regards,

Henning

Guys thank you very much for all your answers. It is a very educational process writing and reading from the users list. For new people in kamailio like myself , the users list is the best way to learn AFTER reading the official documentation.

Thank you once again

P.S. For the records I'm attaching the script i wrote to implement the portability Cheers Alex

The scenario is that we expect calls from customers which we route through a special prefix that they are sending us. For some customers we terminate ported numbers and for others not.That depends on the contract they have. Anyway the script which i will test heavily tomorrow:

From the prefix to domain translation i expect back a value string with 4

numbers separated from dot's . 10.1.1.5 number called 1001 210XXXXXXX 1001=cust prefix and 210XXXXXX number

$var(dial_grp) = 10 the dialplan group in the dialplan query next one.values .0-100 $var(check_p) = 1 if we want to check number for portability . values 0-1 $var(term_p) = 1 if the number is ported if we want to terminate it anyway. values 0-1 $var(num_pr) = 4 this is the prefix(number of digits we want to remove before the checking of portability) if(prefix2domain("2", "0")) {

$var(dial_grp) = $(rd{s.select,0,.}{s.int}); $var(check_p) = $(rd{s.select,1,.}{s.int}); $var(term_p) = $(rd{s.select,2,.}{s.int}); $var(num_pr) = $(rd{s.select,3,.}{s.int});

$rU = $(rU{s.substr,$var(num_pr),0}); # we cat the prefix here

if($var(check_p)==1){

sql_query("ca", "SELECT prefix FROM ported WHERE phonenumber='$rU'", "ra"); $rU = '' + $dbr(ra=>[0,0]) + $rU;

if($var(term_p)==0){ xlog("alx ------- We dont terminate this ported number!!!! ----------"); sl_send_reply("503","Congestion"); exit;

} sql_result_free("ra");

}

On Mon, Dec 21, 2009 at 10:51 PM, Alex Balashov abalashov@evaristesys.comwrote:

On 12/21/2009 03:50 PM, Henning Westerholt wrote:

...

On Mon, December 21, 2009 5:37 pm, Alex Balashov wrote:

...

Try: if(is_avp_set("$avp(s:lnp_prefix)"))

Hey Alex,

also possible is to use it: if (! $avp(s:lnp_prefix)) which is shorter and don't rely on the avpops module to test for null. (In sr the "defined" operator can be used for the same purpose.

I thought that would evaluate the numerical expression value of the AVP, not whether it is null?

-- Alex Balashov - Principal Evariste Systems Web : http://www.evaristesys.com/ Tel : (+1) (678) 954-0670 Direct : (+1) (678) 954-0671