Data replication and its monitoring

Revised version of this report has been published in: Zeszyty Naukowe Wydziału ETI Politechniki Gdańskiej: Technologie Informacyjne, volume 12 (2007) pages: 153-160 ISBN 978-83-60779-01-9

ZESZYTY NAUKOWE WYDZIAŁU ETI POLITECHNIKI GDAŃSKIEJ Nr 5

Seria: Technologie Informacyjne

2007

Marek Kamiński Department of Software Engineering, Gdańsk University of Technology Lufthansa Systems Poland Sp. z o.o.

DATA REPLICATION AND ITS MONITORING Abstract Requirement for IT systems monitoring become increasingly important. It concerns many different issues related to their functioning, so it cannot be generalized easily. This paper describes approach used in Lufthansa Systems Poland to solve problem regarding data replication monitoring. I show in it many issues from general point of view and outline developed system architecture, functionality, interface, and its working principles and point out how those ideas may apply to similar problems.

1. INTRODUCTION Our present world can be characterized by very fast and progressive informatization of almost all domains of our lives. Despite huge progress, almost every complicated system still requires some kind of supervision, control and needs sometimes repair. Many systems have to be up and running all the time and their unplanned downtimes are unacceptable. This is why many IT companies offer their customers – among other services – technical support, assistance and taking care of their systems for 24 hours per day and 365 days per year. This requires at least efficient monitoring because we can never be sure that systems work properly without taking periodical measurements of their states. Almost every system works on the basis of information it has (so it takes some input). Information can be either inserted to them manually, or be delivered to them automatically – for instance, by means of one of many data replication methods. Information is very valuable nowadays, especially when it is up-to-date. Usefulness of outdated information is rather doubtful. Importance of data timeliness is visible (for instance) on stock markets or in flight planning related issues. This is the reason why this article concerns issues related to data replication monitoring.

2. DATA REPLICATION Data replication is the process of creating and managing duplicate versions of data. Various ways and types of data replication exist. We can use snapshot methods, remote replication methods (synchronous or asynchronous), and consider hosts-based, appliancebased or storage-based solutions. Regardless of method, ultimate goal of replication is

2

Marek Kamiński

always the same – to create a complete copy of the source data and keeping replicas synchronized, so that changes made to one replica can be reflected in other replicas. In case of databases, many users can work with their own local copy of a database but have data updated as if they were working on a single, centralized database. In applications where users are geographically widely distributed, replication is often the most efficient method of database access. For the need of this article I assumed the following replication model: insert update target database #1

delete

insert insert update

update

replication method source database

target database #2

delete

delete insert update

Fig. 1. Data replication

target database #n

delete

This model shows unidirectional data replication. Any insert, update or delete operations performed against source database are remembered (queued) by replication mechanism and are performed by it against target (usually remote) database or databases when their turn come. Unidirectional means that data are sent only from source to target, so when target database users perform their individual insert, update or delete operations against their database, those operations are not replicated back. In shown replication, all changes made against source are visible also on target, but reversed situation does not occur. Cloud symbolizes means of data transport and the way in which replication is done. We distinguish direct replication, when data is transported directly from source to target database (when replication path does not contain any intermediate points) and indirect replication, which uses some extra storages (like databases) located between source and target database or databases, and such situation is considered in this article. Ideal replication should fulfill the following requirements: • • • • • •

supports database transactions and preserves data consistency and operations order applies changes immediately and is capable of replicating changes made at any node applies to heterogenous environments, consisted of various vendors solutions is failure resistant and fully transparent for users and database applications is fully scalable and keeps performance high regardless of number of nodes does not cause any additional overhead on resources and locks system contention

Many of them are fulfilled by HVR (HIGH VOLUME REPLICATOR) made by PSB (company from Netherlands) and it is widely and with success used by Lufthansa Systems Company to provide its customers with the data they need. It functions here as indirect replicator and the intermediate point on replication path (called line) is called hub database. After operations against source database are replicated to hub database, replicator processes start to distribute them to many other target databases. Those target databases may act as source databases for next replications. This tree-style connection (called cascade connection and shown in figure 2) can be continued and subsequent levels of replication can be added. HVRMONITOR system – developed in Lufthansa Systems Poland Company – allows for monitoring replication in it:

3

Data replication and its monitoring

tgt db #1

tgt & src db #2

tgt db #n

hub #j

tgt & src db #n+1

hub #1 tgt db #3

tgt db #n+2

