LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@dennis_wang(dennis wang)

Spring BootでPolarDB-Xを利用し、分散型のRDBを体験

Posted at

PolarDB-Xとは

PolarDB-XはAlibaba Cloudで提供されているクラウドネイティブ分散型RDBです。
GCPのSpannerやPingCAPのTiDBと同じカテゴリの水平方向にスケーリングするNewSQLに分類されるデータベースです。
詳細内容はdockerでクラウドネイティブ分散型RDBのPolarDB-XとWordPressの環境を構築 をご参照ください。

dockerでPolarDB-Xをinstallする

$ docker run -d --name some-polardb-x -p 8527:8527 polardbx/polardb-x

$ docker images
REPOSITORY                       TAG           IMAGE ID       CREATED       SIZE
polardbx/polardb-x               latest        e1e6695ee92c   7 weeks ago   1.49GB

polardb-xにログインし、サンプルコード実行するためのdatabaseとuserを作成しておく

  • 以下のエラーが発生している場合は、polardb-xクラスター準備している途中なので、三四分程度経ってから再度ログインしてください。
$ mysql -h127.0.0.1 -P8527 -upolardbx_root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
  • polardb-xにログイン
mysql -h127.0.0.1 -P8527 -upolardbx_root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.29 Tddl Server (ALIBABA)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| DATABASE           |
+--------------------+
| information_schema |
+--------------------+
  • db_exampleというdatabaseを作成
mysql> create database db_example mode="auto";
  • springuserというuserを作成
mysql> create user 'springuser'@'%' identified by 'ThePassword';
  • 新規作成されたuserに権限を付与
mysql> grant all on db_example.* to 'springuser'@'%';

macOSでJDKをインストール

  • JDKすでにインストールしている場合は、以下の部分を飛ばしてください。
$ brew update
$ brew install java
$ echo 'export PATH="/usr/local/opt/openjdk/bin:$PATH"' >> ~/.zshrc
$ source ~/.zshrc

$ java --version
openjdk 18.0.1.1 2022-04-22
OpenJDK Runtime Environment Homebrew (build 18.0.1.1+0)
OpenJDK 64-Bit Server VM Homebrew (build 18.0.1.1+0, mixed mode, sharing)

Spring Bootアプリケーションの作成

  • サンプルコードをclone
git clone https://github.com/spring-guides/gs-accessing-data-mysql.git

cd gs-accessing-data-mysql/initial

ls -al
drwxr-xr-x   3 dan.w  staff    96 Jul 20 14:47 .mvn
-rw-r--r--   1 dan.w  staff   546 Jul 20 14:47 build.gradle
drwxr-xr-x   3 dan.w  staff    96 Jul 20 14:47 gradle
-rwxr-xr-x   1 dan.w  staff  8070 Jul 20 14:47 gradlew
-rw-r--r--   1 dan.w  staff  2763 Jul 20 14:47 gradlew.bat
-rwxr-xr-x   1 dan.w  staff  9895 Jul 20 14:47 mvnw
-rwxr-xr-x   1 dan.w  staff  6301 Jul 20 14:47 mvnw.cmd
-rw-r--r--   1 dan.w  staff  1544 Jul 20 14:47 pom.xml
-rw-r--r--   1 dan.w  staff    42 Jul 20 14:47 settings.gradle
drwxr-xr-x   4 dan.w  staff   128 Jul 20 14:47 src
  • アプリケーションの設定ファイルapplication.propertiesを修正
    src/main/resources/application.properties中のspring.datasource.urlを以下のように修正
spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:8527/db_example
  • Entity Modelを新規作成
vim src/main/java/com/example/accessingdatamysql/User.java

package com.example.accessingdatamysql;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity // This tells Hibernate to make a table out of this class
public class User {
  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private Integer id;

  private String name;

  private String email;

  public Integer getId() {
    return id;
  }

  public void setId(Integer id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getEmail() {
    return email;
  }

  public void setEmail(String email) {
    this.email = email;
  }
}
  • UserRepositoryの新規作成
vim src/main/java/com/example/accessingdatamysql/UserRepository.java

package com.example.accessingdatamysql;

import org.springframework.data.repository.CrudRepository;

import com.example.accessingdatamysql.User;

// This will be AUTO IMPLEMENTED by Spring into a Bean called userRepository
// CRUD refers Create, Read, Update, Delete

public interface UserRepository extends CrudRepository<User, Integer> {

}
  • MainControllerの新規作成
vim src/main/java/com/example/accessingdatamysql/MainController.java

package com.example.accessingdatamysql;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller // This means that this class is a Controller
@RequestMapping(path="/demo") // This means URL's start with /demo (after Application path)
public class MainController {
  @Autowired // This means to get the bean called userRepository
         // Which is auto-generated by Spring, we will use it to handle the data
  private UserRepository userRepository;

