Evaluation of Replication Mechanisms on Selected Database Systems

Abstract

This paper is focused on comparing database replication over spatial data in PostgreSQL and MySQL. Database replication means solving various problems with overloading a single database server with writing and reading queries. There are many replication mechanisms that are able to handle data differently. Criteria for objective comparisons were set for testing and determining the bottleneck of the replication process. The tests were done over the real national vector spatial datasets, namely, ArcCR500, Data200, Natural Earth and Estimated Pedologic-Ecological Unit. HWMonitor Pro was used to monitor the PostgreSQL database, network and system load. Monyog was used to monitor the MySQL activity (data and SQL queries) in real-time. Both database servers were run on computers with the Microsoft Windows operating system. The results from the provided tests of both replication mechanisms led to a better understanding of these mechanisms and allowed informed decisions for future deployment. Graphs and tables include the statistical data and describe the replication mechanisms in specific situations. PostgreSQL with the Slony extension with asynchronous replication synchronized a batch of changes with a high transfer speed and high server load. MySQL with synchronous replication synchronized every change record with low impact on server performance and network bandwidth.

High-availability clusters: A taxonomy, survey, and future directions

“…From a transaction viewpoint, synchronous replication can support all the ACID properties. Therefore, no data loss is usually associated with it [151,97]. However, synchronous replication is challenging with extended distances.…”

“…Different factors influence the selection of a method, and some of the critical factors are [3,4]: the distance between two nodes; the volume of data transported between nodes; type of data; frequency (continuous or burst); business requirements, such as DR. There is a network latency recommendation for synchronous replication, as it implies real-time mirroring, while asynchronous replication does not have the same kind of rigorous requirement [4,97].…”

“…Most database vendors provide a replica or mirroring option to set up standby databases of primary databases [97], and this configuration can effectively be integrated with a HAC. The prerequisite in such a case is that the HAC has support for the specific feature so that the HAC can recognise and support it as part of its operations.…”

PIMDB: Understanding Bulk-Bitwise Processing In-Memory Through Database Analytics

“…The query execution does not modify the database copy, so further data copying is not necessary prior to subsequent query execution. Note that maintaining database copies is common in database systems [65].…”

Database Administration Practical Aspects in Providing Digitalization of Educational Services

“…To achieve good performance in many cases and to increase database accessibility, we propose combining two concepts in MS SQL Server that have previously been used separately -replication and database mirroring [1,2].…”