16. ORM Part 4: Database handling

16.1. Transaction

iFun Engine ORM is used in the iFun Engine’s event threads, and operations that require DB accesses within a single event handler are collected together and processed as a transaction.

void OnMyEvent(const Ptr<const MyEvent> &event) {
// transaction begins

  MyFunction();

// transaction ends
}

void MyFunction() {
// Inside transaction
  ...
}

iFun Engine accesses the database in a non-blocking manner. That is, DB I/O does not block the event thread. If iFun Engine encounters a situation that may block the event thread for DB accesses, it rollbacks the current transaction and resumes the transaction when non-blocking processing is possible.

Rollback happens when an ORM operation is predicted to be blocked by DB I/O. Create(...) / Fetch(...) / Refresh() fall into the case. More specifically, rollback happens when to create an object in the database, to fetch an object from the database, or to lease an object from a remote server that has already cached the object.

Important

If you configure DB sharing as described in Sharding DB server, there could be a chance that some of DB write operations may be lost due to a DB shard server crash or a game server crash. To be prepared the case, it’s required to check if a fetched object is null.

16.1.1. Transaction rollbacks

Functions listed below may raise a transaction rollback and resume the transaction when non-blocking operation is possible. Once a rollback happens, iFun Engine discards changes to objects.

  • Interface class’ Fetch() method.

  • Interface class’ Create() methods taking key attributes.

  • Interface class’ Refresh() method.

Note

Functions that may raise a rollback are tagged with a keyword ROLLBACK in its function signature.

Important

Please note that operations before a function that can cause a rollback can be performed multiple times. So, you should not put operations before such functions, unless the operations are idempotent. The best practice is to put functions that can cause rollbacks in the front of an event handler.

16.1.1.1. Example: Putting operations after a rollback-able function

In the example below, FetchById(...) and Create(...) can cause a transaction rollback. Thus, g_character_create_count may be larger than the actual number of created characters, which is not what we expect.

To prevent such a case, we must move the code updating the global variable after functions that can rollback.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
int g_character_create_count;