  @PostMapping(path="/add") // Map ONLY POST Requests
  public @ResponseBody String addNewUser (@RequestParam String name
      , @RequestParam String email) {
    // @ResponseBody means the returned String is the response, not a view name
    // @RequestParam means it is a parameter from the GET or POST request

    User n = new User();
    n.setName(name);
    n.setEmail(email);
    userRepository.save(n);
    return "Saved";
  }

  @GetMapping(path="/all")
  public @ResponseBody Iterable<User> getAllUsers() {
    // This returns a JSON or XML with the users
    return userRepository.findAll();
  }
}
  • Applicationの新規作成
    • サンプルコードにすでにAccessingDataMysqlApplication があるため、新規作成する必要がない。
package com.example.accessingdatamysql;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class AccessingDataMysqlApplication {

  public static void main(String[] args) {
    SpringApplication.run(AccessingDataMysqlApplication.class, args);
  }

}

  • アプリケーションのの実行
    gs-accessing-data-mysql/initialの配下に./gradlew bootRunを実行し、二分程度立つと、以下の画面が表示される
    image.png

  • 別のタブで以下のコマンドを実行し、３つのuserを作成

$ curl localhost:8080/add -d name=First -d email=username@example.com
Saved%
$ curl localhost:8080/add -d name=Second -d email=username2@example.com
Saved%
$ curl localhost:8080/add -d name=Third -d email=username3@example.com
Saved%

databaseの中身を確認

  • ３つのuserのレコードが作られた。
mysql> use db_example;

mysql> show tables;
+----------------------+
| TABLES_IN_DB_EXAMPLE |
+----------------------+
| hibernate_sequence   |
| user                 |
+----------------------+

mysql> select * from user;
+------+-----------------------+--------+
| id   | email                 | name   |
+------+-----------------------+--------+
|    2 | username2@example.com | Second |
|    3 | username3@example.com | Third  |
|    1 | username@example.com  | First  |
+------+-----------------------+--------+
  • テーブルの作成クエリを確認
    • idをpartition keyとして利用されている
mysql> show full create table user;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE                                                                                                                                                                                                                                                                                     |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE PARTITION TABLE `user` (
	`id` int(11) NOT NULL,
	`email` varchar(255) DEFAULT NULL,
	`name` varchar(255) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_0900_ai_ci
PARTITION BY KEY(`id`)
PARTITIONS 8
/* tablegroup = `tg2` */ |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  • tableのpartition状況を確認
    • 8つのpartitionが自動的に作られた。
mysql> show topology from user;
+----+-------------------------+-----------------+----------------+
| ID | GROUP_NAME              | TABLE_NAME      | PARTITION_NAME |
+----+-------------------------+-----------------+----------------+
|  0 | DB_EXAMPLE_P00000_GROUP | user_xiWe_00000 | p1             |
|  1 | DB_EXAMPLE_P00000_GROUP | user_xiWe_00001 | p2             |
|  2 | DB_EXAMPLE_P00000_GROUP | user_xiWe_00002 | p3             |
|  3 | DB_EXAMPLE_P00000_GROUP | user_xiWe_00003 | p4             |
|  4 | DB_EXAMPLE_P00000_GROUP | user_xiWe_00004 | p5             |
|  5 | DB_EXAMPLE_P00000_GROUP | user_xiWe_00005 | p6             |
|  6 | DB_EXAMPLE_P00000_GROUP | user_xiWe_00006 | p7             |
|  7 | DB_EXAMPLE_P00000_GROUP | user_xiWe_00007 | p8             |
+----+-------------------------+-----------------+----------------+
  • レコードがどのpartitionに保存されているのかを確認
    • INFORMATION_SCHEMAからは統計情報しか取得できなくて、実際の件数は異なる場合はある
    • P1に2件、P7には1件のデータがあることが分かる。
mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='user';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p1             |          0 |
| p2             |          0 |
| p3             |          0 |
| p4             |          0 |
| p5             |          0 |
| p6             |          0 |
| p7             |          1 |
| p8             |          0 |
+----------------+------------+

mysql> select * from user PARTITION (p1);
+------+-----------------------+--------+
| id   | email                 | name   |
+------+-----------------------+--------+
|    2 | username2@example.com | Second |
|    3 | username3@example.com | Third  |
+------+-----------------------+--------+

mysql> select * from user PARTITION (p7);
+------+----------------------+-------+
| id   | email                | name  |
+------+----------------------+-------+
|    1 | username@example.com | First |
+------+----------------------+-------+

まとめ

  • polardb-xはデフォルトはPrimary keyをpartition keyとして利用するため、MySQLのpartition関連のノウハウを持っていなくても簡単にpartition利用できる。
  • 特に分散やpartitionを意識しなくてもシングルノードのMySQLと同じ感覚で、分散型データベース利用できる（もちろん同時処理の性能が水平的に拡張できるという分散型のデータベースの最大のメリットを活かすにはpartiton関連の知識がある程度必要です。）
1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?