[Kamailio-Users] avpops OR sqlops
alex pappas
rebel.pappas at gmail.com
Wed Dec 30 16:05:20 CET 2009
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?
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.
On Wed, Dec 30, 2009 at 4:21 PM, Daniel-Constantin Mierla <miconda at gmail.com
> wrote:
> 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 at 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 at gmail.com>wrote:
>>
>>> Thank you Daniel,
>>>
>>> Cheers
>>> Alex
>>>
>>>
>>> On Thu, Dec 24, 2009 at 4:05 PM, Daniel-Constantin Mierla <
>>> miconda at 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 at 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 at 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 at 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 at 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 at lists.kamailio.orghttp://lists.kamailio.org/cgi-bin/mailman/listinfo/usershttp://lists.openser-project.org/cgi-bin/mailman/listinfo/users
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> Daniel-Constantin Mierla
>>>>>>> * http://www.asipto.com/
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> Kamailio (OpenSER) - Users mailing listUsers at lists.kamailio.orghttp://lists.kamailio.org/cgi-bin/mailman/listinfo/usershttp://lists.openser-project.org/cgi-bin/mailman/listinfo/users
>>>>>
>>>>>
>>>>> --
>>>>> Daniel-Constantin Mierla
>>>>> * http://www.asipto.com/
>>>>>
>>>>>
>>>>
>>>> _______________________________________________
>>>> Kamailio (OpenSER) - Users mailing listUsers at lists.kamailio.orghttp://lists.kamailio.org/cgi-bin/mailman/listinfo/usershttp://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 listUsers at lists.kamailio.orghttp://lists.kamailio.org/cgi-bin/mailman/listinfo/usershttp://lists.openser-project.org/cgi-bin/mailman/listinfo/users
>
>
> --
> Daniel-Constantin Mierla
> * http://www.asipto.com/
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20091230/acdb260c/attachment.htm>
More information about the sr-users
mailing list