[Spce-user] TIPP: database replication with tungsten

Mon Oct 15 07:18:32 EDT 2012

Hi Skyler,

sorry form y bad english :-)

1.
We have tested some month the different replication solutions and products and find out that the tungsten replicator is for us the better one. It is very easy to administer and configuration. In some reasons of failed the restore of a Master and/or a slave is very quickly and easy for the administrator. Note: We have tested only solutions which support a multi-multi master architecture.
In a multi-multi-master architecture with n Nodes every node is a master for n-1 slaves and a slave for n-1 masters. To insert additional Masters ore slave is very easy.
The advantage of tungsten is, that it is a solution which are not integrated in mysql and support replication to other types of database-servers like postgres ore oracle. So it is Database-system independent.

2.
Triggers are some SQL-Scripts which are a part of a table-definition.  A Trigger become active when the table come in the state where is defined as active-statement in the trigger.
Example:
You can defined a Trigger which is automaticly executes after (or before) an Insert (or an update). Triggers are useed to guarantee the data Integrity of the tables. An example from NGCP is to save a new subscriber. The ngcp-admin-scripts save it to the provisioning-tables, the Triggers of this tables ensure that the data is written to the kamailio-subscriber-table. 
The problems of using Triggers are, that the triggers be a part of the data-logic of the application and is moved to the database-server, so that a extensive documentation in the perl ore php code is recommended. If you customize the triggers byself and the maintainer of the source-code make an update of the tables you lost your own customizations of triggers. 
Triggers are not defined in a file, they are a part of the tabledefinition. Using the tool “MySQL Wokbench” it is very easy to customize the triggers.

3.
The problem with a shared kamailio.location table is, that kamailio primary handle the location in memory (Module usrloc). This Module have different modes to work with a databasetable (see kamailio usrloc documentation). So that a second proxy don’t know anything over an registration which is made on the first proxy also not when using a shared location table. So you need to distribute the REGISTER-Request to all the proxy-servers in the Cluster. You can make this on sip-level in the kamailio-scripts. 
When using the location table as shared table there exist an entry for every registration on every proxy (when database mode is used) in the Field “socket” you will find the socket of the kamailio-server on which the REGISTER-Request is received. For Example: You have 2 proxys (192.168.1.11 and 192.168.1.12) you find for each registration of a subscriber two records in the table. One with entry “udp:192.168.1.11:5062” and on another one with entry “udp:192.168.1.12:5062” in the filed “socket”. On this time everything works fine, but there exist a few other problems:
1.     First the usrloc Module will given an warning entry in the kamailio-log every time it found a record in the usrloc table with foreign socket, but he ignore this record. It is a “beauty error”.
2.     The second problems are in the code of NGCP-collectd-plugin for statistiks: In the SQL-Statemnt which is query the Number of registration you will insert a “GROUP by callid” Statement so that all entrys in the location-table witj the same callid are count as one
3.     I am an testing what function are used with the XML-calls from NGCP-admin to the XML-Interface of kamailio. I don’t know today which is the way to automaticly used all defined proxys for XML-Calls (for exmaple to delete registration from the admin-Interface). I think that the table xmlhost and xmlhostgroups are the keys for that, but I don’t found any documention about this, because I think that is a function of the PRO-edition. 

To the last:
What was the intention to use database-replication with tungsten for NGCP CE? 
I have seen that all the service to work on one machine produce some CPU Peaks, so that SIP-latency grow up an we have some “broken” registrations and SIP-timeouts. Our first measure was to use 2 machines: 1 machine for lb, proxy and SBC (the kamailio- and the sems/asterisk services) a 1 machine for the other services like mediator, rate-o-mat, backups, web-services (ngcp-admin, ngcp-csc), statistics an so on, there we using a master-master-replication from mysql. This was build on a customized NGCP 2.4 and work very well, but when the replication stop in some reasons it was very complicated to restore it.
But now we want upgrade to 2.6 because the most of our customizations are being a part of 2.6 and was implemented much more elegant from the sipwiseteam. Now we tested a lot with using 4 machines and tungsten replicator (one machine for the lb-service, two ore more machines for proxy/asterisk/sems services an on machine for the other services like web, rateomat, mediator an so on). In this szenario the master-master-replication from mysql can not use because it can replicate only two machines in master-master-mode. After a few week testing the different solutions we using tungsten as replication-solution. 
Klaus Peter

Von: Skyler [mailto:skchopperguy at gmail.com] 
Gesendet: Sonntag, 14. Oktober 2012 09:52
An: Klaus Peter v. Friedeburg
Cc: spce-user at lists.sipwise.com
Betreff: Re: [Spce-user] TIPP: database replication with tungsten

Hi,

 Thanks for sharing, a few questions on this:

1. How is using tungsten replicator better than http://www.percona.com/doc/percona-xtradb-cluster/features/multimaster-replication.html 
2. What are these table-triggers needed for? Which file(s) to edit?
3. How do you avoid replicating the kamailio.location table? or is this best used behind a virtual IP?

--Skyler
On Sat, Oct 13, 2012 at 5:34 PM, Klaus Peter v. Friedeburg <friedeburg at aco.de> wrote:
Hi all,

if you want to use tungsten replicator for replicate the database in multi-master Setup you must edit the table-triggers.

Insert a new function to all the databases:
**************************************
create function is_master()
    returns boolean
    deterministic
    return if(substring_index(user(),'@',1) != 'replicator',true, false);
**************************************

In the trigger definition insert AFTER the DECLARE lines
**************
  if is_master() then
       ........
  end if;
**************

Klaus Peter
_______________________________________________
Spce-user mailing list
Spce-user at lists.sipwise.com
http://lists.sipwise.com/listinfo/spce-user

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20121015/a08bef86/attachment-0001.html>