schu
Peter Nixon wrote:
On Sat July 12 2003 23:32, Umut Destan wrote:
Hello list, I think I got the sql accounting working, thanx for the answers for that Q. Now I read the document Peter Nixon wrote about VoIP Accounting,.. Isn't MySQL just as par with PostgreSQL in terms of speed? If I had 1 AS5350 sending only little amount of accounting data to freeradius, wouldn't MySQL be able to keep about with it also? Does MySQL really mess up with dates? I'm asking this because it's weird how popular MySQL is it has a flaw like that..
Hi Umut
Disclaimer: I have not used MySQl version 4 so my info may be a little out of date.
Now first of all speed is a subjective thing. It depends very much WHAT you are doing. MySQL has a reputation of being blazingly fast at being a website backend. That is, it is very fast at doing simple SELECTS and pumping data out for display.
HOWEVER
MySQL does not have the full ANSI spec worth of queries, for instance it cannot do SUB SELECTS. It also does not have VIEWS, STORED PROCEDURES and many many other nice things you need to do advanced DB work. The biggest problem with it though is its very poor locking system when doing INSERTS and especially UPDATES. It is also not ACID compliant so there is a posibility that if your system crashes at the right (wrong) time you can lose data even though the database says it has commited it.
The locking is the biggest problem on a RADIUS server though, as it can be many many times slower (10-100 times) than postgres when under high load and doing many UPDATES, while the latest version of Postgres is very similar speed at doing SELECTS now. (It _used_ to be much slower but those speed problems have been fixed)
These are all very good reasons to use Postgres (which is a full featured, powerfull, mostly Oracle compatible database) instead of MySQL (which should more appropriately be called SQLite :-) and you will find many more if you start to play with Postgres. Put simply though, the big reason why you cannot use MySQL to do accounting for Cisco VoIP VSA accounting is that the DATE support in MySQL sucks. Not just a little bit. It sucks like your grandmother sucks on her false teeth! Its Date/Time support is _even worse_ than MS SQL and _that_ is saying something!!!
If you use a Postgres (And have your NASes NTP time synced. You DO have your NAS NTP times synced don't you??) you can simply take the h323starttime, h323setuptime and h323stoptime directly from a RADIUS Stop packet and INSERT it directly into a "timestamp with time zone" field and everyone is happy. You can have NASes in direrent timezones and query from your DB as if they are all in the same timezone, or customers who want their billing records relative to a different timezone etc etc.. EVERYONE IS HAPPY.
With MySQL you are screwed.
You can't INSERT a cisco timestamp into MySQL as it doesn't recognise it, this forces you to do the generic RADIUS style DB accounting as per the default config which basically sucks for VoIP compared to my schema/queries as you have to generate the timestamp on the radius server not the NAS.
This is BAD BAD BAD for several reasons:
a) There can be a delay which means you have to do post processing of your data to make it valid
b) You MUST do an INSERT of Start RADIUS records to get the Start time of a call, then an UPDATE (Remember that MySQL is about as fast as my mother with a deck of punch cards at doing DB UPDATES) when a Stop records arrives. If you missed the Start packet you then again have to do post processing of your data to generate the Start time from the Stop time minus the Call Duration.
When you have two cases that mean you have to post process your data before it is usefull to you, then there is actually no point at all in having a live Database. You will find it is quicker to simply use an import on you detail files script once per hour than fix the dataset in your DB.
(this actually applies to anyone using the default FreeRADIUS sql queries regardless of DB backend, but most ISPs don't care about correct CDR's, they just want a sum of how many minute each user has been online, hence the Start and Stop time is of secondary importance.)
MySQL also has the very nasty habit of shooting itself in the head when it gets under high load (Too many UPDATES on the DB at once) You _will_ hit this problem! Using one Cisco 5350 I found I could generally crash the DB and therefore FreeRADIUS also at about 60 or so concurrent calls (Each 5350 can handle 120 calls or 4 PRIs). It does vary depending on how many short (or zero) length calls you have.
With Postgres I can run around 500 INSERTS per second with a 3 field UNIQUE index:
"create UNIQUE index stopvoipcombo on stopvoip (h323SetupTime, nasipaddress, h323ConfID);"
If you don't make it UNIQUE you can get more as its Postgres doesn't have to do contraint checking...
This translates to _many_NASes worth of calls instead of less than one NAS with MySQL.
MySQL 5 is slated to have _most_ of the features of Postgres.. I think its still a year or 2 away. Intil then use Postgres.
Oh, I forgot to mention the lovely INET field types in Postgres that lets you do all sorts of queries, groups and sorts on IP addresses, and many other cool features that make life with Postgres fun (If a DB can be fun) and life with MySQL a major PITA.
My collegue is trying to duplicate the stuff I have done with Postgres in MS SQL and it can't even do half of it. Unless you want to buy Oracle (Which is about 100x slower than Postgres but _does_ have more features) there isn't a DB that comes close! (I am sure that comment is going bring indignant replies from any Sybase/Firebird/FastDB/SAPDB users on the list but what would they know :-)
Have fun.. Feel free to ignore this email if you don't believe me, or switch to Postgres and never look back...
Cheers
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
