MySQL InnoDB Or MyISAM For CDR

Home » Asterisk Users » MySQL InnoDB Or MyISAM For CDR
Asterisk Users 11 Comments

Which one (InnoDB or MyISAM) is preferred for CDR as far as write performance is concerned?

Thanks, Matt

11 thoughts on - MySQL InnoDB Or MyISAM For CDR

  • MyISAM would be best, in my opinion. The features that cause the little bit of performance overhead in InnoDB wouldn’t be necessary for CDR storage.

    – Logan

  • Very good point. For revenue critical data like CDRs, being ACID compliant is important.

    MyISAM is compliant. And like InnoDB, can have the features making it compliant turned off.

  • Our top priority is the raw Write (INSERT) performance, Read (SELECT) performance is not important. Strict ACID compliance is not necessary either. MySQL (on a separate database server) should be able to handle inserting CDR records (approximately up to 10 records for each call) for about 1000 concurrent calls coming from an Asterisk cluster.

    Matt

    Date: Tue, 25 Sep 2012 18:19:50 -0500
    From: logan@keobi.com To: asterisk-users@lists.digium.com Subject: Re: [asterisk-users] MySQL InnoDB or MyISAM for CDR

    Very good point. For revenue critical data like CDRs, being ACID compliant is important. MyISAM is compliant. And like InnoDB, can have the features making it compliant turned off. MyISAM would be best, in my opinion. The features that cause the little bit of performance overhead in InnoDB wouldn’t be necessary for CDR storage.

    Iirc InnoDB is ACID compliant so might be preferable if MyISAM is not. More information here:

    http://en.wikipedia.org/wiki/ACID

    https://blogs.oracle.com/MySQL/entry/comparing_innodb_to_myisam_performance

    Regards, Patrick

  • MyISAM is faster (on Linux anyway); but you’d better have a UPS on the machine, because it is not very tolerant of unclean shutdowns.

  • Am 26.09.2012 10:45, schrieb A J Stiles:

    You should not havy any problem on inserting into MyISAM or InnoDB – for my opinion. How many calls do you expect? And consider the following:
    the insert query is done by atserisk after hagnup. So your user will not notice it.

  • I’d go for MyISAM and would set up a remote replica if data integrity is important.

    If you have like 1000 calls of (say) 30 seconds avg length, and you create
    10 events per call, you would expect an event every three seconds. This is about 300 inserts per second. Say 600 at peaks. This should be feasible with server-grade hardware without much difficulty. Also as you always INSERT it behaves as a log file (no seeking, no locking) if the table is optimized. l.

    2012/9/26 Matt Hamilton

  • Date: Thu, 27 Sep 2012 10:23:35 +0200
    From: lenz.loway@gmail.com To: asterisk-users@lists.digium.com Subject: Re: [asterisk-users] MySQL InnoDB or MyISAM for CDR

    I’d go for MyISAM and would set up a remote replica if data integrity is important.

    If you have like 1000 calls of (say) 30 seconds avg length, and you create 10 events per call, you would expect an event every three seconds. This is about 300 inserts per second. Say 600 at peaks. This should be feasible with server-grade hardware without much difficulty. Also as you always INSERT it behaves as a log file (no seeking, no locking) if the table is optimized. l.

    We decided to go with MyISAM since it supports concurrent
    inserts (as you suggested). Data integrity (a slight loss of call records) is something we can live by. Right now we use DRBD for replication, but I guess with MyISAM it doesn’t make much sense if the db crashes. We are looking into other options as well.

    Thanks.

  • Another option that seems to be very good for handling logs where you write quite a lot is Cassandra – http://cassandra.apache.org/ – but of course you lose the SQL layer on top – unless you go for something like http://blog.mariadb.org/announcing-the-cassandra-storage-engine/

    This may not be completely off topic here because you get high data security / crash protection and parallel cluster writes, so you could insert tens/hundreds of thousands of events per second on a suitably dimensioned cluster for an Asterisk server cluster of similar size 🙂
    l.

    2012/9/28 Leif Madsen