...

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