Horrible search/tag query performance
Since merging database compaction a lot of people, myself included started noticing search queries on stuff older than 2-3 weeks timing out quite often
Benchmark code:
defmodule Mix.Tasks.Pleroma.Benchmark do
use Mix.Task
alias Mix.Tasks.Pleroma.Common
def run(["search"]) do
Common.start_pleroma()
Benchee.run(%{
"search" => fn ->
Pleroma.Web.MastodonAPI.MastodonAPIController.status_search(nil, "cofe")
end
})
end
def run(["tag"]) do
Common.start_pleroma()
Benchee.run(%{
"tag" => fn ->
%{"type" => "Create", "tag" => "cofe"}
|> Pleroma.Web.ActivityPub.ActivityPub.fetch_public_activities()
end
})
end
end
This is probably not ideal because postgres caches queries etc. The difference may not even sound convincing, but this is the best I could do to prove it instead of just "it feels slower", if you have any better ideas how to benchmark it, please do tell
All databases have been VACUUM FULLed and ANALYZEd before benchmarking
Results on non-compacted db with old search queries (commit cc93775e):
$ mix pleroma.benchmark search
Operating System: Linux
CPU Information: Unrecognized processor
Number of Available Cores: 4
Available memory: 1.88 GB
Elixir 1.8.1
Erlang 21.2.6
Benchmark suite executing with the following configuration:
warmup: 2 s
time: 5 s
memory time: 0 ns
parallel: 1
inputs: none specified
Estimated total run time: 7 s
Benchmarking search...
Name ips average deviation median 99th %
search 2.42 414.06 ms ±7.64% 413.14 ms 444.82 ms
$ mix pleroma.benchmark tag
Operating System: Linux
CPU Information: Unrecognized processor
Number of Available Cores: 4
Available memory: 1.88 GB
Elixir 1.8.1
Erlang 21.2.6
Benchmark suite executing with the following configuration:
warmup: 2 s
time: 5 s
memory time: 0 ns
parallel: 1
inputs: none specified
Estimated total run time: 7 s
Benchmarking tag...
Name ips average deviation median 99th %
tag 7.20 138.81 ms ±110.84% 78.94 ms 532.96 ms
Results on non-compacted db with new search queries (commit 9da8b287):
$ mix pleroma.benchmark search
Operating System: Linux
CPU Information: Unrecognized processor
Number of Available Cores: 4
Available memory: 1.88 GB
Elixir 1.8.1
Erlang 21.2.6
Benchmark suite executing with the following configuration:
warmup: 2 s
time: 5 s
memory time: 0 ns
parallel: 1
inputs: none specified
Estimated total run time: 7 s
Benchmarking search...
Name ips average deviation median 99th %
search 1.82 548.64 ms ±8.33% 559.37 ms 593.72 ms
$ mix pleroma.benchmark tag
Operating System: Linux
CPU Information: Unrecognized processor
Number of Available Cores: 4
Available memory: 1.88 GB
Elixir 1.8.1
Erlang 21.2.6
Benchmark suite executing with the following configuration:
warmup: 2 s
time: 5 s
memory time: 0 ns
parallel: 1
inputs: none specified
Estimated total run time: 7 s
Benchmarking tag...
Name ips average deviation median 99th %
tag 0.77 1.31 s ±25.98% 1.19 s 1.79 s
Results on compacted db with new search queries (commit 9da8b287):
$ mix pleroma.benchmark search
Operating System: Linux
CPU Information: Unrecognized processor
Number of Available Cores: 4
Available memory: 1.88 GB
Elixir 1.8.1
Erlang 21.2.6
Benchmark suite executing with the following configuration:
warmup: 2 s
time: 5 s
memory time: 0 ns
parallel: 1
inputs: none specified
Estimated total run time: 7 s
Benchmarking search...
Name ips average deviation median 99th %
search 2.06 485.65 ms ±10.12% 480.92 ms 605.03 ms
$ mix pleroma.benchmark tag
Operating System: Linux
CPU Information: Unrecognized processor
Number of Available Cores: 4
Available memory: 1.88 GB
Elixir 1.8.1
Erlang 21.2.6
Benchmark suite executing with the following configuration:
warmup: 2 s
time: 5 s
memory time: 0 ns
parallel: 1
inputs: none specified
Estimated total run time: 7 s
Benchmarking tag...
Name ips average deviation median 99th %
tag 1.49 672.48 ms ±15.58% 694.68 ms 777.66 ms
Edited by rinpatch