src db #1

hub #i tgt db #m -1

tgt & src db #m

hub #u

tgt db #w

Fig. 2. Replication topology

To perform replication of operation regarding any table, HVR uses additional tables called buffers. Each replicated table is associated with exactly one pair of buffers on source, hub and target. After insert, update or delete is executed against source database tables, database trigger executes proper procedures and inserts information about them into one of proper pair buffers. Next, HVR processes take this information and send it farther, to those counterparts of affected tables, which are located on the hub database. HVR process, which operates on hub database, starts then to perform so called routing replication phase which distributes operations to hub database buffers associated with proper target databases tables. HVR is very flexible, so during this phase, information can be modified, some additional columns can be added or dropped from database record, information can be split into two pieces, record values can be modified in many ways, and so on. After routing is done, other HVR processes take this information from target databases buffers located on hub, send it farther to buffers on target databases and after they are there, operations performed against source database are repeated against target databases. Information from target database buffers materializes in target database tables. Replication cycle is over.

3. MONITORING Monitoring is the act of collecting information concerning the characteristics and status of resources of interest. Monitoring has been generalized in figure 3: Monitored system

resu lts retu rn process

Monitoring system

m eas urem ent proces s

Fig. 3. Monitoring

sr et e m ar a p d er ot i n o m

.. .

e mi t ni el b a e g n a h c sr et e m ar a p

4

Marek Kamiński

Monitoring system consists of many elements and should fulfill many requirements. It can be either centralized or distributed. It is responsible for integration and processing of all incoming data from all monitoring elements. It presents collected and processed data in convenient way and provides an interface for its users. Sometimes it even makes some decisions based on collected information, performs some actions, etc. Measurement process is a process of examining many characteristics and statuses of resources of interest of one or many monitored systems. Ideal measurement process should be nonintrusive for monitored system and frequency of measurements should be properly adjusted to frequency of monitored phenomenon. Monitored system could have almost any complexity which cannot be assumed beforehand. It should be always possible to extract from set of its characteristics and parameters changeable in time, subset of those ones which have significant meaning and should be monitored. It should be also possible to take measurements of them. Results return process is a process which delivers collected data to monitoring system. Length of delivery path, speed, accessibility and availability of communication medium used for this process and means which can be used for delivery, have significant meaning. Sometimes preprocessing and filtering of collected data should be considered to improve overall system performance. In ideal system, this process should be nonintrusive for communication medium and be as fast as it is needed to meet intended system goals.

4. MEASURED PARAMETERS SELECTION Parameters measured by HVRMONITOR, were inferred from customers requirements. Their scope was also limited to those which can be relatively easily obtained from HVR. Customers usually want to know the answers to the following questions: • • • •

what is replication state (is it up and running or is it failed)? when (or weather) data in their database are up-to-date? what kind of data are waiting for their replication (remain in replication queue)? what is the average replication speed?

Unfortunately their answers lie in many sources and information regarding replication progress is distributed. Some its parts are in source, hub or target databases involved in replication, some can be found in monitored replication lines log files, and some (like average replication speed) are not given explicitly and have to be deduced from events or measurements registered earlier. Data of interest should be collected from many different hosts and databases located on them and this is why constructing such monitoring tool like HVRMONITOR was not trivial. Many challenges and faced and solved problems are common to other applications, so proposed solutions can be useful also for them.

5. HVRMONITOR ARCHITECTURE Before describing problems and their solutions, I outlined HVRMONITOR architecture in figure 4. HVRMONITOR architecture consists of four tiers: The 1st tier is made up of DataBase Administrator’s workstation with his Internet browser which displays web page with regularly refreshed content (for instance: one refresh per 3 minutes) and shows information regarding replication states. This information is

5

Data replication and its monitoring

measurement process

retrieved from the 2nd tier. Any number of browsers can display this interface. Regardless of their number, databases load and system load related to monitoring stays the same. The 2nd tier works quite independently of the 1st tier. Its scheduler ensures that scripts responsible for measurements and data processing are executed regularly. This tier, basing on configuration entries, initiates each measurement on remote machines (indirectly, with the use of the 3rd tier), puts their results (outputs) to one place, processes and inserts them into database. It also sends emails to provide information about replication in other way.

system server

m achine with replication hub #1

script logs

hub

src

output

system server

system scheduler workstation with browser

config file

outputs m achine with replication hub #n-1

mail server

scripts

script

script logs

hub

src

www server

