Optimize Postgres v12 Memory Parameters (memory-settings.yml)

[JoshDi]

I have changed some of my Postgres v12 Memory Parameters (memory-settings.yml) and noticed that live indexing search is working better. Here are the settings I am now using.

Does anyone have any suggestions? I am running my Musicbrainz replication server on a server with 24c/48t and 128gb of ram. Thank you!

local/compose/memory-settings.yml

version: '3.1'

# Description: Customize memory settings

services:
  db:
    shm_size: 4g
    command: postgres -c "shared_buffers=4GB" -c "work_mem=128MB" -c "maintenance_work_mem=4GB" -c "effective_cache_size=64GB" -c "shared_preload_libraries=pg_amqp.so"
  search:
    environment:
      - SOLR_HEAP=4g

[SuperSandro2000]

128GB of RAM is probably the exception and we should not hardcode this into the repo. Also most of those values vary by the amount of RAM you have so maybe a link to a guide which explains this in a good manner would be helpfull.

[JoshDi]

128GB of RAM is probably the exception and we should not hardcode this into the repo. Also most of those values vary by the amount of RAM you have so maybe a link to a guide which explains this in a good manner would be helpfull.

Yes, I agree that this shouldnt be added directly to the repo. Maybe @yvanzo can add this to the memory override settings in the documentation.

https://www.postgresql.org/docs/12/runtime-config-resource.html

work_mem
Sets the maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files. If this value is specified without units, it is taken as kilobytes. The default value is four megabytes (4MB). Note that for a complex query, several sort or hash operations might be running in parallel; each operation will be allowed to use as much memory as this value specifies before it starts to write data into temporary files. Also, several running sessions could be doing such operations concurrently. Therefore, the total memory used could be many times the value of work_mem; it is necessary to keep this fact in mind when choosing the value. Sort operations are used for ORDER BY, DISTINCT, and merge joins. Hash tables are used in hash joins, hash-based aggregation, and hash-based processing of IN subqueries

maintenance_work_mem
Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. If this value is specified without units, it is taken as kilobytes. It defaults to 64 megabytes (64MB). Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than work_mem. Larger settings might improve performance for vacuuming and for restoring database dumps

effective_cache_size
https://www.postgresql.org/docs/12/runtime-config-query.html

Sets the planner's assumption about the effective size of the disk cache that is available to a single query. This is factored into estimates of the cost of using an index; a higher value makes it more likely index scans will be used, a lower value makes it more likely sequential scans will be used. When setting this parameter you should consider both PostgreSQL's shared buffers and the portion of the kernel's disk cache that will be used for PostgreSQL data files, though some data might exist in both places. Also, take into account the expected number of concurrent queries on different tables, since they will have to share the available space. This parameter has no effect on the size of shared memory allocated by PostgreSQL, nor does it reserve kernel disk cache; it is used only for estimation purposes. The system also does not assume data remains in the disk cache between queries. If this value is specified without units, it is taken as blocks, that is BLCKSZ bytes, typically 8kB. The default is 4 gigabytes (4GB). (If BLCKSZ is not 8kB, the default value scales proportionally to it.)

[yvanzo]

work_mem

We set it to 128MB for musicbrainz.org, we should probably set it in MusicBrainz Docker too.

maintenance_work_mem
effective_cache_size

We did not changed these for musicbrainz.org.

[JoshDi]

work_mem

We set it to 128MB for musicbrainz.org, we should probably set it in MusicBrainz Docker too.

maintenance_work_mem
effective_cache_size

We did not changed these for musicbrainz.org.

Good to know. Thank you