• Keine Ergebnisse gefunden

6.3 Comparative Experiments of System Performance

6.3.2 Implementation of Testbeds

We have only borrowed the database schema of PlaneShift, rather than the entire project. Two testbeds using different kinds of databases have been implemented. Based on the experimental requirements, they only support a limited functionality. Since we will compare the database performance of adding, checkpointing and recovering state data, both testbeds support to insert, update and read data to/from the database.

6.3.2.1 Implementation of the Database using MySQL Cluster

In the testbed-MySQL, we have used MySQL Cluster 7.4.4 to manage data, and used JDBC to access the database. MySQL Cluster is deployed on five virtual machines.

There should be at least one management server in the cluster to manage and monitor

9PlaneShift source code:http://planeshift.top-ix.org/pswiki/index.php?title=DatabaseDesign#Character Tables (accessed 20.12.2015).

Figure 6.8: Character State Data Related Tables in the PlaneShift Database

6.3. Comparative Experiments of System Performance 85

C B

A

Management node

Group 1

E D

Group 2 SQL & Data nodes

Figure 6.9: Database Architecture of Testbed-MySQL

all nodes. And it is recommended putting it on a separate node so as to avoid a server failure caused by other processes. Thereby, the cluster is configured with one management node. The other nodes are divided into two groups (number of replicas is two) and configured as both SQL and data nodes (see Figure 6.9). That means, data are distributed on only four nodes.

We have created nine tables in the database to imitate the database schema of PlaneShift (see Figure 6.10). Table names and the number of attributes keep the same with that of original tables. But we have simplified dependencies among tables. Each table has now only one foreign key related to the characters table. The name and type of some attributes also have been modified in order to simplify the code of the testbed. The impact of these modifications on the experimental results is negligible.

In practice, developers always use some advanced technologies or methods to optimize the performance of accessing RDBs. For this reason, Testbed-MySQL also supports prepared statements and stored procedures.

Prepared statement : is typically used with SQL statements, which is a feature in DBMSs used to repeatedly execute similar database statements with high effi-ciency. The statement is a template created with placeholders instead of actual values by the application and sent to the DBMS. At a later time, the certain constant values are passed to substitute placeholders during each execution. The statement is compiled by the DBMS once, so it enhances the performance con-siderably. Furthermore, using prepared statement can also protect the database from SQL injection.

Stored procedure : is a set of SQL statements with an assigned name and parameters (if it has) that is stored in the database in compiled form. Business logic could be embed in the procedure. The conditional logic applied to the results of a SQL statement can determine which subsequent SQL statements are going to be executed. Furthermore, it can be shared by a number of applications by calling

Figure 6.10: Database Schema of Testbed-MySQL

6.3. Comparative Experiments of System Performance 87

A

B

C D

E

Figure 6.11: Database Architecture of Testbed-Cassandra

the procedure. A stored procedure is only compiled when it is created. Therefore, it improves the database performance.

We will use them on all three kinds of operations, and choose the best experimental result of each operation to compare with results from testbed-Cassandra.

Moreover, for data checkpointing, a strategy called CopyUpdated is adopted. That means, only the changed values will be updated into the database. This strategy can significantly reduce the number of operations for each checkpointing. In order to realize it, we have used an in-memory database, H210, in the testbed to store the information of the last checkpoint, which will be used to compare with the current one. Comparative results are used to determine things like which row/column in a table needs to be updated, which row needs to be removed, which data need to be inserted into a table.

6.3.2.2 Implementation of the Database using Cassandra

In the testbed-Cassandra, we have applied Cassandra 2.1.12 to manage data. On the client side, Java Driver is applied to access Cassandra. Similar with that in the Testbed-MySQL, we have deployed a five-node cluster (see Figure 6.11). Different with that in MySQL Cluster, all five nodes are responsible to store data. Furthermore, the repli-cation factor is specified to two. Another significant difference is that there is only one table (Characters table) in the database, which is nested (see Figure 6.12). Other bridge tables have been mapped as map/set type columns of this table. That means, all information of one character is stored in one row.Listing 6.1 on page 89shows the script for creating the Characters table by using CQL. The type and number of attributes in both testbeds are the same.

Cassandra also supports prepared statements, but not yet stored procedures. However, in this testbed, we have used none of them to optimize the performance because we

10H2 website: http://www.h2database.com/html/main.html (accessed 20.12.2015)

characters

id account_id loc_sector_id racegender_id name col1 ... col56 character_events ... item_instances map<int, frozen <events>> ... map<int, frozen <items>>

Figure 6.12: Database Schema of Testbed-Cassandra

Testbed-MySQL Testbed-Cassandra

DBMS MySQL Cluster 7.4.4 Cassandra 2.1.12

API JDBC Java Driver

Number of Nodes 5 5

Number of Data Nodes 4 5

Number of Replicas 2 2

Number of Tables 9 1

Optimization prepared statement & stored procedure none Strategy for Checkpointing CopyUpdated CopyAll

Table 6.2: Comparison of Two Testbeds

want to use the basic operations for the later comparison. For the data checkpointing, we have adopted another strategy calledCopyAll. That means, the current checkpoint will completely substitute the stale one in the column family. This strategy leads to a number of repeated writes, if the change between two checkpoints is small. However, it is ideal for Cassandra because in this way there is only write operations for checkpointing without delete and query operations.

A comparison of two testbeds shows inTable 6.2.

6.3.2.3 Related Work

We have proposed to use different strategies (CopyUpdated andCopyAll) in our testbeds for data checkpointing. The idea of these strategies comes from others’ research.

In [VCS+09], authors have evaluated the overhead, checkpoint, and recovery times of several consistent checkpointing algorithms. They have proposed two fast checkpoint recovery algorithms for MMOGs in another work [CVS+11]. In our project, we focus on how to flush structured checkpoints to Cassandra as well as MySQL Cluster, and fetch them efficiently, which could be considered as an extension of their research.