git.squeep.com Git - akkoma/blob - docs/docs/configuration/postgresql.md

   1 # Optimizing PostgreSQL performance
   2
   3 Akkoma performance is largely dependent on performance of the underlying database. Better performance can be achieved by adjusting a few settings.
   4
   5 ## PGTune
   6
   7 [PgTune](https://pgtune.leopard.in.ua) can be used to get recommended settings. Be sure to set "Number of Connections" to 20, otherwise it might produce settings hurtful to database performance. It is also recommended to not use "Network Storage" option.
   8
   9 If your server runs other services, you may want to take that into account. E.g. if you have 4G ram, but 1G of it is already used for other services, it may be better to tell PGTune you only have 3G. In the end, PGTune only provides recomended settings, you can always try to finetune further.
  10
  11 ### Example configurations
  12
  13 Here are some configuration suggestions for PostgreSQL 10+.
  14
  15 #### 1GB RAM, 1 CPU
  16 ```
  17 shared_buffers = 256MB
  18 effective_cache_size = 768MB
  19 maintenance_work_mem = 64MB
  20 work_mem = 13107kB
  21 ```
  22
  23 #### 2GB RAM, 2 CPU
  24 ```
  25 shared_buffers = 512MB
  26 effective_cache_size = 1536MB
  27 maintenance_work_mem = 128MB
  28 work_mem = 26214kB
  29 max_worker_processes = 2
  30 max_parallel_workers_per_gather = 1
  31 max_parallel_workers = 2
  32 ```
  33
  34 ## Disable generic query plans
  35
  36 When PostgreSQL receives a query, it decides on a strategy for searching the requested data, this is called a query plan. The query planner has two modes: generic and custom. Generic makes a plan for all queries of the same shape, ignoring the parameters, which is then cached and reused. Custom, on the contrary, generates a unique query plan based on query parameters.
  37
  38 By default PostgreSQL has an algorithm to decide which mode is more efficient for particular query, however this algorithm has been observed to be wrong on some of the queries Akkoma sends, leading to serious performance loss. Therefore, it is recommended to disable generic mode.
  39
  40
  41 Akkoma already avoids generic query plans by default, however the method it uses is not the most efficient because it needs to be compatible with all supported PostgreSQL versions. For PostgreSQL 12 and higher additional performance can be gained by adding the following to Akkoma configuration:
  42 ```elixir
  43 config :pleroma, Pleroma.Repo,
  44   prepare: :named,
  45   parameters: [
  46     plan_cache_mode: "force_custom_plan"
  47   ]
  48 ```
  49
  50 A more detailed explaination of the issue can be found at <https://blog.soykaf.com/post/postgresql-elixir-troubles/>.