asterisk2billing.org
http://forum.asterisk2billing.org/

How do I make the database redundant?
http://forum.asterisk2billing.org/viewtopic.php?f=33&t=12283
Page 1 of 1

Author:  egrep [ Fri Mar 25, 2016 3:05 pm ]
Post subject:  How do I make the database redundant?

I am setting up wholesale sip trunking. It needs to be a solid setup. Downtime is not an option.

My current plan is 2 A2billings servers installed in separate datacenters. Each talking to a common database on one or the other server. That still leaves the database as a single point of failure. At a minimum I will probably do master-slave and live with the fact I would have to manually switch over if the master is not available. Even if I create scripts and document everything and test it all beforehand it's still going to be a manual process that involves downtime.

Is there a better way? I have looked at master-master and don't think it's a very good option from what I have read. A lot more complicated and has problems with split brain etc. Not really recommended for WAN either.

So still looking for a better solution. Galera looks nice but I don't think it's compatible with A2billing (only works with Innodb and maybe some other issues).

Author:  egrep [ Fri Apr 01, 2016 3:09 pm ]
Post subject:  Re: How do I make the database redundant?

In the spirit of giving back I would like to report some preliminary findings.

A2billing v2.2
Asterisk v13
CentOS v7
Mariadb v10.1 running as a Galera cluster

Add:
Code:
innodb_flush_log_at_trx_commit = 0

To /etc/my.cnf.d/server.cnf

Galera cluster running on Mariadb v10.1 seems to get the job done. It is relatively easy to setup. 3 A2billing servers all running as masters separated by about 30ms ping time from each other. You need to install or convert all your A2billing database tables from MyISAM to InnoDB. I have installed as InnoDB from scratch and tried converting a standard install already using MyISAM and it seems to work either way without a hitch

The one major problem I ran into was importing ratecards via the GUI on the cluster. It was impractically slow. No amount of Galera and InnoDB optimization makes any significant improvement. Maybe about 200-400 rows per minute is about as fast as it gets. Even on a LAN it is slow so it is a code issue due to INNODB and/or Galera cluster rather than because of the WAN speed.

Make sure to set "innodb_flush_log_at_trx_commit = 0" (or 2) otherwise it will be extremely slow. That takes care of one issue that you will see even if just running INNODB with no Galera cluster. The other change requires a code change to /admin/Public/CC_ratecard_import_analyze.php. The problem is that commits are done for every line in the rate card which apparently is a very slow operation on Galera. What I did was create a loop so that commits are only done every 1000 lines. It's actually quite easy with the Adodb abstraction layer that A2billing uses. Just add $DBHandle->StartTrans(); at the beginning of the loop and only use $DBHandle->CompleteTrans(); every 1000 lines. That increased the speed hundreds of times to where it was close to what it would be on a standalone database.

My A2billing servers are nothing special. VPS servers with 1GB RAM and 2 CPU cores. Just some basic MySQL optimization. I followed guidelines for optimizing Galera for WAN links and did some of the basic InnoDB optimizations. Chatter between the masters is not bandwidth intensive even under heavy load.

Page 1 of 1 All times are UTC
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/