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/