How to resolve memory issues in Odoo & PostgreSQL?

tenthplanet blog odoo How to resolve memory issues in Odoo PostgreSQL

The most important steps that you must take in order to resolve memory issues in Odoo are:
1) Try to remove unwanted un-used addons from “addons” folder.

2) Tune postgres for better performance.

Apart from this, following are some more steps that you should consider:

listen_addresses

PostgreSQL only responds to connections from the local host, by default. You need to change listen_addresses from its default, if you want your server to be accessible from other systems via standard TCP/IP networking. The usual approach is as follows:listen_addresses = ‘*’And then control who can and cannot connect via the pg_hba.conf file.

max_connections

max_connections sets exactly the maximum number of client connections that are allowed. If you want to have thousands instead, you should consider using connection pooling software to reduce the connection overhead.

shared_buffers

The shared_buffers configuration parameter determines how much memory is dedicated to PostgreSQL to use for caching data. One reason the defaults are low is because on some platforms which have large values requires invasive action like recompiling the kernel.Be aware that if your system or PostgreSQL build is 32-bit, it might not be practical to set shared_buffers above 2 ~ 2.5GB.Also note that on Windows, large values for shared_buffers aren’t as effective, and you may find better results keeping it relatively low and instead using the OS cache more. On Windows the useful range is 64MB to 512MB.

effective_cache_size

effective_cache_size should be set to an estimate of how much memory is available for disk caching by the OS and within the database itself, after taking into account what’s used by the OS itself and other applications. This is a guideline for how much memory you expect to be available in the operating system and PostgreSQL buffer caches, not an allocation! This value is used only by the PostgreSQL query planner to figure out whether plans it’s considering would be expected to fit in RAM or not. If it’s set too low, indexes may not be used for executing queries the way you’d expect.checkpoint_segments checkpoint_completion_target

PostgreSQL writes new transactions to the database in files called WAL segments that are 16MB in size. Every time checkpoint_segments worth of these files have been written, by default, a checkpoint occurs.

work_mem

If you do a lot of complex sorts, and have a lot of memory, then increasing the work_mem parameter allows PostgreSQL to do larger in-memory sorts which, unsurprisingly, will be faster than disk-based equivalents.

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. It defaults to 16 megabytes (16MB). 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.