December 16, 2013
Most of the server side applications are database driven these days i.e. they store the configuration in database so as to support on the fly changes without a need of restart. But this requires monitoring of all the configuration tables at regular intervals. Most of the monitoring cycles are a simple waste of CPU as the configuration changes are not going to be that frequent. This problem may be overcome to some extent by having a larger monitoring interval but in that case the database changes will not be incorporated in real time.
Database Trigger Mediation Layer (DTML) addresses the above mentioned issues in an elegant and rather conservative manner for the applications using MySQL as the database on a Linux box.
The above problem may be addressed in two possible ways:
This approach relies on the application (making database change) to inform ALL the other applications, either on the local node or on some remote node.It requires all the application modules to implement the following function for updating the data set in DB on which other application modules are also dependent :-
Application module makes changes to the database.
(Message to notify data changes to application modules located on the same server and other servers in the database cluster). Interested Application module implements the following function for receiving notification messages:-
This approach frees the application (making the database change) from the responsibility of informing the dependent applications. Hence, the application (making the change) need not be aware of other interested applications. It makes use of MySQL triggers and MySQL “sys_exec” User Defined Function
MySQL trigger: A named database object that is associated with a table and that activates when a particular event occurs for the table. A trigger is defined to activate when an INSERT, DELETE, or UPDATE statement executes for the associated table. A trigger can be set to activate either before or after the triggering statement.
User Defined Function: A function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment. In SQL databases, a user-defined function provides a mechanism for extending the functionality of the database server by adding a function that can be evaluated in SQL statements (like triggers)
“Sys_exec” UDF: sys_exec takes one command string argument and executes it. sys_exec() UDF function can be used to run any shell command (via database triggers). This shell command can in turn invoke an application which may notify all the interested applications about the trigger condition
During initialization time, MySQL triggers are associated with the tables storing data which many application modules are interested.
(Message to notify data changes (table and row) to application modules located on the same server and other servers in the cluster)
Interested Application module implements the following function for receiving notification messages:-
DTML uses the application agnostic approach primarily because of three reasons:
DTML uses multicast approach in favor of point to point approach because:
An application may ask DTML to monitor a table for some specific condition and to notify the application (synchronously) whenever that situation is encountered. DTML has been designed to work in a clustered deployment and hence DTML generates a real time cluster wide notification and not just a local notification on the node on which changes were recorded.
DTML solution consists of three components:
NetLink sockets are used for communicating between user space and kernel space
Multicast group mechanism, facilitated by NetLink sockets, is used to notify all the registered applications on the local node in a single go. UDP based multicast mechanism is used to propagate the notification to the remote kernel module(s). All the kernel nodules on the various nodes of the cluster join the same multicast group. The local kernel module sends out a UDP based multicast message on the LAN. The remote kernel module(s) receives this multicast message and propagate the notification to the applications hosted locally. A separate NetLink multicast group is used for every database table (this 1:1 mapping has to be statically defined)