rlm_cache filtering

[Alan DeKok]

On Jul 15, 2019, at 4:34 PM, liran kessel <lirankessel at gmail.com> wrote:
> 
> I have activated rlm_cache so as to reduce the amount of times the freeradius will query the DB.

  No.

  You need to read the instructions in mods-available/cache.  You can't just call the "cache" module and expect it to do what you want.  The documentation explains how the module works.

> I made the Calling-Station-Id the cache key and have added this code to the accounting section:
> 
> cache

  Which does... what?  Read the documentation for the "cache" module.  The behaviour is explained there.

>       if (ok){
>               sql
>               }
>       else   {
>                       if ("%{sql:select case when exists(select * from [TABLE_NAME] where ne_id='%{Calling-Station-Id}') then 1 else 0 end}" == 1){
>                       sql
>                       }
>               }

  That doesn't do what you want.  It still hits SQL for users who aren't your users.

> However what happens is that all Calling-Station-Ids are stored to the cache and so the 2nd time I get a packet they execute the sql module.
> How can I configure filtering of the cache in the “else” section so as to decide which Calling-Station-Id to save in cache and which not?

  Read the documentation for the cache module.  It always caches what you tell it to cache.  The solution here is to *not* cache the Calling-Station-Id.  But instead cache the *status* of the user.

  In English, what you want is something like:

	cache lookup
	if (noop) {			# user status is unknown
		if (check SQL for user) {
			update control {
				my-user := yes
			}
		}
		else {
			update control {
				my-user := no
			}
		}
		cache store
	}
	elsif (my-user) {
		sql
	}

  You will have to:

* create the "my-user" attribute in raddb/dictionary
* update the "cache" module to cache the "my-user" attribute
* set  &control:Cache-Status-Only attribute before doing the cache lookup
* set the cache TTL to something reasonable (larger than 10)
* update the above pseudo-code to more correct "unlang"

  The above will do the following:

* for cached entries, ONLY hit SQL for your users, and NEVER for other users
* for non-cached entries, hit SQL twice for your users, and once for other users

  If you set the cache TTL to a larger value (i.e. hours), then 99% of packets will hit the cache.

  The other thing to note is that you may want to investigate your SQL server.  It should be able to handle 100's to 1000's of reads per second.  And if you only have 100K users, you shouldn't be getting 100's of packets per second.

  So something else may be going wrong there.  Either you're receiving too many packets to be normal, OR your SQL server is extremely slow.

  Alan DeKok.