AW: (RADIATOR) Question about Radiator/ORACLE performance

[Wallner Martin]

Same here, with PostGreSQL. 
The 'standard' (as in 'goodies') way to store data is not more that a (sorry, Hugh, Mike :-) gloryfied textfile with indexes. We had massive problems with the size generated (even in a month), and, after unsuccessfully trying to optimize our way to generate the billing data (which started to take more than 6 hours), we rewrote the export to our billing from the DB and the gathering of the data from Radius with rules and restraints (basically, we implemented that only the last data will be stored, as in if there is a duplicate of f.e. a Start-Record, it will be updated, not just newly written) so that only one record per type and day is recorded (Start-Alive-Stop, Start-Alive, Alive, Alive-Stop), so max 3 records per dial in session per day). Sessions over day will be held open (with the last Alive Record of the day, and then continued with a new record for the next day)... This compressed our Database from around 30 GB per 5-6 Month to around 5 GB for now 8 Month... Also, due to the now more Updates than Inserts, auto-indexoptimization and a bit of index-tinkering, the Database stays searchable in reasonable times (the old one used to work up to 10 Mins+ to find a dataset...)

So, we are currently taking in around 15 requests (Acc and Auth) per server/sec, and we don't have any congestion problems towards the Database anymore, even when the Billing-Database starts to fumble across the Accountingtable for the dayly billing run... (which now takes around 40 Mins, instead of 6+ before...)

Martin Wallner


-----Ursprüngliche Nachricht-----
Von: owner-radiator at open.com.au [mailto:owner-radiator at open.com.au] Im Auftrag von Joe Hughes
Gesendet: Freitag, 06. Juni 2008 18:10
An: Ricardo Martinez
Cc: radiator at open.com.au
Betreff: Re: (RADIATOR) Question about Radiator/ORACLE performance

> I have a Radiator configured exclusively for accounting. All received 
> requests are sent to a database using <AuthBy SQL>.  Communication 
> with the DB and inserts work great but once in a while, when DB is 
> busy, the response to a given insert could take a longer than usual

We had a similar problem on MSSQL, this was most noticeable during periods of routine database maintenance and SQL backups.  We fixed it by optimizing the Accounting table, the file-groups and most importantly the indexes (Clustered indexes can be bad in this case!).
Maybe review your indexes, or even possibly remove them. We have a trigger that fires post-INSERT which runs some operations on the data
- as quickly and efficiently as possible. Periodically we export the accounting table and clear it down.

--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au To unsubscribe, email 'majordomo at open.com.au' with 'unsubscribe radiator' in the body of the message.

--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.