the first tier

database

the second tier

results return process

output

the third tier

m achine with replication hub #n

script logs

hub

src

output

the fourth tier

Fig. 4. HVRMONITOR architecture

The 3rd tier performs measurement actions in behalf of the 2nd tier. If the 2nd tier does not request them, this tier does nothing. Every time those actions are triggered by the previous tier, this tier connects to all 4th tier machines defined in configuration file entries, executes on them proper monitoring scripts and transfer their results to outputs folder. The last, 4th tier is made up of machines with hubs. Only on them real monitoring is performed, because only them contain significant information. Besides having hubs (with replication buffers), only they contain also replication lines log files. It is also good to mention here that source, hub or target database or databases do not have to be located on the same host (this is possible but not mandatory). As mentioned earlier, this tier machines contain also monitoring script which retrieves useful information from replication lines hub buffers, from their log files and – if located on the same machine – from source databases.

6. FACED PROBLEMS, DEVELOPED SOLUTIONS AND MECHANISMS During development and implementation of HVRMONITOR, many problems have been faced. I described some of them briefly in a generalized way because such approach shows their potential usefulness in similar issues. Five groups of problems were distinguished:

6

Marek Kamiński

The 1st group concerns non-interactive access to remote machines. Each user connects to remote machine, using ssh method. Access to account is granted when user provides valid password. If operations need to be performed on many machines quickly, research aimed at development of proper automation methods should be done. One of such methods involves local and remote machine mailsystems. In this email-based method, local machine sends emails, remote machine receives, processes them, performs requested actions and sends results back. This is not very reliable, mails transfer can take long time and it is asynchronous way of communication (so emails order is not kept). Other, rather faster, methods are based on SNMP or on other protocols. All of them needs proper configuration of appropriate network services on monitored environment. If it is maintained by company providing monitoring, proper configuration can be done easily. If it is maintained by customer (who have different security rules), such configuration is a problem. Fortunately, almost always ssh (secured shell) way of access is granted, because otherwise almost none maintenance is provided. It allows for remote login (encrypted connection) so person who takes care of system can work remotely. Ssh often comes with scp (secured copy) so bidirectional file transfer is possible. The only problem is the need of typing passwords. It was solved by identification and utilization of ssh keys mechanism. When user establishes connection with remote machine, asymmetrical cryptography is used instead of passwords. One pair of correlated keys (private and public) is used by authorization. After keys pair generation (ssh-keygen command) is done, private key should be placed in secured local machine location and public key should be placed in proper remote machine location. Since now no password is needed, and each command can be run with no user interaction. The 2nd group concerns transmission speed. Path from the 1st tier machine to all 4th tier machines is long. Usually when the path is longer, longer time is needed to transport commands and data. This was solved by introduction two useful techniques: compression and parallelism. Parallelism is used between the 3rd and the 4th tier and concerns overall measurement process which consists of individual measurement process per each hub. All those processes start to work simultaneously, so they are executed in parallel. Answers can arrive to the 3rd tier at different time intervals, what depends on many factors. Compression is used between the 3rd and the 2nd tier. When all results (outputs) are collected on the 3rd tier (or when deadline occurred), they are compressed and retrieved (using scp method) by the 2nd tier. Those methods seem to be simple but they are very powerful. Data compression was implemented by PSB in their HVR product. Also parallelism is worth consideration if possible, because as HVRMONITOR shows, it can speed up developed system many times. The 3rd group concerns db operations speed. Problem is not noticed in small databases. When db is many times bigger, any kind of operation (also select using indexes) takes considerably longer time. It was solved in two ways. Process which removes periodically all unnecessary data from tables has been installed. Such processes are known as cleanups. For backup reason, all data designated for removal are copied earlier to some other storage. This caused that web interface queries do not take as much time as they have taken before. Thanks to this, select operations performance is always not higher and not lower than some measurable, constant values, while before this solution it was getting worse as number of rows in database was increasing. The second important issue from this group of problems concerned the speed of insert and update operations. This speed became higher thanks to cleanup solution, but it was still not high enough to meet intended goals. Problem was specific because almost all considered operations were using long and nested subselect clauses (because of hierarchical data structure: hosts own many hubs, hubs own many channels and channels own many lines) – what was most likely its reason. Many subselects

Data replication and its monitoring

7

