An extended infological (ER) model is proposed, which takes into account deeper knowledge of data flows in the subject area due to the added maps of data processing operations. This model is the basis of the method of designing heterogeneous databases due to the possibility of better breaking down a monolithic data model into separate logical structures and selecting a heterogeneous set of appropriate logical database models. In the example of designing a heterogeneous database in the field of e-commerce, the application of the model at the main stages of designing such a database is shown. The issues of choosing data organization logics for parts of a heterogeneous database and database management systems (DBMS) for their physical implementation are considered, taking into account the possibilities of data caching in the corresponding DBMS. The results of the study of data caching mechanisms in the NoSQL systems MongoDB and Redis, as well as the relational system MSSQL, are given. Recommendations for the use of researched database management systems for database development, including for distributed heterogeneous databases, are formulated.
-commerce
systems, the issue of scaling such systems is important. The use of distributed heterogeneous
databases (HDB) [
The existing approaches to the modeling of distributed HDBs are mainly presented in the form
of general recommendations for the division of a database monolith when designing systems for a
microservice architecture [
Choosing a DBMS for physical implementation, including parts of the DBMS, requires the
developer to clearly understand not only the logic of its operation, but also additional capabilities
and tools offered by the DBMS [
Therefore, to solve the identified problems that have arisen before the developers of GBD, the task of choosing adequate logical models and corresponding effective DBMS for the implementation of individual parts of GBD is urgent. Such task requires not only the development of a general information technology for DBMS design, but also an experimental study of the performance of special mechanisms, such as caching, in modern DBMSs.
Modern information systems require the use of high-performance databases and scalable solutions.
That is why developers use HDBs more often [
In the theory of databases, an end-to-end approach to their design through conceptual,
infological (or ER-) modeling to logical, and then physical modeling has been developed a long time ago
[
It should be noted that with the advent of non-relational models, including NoSQL models, the
importance of the ER model in the design process has increased. The ER-model continues to be the
basis for further modeling of database logic, as it is as general as possible and does not contain details
inherent, for example, to relational databases or other logics [
Currently, approaches to modeling distributed HDBs are mainly developing in the direction of
integration of existing databases into a heterogeneous system [
NoSQL systems, which provide high performance and wide functionality [
NoSQL DBMSs of the "key-value" type are quite widespread in use when it comes to data caching
[
Another quite popular type of NoSQL system is document-oriented DBMS. Each saved record
looks like a separate document with its own set of fields. Documents are characterized by flexibility
and hierarchy, which allows them to develop in accordance with the growing needs of applications.
Among document-oriented DBMS, MongoDB [
We should not forget relational DBMSs, while researching data caching mechanisms, which even
in heterogeneous solutions are the basis for storing and processing a large part of data. The model
of storing data in the form of structured tables provides efficient caching strategies because data is
easily identified based on tables and indexes. Relational databases are characterized not only by
structured data but also by the use of indexing, support for ACID transactions [
Meanwhile, the available documentation on NoSQL DBMS and existing recommendations do not provide developers of heterogeneous DBs with an end-to-end methodology for their design and selection of appropriate DBMSs, which would clearly indicate the effectiveness of the resulting DB. Solving such questions requires not only a deep understanding of the capabilities of such DBMSs but also experimental studies of additional mechanisms available in them. Thus, the study of the effectiveness of caching mechanisms in relational and NoSQL systems is relevant.
The purpose of this article is to study the effectiveness of data caching in NoSQL and relational DBMS with the aim of developing qualitative recommendations regarding the selection of DBMS for the implementation of appropriate business logic when solving the problems of building heterogeneous databases.
To achieve the goal, the following tasks must be solved: • • • • •
To develop an extended infological (ER) model as a basis for designing HDB.
On the basis of the proposed model, design HDB in the field of electronic commerce. To plan an experimental study, which includes the development of performance evaluation criteria, the possibility of configuring data in a certain format at the same time for all DBMSs, and the development of stress situations for verification.
Conduct an analysis and implement caching mechanisms in the selected for the heterogeneous database DBMSs.
Conduct an experimental study and formulate recommendations for selecting DBMSs that effectively use the caching mechanism when building HDBs.
Existing studies and practices of designing HDBs have shown that when dividing a monolithic
database into local heterogeneous parts (DBs), it is important to consider the business logic of the
system, namely, data processing operations [
Query and transaction modeling is not included in the design methodology of a classic relational
database, but the use of different semantic models to represent data processing operations is noted
by many researchers [
The paper proposes an extended ER-model (EER), which can be presented in the form of a tuple =< E, R, O >, where = { | = 1, } set of entities of the subject area; (1) set of relations (interconnections) between entities; = { | = 1, } set of data processing operations. { | = 1, }. Set of relations (interconnections) between entities ∈ represents as a matrix = ‖ ‖ ( , = 1, ), the elements of which determine the type of relationship directed from the entity Е to the entity
.
An important component of the EER model, which extends the traditional ER model, is the set of data processing operations. Every operation has certain characteristics that set requirements for the nature of its processing and describe the essence of this operation in the database. So, every operation can be represented as a tuple =< , САР ,
, >, where a vector-string = ‖ ‖ specifies the execution map with k-th operation; (2) sets - estimated frequency of the operation; of entities where the elements
= { executed on the corresponding entity
Vector string elements are indexed along the axes themselves with k-th operation and a set | = 1, } is a sequence of CRUD or E-operations
The description of the CRUDE operation can be represented by a tuple where _ vector - the order in which the j-th operator is executed within the k-th operation; ∈ { , , , , } - letter indicating the type of CRUD or E-operation; _ - the maximum number of instances of the i-th entity that will be processed by the operator.
The requirements for processing an operation according to the CAP theorem can be given by a .
, СА = ‖ ‖, = 1,3.
will take the value 1, if in the requirements for the processing of the k-th
operation there is, respectively, the requirement "(C)onsistency" for the component 1, requirement
"(A)valability" for the component 2, "(P)artitioning" requirement for a constituent 3. In the
absence of a corresponding requirement,
1. Selection and preliminary preparation of input data: at this stage, the analysis is carried out
for the selected subject area, as well as the stages of conceptual and informational database
modeling, which correspond to the traditional database design methodology; namely, UML
and other diagrams are constructed that provide the results of the analysis to the domain (for
example, a generic class diagram for preliminary modeling of the relationships between the
main entities of the domain, a data flow diagram for a general representation of business
processes, etc.), and for a more formalized representation of entities and the relationships
between them, an ER diagram is developed [
Construction of the EER-diagram: at this stage, models of data processing operations inherent in the defined business process are added to the ER-diagram in the form of operation performance maps (2). 3. EER decomposition: at this stage, the EER is decomposed into a system of interconnected local EER-models based on the analysis of a set of operation execution maps.
Determination of types of logical models for further design of local EERs: at this stage, types of logical DB models are determined (in the case of using NoSQL models, specific (3)
(4) recommendations for NoSQL DBMSs are possible) for selected local EER models, taking into account the peculiarities of logical models. 5. Logical design of a heterogeneous database: at this stage, the construction of local logical models is carried out, considering the peculiarities of data organization and limitations of the integrity of the selected data models or DBMS.
The proposed model (1)-(4) makes it possible to form a matrix on the basis of which the best options for dividing the database into separate fragments can be algorithmically found. The algorithm includes some aspects of subjectivity and requires presentation in a separate work.
The applied field of e-commerce was chosen for the research of caching mechanisms. Let us consider some points regarding using the EER model and the proposed approach to designing HDB in this area. So, the analysis and conceptual modeling of the e-commerce industry was carried out. Let us consider a fragment of the business process consisting of searching for goods, working with the shopping cart, and creating an order.
The basic ER diagram was developed using "Crow's foot" notation. Based on the basic ER diagram, an extended EER model (Fig. 1) was developed, which simulates maps of the execution of operations. Figure 1 shows the map of the execution of the operation 1 (the transaction of adding the product to the cart).
Figure 2 separately, for clarity, shows a map of the execution of several operations that are part of the developed EER model for the task of working with a shopping cart, which simulates: О1 the transaction of adding the product to the cart. О2 the transaction of editing the quantity of goods in the cart. О3 request to filter products by brand and price. О4 request to receive a list of products from the basket by the unique Basket identifier. О5 request to calculate the total amount for the products selected for the basket by the unique Basket identifier.
A transaction 2 starts with editing (operation U (Product_basket entity is used) and ends with the appropriate editing of the quantity of the available product in the entity Product. The estimated intensity of execution of such a transaction is 25,000 operations per day. Similarly, other operations related to viewing and searching for products on the website of the online store were modeled, which include operations for filtering products, counting the total number of selected products, analyzing completed orders, etc.
At the next stage of the proposed approach to HBD design, the expanded info model was decomposed into local EER models. Based on the analysis of maps of the execution of operations and the requirements for their processing, the following sets of entities were selected, which require further logical design based on a common logic of working with them (Fig. 3):
Product and Category entities (local EER Catalog) involved in joint operations to search for goods that are critical from the point of view of their intensity (more than 50,000 executions per day) and Consistency and Partition Tolerance requirements regarding their processing. Entities User, Order and Product_order (local EER Ordering), involved in joint operations to form an order and transfer goods from the basket to the order, which are not so critical in terms of the intensity of execution (up to 5000 transactions per day), but have enhanced requirements regarding Availability and Consistency of data.
Entities Basket and Product_Basket (local EER Basket) involved in joint basket operations, which have similar performance intensity characteristics (30,000 transactions per day) and the same Partition Tolerance and Consistency requirements for their processing.
It should be noted that during the decomposition of the global EER model, the analysis of operation execution maps allowed for interesting and more efficient redesign of some transactions and database entities, but this is a topic for a separate publication.
At the next stages, the most adequate logical database models and corresponding DBMS were selected for the selected local EER models for further implementation, as well as a logical model of a heterogeneous database was designed, namely: • • •
Document-oriented model and NoSQL DBMS MongoDB for the Catalog model Key-value NoSQL model and the corresponding Redis DBMS for the Basket model Relational data model and MS SQL Server DBMS for the Ordering model.
The design decisions made at the last stages are based on the peculiarities of data organization in
the selected logical database models, on the relation of the selected DBMS to the CAP theorem, as
well as on the presence of certain advantages indicated by the developers of these DBMS. Yes, Redis
was chosen, among other things, because of the small amount of data that needs to be processed in
one operation and its short-term nature. Also, the biggest need in the Basket database (getting the
list of products in the basket) can be solved by storing data under a single key and the ability not to
waste time on the "JOIN"-combination of data provided by the "key-value" logic. Document-oriented
databases are better if there is a need for complex "JOIN" queries, which can be quickly processed
due to built-in collections, in the form of which products with many properties are usually presented.
Therefore, the MongoDB DBMS will allow, when working with the catalog of goods (with a more
complex and real structure of this local model), to increase the speed of query execution due to
getting rid of complex JOIN queries, which is possible in the case of nesting collections one into
another [
Meanwhile, all DBMSs selected for a heterogeneous database support data caching to one degree or another, which left open the question of the effectiveness of the selected DBMSs and led to the study of caching mechanisms in them.
An analysis of caching mechanisms in Redis, MongoDB, and MS SQL Server DBMSs was carried out, which allowed to develop the necessary software tools for conducting research.
So, according to its structure, Redis stores data in the form of key-value pairs in RAM. This allows
for very fast access to data, as requests are processed directly in memory. Redis uses several separate
functions to directly operate the caching mechanism. A deletion policy where Redis deletes keys that
have not been used for a significant period of time is one such [
MongoDB provides different caching mechanisms than Redis that allow for complex queries and
data analysis, while Redis is better suited for cases where very fast access to data with low latency is
required. The main data storage mechanism in MongoDB is WiredTiger [
WiredTiger uses two types of caching: • •
Internal cache for storing recently used data pages.
Query plans to avoid recompiling plans for the same queries.
Support for data replication and sharding functions allows you to increase the amount of cache by distributing data between different physical machines and reducing the load on each individual server.
MS SQL Server provides a more complex caching implementation structure compared to other
DBMS. The main caching mechanism is a buffer cache that stores pages of data and indexes in RAM
[
MS SQL Server supports memory-optimized tables and indexes that are stored entirely in RAM. In-Memory OLTP technology is used to support memory-optimized tables, which uses various data structures and algorithms to store tables entirely in memory.
The following criteria for assessing the quality of caching mechanisms were chosen for conducting an experimental study: • • • • •
Cache memory hit rate (%), which estimates the share of successful cache memory accesses; the higher the coefficient, the more effective the caching mechanism in the DBMS. Cache miss rate (%), which determines the proportion of unsuccessful attempts to access the cache memory.
Experiment execution time (ms); each DBMS has its own characteristics of the implementation of the experiment, so this indicator will be able to determine which of the DBMSs copes with the implementation of the experiment faster.
The use of system resources during the execution of a request from the cache, including the use of disk space (KB) and the time of using the processor or driver (ms); the more the request from the cache consumes resources, the more bottlenecks there are in the performance of the database.
The size of the cache memory (Mb), which is allocated for working with data; shows how sufficient the level of allocated memory is; by its values, it will be possible to determine what consequences the corresponding volume can lead to.
Based on the received logical model of the heterogeneous database (Fig. 3), for experiments it was
decided to use 3 homogeneous databases built on the basis of a common ER model (Fig. 1): relational
DB scheme, document-oriented logical model under MongoDB (with a "normalized" approach to the
design of relationships [
A data set was designed to be uploaded to each DBMS. Since each of the selected DBMSs has its own data storage structure, the JSON format has become the best option for data loading. The data set contains information about users who have selected products and wish to place an order, or those who have already placed an order. Thus, such a data set reflects the widespread main stages of using the online ordering system.
The hardware and software were chosen to organize the experiments. The hardware equipped with an Intel Core i7-1185G7 CPU (4 cores, 8 threads) and 16 GB of RAM was chosen to provide sufficient performance for the conducted experiments. Windows 11 64-bit was picked as an operating system. MS SQL Server 2022, MongoDB 8.0 and Redis 7.2 were picked as the latest supported versions of chosen DBMSs.
To conduct the experiments, the operations, namely the read requests, on which the measurements will be performed (some of them are depicted on the operation execution map in Figure 2) were defined. As a rule, at the stage of adding goods to the basket, system users are interested in sorting and filtering functions according to the provided categories. Therefore, read operations for the Product entity were selected for the study, among which are: • • • •
The operation of finding a list of goods by category with sorting by price (exp. No. 1). The operation of finding a list of goods in the range of the price per unit of goods (exp. No. 2).
The operation of finding a list of products with filtering by several attributes, namely by brand and price range, with sorting by product name (exp. No. 3).
Such operations have both simple queries and complex ones, with the addition of several filtering and sorting attributes. Thus, we were able to check at which levels the DBMS uses caching mechanisms and at which levels it does not.
Operations for Basket and Product_basket entities are also considered, which include: • • •
Selecting goods added to the basket by the unique Basket identifier (exp. No. 4).
Each user's selection of goods added to the basket (exp. No. 5).
The operation of using the aggregate function to calculate the total amount of goods added to the basket by the unique Basket identifier (exp. No. 6).
For the study, a more complex operation for the Order entity (exp. No. 7) was also included, which includes the calculation of the total cost of paid orders grouped by each user, which is suitable for analytical data and for understanding how much money was spent on goods.
Experiments were conducted on seven queries for each DBMS separately, considering the availability of symmetric data for the subject area. The results of the experiments according to the "cache hit rate" metric are shown in Table 1.
According to these results, it should be concluded that Redis is the most effective for this indicator, since a value less than 100 is possible only in the absence of a suitable key for conducting the experiment. At the same time, MSSQL has a zero value for experiment number 2. Such a result signals the non-use of caching mechanisms for a query that was recognized by MSSQL as very simple.
It should be noted the tendency for MSSQL and MongoDB not to reach the maximum values in both metrics due to the peculiarities of the implementation of caching mechanisms. Both DBMSs have built-in capabilities to check if old plans are needed, if they need to be deleted, or if an existing one needs to be replaced, and therefore it is very difficult to get a 100% result, even if the query has already been executed several times before.
The results of the experiments according to the "experiment execution time" metric are shown in Table 2.
It should be noted a significant difference for the worse in the execution time of Redis experiments compared to other DBMSs. Such numbers are due to the complexity of implementing experiments for key-value DBMSs, the main goal of which is to return data by the selected key as quickly as possible. However, such DBMSs are not capable of processing requests for filtering, sorting or any other actions related to data manipulation. Therefore, the capabilities of the StackExchange. Redis library of .NET technology were used to perform the experiments.
The results of the experiments according to the "system resource utilization" metrics are shown in Table 3 and Table 4. According to this metric, we obtained the results of the execution time of the request by the processor or DBMS driver and the amount of disk space occupied by the request. In the case of the Redis DBMS, the amount of disk space is calculated based on the specified number of keys that were used for the experiment and the occupied memory of each. For other DBMSs, this is the occupied memory of the query execution plan.
As MSSQL did not use caching for the second experiment, the corresponding resources were not calculated. It is possible to conclude that MongoDB consumes the least amount of memory for storing query execution plans. The results of the experiments according to the "cache size" metric are shown in Table 5. The metric determines how much cache memory is allocated by each DBMS during a request to the cache. The results show that MSSQL spends quite a lot of memory on cache usage, which is due to the complex and multifaceted structure of caching mechanisms. In turn, Redis uses the least amount of memory to store cache pages and keys.
Experiments conducted for the Redis DBMS proved the effectiveness of caching for simple queries that do not require data processing from several tables. The speed of finding the necessary keys and the amount of allocated memory for caching is a significant advantage for choosing this DBMS if distributed caching is needed.
MongoDB performed best for experiments that query data from multiple DB entities. This is due to the feature of the logical model and the DBMS itself, which returns data in the form of collections and stores them in the BSON format, thereby guaranteeing the speed and efficiency of data filtering 100 and searching. The best results were obtained despite the fact that when modeling under MongoDB, a "normalized" approach was used, which did not involve nesting collections into each other. That is why this DBMS is best suited for services related to obtaining a large volume of data. It should also be noted that MongoDB uses a relatively small amount of memory for caching, has a significant query execution speed, and shows good performance.
MSSQL has the best structure for building caching and opportunities for obtaining data about this process. The speed of operation, minimal use of system resources and one of the best indicators of "hitting" in the cache memory make MSSQL an example of a reliable database for using caching. However, you should also be careful with the amount of data to cache, as the DBMS takes quite a large amount of memory for caching, which can reach up to 9 GB, while other DBMSs use no more than 2 MB for caching. Therefore, it is recommended to use MSSQL for caching applications with a small amount of data.
The results of the experiments made it possible to develop the following recommendations regarding the choice of DBMS for the implementation of appropriate business logic during the construction of distributed, including heterogeneous, databases.
The Redis DBMS is recommended for use in databases and operations (queries) that do not require data processing from several entities. This is due to the fact that Redis only stores data in key-value format, so the execution time of the experiments was significantly longer, ranging from 25 to 109 ms. It is recommended to use Redis for data structures that are frequently updated and do not store "archived data". This is due to the support of the TTL (Time To Live) mechanism, which sets the lifetime of the keys, as well as the involvement of LFU (Least Frequently Used) and LRU (Least Recent Used) algorithms for caching, which automatically free memory and delete the least used or oldest keys. Another key component of choosing a given DBMS is the amount of allocated memory for caching. Experiments showed a range from 0.38 to 0.43 MB, which is the best indicator among DBMS and is due to the use of ZIPLIST and INTSET storage technologies, which allow to minimize the use of memory for small arrays and sets.
Recommendations regarding the use of Redis for distributed caching should be expressed separately, since simple queries related to key searches are performed in Redis 2 times faster than in other DBMSs. Thereby, minimizing the response time to discover data in the cache.
The MongoDB DBMS is recommended to be used for databases and operations (queries) with a complex structure, which involve the combination of several entities and the possibility of a filtering or search function. This is confirmed by measurements based on the system resource metric, according to which requests occupied a very small percentage of memory, up to 1KB each. As the number of entities in the query increases, the effect of using DBMS will increase, thanks to the support of data replication and sharding functions. This allows you to increase the amount of cache by distributing data between different physical machines and reduce the load on each server. Separately, it should be noted that the design of this database in a non-normalized form will increase the efficiency in the execution of requests, since WiredTiger has a caching mechanism that uses the Least Recently Used (LRU) algorithm for cache management.
The MSSQL DBMS did not show the best results in the area of caching, but we should not forget about such strengths of MSSQL as data security, support of data integrity and ACID-properties of transactions, because of which this DBMS is chosen more often. An important feature of DBMS is the division of simple and complex queries, determining whether to use caching or not. Nevertheless, it should be noted separately the high rate of hitting the cache memory, which reaches almost 100%.
The paper presents the results of the study of the effectiveness of data caching in NoSQL DBMS Redis and MongoDB, as well as relational MSSQL, to solve the problem of choosing the most effective DBMS for database development, including heterogeneous ones.
An extended infological model of EER was proposed as a basis for the design of the HBD and the general principles of its design. The paper provides a mathematical representation of the model and a visual presentation of its extension in the form of operation execution maps, which is a convenient tool for database developers. The use of the proposed model, which considers more in-depth knowledge of data flows in the subject area, and the corresponding approach to the design of the HBD will allow developers to obtain the following advantages: • • •
The possibility of designing more effective data processing operations for the business logic of applications and the corresponding reduction of their execution time.
The ability to design (decompose) databases for microservice architecture with a reasonable choice of logical database models and corresponding DBMS.
As a result, the high scalability of the information systems based on the developed HBD is ensured.
In the future, the EER model can be supplemented with the possibility of modeling data integrity restrictions and the approach to the design of the HBD - the stage of considering such restrictions during constructing a logical model. A heterogeneous database in the field of electronic commerce was designed, and experiments were planned and carried out to study caching mechanisms in selected DBMSs in the paper. The results of the experiments made it possible to develop detailed recommendations regarding the choice of DBMSs for the implementation of distributed parts, including HDBs. Developers can use these recommendations to design real systems, particularly in electronic commerce, space engineering, and emergency risk reduction.
The authors have not employed any Generative AI tools.