Daniel-Constantin Mierla writes:

Overall, I would not like to get nathelper connecting to database of usrloc, instead usrloc can be updated for a runtime mode where it does only read operations - in db only mode, if save() is not used, then only thing to be disabled is the timer for deleting auto-expired records. In this way, any change to database structure/backend type has to be updated in one place.

daniel,

thanks for your reply. the above proposal sounds very promising indeed. regarding socket, it could be a module param that can be set on the dispatcher host to override socket value in location table.

-- juha

Juha Heinanen writes:

could it be that in db_mode=3, ul.get_all_ucontacts does not work at all? any other ideas?

it turned out that if config is not making any save or lookup calls, there is no domains registered in usrloc. i got the records from location table after i added usrloc mod param

modparam("usrloc", "preload", "location")

next problem is that usrloc complains about non-local socket:

Sep 15 21:14:01 siika /usr/sbin/sip-proxy[13380]: WARNING: usrloc [dlist.c:239]: non-local socket udp:192.98.103.2:5070...ignoring

so it does not help that nathelper has "natping_socket" module param defined, because nathelper does not get a chance to override the socket.

if the above is correct, there is two ways to solve the problem:

1) introduce "socket" param to registrar module or

2) add "non_local_socket_ok" param to usrloc module.

comments?

-- juha

Juha Heinanen writes:

one more thing. the all contacts query that usrloc makes looks like this:

120915 21:48:04 1397 Query select received, contact, socket, cflags, path, ruid from location where expires > '2012-09-15 21:48:04' and cflags & 0 = 0 and id % 20 = 0

there is currently no index for expires and cflags. should i add one or both of them?

i did some tests on location table with 10000 records. i tried with and without index on expires. i also tried the same with integer expires field that i called stamp. in all cases i repeated the above select five times and calculated average execution time.

here are results without index on expires or stamp:

# ./location.php expires 0.02223800 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0 stamp 0.01464875 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0 expires 0.01949225 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0 stamp 0.01462175 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0 expires 0.01813800 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0 stamp 0.01471850 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0 expires 0.02328850 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0 stamp 0.01501425 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0 expires 0.02514450 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0 stamp 0.01482900 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0 expires ave 0.02166025 stamp ave 0.01476645

and here are result with index on expires and stamp: # ./location.php expires 0.01195225 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0 stamp 0.00652925 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0 expires 0.01188325 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0 stamp 0.00680575 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0 expires 0.01177150 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0 stamp 0.00694525 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0 expires 0.01189250 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0 stamp 0.00696700 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0 expires 0.01188350 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where expires > '2012-09-16 11:15:00' and cflags & 0 = 0 and id % 20 = 0 stamp 0.00690475 select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where stamp > 1347783300 and cflags & 0 = 0 and id % 20 = 0 expires ave 0.0118766 stamp ave 0.0068304

the conclusion is that without index, select takes about twice as much time as with index. another conclusion is that integer stamp is about two times as efficient as datetime expires.

i'll thus add in location table an index on expires. at some point it would make sense to replace datetime type of expires field with unsigned int.

-- juha

Klaus Darilion writes:

I wonder why "expires" is checked at all? Expired contacts will be deleted automatically so the chance to ping expired contacts should be very small.

klaus,

i wondered the same. it would not really hurt much if a small number of expired contacts are binged for vain.

otherwise regarding the query, these two conditions

cflags & 0 = 0 and id % 20 = 0

are very inefficient, because an operation is applied to a field before comparison. if nat flag would always be the largest flag, comparison could go like this:

cflags >= "value of last flag"

and an index could be added on cflags.

-- juha

Klaus Darilion writes:

Not sure if an index would be much faster here. Also the cflags & 0 = 0 should be rather fast.

klaus,

as i wrote, if an operation is done on index field, the index cannot be used:

mysql> explain select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where cflags & 16 = 16 and id % 20 = 0; +----+-------------+----------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | location | ALL | NULL | NULL | NULL | NULL | 10035 | Using where | +----+-------------+----------+------+---------------+------+---------+------+-------+-------------+

mysql> explain select SQL_NO_CACHE received, contact, socket, cflags, path, ruid from location where cflags >= 15 and id % 20 = 0; +----+-------------+----------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | location | range | cflags_idx | cflags_idx | 4 | NULL | 1309 | Using where | +----+-------------+----------+-------+---------------+------------+---------+------+------+-------------+

in terms of execution time, the latter is about half of previous.

-- juha

Klaus Darilion writes:

That's indeed more performance gain than I expected. But hard-coding the NAT flag is a bit ugly. Unfortunately I do not have a better idea.

i agree that it would be ugly, but if at some point people get serious about running usrloc in db only mode then these kind of performance issues should be dealt with.

-- juha