Re: [SR-Users] PDT ran out of pkg memory using pdt_list [SOLVED} - sr-users

22 Jul 2010

...
   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