Re: [asterisk-users] MySQL InnoDB or MyISAM for CDR
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 leif.mad...@asteriskdocs.org On 27/09/12 11:45 AM, Matt Hamilton wrote: Date: Thu, 27 Sep 2012 10:23:35 +0200 From: lenz.lo...@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. This may or may not be relevant, but you can also check out MySQL/Galera[0] for clustering solutions. Not sure if that gets you closer or further from your goal though :) It uses a modified InnoDB to allow a multi-master MySQL cluster. I used a chef cookbook to deploy it[1]. [0] http://www.codership.com/content/using-galera-cluster [1] http://support.severalnines.com/entries/21453521-opscode-s-chef-mysql-galera-and-clustercontrol -- Leif Madsen http://www.oreilly.com/catalog/asterisk -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users -- Loway - home of QueueMetrics - http://queuemetrics.com Test-drive WombatDialer beta @ http://wombatdialer.com -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] MySQL InnoDB or MyISAM for CDR
On 27/09/12 11:45 AM, Matt Hamilton wrote: Date: Thu, 27 Sep 2012 10:23:35 +0200 From: lenz.lo...@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. This may or may not be relevant, but you can also check out MySQL/Galera[0] for clustering solutions. Not sure if that gets you closer or further from your goal though :) It uses a modified InnoDB to allow a multi-master MySQL cluster. I used a chef cookbook to deploy it[1]. [0] http://www.codership.com/content/using-galera-cluster [1] http://support.severalnines.com/entries/21453521-opscode-s-chef-mysql-galera-and-clustercontrol -- Leif Madsen http://www.oreilly.com/catalog/asterisk -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
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. 2012/9/26 Matt Hamilton mistral9...@hotmail.com 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 -- Loway - home of QueueMetrics - http://queuemetrics.com Test-drive WombatDialer beta @ http://wombatdialer.com -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] MySQL InnoDB or MyISAM for CDR
Date: Thu, 27 Sep 2012 10:23:35 +0200 From: lenz.lo...@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. -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] MySQL InnoDB or MyISAM for CDR
On Tuesday 25 September 2012, Matt Hamilton wrote: Which one (InnoDB or MyISAM) is preferred for CDR as far as write performance is concerned? Thanks, Matt 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. -- AJS Answers come *after* questions. -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] MySQL InnoDB or MyISAM for CDR
If I remember correctly, INNODB offers row level locking while MyISAM does not. On 09/26/2012 05:18 AM, Thorsten Göllner wrote: Am 26.09.2012 10:45, schrieb A J Stiles: On Tuesday 25 September 2012, Matt Hamilton wrote: Which one (InnoDB or MyISAM) is preferred for CDR as far as write performance is concerned? Thanks, Matt 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. 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. -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
[asterisk-users] MySQL InnoDB or MyISAM for CDR
Which one (InnoDB or MyISAM) is preferred for CDR as far as write performance is concerned? Thanks, Matt -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] 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 On Sep 25, 2012 4:15 PM, Matt Hamilton mistral9...@hotmail.com wrote: Which one (InnoDB or MyISAM) is preferred for CDR as far as write performance is concerned? Thanks, Matt -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] MySQL InnoDB or MyISAM for CDR
On 09/25/2012 11:18 PM, Logan Bibby wrote: 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 -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
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. On Sep 25, 2012 6:12 PM, Patrick Lists asterisk-l...@puzzled.xs4all.nl wrote: On 09/25/2012 11:18 PM, Logan Bibby wrote: 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 http://en.wikipedia.org/wiki/ACID https://blogs.oracle.com/**MySQL/entry/comparing_innodb_** to_myisam_performancehttps://blogs.oracle.com/MySQL/entry/comparing_innodb_to_myisam_performance Regards, Patrick -- __**__**_ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/**mailman/listinfo/asterisk-**usershttp://lists.digium.com/mailman/listinfo/asterisk-users -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [asterisk-users] MySQL InnoDB or MyISAM for CDR
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: lo...@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. On Sep 25, 2012 6:12 PM, Patrick Lists asterisk-l...@puzzled.xs4all.nl wrote: On 09/25/2012 11:18 PM, Logan Bibby wrote: 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 -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users -- _ -- Bandwidth and Colocation Provided by http://www.api-digital.com -- New to Asterisk? Join us for a live introductory webinar every Thurs: http://www.asterisk.org/hello asterisk-users mailing list To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
