From ef56488349a257def67d6c906a1f71e9bbed397e Mon Sep 17 00:00:00 2001 From: William Pitcock Date: Thu, 29 Nov 2018 06:53:17 +0000 Subject: mix: add task to compact the database --- lib/mix/tasks/compact_database.ex | 57 +++++++++++++++++++++++++++++++++++++++ 1 file changed, 57 insertions(+) create mode 100644 lib/mix/tasks/compact_database.ex (limited to 'lib/mix/tasks') diff --git a/lib/mix/tasks/compact_database.ex b/lib/mix/tasks/compact_database.ex new file mode 100644 index 000000000..b84b340ac --- /dev/null +++ b/lib/mix/tasks/compact_database.ex @@ -0,0 +1,57 @@ +defmodule Mix.Tasks.CompactDatabase do + @moduledoc """ + Compact the database by flattening the object graph. + """ + + require Logger + + use Mix.Task + import Mix.Ecto + import Ecto.Query + alias Pleroma.{Repo, Object, Activity} + + defp maybe_compact(%Activity{data: %{"object" => %{"id" => object_id}}} = activity) do + data = + activity.data + |> Map.put("object", object_id) + + {:ok, activity} = + Activity.change(activity, %{data: data}) + |> Repo.update() + + {:ok, activity} + end + + defp maybe_compact(%Activity{} = activity), do: {:ok, activity} + + defp activity_query(min_id, max_id) do + from( + a in Activity, + where: fragment("?->>'type' = 'Create'", a.data), + where: a.id >= ^min_id, + where: a.id < ^max_id + ) + end + + def run(args) do + Application.ensure_all_started(:pleroma) + + max = Repo.aggregate(Activity, :max, :id) + Logger.info("Considering #{max} activities") + + chunks = 0..(round(max / 100)) + + Enum.each(chunks, fn (i) -> + min = i * 100 + max = min + 100 + + activity_query(min, max) + |> Repo.all() + |> Enum.each(&maybe_compact/1) + + IO.write(".") + end) + + Logger.info("Finished.") + end +end -- cgit v1.2.3 From 1a360a4eaa57d57ccc4f03dfa25e82a240d0175a Mon Sep 17 00:00:00 2001 From: William Pitcock Date: Sat, 1 Dec 2018 22:17:28 +0000 Subject: compact database task: fix formatting --- lib/mix/tasks/compact_database.ex | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'lib/mix/tasks') diff --git a/lib/mix/tasks/compact_database.ex b/lib/mix/tasks/compact_database.ex index b84b340ac..7de50812a 100644 --- a/lib/mix/tasks/compact_database.ex +++ b/lib/mix/tasks/compact_database.ex @@ -39,9 +39,9 @@ defmodule Mix.Tasks.CompactDatabase do max = Repo.aggregate(Activity, :max, :id) Logger.info("Considering #{max} activities") - chunks = 0..(round(max / 100)) + chunks = 0..round(max / 100) - Enum.each(chunks, fn (i) -> + Enum.each(chunks, fn i -> min = i * 100 max = min + 100 -- cgit v1.2.3 From a11ca87f40fd85341afa4445d3b7303ae8e92b76 Mon Sep 17 00:00:00 2001 From: rinpatch Date: Thu, 18 Apr 2019 13:10:38 +0300 Subject: Add a migration to remove embeded objects --- lib/mix/tasks/compact_database.ex | 57 --------------------------------------- 1 file changed, 57 deletions(-) delete mode 100644 lib/mix/tasks/compact_database.ex (limited to 'lib/mix/tasks') diff --git a/lib/mix/tasks/compact_database.ex b/lib/mix/tasks/compact_database.ex deleted file mode 100644 index 17b9721f7..000000000 --- a/lib/mix/tasks/compact_database.ex +++ /dev/null @@ -1,57 +0,0 @@ -defmodule Mix.Tasks.CompactDatabase do - @moduledoc """ - Compact the database by flattening the object graph. - """ - - require Logger - - use Mix.Task - import Ecto.Query - alias Pleroma.Activity - alias Pleroma.Repo - - defp maybe_compact(%Activity{data: %{"object" => %{"id" => object_id}}} = activity) do - data = - activity.data - |> Map.put("object", object_id) - - {:ok, activity} = - Activity.change(activity, %{data: data}) - |> Repo.update() - - {:ok, activity} - end - - defp maybe_compact(%Activity{} = activity), do: {:ok, activity} - - defp activity_query(min_id, max_id) do - from( - a in Activity, - where: fragment("?->>'type' = 'Create'", a.data), - where: a.id >= ^min_id, - where: a.id < ^max_id - ) - end - - def run(_args) do - Application.ensure_all_started(:pleroma) - - max = Repo.aggregate(Activity, :max, :id) - Logger.info("Considering #{max} activities") - - chunks = 0..round(max / 100) - - Enum.each(chunks, fn i -> - min = i * 100 - max = min + 100 - - activity_query(min, max) - |> Repo.all() - |> Enum.each(&maybe_compact/1) - - IO.write(".") - end) - - Logger.info("Finished.") - end -end -- cgit v1.2.3 From 099f89367efaf4032b8e937258b2c1a90f16b047 Mon Sep 17 00:00:00 2001 From: rinpatch Date: Thu, 18 Apr 2019 23:34:01 +0300 Subject: Replace embedded object migration with a mix task --- lib/mix/tasks/pleroma/database.ex | 45 +++++++++++++++++++++++++++++++++++++++ 1 file changed, 45 insertions(+) create mode 100644 lib/mix/tasks/pleroma/database.ex (limited to 'lib/mix/tasks') diff --git a/lib/mix/tasks/pleroma/database.ex b/lib/mix/tasks/pleroma/database.ex new file mode 100644 index 000000000..ce3252af5 --- /dev/null +++ b/lib/mix/tasks/pleroma/database.ex @@ -0,0 +1,45 @@ +# Pleroma: A lightweight social networking server +# Copyright © 2017-2018 Pleroma Authors +# SPDX-License-Identifier: AGPL-3.0-only + +defmodule Mix.Tasks.Pleroma.Database do + alias Mix.Tasks.Pleroma.Common + use Mix.Task + + @shortdoc "A collection of database related tasks" + @moduledoc """ + A collection of database related tasks + + ## Replace embedded objects with their references + + Replaces embedded objects with references to them in the `objects` table. Only needs to be ran once. The reason why this is not a migration is because it could significantly increase the database size after being ran, however after this `VACUUM FULL` will be able to reclaim about 20% (really depends on what is in the database, your mileage may vary) of the db size before the migration. + + mix pleroma.database remove_embedded_objects + + Options: + - `--vacuum` - run `VACUUM FULL` after the embedded objects are replaced with their references + """ + def run(["remove_embedded_objects" | args]) do + {options, [], []} = + OptionParser.parse( + args, + strict: [ + vacuum: :boolean + ] + ) + + Common.start_pleroma() + + Ecto.Adapters.SQL.query!( + Pleroma.Repo, + "update activities set data = jsonb_set(data, '{object}'::text[], data->'object'->'id') where data->'object'->>'id' is not null;" + ) + + if Keyword.get(options, :vacuum) do + Ecto.Adapters.SQL.query!( + Pleroma.Repo, + "vacuum full;" + ) + end + end +end -- cgit v1.2.3 From 945325013af6dde3f1da2417753bb97f55911a84 Mon Sep 17 00:00:00 2001 From: rinpatch Date: Thu, 18 Apr 2019 23:58:59 +0300 Subject: remove query timeouts --- lib/mix/tasks/pleroma/database.ex | 14 ++++++++------ 1 file changed, 8 insertions(+), 6 deletions(-) (limited to 'lib/mix/tasks') diff --git a/lib/mix/tasks/pleroma/database.ex b/lib/mix/tasks/pleroma/database.ex index ce3252af5..d657c1ef0 100644 --- a/lib/mix/tasks/pleroma/database.ex +++ b/lib/mix/tasks/pleroma/database.ex @@ -30,15 +30,17 @@ defmodule Mix.Tasks.Pleroma.Database do Common.start_pleroma() - Ecto.Adapters.SQL.query!( - Pleroma.Repo, - "update activities set data = jsonb_set(data, '{object}'::text[], data->'object'->'id') where data->'object'->>'id' is not null;" + Pleroma.Repo.query!( + "update activities set data = jsonb_set(data, '{object}'::text[], data->'object'->'id') where data->'object'->>'id' is not null;", + [], + timeout: :infinity ) if Keyword.get(options, :vacuum) do - Ecto.Adapters.SQL.query!( - Pleroma.Repo, - "vacuum full;" + Pleroma.Repo.query!( + "vacuum full;", + [], + timeout: :infinity ) end end -- cgit v1.2.3 From 73b8c5387b25caaf27734f7018dc4702d49af7de Mon Sep 17 00:00:00 2001 From: rinpatch Date: Fri, 19 Apr 2019 00:17:37 +0300 Subject: Add some logging --- lib/mix/tasks/pleroma/database.ex | 4 ++++ 1 file changed, 4 insertions(+) (limited to 'lib/mix/tasks') diff --git a/lib/mix/tasks/pleroma/database.ex b/lib/mix/tasks/pleroma/database.ex index d657c1ef0..ab9a3a7ff 100644 --- a/lib/mix/tasks/pleroma/database.ex +++ b/lib/mix/tasks/pleroma/database.ex @@ -4,6 +4,7 @@ defmodule Mix.Tasks.Pleroma.Database do alias Mix.Tasks.Pleroma.Common + require Logger use Mix.Task @shortdoc "A collection of database related tasks" @@ -29,6 +30,7 @@ defmodule Mix.Tasks.Pleroma.Database do ) Common.start_pleroma() + Logger.info("Removing embedded objects") Pleroma.Repo.query!( "update activities set data = jsonb_set(data, '{object}'::text[], data->'object'->'id') where data->'object'->>'id' is not null;", @@ -37,6 +39,8 @@ defmodule Mix.Tasks.Pleroma.Database do ) if Keyword.get(options, :vacuum) do + Logger.info("Runnning VACUUM FULL") + Pleroma.Repo.query!( "vacuum full;", [], -- cgit v1.2.3