postgres vs mysql start / stop times

[Peter Nixon]

On Fri 13 Jul 2007, Hugh Messenger wrote:
> Something I noticed whilst comparing the postgres and mysql rlm_sql
> configs is that when postgres sets an AcctStartTime or AcctStopTime, it
> figures in the delay time, whereas the mysql queries don't.

Yep. This was something I added a couple of years ago because I realised that 
my report database was spending half of its time recalculating the the 
Session Time every time the report was rerun. Its much more efficient to do 
it once, and then just set the delay time to zero which gives the same end 
result to any reports/scripts that expect to have to calculate it itself.

> For example, in accounting_stop_query, postgres has:
>
> AcctStopTime = (now() - '%{Acct-Delay-Time:-0}'::interval)
>
> . whereas mysql just has:
>
> AcctStopTime       = '%S'
>
> Where %S is "request timestamp in SQL format".  Which should be pretty
> darn close to now().

Yeah. They both work out the same to within a couple of miliseconds with 
now() being more efficient in terms of DB parsing. I though I had changed 
everything back to %S (for accuracy reasons when running radrelay) but it 
appears that I let cvs head and the 1.1.x branch get out of sync. Fixed.

> I presume this is just down to the personal preferences of whoever wrote
> the two sets of queries?  Personally, I wouldn't expect the queries to
> apply the delay time to the start/stop time, so the MySQL version is
> "correct".  IMHO interpretation of the data should be left to the backend
> provisioning.

Yeah. It's all my fault :-)

No really.. Once you have millions of records hitting your accounting DB 
every day you start to care about doing things the most efficient way.

But, if you follow your reasoning you we shouldn't:

Calculate 64bit (ie >4GB data trafic) values with:
(('%{Acct-Input-Gigawords:-0}'::bigint << 32) 
+ '%{Acct-Input-Octets:-0}'::bigint)

Calculate missing start time on "accounting_update_query_alt" with:
('%S'::timestamp -  '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::interval)

I am sure you can see why both a very usefull to have...

> No biggie, just an observation.

I take the the viewpoint that the more work I can do inside with FreeRADIUS 
or the DB (at record commit time) the less work (and questions on the 
mailing list) that the end user has to do. I like software that just does 
the "right thing" wherever possible, and I think calculating this sort of 
rubbish out so that the end user never even sees it is what the end users 
actually want.

The alternative is that we have to explain to every user how to recreate 
missing start times on a subset of their records, add a Gigaword to an Octet 
to get data usage and subtract start and stop delay times to get accurate 
records.
Also, consider how well DBs index functions as opposed to plain datetime 
fields (Im not sure if MySQL can even DO it..)

If you still feel differently, please speak up, I am open to suggestions. 
No-one was taking much care of the postgresql code when I started using it 
about 6 years ago, so I just kinda adopted it and fixed/sped/cleaned things 
up as I ran into them. I really need to sit down and port it all across to 
the other DBs.. I did significant work on the default indexes also with 
performance tests on multi GB tables...

Cheers
-- 

Peter Nixon
http://peternixon.net/