Peter, First off, thank you very much for your long and insightful mail. As always, experience is king and I will listen to you & start learning Postgre. Have a good one, Umut
On Sun, 13 Jul 2003 00:46:07 +0300 Peter Nixon <[EMAIL PROTECTED]> 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 > > -- > > Peter Nixon > http://www.peternixon.net/ > PGP Key: http://www.peternixon.net/public.asc > > > - > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html > - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
