Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Elixirでスケールアウト可能なWebDB開発.pdf

tuchiro
August 24, 2018
320

 Elixirでスケールアウト可能なWebDB開発.pdf

tuchiro

August 24, 2018
Tweet

Transcript

  1. Web サーバー ( 一般的に水平) セッション管理の方式検討が必要 LB がボトルネックになる可能性 データベース ( 一般的に垂直)

    リードレプリケーションや、パーティショニング で水平も可能 DB 製品によっては構成を増やすことでりライセン ス料に影響
  2. 比較結果 構成 EC2 DB(RDS MySQL) 合計金額($/month) 基準との比率 基準 t2-midium 2台

    db.m3.medium 147.85 - Web サーバー2倍 t2-midium 4 台 db.m3.medium 210.62 1.4 DB 2倍 t2-midium 2台 db.m3.large 234.45 1.6 Web サーバー4 倍 t2-midium 8 台 db.m3.medium 330.63 2.2 DB4 倍 t2-midium 2台 db.m3.xlarge 417.79 2.8 Web サーバー8 倍 t2-midium 16 台 db.m3.medium 570.65 3.9 DB8 倍 t2-midium 2台 db.m3.2xlarge 769.15 5.2 ※ Amazon EC2 インスタンスタイプ表 ※ Amazon RDS インスタンスタイプ表
  3. defmodule EctoRelationSample.Account.User do use Ecto.Schema import Ecto.Changeset schema "users" do

    field :email, :string field :name, :string has_many :posts, EctoRelationSample.Contents.Post # this was added timestamps() end @doc false def changeset(user, attrs) do user |> cast(attrs, [:name, :email]) |> validate_required([:name, :email]) end end
  4. defmodule EctoRelationSample.Contents.Post do use Ecto.Schema import Ecto.Changeset schema "posts" do

    field :body, :string field :title, :string belongs_to :user, EctoRelationSample.Account.User # this was added timestamps() end @doc false def changeset(post, attrs) do post |> cast(attrs, [:title, :body]) |> validate_required([:title, :body]) end end
  5. iex(1)> user = %User{name: "John Doe", email: "[email protected]"} iex(2)> user

    = Repo.insert!(user) iex(3)> post = Ecto.build_assoc(user, :posts, %{title: "sample ecto relation", body: "ecto assocs example"}) iex(4)> Repo.insert!(post) iex(5)> post = Ecto.build_assoc(user, :posts, %{title: "sample ecto relation2", body: "ecto assocs example section 2"}) iex(6)> Repo.insert!(post)
  6. iex(7)> Repo.all(User) |> Repo.preload(:posts) [ %EctoRelationSample.Account.User{ __meta__: #Ecto.Schema.Metadata<:loaded, "users">, email:

    "[email protected]", id: 1, inserted_at: ~N[2018-08-21 06:12:52.155453], name: "John Doe", posts: [ %EctoRelationSample.Contents.Post{ __meta__: #Ecto.Schema.Metadata<:loaded, "posts">, body: "ecto assocs example", id: 1, inserted_at: ~N[2018-08-21 06:13:22.566436], title: "sample ecto relation", updated_at: ~N[2018-08-21 06:13:22.566447], user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 1 }, %EctoRelationSample.Contents.Post{ __meta__: #Ecto.Schema.Metadata<:loaded, "posts">, body: "ecto assocs example section 2", id: 2, inserted_at: ~N[2018-08-21 06:14:08.199156], title: "sample ecto relation2", updated_at: ~N[2018-08-21 06:14:08.199165], user: #Ecto.Association.NotLoaded<association :user is not loaded>, user_id: 1 } ], updated_at: ~N[2018-08-21 06:12:52.161839] } ]
  7. iex(26)> Repo.all(Post) |> Repo.preload(:user) [ %EctoRelationSample.Contents.Post{ __meta__: #Ecto.Schema.Metadata<:loaded, "posts">, body:

    "ecto assocs example", id: 1, inserted_at: ~N[2018-08-21 06:13:22.566436], title: "sample ecto relation", updated_at: ~N[2018-08-21 06:13:22.566447], user: %EctoRelationSample.Account.User{ __meta__: #Ecto.Schema.Metadata<:loaded, "users">, email: "[email protected]", id: 1, inserted_at: ~N[2018-08-21 06:12:52.155453], name: "John Doe", posts: #Ecto.Association.NotLoaded<association :posts is not loaded>, updated_at: ~N[2018-08-21 06:12:52.161839] }, user_id: 1 } ]
  8. preload で発行されるSQL を確認 2018-08-20 23:25:12.371 PDT [89205] LOG: execute ecto_450:

    SELECT p0."id", p0."body", p0."title", p0."user_id", p0."inserted_at", p0."updated_at" FROM "posts" AS p0 2018-08-20 23:25:12.377 PDT [89206] LOG: execute ecto_482: SELECT u0."id", u0."email", u0."name", u0."inserted_at", u0."updated_at", u0."id" FROM "users" AS u0 WHERE (u0."id" = ANY($1)) 2018-08-20 23:25:12.377 PDT [89206] DETAIL: parameters: $1 = '{3,1}'
  9. Ecto.Query を用いた場合 iex(30)> import Ecto.Query Ecto.Query iex(31)> query = from

    u in User, join: p in Post, where: u.id == p.user_id, group_by: u.name, select: {u.name, count(p.id)} iex(32)> Repo.all(query) [debug] QUERY OK source="users" db=2.1ms SELECT u0."name", count(p1."id") FROM "users" AS u0 INNER JOIN "posts" AS p1 ON TRUE WHERE (u0."id" = p1."user_id") GROUP BY u0."name" [] [{"John Doe", 2}, {"nanashi gonbe", 1}] 2018-08-21 00:13:12.521 PDT [89207] LOG: execute ecto_642: SELECT u0."name", count(p1."id") FROM "users" AS u0 INNER JOIN "posts" AS p1 ON TRUE WHERE (u0."id" = p1."user_id") GROUP BY u0."name" 戻りがタプルのリスト・・・ [{"John Doe", 2}, {"nanashi gonbe", 1}]
  10. Ecto.Adapters.SQL を用いた場合 Ecto.Query iex(40)> sql = "select u.name, count(p.*) from

    users u join posts p on u.id = p.user_id group by u.name" iex(41)> Ecto.Adapters.SQL.query(Repo, sql, []) [debug] QUERY OK db=28.0ms queue=0.1ms select u.name, count(p.id) from users u inner join posts p on u.id = p.user_id group by u.name [] {:ok, %Postgrex.Result{ columns: ["name", "count"], command: :select, connection_id: 90367, num_rows: 2, rows: [["John Doe", 2], ["nanashi gonbe", 1]] }}
  11. Ecto.Adapters.SQL をWrapp した実装 defmodule AppExUtils.Ecto.EctoUtil do import Ecto.Query, warn: false

    @spec query(Repo, string, [list]) :: [list] def query(repo, sql, params) do Ecto.Adapters.SQL.query(repo, sql, params) |> result_to_map_list() end defp result_to_map_list(nil) do # 戻りが無いSQL の場合、nil で処理する nil end defp result_to_map_list({:error, error}) do # エラーはスルー {:error, error} end
  12. defp result_to_map_list({:ok, result}) do columns = result.columns case columns do

    nil -> [num_rows: result.num_rows] _ -> rows = result.rows list_maps = Enum.map(rows, fn row -> row_columns_to_map(row, columns) end end defp row_columns_to_map(row, columns) do map_result = Enum.map(Enum.with_index(row, 0), fn {k, i} -> [Enum.at(columns, i), k] |> Enum.map(fn [a, b] -> {String.to_atom(a), convert(b)} end) |> Map.new() end def convert({{year, month, day}, {hour, minites, sec, msec}}) do # 日時解釈できるものはDateTime に変換 Timex.to_datetime({{year, month, day}, {hour, minites, sec, msec}}) end def convert(attr) do attr end end
  13. iex(1)> alias AppExUtils.Ecto.EctoUtil AppExUtils.Ecto.EctoUtil iex(2)> sql = "select u.name, count(p.id)

    from users u inner join posts p on u.id = p.user_id group by u.name" iex(3)> EctoUtil.query(Repo, sql, []) [%{count: 2, name: "John Doe"}, %{count: 1, name: "nanashi gonbe"}] 2018-08-21 00:29:23.087 PDT [90366] LOG: execute <unnamed>: select u.name, count(p.id) from users u inner join posts p on u.id = p.user_id group by u.name 戻りをmap のリストにする(通常のEcto.Repo 関数の 戻りに近い)ことで実装時の煩わしさを排除 [%{count: 2, name: "John Doe"}, %{count: 1, name: "nanashi gonbe"}]
  14. Enum を使い倒す実装 例えばSQL のgroup by に変わる関数 def group_by(maps, group_by_key_list) do

    maps |> Enum.group_by(fn(map) -> group_by_key_list |> Enum.map(fn(key) -> map[key] end) end) |> Map.to_list() |> Enum.map(fn(group_by_result) -> {group_value_list, maps} = group_by_result group_by_keys_map = group_by_key_list Enum.zip(group_value_list) |> Enum.into(%{}) {group_by_keys_map, maps} end) end
  15. iex(2)> users = [%{name: "hogehoge", address: "aaa", age: 16}, %{name:

    "hogehoge", address: nil, age: 4}, %{name: "fugafuga", address: "aaa",age: 16}] [ %{address: "aaa", age: 16, name: "hogehoge"}, %{address: nil, age: 4, name: "hogehoge"}, %{address: "aaa", age: 16, name: "fugafuga"} ] iex(4)> EnumLikeSqlUtil.group_by(users, [:name]) [ {%{name: "fugafuga"}, [%{address: "aaa", age: 16, name: "fugafuga"}]}, {%{name: "hogehoge"}, [ %{address: "aaa", age: 16, name: "hogehoge"}, %{address: nil, age: 4, name: "hogehoge"} ]} ]
  16. def sum(group_by_results, sum_key_list) do initial_result = sum_key_list |> Enum.reduce(%{}, fn(key,

    initial_result) -> Map.put(initial_result, key, 0) end) sum_result_maps = group_by_results |> Enum.map(fn(group_by_result) -> {group_by_keys_map, maps} = group_by_result sum_result_map = maps |> Enum.reduce(initial_result, fn(map, result_map) -> sum_tmp_map = sum_key_list |> Enum.reduce(result_map, fn(key, tmp_map) -> Map.put(tmp_map, key, tmp_map[key] + map[key]) end) end) Map.merge(group_by_keys_map, sum_result_map) end) end
  17. def count(group_by_results, count_key_list \\ []) do initial_result = count_key_list |>

    Enum.reduce(%{}, fn(key, initial_result) -> Map.put(initial_result, key, 0) end) count_result_maps = group_by_results |> Enum.map(fn(group_by_result) -> {group_by_keys_map, maps} = group_by_result all_count = maps |> Enum.count() count_map = %{all_count: all_count} col_count_map = count_key_list |> Enum.reduce(count_map, fn(count_key, count_map) -> col_count = maps |> Enum.count(fn(map) -> map[count_key] != nil end) Map.put(count_map, count_key, col_count) end) count_map = count_map |> Map.merge(col_count_map) {group_by_keys_map, count_map} end) end
  18. iex(5)> users |> EnumLikeSqlUtil.group_by([:name]) |> EnumLikeSqlUtil.count([:address]) [ {%{name: "fugafuga"}, %{address:

    1, all_count: 1}}, {%{name: "hogehoge"}, %{address: 1, all_count: 2}} ] iex(6)> users |> EnumLikeSqlUtil.group_by([:name]) |> EnumLikeSqlUtil.sum([:age]) [%{age: 16, name: "fugafuga"}, %{age: 20, name: "hogehoge"}]