looked similarly. To solve it, method based on storing frequently used results in temporary tables were used. Thanks to such auxiliary tables acting as „cache memory”, performance increased over ten times! Those methods could undoubtedly help in many other cases. The 4th group concerns means of synchronization between the client and server. It stems from the fact that the 1st and the 2nd tier works independently. The 2nd tier inserts new bunch of data into db periodically and the 1st tier, reads and presents the latest bunch also periodically. Server works continuously, while client can be started or stopped at any time. The problem was: how to ensure that regardless of the client start time, every refresh of its content shows newer data bunch than previous refresh. It was solved by setting frequencies of refreshes to slightly different values. Server frequency is a bit higher than clients one. Since now it can be mathematically proven that each client refresh shows newer content. The last, 5th group of problems concerns data consistency and is related to the previous problem. It stems from the fact that server insert and client select operations take noticeable time. What would happen when during client selects, server were replacing older data bunch with the newer one? The image shown by web interface would become inconsistent, because it would show one data bunch as a composition of the previous data bunch and the newer one. To prevent such strange behaviour, the following mechanisms were introduced: • •

every bunch of data is given a unique number (called consistency number) when all its records are completely inserted into database tables every client refresh retrieves from many tables only data belonging to one (and the most recent) consistency number Such solution works very fine and its correctness can be also mathematically proven.

7. SCREENSHOT AND FEATURES The 1st noticeable feature of HVRMONITOR is showing hubs hierarchy. Hubs delivering data to other hubs are at the top. Each hub is represented by colorful box with customer (e.g. airline) logo. It can have many channels, each of which contains many lines. Each line can be in many states and is described completely by single row in hub box. Its state can be recognized quickly by the color of small filled square at the left side of its 3-letter code. All colors are described in legend. Important states color the whole row, as for instance line gr3 in figure 5. Each line is also described by 3 numbers. The 1st of them (rows) says how many rows wait for replication in hub buffers. The 2nd (jobAge) says how many seconds ago the last replication cycle has been finished. The last (logAge) says how many seconds ago replication wrote last information into its log file (because of some reasons this information is important). More details (names of replication line’s host and database) are shown in small yellow tooltip, as for airlineX and databaseY. When customer wants to know more, for instance: what is exact distribution of queued rows in buffers (to ensure if data of his interest arrived), DBA click on colored square and see it in the way, as it is shown for fp2. Figure 5 shows information in so called „minimized mode” (with some filtering criteria). After switching to „maximized mode” interface shows more. Filtering prevents system from showing less important data. Its other features are shown next to each customer logo. They show if considered HVR hub server process is up and running (and if so, for how long time), the percent usage of logs filesystem and the timestamp identifying time when measurement was made. HVRMONITOR system was built using MySQL, PHP and Perl technologies.

8

Marek Kamiński

Fig. 5. Screenshot

8. CONCLUSIONS HVRMONITOR is successfully used by Lufthansa Systems helpdesk. Many tests show that its features, performance and reliability fulfill essential requirements. Because it shows detailed replication information, it is more useful than previous tools. Tasks related to prior monitoring were partially eliminated. It saves time, so employee can solve other problems. Outlined ideas and mechanisms can be extended or used to solve other problems. It is quite thinkable that system could automatically count meaningful statistics in order to prove problems or find bottlenecks. Other interesting idea is to propose more general, „reactive” monitoring system. By this term I mean system, extended with the ability to repair many errors and failures automatically, and my future research will head towards this direction.

REFERENCES [1] Zdenko Škiljan,Branimir Radič: „Monitoring systems: Concepts and tools” [2] Mohamed Ziad Albari: „A Taxonomy of Runtime Software Monitoring Systems” [3] Don O'Neill – Linux Today: „Building Linux Monitoring Portals with Open Source” [4] Jim McKinstry – Computer Technology Review: „Data Replication” [5] W.Khadzynov, M.Maksymiuk: „Realizacja replikacji w systemach heterogenicznych baz danych” [6] PSB: HVR documentation (HVR white paper, HVR users guide, HVR reference manual)

REPLIKACJA DANYCH I JEJ MONITOROWANIE Streszczenie Problem monitorowania systemów IT nabiera ostatnio coraz większego znaczenia a uogólnienie go nie jest proste. Artykuł ten przedstawia jak rozwiązano w firmie Lufthansa Systems Poland problem monitorowania replikacji danych. Zarysowano w nim wiele kwestii w sposób ogólny, przedstawiono też zasady działania systemu oraz wskazano gdzie jeszcze mogą one znaleźć zastosowanie.