[SR-Users] PDT ran out of pkg memory using pdt_list [SOLVED}

[JR Richardson]

>> try to play with mysql module timeout parameter in case the records are not
>> loaded fast enough:
>>
>> http://kamailio.org/docs/modules/stable/modules/db_mysql.html#id2928400
>>
>> Regards,
>> Ramona
>
> Ok, I figured out that sr was taking about 9-10 seconds to load 300K
> records so I increased:
>  # ----- db_mysql params -----
> modparam("db_mysql", "timeout_interval", 20)
>
> And found that the table would load, so I started increasing the
> record count by 60K, then the fault condition occurred again at around
> 400K records, then went away by itself.  I got all the way to 760K
> records without a problem then hit another wall at 800K records.
>
> I think there is something flaky going on, I can not reproduce it
> reliable, sometimes the table loads just fine and other times it does
> not load at all.
>
> I started monitoring mysqld.  When I restart sr or issue pdt_reload
> and msqld has a load, the table seems to load fine, regardless of the
> table size or "timeout_interval" setting.  When the problem is
> occurring, myslqd has zero usage.  When the issue was occurring, I
> restarted the mysql process and the table loaded into sr.  I'm now
> suspecting I have a buggy mysqld so I'm going to try to update that
> and see if I can reproduce the problem.  So for now I can't isolate
> the issue, but I'm still working on it.
>
> I do see a difference with the "timeout_interval" during sr restarts,
> the longer the time the better it does.  Also there is a time
> difference when loading the table, sometimes it takes 30 seconds,
> sometimes 90 seconds.  I think maybe there is a combination of issues
> going on here.  I need to rebuild a fresh install and test a bit more
> methodically to find a root cause.  Also I want to grab a newer
> version of mysql-server.
>
> I'll update with my findings.

# ----- db_mysql params -----
modparam("db_mysql", "timeout_interval", 20)

Time interval (in seconds) after that an connection attempt, read or
write request is aborted. The value counts three times, as several
retries are done from the driver before it gives up.

"The values counts 3 times" this was my issue, as I was adjusting the
value between 10, 20, 30, 60, 120, and having inconsistent table load
times from mysqld.  After a reboot, mysql started performing better,
more consistent with load times.

My table with 800K records was taking between 30-38 seconds to load.
With timeout_interval set to 10 (30 second timeout), I was right on
the edge of the table loading or not loading, sometimes it would and
other times it would not.  So giving a bit of cushion on the
timeout_interval seems to resolve the problem.  Adjusting this value I
could consistently reproduce the problem.  So for large database
tables, this timeout value should be set to about 1 second per 20K
records.

As far as the other issue goes with using pdt_list and pkg memory
size, compileing mi using system mem solved this issue as well.  Also
pdt_list has the option to search for prefixes which works in my case
without the need to increase pkg memory or system memory:

sip-router2:~# kamctl fifo pdt_list . 99999
PDT::  SDOMAIN=* PREFIX=999990 DOMAIN=2
PDT::  SDOMAIN=* PREFIX=999991 DOMAIN=2
PDT::  SDOMAIN=* PREFIX=999992 DOMAIN=2
PDT::  SDOMAIN=* PREFIX=999993 DOMAIN=2
PDT::  SDOMAIN=* PREFIX=999994 DOMAIN=2
PDT::  SDOMAIN=* PREFIX=999995 DOMAIN=2
PDT::  SDOMAIN=* PREFIX=999996 DOMAIN=2
PDT::  SDOMAIN=* PREFIX=999997 DOMAIN=2
PDT::  SDOMAIN=* PREFIX=999998 DOMAIN=2
PDT::  SDOMAIN=* PREFIX=999999 DOMAIN=2

Using the list prefix search option will pipe out about 10,000
records, but errors out with "no more pkg mem" past that.

Thanks for all your input on this thread to help me isolate the issue.

JR
-- 
JR Richardson
Engineering for the Masses