void OnCreateCharacter(const Ptr<Session> &session, const Json &message) {
  // Below code can be evaluated multiple times because of transaction rollbacks, which is a bug.
  ++g_character_create_count;

  // Below code is idempotent.
  // So they're safe even in a rollback situation.
  std::string id = message["id"].GetString();
  std::string name = message["name"].GetString();

  // Fetch/Create below can trigger a transaction rollback and restart this event handler multiple times.
  Ptr<User> user = User::FetchById(id);
  Ptr<Character> new_character;
  if (user) {
    Ptr<Character> old_character = user->GetMyCharacter();
    if (old_character) {
      old_character->SetHp(0);
      old_character->SetLevel(0);
    }
    new_character = Character::Create(name);
    user->SetMyCharacter(new_character);
  }

  Json response;
  if (new_character)
    response["result"] = true;
  else
    response["result"] = false;

  session->SendMessage("create_character", response);
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
static int the_character_create_count;

void OnCreateCharacter(Session session, JObject message)
{
  // Below code can be evaluated multiple times because of transaction rollbacks, which is a bug.
  ++the_character_create_count;

  // Below code is idempotent.
  // So they're safe even in a rollback situation.
  string id = (string) message ["id"];
  string name = (string) message ["name"];

  // Fetch/Create below can trigger a transaction rollback and restart this event handler multiple times.
  User user = User.FetchById (id);
  Character new_character = null;

  if (user)
  {
    Character old_character = user.GetMyCharacter ();
    if (old_character)
    {
      old_character.SetHp (0);
      old_character.SetLevel (0);
    }
    new_character = Character.Create (name);
    user.SetMyCharacter (new_character);
  }

  JObject response = new JObject();
  if (new_character)
    response ["result"] = true;
  else
    response ["result"] = false;

  session.SendMessage ("create_character", response);
}

We can fix the problem like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
int g_character_create_count;

void OnCreateCharacter(const Ptr<Session> , const Json ) {
  // Idempotent. So not a problem.
  std::string id = message["id"].GetString();
  std::string name = message["name"].GetString();

  Ptr<User> user = User::FetchById(id);
  Ptr<Character> new_character;
  if (user) {
    Ptr<Character> old_character = user->GetMyCharacter();
    if (old_character) {
      old_character->SetHp(0);
      old_character->SetLevel(0);
    }
    new_character = Character::Create(name);
    user->SetMyCharacter(new_character);
  }

  ++g_character_create_count;

  Json response;
  if (new_character)
    response["result"] = true;
  else
    response["result"] = false;

  session->SendMessage("create_character", response);
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
int the_character_create_count;

void OnCreateCharacter(Session session, JObject message)
{
  // Idempotent. So not a problem.
  string id = (string) message ["id"];
  string name = (string) message ["name"];

  User user = User.FetchById (id);
  Character new_character = null;
  if (user)
  {
    Character old_character = user.GetMyCharacter ();
    if (old_character)
    {
      old_character.SetHp (0);
      old_character.SetLevel (0);
    }
    new_character = Character.Create (name);
    user.SetMyCharacter (new_character);
  }

  ++the_character_create_count;

  JObject response = new JObject ();
  if (new_character)
    response["result"] = true;
  else
    response["result"] = false;

  session.SendMessage ("create_character", response);
}

16.1.1.2. Example: Splitting an event to avoid transaction rollbacks

It may not be always possible to make code before rollback-able functions idempotent or to put rollback-able function in the front of an event handler. Please remember that a transaction is executed after leaving an event handler and so transaction rollbacks mean abort the current event and restart it later. We can leverage it. If we split an event into multiple ones, we can control which code is aborted and restarted.

In the example below, we have split an event in the previous example.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
void OnCreateCharacter(const Ptr<Session> &session, const Json &message) {
  ++g_character_create_count;

  std::string id = message["id"].GetString();
  std::string name = message["name"].GetString();

  function next_step =
      bind(&OnCreateCharacter2, session, id, name);

  Event::Invoke(next_step, session->id());
}


void OnCreateCharacter2(
    const Ptr<Session> &session, const std::string &id, const string &name) {
  Ptr<User> user = User::FetchById(id);
  Ptr<Character> new_character;
  if (user) {
    Ptr<Character> old_character = user->GetMyCharacter();
    if (old_character) {
      old_character->SetHp(0);
      old_character->SetLevel(0);
    }
    new_character = Character::Create(name);
    user->SetMyCharacter(new_character);
  }

  Json response;
  if (new_character)
    response["result"] = true;
  else
    response["result"] = false;

  session->SendMessage("create_character", response);
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
void OnCreateCharacter(Session session, JObject message)
{
  ++the_character_create_count;

  string id = (string) message ["id"];
  string name = (string) message ["name"];

  Event.Invoke(() => {
    OnCreateCharacter2 (session, id, name);
  }, session.Id);
}


void OnCreateCharacter2(Session session, string id, string name)
{
  User user = User.FetchById (id);
  Character new_character = null;

  if (user)
  {
    Character old_character = user.GetMyCharacter ();
    if (old_character)
    {
      old_character.SetHp (0);
      old_character.SetLevel (0);
    }
    new_character = Character.Create (name);
    user.SetMyCharacter (new_character);
  }

  JObject response = new JObject();
  if (new_character)
    response ["result"] = true;
  else
    response ["result"] = false;

  session.SendMessage ("create_character", response);
}

16.1.2. Detecting unwanted rollbacks

Unwanted transaction rollback can lead to unexpected behavior, and it’s very hard to debug. Thus, iFun Engine provides these rollback-related utility function for debugging purposes.

void AssertNoRollback()

If rollback happens after this function, iFun Engine terminates with a log message like this:

transaction rollback raised after 'AssertNoRollback()': event_name=on_create, model_name=User

Tip

If you use SetEventName(), you can get an event name like above. For events without names log message will look like event_name=(unnamed). Please see Assigning names to events for debugging for details.

Tip

AssertNoRollback() can be turned on/off through MANIFEST.json. Please refer to enable_assert_no_rollback in ORM parameters.

Tip

iFun Engine API functions may have a keyword ASSERT_NO_ROLLBACK in function signature. Those functions are not idempotent, and hence they internally calls AssertNoRollback() and automatically detect if they are invoked after rollback-able functions. Here’s a list of those functions (including, but not limited to)

  • AccountManager::CheckAndSetLoggedIn()

  • AccountManager::CheckAndSetLoggedInAsync()

  • AccountManager::SetLoggedOut()

  • AccountManager::SetLoggedOutAsync()

  • AccountManager::SetLoggedOutGlobal()

  • AccountManager::SetLoggedOutGlobalAsync()

  • AccountManager::Locate()

  • AccountManager::LocateAsync()

  • AccountManager::SendMessage()

  • AccountManager::BroadcastLocally()

  • AccountManager::BroadcastGlobally()

  • MatchmakingClient::StartMatchmaking()

  • MatchmakingClient::CancelMatchmaking()

  • Session::SendMessage()

  • Session::BroadcastLocally()

  • Session::BroadcastGlobally()

  • Rpc::Call()

  • Rpc::ReadyBack handlers

  • ApiService::ResponseWriter handlers

  • Timer::ExpireAt()

  • Timer::ExpireAfter()

  • Timer::ExpireRepeatedly()

  • Timer::Cancel()

16.1.2.1. Example: Actively detecting rollbacks using AssertNoRollback()

In the example below, we detect that ++g_character_create_count is executed multiple times due to Character::Create(name) by using AssertNoRollback().

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
int g_character_create_count;

void OnMyHandler(const Ptr<Session> &session, const Json &message) {
  string id = message["id"].GetString();
  string name = message["name"].GetString();

  Ptr<User> user = User::FetchById(id);

  AssertNoRollback();
  ++g_character_create_count;

  if (not user->GetMyCharacter()) {
    Ptr<Character> character = Character::Create(name);
  }
  ...
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
int the_character_create_count;

void OnMyHandler(Session session, JObject message)
{
  string id = (string) message["id"];
  string name = (string) message["name"];

  User user = User.FetchById(id);

  AssertNoRollback();
  ++the_character_create_count;

  if (user.GetMyCharacter() == null) {
    Character character = Character.Create (name);
  }
  ...
}

16.1.2.2. Example: Functions internally calling AssertNoRollback()

In the example below, Item::Create(item_id) can raise a transaction rollback. We will see how to prevent from sending a packets multiple times thank to Session::SendMessage() internally invoking AssertNoRollback().

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
void OnLogin(const Ptr<Session> &session, const Json &message) {
  string id = message["id"].GetString();
  Ptr<User> user = User::FetchById(id);
  Ptr<Character> character = user->GetMyCharacter();

  Json response;
  response["result"] = true;
  response["character_name"] = character->GetName();
  session->SendMessage("login_reply", response);

  // Attendance rewards.
  Uuid item_id = RandomGenerator::GenerateUuid();
  Ptr<Item> gift = Item::Create(item_id);

  ArrayRef<Ptr<Inventory> > inventory = character->GetInventory();
  inventory.PushBack(gift);
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
void OnLogin(Session session, JObject message)
{
  string id = (string) message ["id"];
  User user = User.FetchById(id);
  Character character = user.GetMyCharacter();

  JObject response = new JObject();
  response ["result"] = true;
  response ["character_name"] = character.GetName ();
  session.SendMessage ("login_reply", response);

  // Attendance rewards.
  System.Guid item_id = RandomGenerator.GenerateUuid();
  Item gift = Item::Create(item_id);

  ArrayRef<Inventory> inventory = character.GetInventory();
  inventory.PushBack(gift);
}

16.2. Managing DB Server

16.2.1. DB caching

As explained in Object caching on fetch, iFun Engine ORM automatically caches objects when Create(...) or Fetch(...) gets invoked.

Cached object gets unloaded from the cache when one of following conditions happens.

  • If the object is not fetched by Fetch(...) for longer than ``cache_expiration_in_ms` as explained in ORM parameters.

  • If the object gets deleted by the Delete() method.

16.2.2. Sharding DB server

To shard DB, you may consider database solutions like MySQL Cluster or iFun Engine ORM’s sharding feature.

If you set the key_database section and the range_end field in the object_databases section in MANIFEST.json, iFun Engine ORM automatically handles database sharding according to object’s ID value. The example below illustrates to have 2 database shard servers.

"Object": {
  "cache_expiration_in_ms" : 3000,
  "enable_database" : true,

  // key_database and object_databases works
  // only if enable_database is true.

  // This configures a database holding the keys of objects.
  "key_database" : {
    // MySQL address for the key database. (default: tcp://127.0.0.1:3306)
    "address": "tcp://127.0.0.1:3306",
    "id": "funapi",                     // MySQL ID for the key database.
    "pw": "funapi",                     // MySQL password for the key database.
    "database": "funapi_key"            // MySQL schema name for the key database.
  },
  // This configures databases holding ORM data.
  // Depending on the range_end values, ORM data will be distributed across databases.
  "object_databases": [
    {
      // Object ID range value for DB sharding.
      // Object whose ID is smaller than this boundary will be stored in this shard instance.
      "range_end": "80000000000000000000000000000000",
      "address": "tcp://127.0.0.1:3306",  // MySQL host for the ORM shard database.
      "id": "funapi",                     // MySQL ID for the ORM shard database.
      "pw": "funapi",                     // MySQL password for the ORM shard database.
      "database": "funapi1"               // MySQL schema name for the ORM shard database.
    },
    {
      "range_end": "FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF",
      "address": "tcp://127.0.0.1:3306",
      "id": "funapi",
      "pw": "funapi",
      "database": "funapi2"
    }
  ],
  "db_read_threads_size" : 8,
  "db_write_threads_size" : 16,
  "enable_assert_no_rollback" : true
}

16.2.2.1. Tables/Procedures generated in the database by iFun Engine sharding

When using the sharding feature, tables and procedures generated in object_databases and key_database are like these:

16.2.2.1.1. For object_databases

  • Table

    • tb_Object_{{ObjectName}}

    • tb_Object_{{ObjectName}}_ArrayAttr_{{AttributeName}}

    • tb_Object_{{ObjectName}}_MapAttr_{{AttributeName}}

  • Procedure

    • sp_Object_Get_{{ObjectName}}

    • sp_Object_Insert_{{ObjectName}}

    • sp_Object_Update_{{ObjectName}}

    • sp_Object_Delete_{{ObjectName}}

    • sp_Object_Array_{{ObjectName}}_{{AttributeName}}

    • sp_Object_Map_{{ObjectName}}_{{AttributeName}}

16.2.2.1.2. For key_database

  • Table

    • tb_Key_{{ObjectName}}_{{KeyAttributeName}}

  • Procedure

    • sp_Object_Get_Object_Id_{{ObjectName}}By{{KeyAttributeName}}

    • sp_Object_Key_Insert_{{ObjectName}}_{{KeyAttributeName}}

    • sp_Object_Delete_Key_{{ObjectName}}

16.2.2.2. Migration when reconfiguring Sharding

Data migration is required if the number of shard servers changes (adding/removing DB servers).

iFun Engine has a script named object_db_migrate.py that simplifies the process. Install the program as a follow:

Ubuntu

$ sudo apt-get install python-funapi1-dev

CentOS

$ sudo yum install python-funapi1-devel

Run object_db_migrator.py like this:

$ cd /usr/lib/python2.7/dist-packages/funapi/object/
$ object_db_migrator.py --old_manifest='/home/test/OLD_MANIFEST.Json' --new_manifest='/home/test/NEW_MANIFEST.json'

After finishing running, you may see a log message like below and a temporary directory under /tmp. Migration scripts will be created under the temporary directory.

$ cd /usr/lib/python2.7/dist-packages/funapi/object/
$ object_db_migrator.py --old_manifest='/home/test/OLD_MANIFEST.Json' --new_manifest='/home/test/NEW_MANIFEST.json'

Checks model fingerprint
Makes migration data
Creates a migration file: /tmp/tmp9eqI2T/insert_into_funapi2_from_funapi1_40000000000000000000000000000000_80000000000000000000000000000000.sql
Creates a migration file: /tmp/tmp9eqI2T/insert_into_funapi3_from_funapi2_80000000000000000000000000000000_ffffffffffffffffffffffffffffffff.sql
Creates a migration file: /tmp/tmp9eqI2T/delete_from_funapi1_00000000000000000000000000000000_80000000000000000000000000000000.sql
Creates a migration file: /tmp/tmp9eqI2T/delete_from_funapi2_80000000000000000000000000000000_ffffffffffffffffffffffffffffffff.sql
Creates migration files to /tmp/tmp9eqI2T
Done

SQL scripts are named by these rules:

  • insert_into_{{shard1}}_from_{{shard2}}_{{range_start}}_{range_end}}.sql: Copies data from {{range_start}} to {{range_end}} in {{shard2}} to {{shard1}}.

    For example, insert_into_funapi2_from_funapi1_4000_8000.sql means that it will copy data from ID 4000 to ID 8000 in the funapi1 shard server to the funapi2 shard server.

  • delete_from_{{shard}}_{{range_start}}_{{range_end}}.sql: Deletes from {{range_start}} to {{range_end}} in {{shard}}.

    For example, delete_from_funapi1_0000_8000.sql means that it will delete from ID 0000 to 8000 in the funapi1 shard server.

It’s recommended to run delete scripts first and insert scripts later.

Note

Executing order is not relevant if shard databases are located in different servers. If shard databases, however, are in the same server, running insert scripts before delete scripts can be problematic because the latter will delete new records created by the former.

There could be two ways to run the scripts.

Method 1: Sourcing the scripts on a MySQL prompt

mysql> source /tmp/tmp9eqI2T/insert_into_funapi2_from_funapi1_4000_8000.sql

Method 2: Running the scripts from the shell

$ mysql -h localhost -u root -p < /tmp/tmp9eqI2T/insert_into_funapi2_from_funapi1_4000_8000.sql

Warning

Insert scripts are not idempotent. So, running the same insert script multiple times will display this log message:

ERROR 1062 (23000): Duplicate entry '\x81b\xA7\x98z4E9\x8A\xE8\x9Fp\xF9\xEB\xEF\x99' for key 'PRIMARY'

Important

Please be aware that object_db_migrator.py migrates only data, not tables or procedures. To populate tables and procedures, use the export_db_schema option explained in Required DB permissions to get a schema script and run the script before insert/delete scripts.

Example: Increases the number of shards from 2 to 3

Assume we want to add one more shard server to an existing shard group of 2 servers. Then, we will need to write key_database and object_databases like this:

OLD_MANIFEST.json (with 2 shard servers)

"Object": {
  "key_database" : {
    "address": "tcp://127.0.0.1:3306",
    "id": "funapi",
    "pw": "funapi",
    "database": "funapi_key"
  },
  "object_databases": [
    {
      "range_end": "80000000000000000000000000000000",
      "address": "tcp://127.0.0.1:3306",
      "id": "funapi",
      "pw": "funapi",
      "database": "funapi1"
    },
    {
      "range_end": "FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF",
      "address": "tcp://127.0.0.1:3306",
      "id": "funapi",
      "pw": "funapi",
      "database": "funapi2"
    }
  ],
}

NEW_MANIFEST.json (with 3 shard servers)

"Object": {
  "key_database" : {
    "address": "tcp://127.0.0.1:3306",
    "id": "funapi",
    "pw": "funapi",
    "database": "funapi_key"
  },
  "object_databases": [
    {
      "range_end": "40000000000000000000000000000000",
      "address": "tcp://127.0.0.1:3306",
      "id": "funapi",
      "pw": "funapi",
      "database": "funapi1"
    },
    {
      "range_end": "80000000000000000000000000000000",
      "address": "tcp://127.0.0.1:3306",
      "id": "funapi",
      "pw": "funapi",
      "database": "funapi2"
    },
    {
      "range_end": "FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF",
      "address": "tcp://127.0.0.1:3306",
      "id": "funapi",
      "pw": "funapi",
      "database": "funapi3"
    }
  ],
}

In short, changes in the shard range will be like this table:

DB

OLD_MANIFEST.json

NEW_MANIFEST.json

funapi1

0000… ~ 8000…

0000… ~ 4000…

funapi2

8000… ~ FFFF…

4000… ~ 8000…

funapi3

8000… ~ FFFF…

Run object_db_migrator.py with the MANIFEST.json files.

$ cd /usr/lib/python2.7/dist-packages/funapi/object/
$ object_db_migrator.py --old_manifest='/home/test/OLD_MANIFEST.Json' --new_manifest='/home/test/NEW_MANIFEST.json'

Checks model fingerprint
Makes migration data
Creates a migration file: /tmp/tmp9eqI2T/insert_into_funapi2_from_funapi1_40000000000000000000000000000000_80000000000000000000000000000000.sql
Creates a migration file: /tmp/tmp9eqI2T/insert_into_funapi3_from_funapi2_80000000000000000000000000000000_ffffffffffffffffffffffffffffffff.sql
Creates a migration file: /tmp/tmp9eqI2T/delete_from_funapi1_00000000000000000000000000000000_80000000000000000000000000000000.sql
Creates a migration file: /tmp/tmp9eqI2T/delete_from_funapi2_80000000000000000000000000000000_ffffffffffffffffffffffffffffffff.sql
Creates migration files to /tmp/tmp9eqI2T
Done

We got 4 scripts reflecting the change table.

  • A script to copies from 4000… to 8000… in funapi1 to funapi2.

  • A script to copies from 8000… to ffff… in funapi2 to funapi3.

  • A script to delete from 0000… to 8000… in funapi1.

  • A script to delete from 8000… to ffff… in funapi2.

Run the scripts.

$ mysql -h localhost -u root -p < /tmp/tmp9eqI2T/delete_from_funapi1_00000000000000000000000000000000_80000000000000000000000000000000.sql
$ mysql -h localhost -u root -p < /tmp/tmp9eqI2T/delete_from_funapi2_80000000000000000000000000000000_ffffffffffffffffffffffffffffffff.sql

$ mysql -h localhost -u root -p < /tmp/tmp9eqI2T/insert_into_funapi2_from_funapi1_40000000000000000000000000000000_80000000000000000000000000000000.sql
$ mysql -h localhost -u root -p < /tmp/tmp9eqI2T/insert_into_funapi3_from_funapi2_80000000000000000000000000000000_ffffffffffffffffffffffffffffffff.sql

16.2.3. Required DB permissions

16.2.3.1. Minimum permissions

iFun Engine ORM requires the minimum permission below to automatically create/delete/update records.

DB

Permissions

information_schema

SELECT

각 object DB 와 key DB

SELECT, INSERT, UPDATE, DELETE, EXECUTE

16.2.3.2. Additional permission to automatically handle schema changes

iFun Engine ORM can create/update DB tables or procedures. If you want to use the feature, following permissions are also required.

Target

Permissions

TABLE, INDEX, PROCEDURE

CREATE, ALTER, DROP

16.2.3.3. Extracting schema script when not using the automatic schema changes

If you want to allow only the minimum permissions to iFun Engine ORM, you need to extract schema script and populate schema manually.

To get a schema script, set export_db_schema explained in ORM parameters to true and run the game server. After generating a script file, the server will exit. Generated scripts will be located in directory like

만약, DB 에 이미 테이블들이 존재하고 ORM 정의를 수정해서 스킴의 변경이 필요한 상황에서는 스킴의 변경에 대한 내용이 script 파일에 들어가게 됩니다.

/tmp/{{project_name}}_schema/{{project_name}}_schema.{{range_begin}}-{{range_end}}.sql.

Important

The export_db_schema option will generate an SQL script encodes a delta relative to the current database from which the script was generated. Thus, the status of a database from which the script was generated and a database on which the script will be run must have the same schema.

For example, you should make the schemas of a staging server’s database and a live server’s database the same, if you are planning to generate a schema file on the staging server and apply the script to the live server. (Data does not necessarily have to be the same. Only schema matters.)

Note

A script generated by export_db_schema carries a schema version number based on ORM files. This may result in automatically making the iFun Engine upgrade the schema if you populated your database using a schema script manually generated by mysqldump. And it may complain that iFun Engine needs permissions described in Additional permission to automatically handle schema changes to upgrade the database.

Tip

If you are running the game server from a shell prompt, you can also pass --export_db_schema instead of setting export_db_schema in MANIFEST.json.

$ ./my_project-local.sh --export_db_schema

16.2.4. Database fail-over

iFun Engine automatically tries to reconnect to the database if it loses a connection to the database.

This property may be useful to implement database fail-over. Suppose you have set up master-slave replication on MySQL. If the master server goes down, you can assign (manually or automatically by a monitoring script) the IP address of the master database to the slave one. Then, iFun Engine will initiate a connection to the slave database.

In sum, following steps will happen.

  1. MySQL master crashes (iFun Engine loses a connection to the DB server.)

  2. DBA switches the slave server to the master mode.

  3. DBA re-assign the IP address of the master DB server to the slave server.

  4. iFun Engine recovers a connection to the database.

Tip

DBA can automate the #2 and #3 steps either by a script or by a solution like Master HA.

16.2.5. Accessing the database the iFun Engine ORM uses

You might need to directly access the database the iFun Engine ORM uses for purposes of customer support and in-game event rewards. In this case, use the following guideline. Please note that the guideline applies only when you access either tables or columns generated by the ORM subsystem. If your access is irrelevant to them (e.g., accessing another tables or columns) you are OK to do so.

Tip

If you need to make iFun Engine game server access the schema that were not generated by the ORM subsystem, please refer to DB access part 1: MySQL.

16.2.5.1. If game server is not running

If there’s no game server made by iFun Engine running, you have no limitation to access the database.

16.2.5.2. If game server is running and you need to read data from the DB

Even if the game server made by iFun Engine is running, you can freely read the database that ORM manages. But you need to remember that some of data in the database could be stable because iFun Engine might be updating some data.

16.2.5.3. If game server is running and you need to add/update/delete data

If you simultaneously update the database while iFun Engine is writing, it may cause a permanent, critical damage to the database. Therefore, you must not update the database outside iFun Engine (e.g., MySQL connector, SQL script), while iFun Engine is running. We strongly recommend to shutdown iFun Engine game server before accessing the database.

If you really need to access the database while the game server is running, we recommend to use Server management part 1: Adding RESTful APIs for safe accesses.

16.3. ORM Performance Tuning

16.3.1. Guideline to efficient ORM usage

iFun Engine automatically locks/unlocks game objects when the objects are accessed by the engine. Thus, it’s important to make iFun Engine minimize the locked region and run operations in a batch manner. You can do by following these guideline:

  • Use the Foreign flag unless object has explicit ownership to other object.

  • Minimize the number of game objects that are accessed at a time.

  • If you have game objects that are accessed very frequently, have another system like Redis to handle the objects. (e.g., globally shared object)

  • Prefers vector-style Fetch() to repeating Fetch() that reads a single object.

  • Maintain event handler as small as possible by splitting an event handler if it touches many irrelevant objects at the same time. To split an event, use Event::Invoke().

16.3.2. Frequently used objects

You may have objects that are widely shared, and hence very frequent accesses are expected. It’s recommended to have another system other than iFun Engine ORM (e.g., Redis) to handle such objects.

16.3.3. Fetching multiple objects in a batch

It’s recommended to use a vector-style Fetch() instead of repeating Fetch() that reads a single object.

The code below illustrates how to read the whole item in one’s inventory.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
void ArrayToVector(const ArrayRef<Object::Id> &array,
                   std::vector<Object::Id> *vector) {
  for (size_t i = 0; i < array.Size(); ++i) {
    vector->push_back(array.GetAt(i));
  }
}

void FetchTwoUsersItems() {
  Ptr<User> user1 = User::Fetch(user1_uuid);
  Ptr<User> user2 = User::Fetch(user2_uuid);

  std::vector<Object::Id> id_list;

  ArrayToVector(user1->GetInventory(), &id_list);
  ArrayToVector(user2->GetInventory(), &id_list);

  std::vector<std::pair<Object::Id, Ptr<Item>>> items;
  Item::Fetch(id_list, &items);
  ...
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
public static void ArrayToSortedSet(ArrayRef<System.Guid> array,
                                    SortedSet<System.Guid> sorted_set)
{
  foreach (System.Guid guid in array)
  {
    sorted_set.Add (guid);
  }
}

void FetchTwoUsersItems()
{
  User user1 = User.Fetch (user_guid);
  User user2 = User.Fetch (user_guid);

  SortedSet<Guid> id_set = new SortedSet<Guid> ();
  ArrayToSortedSet (user1.GetInventory(), id_set);
  ArrayToSortedSet (user2.GetInventory(), id_set);

  Dictionary<System.Guid, Item> items = Item.Fetch (id_set);
  ...
}

16.3.4. Minimizing the number of Fetches by the Foreign flag

If some object field is tagged as Foreign, the field is not subject to cascaded fetches, which improves the performance.

Problematic ORM model:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
{
  "User": {
    "Name": "String KEY",
    "MailBox": "Mail[]"
  },

  "Mail": {
    ...
  }
}

In the example above, fetching the User will trigger fetching the MailBox array. If the User has 100 Mails on average, this will result in significant performance overhead.

Since mailbox data is not required unless we are processing mailbox-related packets, we do not have to load the MailBox array every time we fetch the User. In such a case, we can apply the Foreign flag to the MailBox to avoid such unnecessary fetches.

Desired ORM model:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
{
  "User": {
    "Name": "String KEY",
    "MailBox": "Mail[] Foreign"
  },

  "Mail": {
    ...
  }
}

16.3.4.1. Example: Friends list

Incorrect model:

{
  "User": {
    "Name": "String KEY",
    "Inventory": "Item[]",
    "Friends": "User[]"
  },
  "Item": {
    ...
  }
}

In the example above, fetching the User will result in fetching the User objects in the Friends field. This repeats as we are fetching another User*s. This model is incorrectly defined as there’s no ownership relationship between friends. So, we need to add the ``Foreign`` flag to the *Friends field.

Correct model:

{
  "User": {
    "Name": "String KEY",
    "Inventory": "Item[]",
    "Friends": "User[] Foreign"
  },
  "Item": {
    ...
  }
}

Now the Friends field is defined as foreign. According to Array/Map with the Foreign flag, the User class may have two kinds of getters like these:

  • User::GetFriends() : Returns an array of UUIDs of friends.

  • User::FetchFriends() : Fetches User objects and returns an array of the User objects.

Here’re a usage example:

Fetching all the friends

Ptr<User> user = User::Fetch(user_uuid);
ArrayRef<Ptr<User> >  friends = user->FetchFriends();

User user = User.Fetch(user_guid);
ArrayRef<User> friends = user.FetchFriends();

Getting an array of object IDs

Ptr<User> user = User::Fetch(user_uuid);
ArrayRef<Object::Id>  friends = user->GetFriends();

User user = User.Fetch(user_guid);
ArrayRef<System.Guid> friends = user.GetFriends ();

16.3.4.2. Example: To avoid unnecessary inventory loading when handling friends list

If we need the names of friends, we may write a code using the ORM definition above.

 Ptr<User> user = User::Fetch(user_uuid);
 ArrayRef<Ptr<User> > friends = user->FetchFriends();
 std::vector<std::string> names;
 for (size_t i = 0; i < friends.Size(); ++i) {
   if (friends.GetAt(i)) {
    names.push_back(friends.GetAt(i)->GetName());
   }
 }

User user = User.Fetch (user_guid);
ArrayRef<User> friends = user.FetchFriends();
SortedSet<string> names = new SortedSet<string>();
foreach (User friend in friends)
{
  names.Add (friend.GetName ());
}

Please be aware that we fetches user’s inventory when we call user->FetchFriends(). This can result in a significant performance burden. To avoid such a case, we need to add the Foreign tag to Inventory.

Fixed model:

{
  "User": {
    "Name": "String KEY",
    "Inventory": "Item[] Foreign",
    "Friends": "User[] Foreign"
  },
  "Item": {
    ...
  }
}

If we need to read the inventory of a user, we can do like this:

Ptr<User> user = User::Fetch(user_uuid);
ArrayRef<Ptr<Item> > inventory = user->FetchInventory();

for (size_t i = 0; i < inventory.Size(); ++i) {
  Ptr<Item> item = inventory.GetAt(i);
  ...
}

User user = User.Fetch(user_guid);
ArrayRef<Item> inventory = user.FetchInventory ();
foreach (Item item in inventory)
{
  ...
}

If we need to access only a specific item in the inventory, we can do like this:

Ptr<User> user = User::Fetch(user_uuid);
ArrayRef<Object::Id> inventory = user->GetInventory();
Ptr<Item> item = Item::Fetch(inventory.GetAt(3));

User user = User.Fetch (user_guid);
ArrayRef<Guid> inventories = user.GetInventory();
Item my_item = Item.Fetch(inventories.GetAt(3));

16.3.5. Appropriate lock type usage

As explained in LockType when fetching, iFun Engine supports lock types as follows:

  • kReadLock

  • kWriteLock

  • kReadCopyNoLock

kReadLock allows another readers and disallows another write while locked.

kWriteLock rejects another readers and writer while locked. Hence, preferring kReadLock whenever possible is recommended.

Tip

For your convenience, Fetch(...) has a default parameter of kWriteLock. But you should change this accordingly.

Please note that even if you prefer kReadLock, you can still block other thread that tries to get a kWriteLock on the object (especially when the read thread takes very long time).

kReadCopyNoLock simply duplicates the target object instead of getting a read lock on it. This is helpful to ease the blocking by a read lock. Please be aware, however, that since kReadCopyNoLock returns a copy of the target object, it may be slightly stale.

This staleness is OK in many cases, especially if we just want to display data (e.g., showing friends list, ranking data), since the player can manually refresh to get a updated data. So, it’s recommended to use kReadCopyNoLock if you are handling read-only objects and can tolerate with slight staleness.

In the example below, we are building a list of friends using kReadCopyNoLock.

Json response;

Ptr<User> user = User::Fetch(user_uuid);
ArrayRef<Ptr<User> >  friends = user->FetchFriends(kReadCopyNoLock);

response["friends"].SetArray();
for (size_t i = 0; i < friends.Size(); ++i) {
  Ptr<User> friend = friends.GetAt(i);
  if (not friend) {
    continue;
  }

  Json friend_json;
  friend_json.SetObject();
  friend_json["name"] = friend->GetName();
  friend_json["level"] = friend->GetLevel();  // Assume user has the Level field.

  response["friends"].PushBack(friend_json);
}

JObject response = new JObject ();
JArray friends_json = new JArray ();

User user = User.Fetch (user_guid);
ArrayRef<User> friends = user.FetchFriends (funapi.LockType.kReadCopyNoLock);

foreach (User friend in friends)
{
  JObject obj = new JObject ();
  obj ["name"] = friend.GetName ();
  obj ["level"] = friend.GetLevel (); // Assume user has the Level field.
  friends_json.Add(obj);
}

response ["friends"] = friends_json;

16.3.6. Adding indices to the database

It’s OK to add extra indices to the tables generated by the ORM. It’s also allowed to update the procedures generated by the ORM (see (고급) DB Schema Generated by The ORM), as long as its signature does remain the same. You might be able to get extra performance improvement by adding additional indices or simplifying the procedures.

16.4. (Advanced) Searching Objects From The Database

If Fetch...(...) is not sufficient to meet your taste, you may want to use SQL SELECT-like search methods.

iFun Engine ORM generates a method like below for each model and each field.

void ObjectName::SelectBy{{AttributeName}}(cond_type, cond_value, callback)

  • cond_type: Object::kEqualTo, Object::kLessThan, or Object::kGreaterThan

  • cond_value: reference value.

  • callback: functor in the type of void(const Ptr<std::set<Object::Id> > &object_ids).

    object_ids will contain searched object IDs. If error, object_ids will be NULL.

To get an object, you can run Fetch(...) using the returned object IDs. Because it’s possible for some of the objects to get updated between the search time and the access time, it’s required to check if a object still meets the criteria.

Important

Please be careful with the condition not to return too many objects.

Important

For a performance reason, you need to manually set an index on the evaluated column. Please remember that iFun Engine cannot know which column will be searched and hence it cannot set an index automatically.

Note

This feature is for operation purposes, not for game contents implementation purposes.

Note

If you need to search with more complex conditions, you may consider issuing SQL queries using DB access part 1: MySQL. Please make sure that your SQL statements are read-only to prevent from rendering iFun Engine’s ORM.

16.4.1. Example: Getting a list of users with level more than 100

Suppose we found a program bug and level more than 100 must not appear. Then we may want to cut the max level of users to 99. This is absolutely for operation purposes.

Since it’s possible to update a Character between Character::SelectByLevel(...) and Character::Fetch(...), we will check if a read character still meets the search criteria after fetching it.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
void Select() {
  Character::SelectByLevel(Object::kGreaterThan, 99, OnSelected);
}

void OnSelected(const Ptr<std::set<Object::Id> &object_ids) {
  if (not object_ids) {
    LOG(ERROR) << "error";
    return;
  }

  auto level_down = [](const Object::Id &object_id) {
    Ptr<Character> character = Character::Fetch(object_id);
    if (not character || character->GetLevel() < 100) {
      return;
    }
    character->SetLevel(99);
  };

  for (const Object::Id &object_id: *object_ids) {
    Event::Invoke(bind(level_down, object_id));
  }
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
void Select()
{
  Character.SelectByLevel(Object.kGreaterThan, 99, OnSelected);
}

void OnSelected(SortedSet<Object.Id> object_ids)
{
  if (object_ids = null)
  {
    Log.Error ("error");
    return;
  }

  auto level_down = [] (Object.Id &object_id) {
    Character character = Character.Fetch (object_id);
    if (character == null || character.GetLevel () < 100)
    {
      return;
    }
    character.SetLevel (99);
  };

  for (Object.Id &object_id in object_ids)
  {
    Event.Invoke (() => { level_down(object_id); });
  }
}

16.5. (고급) DB Schema Generated by The ORM

16.5.1. Automatic schema updates by the ORM

iFun Engine checks if the database schema respects ORM definition files. So, it compares the schema of the current database and tries to automatically update the schema once you rebuild and restart the game server after changing some of ORM definition files. During this step, it updates the schema in this way:

  • Creates an SQL table and procedures for each of new object model.

  • Tries to alter SQL columns and matching procedures if object model has changes in fields.

Tip

iFun Engine never drops a column or a table even if they get removed from an object model. This is to prevent a catastrophe caused by a simple, silly mistake.

iFun Engine decides that it cannot alter a column if one of the conditions below meets:

  • n gets smaller in String(n).

  • Either db_string_length or db_key_string_length explained in ORM parameters gets smaller than before

  • Primitive type is changed to another primitive type.

Once it decides it cannot alter the schema, it terminates with an error message like this. You need to manually update the schema.

Example: changing the type of Character’s Level from Integer to String(24)

F1028 15:56:50.045100  3951 object_database.cc:996] Mismatches object_model=User, table_name=tb_Object_User, column_name=col_Cash, expected_model_column(type=VARCHAR(24), character_set=utf8), mismatched_db_column(type=bigint(8), character_set=)

16.5.2. Shortening string max length

If the max length of a string increase, iFun Engine automatically reflects the change to the database. You should manually resolve the issue, however, if the max length gets decreased. There could be two ways to resolving the issue.

Method 1: If it’s your test DB and you can drop the DB, run DROP {{dbname}} and CREATE {{dbname}} and then reboot the game server.

Method 2: If you cannot drop the DB, make your SQL alter scripts and run it like this:

-- key table changes
ALTER TABLE tb_Key_{{Object_Name}}_{{KeyColumnName}} MODIFY col_{{KeyColumnName}} CHAR({{NewLength}});

-- object table changes
ALTER TABLE tb_Object_{{ObjectName}} MODIFY col_{{KeyColumnName}} CHAR({{NewLength}});

In the example below, we changed the max length of a key tring from 20 to 12.

ALTER TABLE tb_Key_User_Name MODIFY col_Name CHAR(12);
ALTER TABLE tb_Object_User MODIFY col_Name CHAR(12);

Run the script and restart the game server. If you alter the column manually, DB procedure will be automatically updated by iFun Engine.

Note

If you are using DB shards explained in Sharding DB server, you need to run the SQL alter script both on the key DB and on the object DB.

Tip

If you have many fields to update, you can generate an alter script using the MySQL script below:

Suppose we want to decrease the max length of key string from 20 to 12, and the max length of string from 4096 to 100, respectively.

-- Enters the database name in MANIFEST.json.
USE [database];

-- Enters the current type of key string.
SET @org_key_string_length = 'CHAR(20)';

-- Enters the new type of key string.
SET @key_string_length = 'CHAR(12)';

-- Enters the current type of string.
SET @org_string_length = 'VARCHAR(4096)';

-- Enters the new type of string.
SET @string_length = 'VARCHAR(100)';

-- Generates an SQL script containing ALTERs.
SELECT CONCAT(GROUP_CONCAT(sql_script separator '; '), ';') AS sql_script
FROM
(
  SELECT GROUP_CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ',
         @key_string_length separator '; ') AS 'sql_script'
  FROM information_schema.columns
  WHERE table_schema = DATABASE() AND column_type = @org_key_string_length
  UNION ALL
  SELECT GROUP_CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ',
         @string_length separator '; ') AS 'sql_script'
  FROM information_schema.columns
  WHERE table_schema = DATABASE() AND column_type = @org_string_length
) AS A;

If you run the scrip above, you will get a script like this:

ALTER TABLE tb_Key_Character_Name MODIFY col_Name CHAR(12); ALTER TABLE tb_Key_User_Id MODIFY col_Id CHAR(12); ALTER TABLE tb_Object_Character MODIFY col_Name CHAR(12); ALTER TABLE tb_Object_User MODIFY col_Id CHAR(12); ALTER TABLE tb_Object_Character MODIFY col_Name2 VARCHAR(100); ALTER TABLE tb_Object_Character MODIFY col__tag VARCHAR(100); ALTER TABLE tb_Object_User MODIFY col__tag VARCHAR(100);

16.5.3. Mapping between the ORM types and SQL types

The ORM field types are mapped to SQL column types in this way:

iFun Engine Type

SQL Type

Bool

tinyint(1)

Integer

bigint(8)

Double

double

String

Depends on MANIFEST.json. varchar(4096) by default. char(12) if key.

Object

binary(16)

User Defined Object

binary(16)

Note

For the String type, you can change the value by adjusting db_string_length and db_key_string_length as explained in ORM parameters.

16.5.4. Table naming by the ORM

The ORM generates tables like this:

Tip

col__ObjectId_ is in the binary format and you can convert to human readable by hex().

16.5.4.1. tb_Object_{{ObjectName}}

For each object model. It will generated on every shard sever. Fields for the object model are created as SQL columns.

Columns

Description

col__ObjectId_

UUID auto-generated by iFun Engine to identify each object.

col_{{AttributeName}}

…

16.5.4.2. tb_Key_{{ObjectName}}_{{KeyAttributeName}}

For each key field of an object model. It will generated only on a key server.

Columns

설명

col__ObjectId_

UUID auto-generated by iFun Engine to identify each object.

col_{{KeyAttributeName}}

Object key value. This is set as primary key.

16.5.4.3. tb_Object_{{ObjectName}}_ArrayAttr_{{AttributeName}}

For each array type. It will generated on every shard server. col__ObjectId_ and col__Index_ become a composite key.

Columns

설명

col__ObjectId_

UUID auto-generated by iFun Engine to identify each object.

col__Index_

Array index value. Its SQL type is bigint.

col__Value_

Array element value.

16.5.4.4. tb_Object_{{ObjectName}}_MapAttr_{{AttributeName}}

For each map type. It will generated on every shard sever. col__ObjectId_ and col__Key_ become a composite key.

Columns

설명

col__ObjectId_

UUID auto-generated by iFun Engine to identify each object.

col__Key_

Map’s key

col__Value_

Value for the given key.

16.5.5. DB Index & Constraint generated by the ORM

iFun Engine sets the col__ObjectId_ column as Primary Key. Also it sets a column for each key field as Non-Clustred Index and Unique Constraint.

Tip

You can add extra indices or constraints in addition to ones generated by the ORM.

16.5.6. DB Procedure generated by the ORM

iFun Engine creates procedures for object models and accesses database through the procedures.

  • sp_Object_Get_{{ObjectName}} : To fetch an object from its object ID.

  • sp_Object_Get_Object_Id_{{ObjectName}}By{{KeyAttributeName}} : To fetch an object from its key.

  • sp_Object_Key_Insert_{{ObjectName}}_{{KeyAttributeName}} : To add a new key to the database.

  • sp_Object_Insert_{{ObjectName}} : To add a new object into the database.

  • sp_Object_Update_{{ObjectName}} : To update an object.

  • sp_Object_Delete_{{ObjectName}} : To delete an object.

  • sp_Object_Delete_Key_{{ObjectName}} : To delete a key of an object.

  • sp_Object_Array_{{ObjectName}}_{{ArrayAttributeName}} : To add/remove array elements.

Tip

As with index/constraint, procedure can be updated as long as its signature remains the same and you keep the ordering of returned rowset columns.

16.6. (Advanced) Profiling The ORM

iFun Engine can provide statistics for each of databases specified in ORM parameters. To enable the feature, you need to configure the parameters below in MANIFEST.json

To get a statistics, invoke the REST API below:

GET http://{ip}:{api-service-port}/v1/counters/funapi/object_database_stat/

The result will be grouped by range_end values, and the meaning of each statistics is like this:

Statistics

all_time

Cumulated statistics

last1min

Statistics for the last 1minute

write_count

The number of write queries executed.

write_mean_in_sec

Write query execution time on average.

write_stdev_in_sec

Write query execution time stdev.

write_max_in_sec

The maximum of write query execution time.

read_count

The number of read queries executed.

read_mean_in_sec

Read query execution time on average.

read_stdev_in_sec

Read query execution time stdev.

read_max_in_sec

The maximum of read query execution time.

Note

range_end of 00000000-0000-0000-0000-000000000000 indicates the key_database (It’s the same even if sharding is off.) It includes key-related statistics.

Output example

{
  "00000000-0000-0000-0000-000000000000": {
      "database": "funapi",
      "address": "tcp://127.0.0.1:3306",
      "all_time": {
          "write_count": 4,
          "write_mean_in_sec": 0.000141,
          "write_stdev_in_sec": 0.000097,
          "write_max_in_sec": 0.000286,
          "read_count": 1000,
          "read_mean_in_sec": 0.031476,
          "read_stdev_in_sec": 0.033169,
          "read_max_in_sec": 0.104138
      },
      "last1min": {
          "write_count": 0,
          "write_mean_in_sec": 0.0,
          "write_stdev_in_sec": 0.0,
          "write_max_in_sec": 0.0,
          "read_count": 0,
          "read_mean_in_sec": 0.0,
          "read_stdev_in_sec": 0.0,
          "read_max_in_sec": 0.0
      }
  },
  "ffffffff-ffff-ffff-ffff-ffffffffffff": {
      "database": "funapi",
      "address": "tcp://127.0.0.1:3306",
      "all_time": {
          "write_count": 4,
          "write_mean_in_sec": 0.000086,
          "write_stdev_in_sec": 0.00006,
          "write_max_in_sec": 0.000176,
          "read_count": 19989,
          "read_mean_in_sec": 0.057533,
          "read_stdev_in_sec": 0.045418,
          "read_max_in_sec": 0.198318
      },
      "last1min": {
          "write_count": 0,
          "write_mean_in_sec": 0.0,
          "write_stdev_in_sec": 0.0,
          "write_max_in_sec": 0.0,
          "read_count": 0,
          "read_mean_in_sec": 0.0,
          "read_stdev_in_sec": 0.0,
          "read_max_in_sec": 0.0
      }
  }
}

Note

A result will look like this even if you do not use sharding explained in Sharding DB server.

If you are using the database sharding, output can be like this:

MANIFEST.json for sharding

"Object": {
  "enable_database" : true,
  "key_database" : {
    "address": "tcp://127.0.0.1:3306",
    "id": "funapi",
    "pw": "funapi",
    "database": "funapi_key"
  },
  "object_databases": [
    {
      "range_end": "80000000000000000000000000000000",
      "address": "tcp://127.0.0.1:3306",
      "id": "funapi",
      "pw": "funapi",
      "database": "funapi_obj1"
    },
    {
      "range_end": "FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF",
      "address": "tcp://127.0.0.1:3306",
      "id": "funapi",
      "pw": "funapi",
      "database": "funapi_obj2"
    }
  ]
}

Output example

{
  "00000000-0000-0000-0000-000000000000": {
      "database": "funapi_key",
      "address": "tcp://127.0.0.1:3306",
      "all_time": {
          "write_count": 4,
          "write_mean_in_sec": 0.000141,
          "write_stdev_in_sec": 0.000097,
          "write_max_in_sec": 0.000286,
          "read_count": 1000,
          "read_mean_in_sec": 0.031476,
          "read_stdev_in_sec": 0.033169,
          "read_max_in_sec": 0.104138
      },
      "last1min": {
          "write_count": 0,
          "write_mean_in_sec": 0.0,
          "write_stdev_in_sec": 0.0,
          "write_max_in_sec": 0.0,
          "read_count": 0,
          "read_mean_in_sec": 0.0,
          "read_stdev_in_sec": 0.0,
          "read_max_in_sec": 0.0
      }
  },
  "80000000-0000-0000-0000-000000000000": {
      "database": "funapi_obj1",
      "address": "tcp://127.0.0.1:3306",
      "all_time": {
          "write_count": 4,
          "write_mean_in_sec": 0.0001,
          "write_stdev_in_sec": 0.000061,
          "write_max_in_sec": 0.000191,
          "read_count": 20011,
          "read_mean_in_sec": 0.055629,
          "read_stdev_in_sec": 0.046967,
          "read_max_in_sec": 0.224221
      },
      "last1min": {
          "write_count": 0,
          "write_mean_in_sec": 0.0,
          "write_stdev_in_sec": 0.0,
          "write_max_in_sec": 0.0,
          "read_count": 0,
          "read_mean_in_sec": 0.0,
          "read_stdev_in_sec": 0.0,
          "read_max_in_sec": 0.0
      }
  },
  "ffffffff-ffff-ffff-ffff-ffffffffffff": {
      "database": "funapi_obj2",
      "address": "tcp://127.0.0.1:3306",
      "all_time": {
          "write_count": 4,
          "write_mean_in_sec": 0.000086,
          "write_stdev_in_sec": 0.00006,
          "write_max_in_sec": 0.000176,
          "read_count": 19989,
          "read_mean_in_sec": 0.057533,
          "read_stdev_in_sec": 0.045418,
          "read_max_in_sec": 0.198318
      },
      "last1min": {
          "write_count": 0,
          "write_mean_in_sec": 0.0,
          "write_stdev_in_sec": 0.0,
          "write_max_in_sec": 0.0,
          "read_count": 0,
          "read_mean_in_sec": 0.0,
          "read_stdev_in_sec": 0.0,
          "read_max_in_sec": 0.0
      }
  }
}

16.7. ORM parameters

  • enable_database: Enables a connection to MySQL database for iFun Engine’s ORM. If false, ORM operation is done within volatile memory. This is useful when running simple tests during development. (type=bool, default=false)

  • db_mysql_server_address: MySQL server address to use (type=string, default=”tcp://127.0.0.1:3306”)

  • db_mysql_id: MySQL user name to use. (type=string, default=””)

  • db_mysql_pw: MySQL password to use. (type=string, default=””)

  • db_mysql_database: MySQL schema name to use. (type=string, default=””)

  • cache_expiration_in_ms: Milliseconds until cached objects from the DB are removed from the cache (type=int64, default=300000)

  • copy_cache_expiration_in_ms: Milliseconds until objects copied from a remote server are removed from the cache (type=int64, default=700)

  • enable_delayed_db_update: Whether to delay DB updates and to process them in a batch manner. (type=bool, default=false)

  • db_update_delay_in_second: Seconds to delay DB updates if enable_delayed_db_update is on. (type=int64, default=10)

  • db_read_connection_count: Number of DB connections for reading (type=int64, default=8)

  • db_write_connection_count: Number of DB connections for writing (type=int64, default=16)

  • db_key_shard_read_connection_count: Number of DB connections for reading from the key database when using object_subsystem_sharding. (type=int64, default=8)

  • db_key_shard_write_connection_count: Number of DB connections for writing to the key database when using object_subsystem_sharding. (type=int64, default=16)

  • db_character_set: DB character set (type=string, default=”utf8”)

  • export_db_schema: Output the DB schema script and exit if true (type=bool, default=false) Please refer to Required DB permissions (type=bool, default=false)

Parameters that rarely needs to be changed:

  • db_string_length: Length of responding SQL VARCHAR for text string properties (type=int32, default=4096)

  • db_key_string_length: Length of responding SQL CHAR for key text string properties (type=int32, default=12)

  • use_db_stored_procedure: Whether to use MySQL stored procedures instead of RAW SQL statements. (type=bool, default=true)

  • use_db_stored_procedure_full_name: 축약된 이름 대신 긴 이름의 stored procedure 를 사용할지 여부 (type=bool, default=true)

  • use_db_char_type_for_object_id: Uses CHAR(32) instead of BINARY(16) in SQL DB to display object ID (type=bool, default=false)

  • enable_assert_no_rollback: Enable AssertNoRollback() check in code. Please see Detecting unwanted rollbacks. (type=bool, default=true)

  • use_db_select_transaction_isolation_level_read_uncommitted: Sets TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED when issuing SELECT queries. Settings to false falls back to the MySQL default value. (type=bool, default=true)

16.8. How to set up MySQL(MariaDB)

This section is for a programmers not familiar with MySQL/MariaDB setup. It also provides configuration for performance optimization.

You need to edit a configuration file and reboot MySQL/MariaDB. Configuration file is located in a different directory depending on your OS.

  • On Ubuntu

/etc/mysql/my.cnf

or

/etc/mysql/conf.d/mysql.cnf

  • On Centos

/etc/my.cnf

Configuration should be written in the form of key=value and must reside in the [mysqld] configuration section.

  • Incorrect example

[mysqldump]
...
max_connections = 300

  • Correct example

[mysqld]
...
max_connections = 300

16.8.1. Basic configuration

If you are running an iFun Engine game server and a database on the same machine, the configuration below is unimportant. If you use separate machines, however, you better check it out.

  • bind-address:

Network address from where the database receives connections. On Ubuntu, this value is set to 127.0.0.1 and hence only local host is allowed to make a connection to the database.

To make the database receives connections from anywhere, change this value to 0.0.0.0 (meaning anyhost) or just comment out the line.

[mysqld]
...
# bind-address = 127.0.0.1

  • max_connections:

The maximum number of connections that the database can handle. The default value is 151 connections and it may not sufficient for a live service if you run many game servers. Recommended values are 300-500 on a machine with 8GB RAM. It’s also recommended to check open_files_limit explained below.

iFun Engine makes connections as many as this formula.

  • #game servers x #object_databases set in MANIFEST.json x #connections set in MANIFEST.json

The number of connections in MANIFEST.json are the sum of these parameters:

  • db_read_connection_count

  • db_write_connection_count

  • db_key_shard_read_connection_count

  • db_key_shard_write_connection_count

[mysqld]
...
max_connections = 300

  • open_files_limit:

The number file descriptors that the database can open simultaneously.

This value is set as max_connections x 5, and so may be sufficient in many cases. If you have many object models (that is, more tables) you may need to consider increasing the value. Or you can simply set it to 10000 and increase it later when needed. More values may consume more memory.

[mysqld]
...
open_files_limit = 10000

On Ubuntu 16.04 or on CentOS 7, you should set this value in a systemd configuration file instead of my.cnf.

$ sudo vi /lib/systemd/system/mysql.service

[Service]
...
LimitNOFILE=10000

16.8.2. (Advanced) Performance optimization guide

You may need to tune the database configuration. In this case, it’s recommended to change one by one to fully understand its effect.

  • innodb_buffer_pool_size:

    Sets the cache size for a table and index data. The larger the value, the less disk I/O. But too large value will trigger memory swap and make the database thrash.

    The best practice is to set the value to the 80% of system memory and to monitor if memory swapping happens by iostat or vmstat. If the database is not on a designated machine or there’s another factor can cause memory usage (e.g., large max_connections) you better start from less than 80%.

    [mysqld]
...
innodb_buffer_pool_size = 8GB   # Assuming the machine has 10GB RAM

  • innodb_log_file_size:

    Sets the maximum size of redo log files. The higher value it becomes, the less disk I/O and the more recovery time from failure. It’s recommended to have 64MB-512MB. Especially, this can effectively reduces disk I/O if there could be many UPDATEs in a second.

    Note

    To change this value, you must stop MySQL and remove/move existing redo files (/var/lib/mysql/lib_logfile*).

    [mysqld]
...
innodb_log_file_size = 256MB

  • innodb_flush_method:

    Sets a way to flush data in memory to disk. The default value is O_DSYNC and leverages the page caching provided by the OS.

    If innodb_buffer_pool_size is big enough, changing to O_DIRECT allows you to ignore the OS’s page cache. This may help because it prevents from being cached at both MySQL and OS.

    If you use O_DIRECT while innodb_buffer_pool_size is not sufficient, this can increase disk I/O. So you need intensive test before determining a vlaue.

    [mysqld]
...
innodb_flush_method=O_DIRECT

  • innodb_flush_log_at_trx_commit:

    Sets when redo log is recorded. Possible values are 0, 1, or 2, and 1 is the default.

    innodb_flush_log_at_trx_commit=0 : Regardless of transaction, writes the content in the log buffer to the log file, and then flushes to disk once a second. If MySQL stalls, log buffer at most for a second could be lost. This option has the least disk overhead while it’s the most risky.

    innodb_flush_log_at_trx_commit=1 : For each transaction completed, writes the content in the log buffer to the log file, and then flushes to disk. This option is the slowest and has the most disk I/O, but it’s also the most robust.

    innodb_flush_log_at_trx_commit=2 : For each transaction completed, writes the content in the log buffer to the log file. And flushes to disk once a second. This option is safe from MySQL stall, but not safe from OS hang.

    Since values other than the default cannot cause a data loss, it’s recommended to tweak other parameters before changing this configuration. Even with the value of 1, if there’s write-back cache implemented at the hardware-level, data could be lost. So, you also need to check the disk hardware cache. If you data loss is tolerable, the value of 2 is more recommended.

    [mysqld]
...
innodb_flush_log_at_trx_commit=2