Odoo: psycopg2.OperationalError: FATAL: too many connections for role "p_my_oerp_master_11234451"
Since Today (the 30 of September), using Odoo v13 on Odoo.sh, after logging in my odoo account as "ADMIN" or another odoo user, i got this error every minute and all "Odoo internal Users" get disconnected from their session every couple of minutes:
psycopg2.OperationalError: FATAL: too many connections for role "p_my_oerp_master_1123445"
TRACEBACK:
2022-09-30 17:01:32,442 30903 INFO ? odoo.addons.base.models.ir_actions_report: Will use the Wkhtmltopdf binary at /usr/local/bin/wkhtmltopdf
2022-09-30 17:01:32,657 30903 INFO ? odoo.service.server: HTTP service (werkzeug) running through socket activation
2022-09-30 17:01:32,666 30903 INFO ? odoo.sql_db: Connection to the database failed
2022-09-30 17:01:32,667 30903 CRITICAL ? odoo.service.server: Failed to initialize database `my-oerp-master-1123445`.
Traceback (most recent call last):
File "/home/odoo/src/odoo/odoo/service/server.py", line 1194, in preload_registries
registry = Registry.new(dbname, update_module=update_module)
File "/home/odoo/src/odoo/odoo/modules/registry.py", line 75, in new
registry.init(db_name)
File "/home/odoo/src/odoo/odoo/modules/registry.py", line 144, in init
with closing(self.cursor()) as cr:
File "/home/odoo/src/odoo/odoo/modules/registry.py", line 563, in cursor
return self._db.cursor()
File "/home/odoo/src/odoo/odoo/sql_db.py", line 669, in cursor
return Cursor(self.__pool, self.dbname, self.dsn, serialized=serialized)
File "/home/odoo/src/odoo/odoo/sql_db.py", line 196, in __init__
self._cnx = pool.borrow(dsn)
File "/home/odoo/src/odoo/odoo/sql_db.py", line 552, in _locked
return fun(self, *args, **kwargs)
File "/home/odoo/src/odoo/odoo/sql_db.py", line 620, in borrow
**connection_info)
File "/usr/local/lib/python3.6/dist-packages/psycopg2/__init__.py", line 130, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL: too many connections for role "p_my_oerp_master_1123445"
2022-09-30 17:01:32,727 30903 INFO ? odoo.http: HTTP Configuring static files
2022-09-30 17:01:32,736 30903 INFO my-oerp-master-1123445 odoo.sql_db: Connection to the database failed
2022-09-30 17:01:32,737 30903 INFO my-oerp-master-1123445 odoo.http: Generating nondb routing
2022-09-30 17:01:32,755 30903 INFO None odoo.sql_db: Connection to the database failed
2022-09-30 17:01:32,755 30903 WARNING my-oerp-master-1123445 odoo.service.model: method ir.cron.acquire_job failed during rpc call: FATAL: too many connections for role "p_my_oerp_master_1123445"
2022-09-30 17:01:32,755 30903 ERROR my-oerp-master-1123445 odoo.http: FATAL: too many connections for role "p_my_oerp_master_1123445"
Traceback (most recent call last):
File "/home/odoo/src/odoo/odoo/modules/registry.py", line 61, in __new__
return cls.registries[db_name]
File "/home/odoo/src/odoo/odoo/tools/func.py", line 69, in wrapper
return func(self, *args, **kwargs)
File "/home/odoo/src/odoo/odoo/tools/lru.py", line 44, in __getitem__
a = self.d[obj].me
KeyError: 'my-oerp-master-1123445'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/home/odoo/src/odoo/odoo/http.py", line 1479, in dispatch
odoo.registry(db).check_signaling()
File "/home/odoo/src/odoo/odoo/__init__.py", line 104, in registry
return modules.registry.Registry(database_name)
File "/home/odoo/src/odoo/odoo/modules/registry.py", line 63, in __new__
return cls.new(db_name)
File "/home/odoo/src/odoo/odoo/modules/registry.py", line 75, in new
registry.init(db_name)
File "/home/odoo/src/odoo/odoo/modules/registry.py", line 144, in init
with closing(self.cursor()) as cr:
File "/home/odoo/src/odoo/odoo/modules/registry.py", line 563, in cursor
return self._db.cursor()
File "/home/odoo/src/odoo/odoo/sql_db.py", line 669, in cursor
return Cursor(self.__pool, self.dbname, self.dsn, serialized=serialized)
File "/home/odoo/src/odoo/odoo/sql_db.py", line 196, in __init__
self._cnx = pool.borrow(dsn)
File "/home/odoo/src/odoo/odoo/sql_db.py", line 552, in _locked
return fun(self, *args, **kwargs)
File "/home/odoo/src/odoo/odoo/sql_db.py", line 620, in borrow
**connection_info)
File "/usr/local/lib/python3.6/dist-packages/psycopg2/__init__.py", line 130, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL: too many connections for role "p_my_oerp_master_1123445"
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/home/odoo/src/odoo/odoo/modules/registry.py", line 61, in __new__
return cls.registries[db_name]
File "/home/odoo/src/odoo/odoo/tools/func.py", line 69, in wrapper
return func(self, *args, **kwargs)
File "/home/odoo/src/odoo/odoo/tools/lru.py", line 44, in __getitem__
a = self.d[obj].me
KeyError: 'my-oerp-master-1123445'
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "/home/odoo/src/odoo/odoo/http.py", line 139, in dispatch_rpc
result = dispatch(method, params)
File "/usr/local/bin/odoo-rpc", line 26, in dispatch
return odoo_dispatch(method, [db, odoo.SUPERUSER_ID, None] + list(params))
File "/home/odoo/src/odoo/odoo/service/model.py", line 38, in dispatch
registry = odoo.registry(db).check_signaling()
File "/home/odoo/src/odoo/odoo/__init__.py", line 104, in registry
return modules.registry.Registry(database_name)
File "/home/odoo/src/odoo/odoo/modules/registry.py", line 63, in __new__
return cls.new(db_name)
File "/home/odoo/src/odoo/odoo/modules/registry.py", line 75, in new
registry.init(db_name)
File "/home/odoo/src/odoo/odoo/modules/registry.py", line 144, in init
with closing(self.cursor()) as cr:
File "/home/odoo/src/odoo/odoo/modules/registry.py", line 563, in cursor
return self._db.cursor()
File "/home/odoo/src/odoo/odoo/sql_db.py", line 669, in cursor
return Cursor(self.__pool, self.dbname, self.dsn, serialized=serialized)
File "/home/odoo/src/odoo/odoo/sql_db.py", line 196, in __init__
self._cnx = pool.borrow(dsn)
File "/home/odoo/src/odoo/odoo/sql_db.py", line 552, in _locked
return fun(self, *args, **kwargs)
File "/home/odoo/src/odoo/odoo/sql_db.py", line 620, in borrow
**connection_info)
File "/usr/local/lib/python3.6/dist-packages/psycopg2/__init__.py", line 130, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL: too many connections for role "p_my_oerp_master_1123445"
My problem seems to be caused by an hanging process. In Odoo.sh > Monitoring tab ... The "Performance analysis" tool enables to start a profiling session of your Odoo workers. For each session, an interactive flamegraph is generated for you to visualize what your Odoo workers are spending time on. One of these profiling session provide this ouput Flamegraph:
And the output of ps -ax Terminal Command:
my_oerp_master_11234451 [production/v13.0]:~$ ps -ax
PID TTY STAT TIME COMMAND
1 ? Ss 2:29 ODOO.SH: [my_oerp_master_11234451 / production / 13.0]
106 ? SNsl 0:56 python3 /usr/local/bin/odoo-rpc --database=my_oerp_master_11234451
4807 ? Ssl 8:44 python3 /home/odoo/src/odoo/odoo-bin --database=my_oerp_master_11234451 --logfile=/home/odoo/logs/odoo.log
5899 ? RNs 0:01 /opt/odoo.sh/jupyterlab/bin/python3 /opt/odoo.sh/jupyterlab/bin/odoo-webshell --ip=0.0.0.0 --port=8889 -
5903 pts/0 SNs 0:00 /bin/bash -l
8730 ? SNsl 0:53 python3 /usr/local/bin/odoo-rpc --database=my_oerp_master_11234451
10738 ? SNsl 0:56 python3 /usr/local/bin/odoo-rpc --database=my_oerp_master_11234451
11263 ? SNsl 0:56 python3 /usr/local/bin/odoo-rpc --database=my_oerp_master_11234451
11334 pts/3 SNs+ 0:00 /bin/bash 12134 ? SNsl 0:56 python3 /usr/local/bin/odoo-rpc --database=my_oerp_master_11234451
12864 ? SNsl 0:56 python3 /usr/local/bin/odoo-rpc --database=my_oerp_master_11234451
13384 ? SNsl 0:49 python3 /usr/local/bin/odoo-rpc --database=my_oerp_master_11234451
13584 pts/0 RN+ 0:00 ps -ax
16281 ? SNsl 0:48 python3 /usr/local/bin/odoo-rpc --database=my_oerp_master_11234451
17130 ? SNsl 0:51 python3 /usr/local/bin/odoo-rpc --database=my_oerp_master_11234451
17882 ? SNsl 0:51 python3 /usr/local/bin/odoo-rpc --database=my_oerp_master_11234451
20516 ? SNsl 0:09 /opt/odoo.sh/jupyterlab/bin/python3 /opt/odoo.sh/jupyterlab/bin/jupyter-lab --debug --ip=0.0.0.0 --port=8888
29069 pts/1 SNs 0:00 /bin/bash
29079 pts/1 SN+ 0:02 less +F /home/odoo/logs/odoo.log
29094 pts/2 SNs+ 0:00 /bin/bash
30181 ? SNsl 0:56 python3 /usr/local/bin/odoo-rpc --database=my_oerp_master_11234451
my_oerp_master_11234451 [production/v13.0]:~$
After more investigation, it seems that crons ("Server Actions" in Odoo) having a recurrence set to None in Odoo, but running in fact every couple of secondes, were the original cause for these "Too many connections..." - Error and the Session disconnection every couple of secondes. On the server side, these crons correspond to processes that use odoo-rpc library (external API).
This hypothesis is confirmed by comparing the running processes during the issue (which lasts 5 days long) and after the cron processes get killed (somehow):
DURING ISSUE: running processes (px ax)
my_oerp_master_11234451 [production/v13.0]:~$ ps -ax
PID TTY STAT TIME COMMAND
1 ? Ss 2:29 ODOO.SH: [my_oerp_master_11234451 / production / 13.0]
106 ? SNsl 0:56 python3 /usr/local/bin/odoo-rpc --database=my_oerp_master_11234451
4807 ? Ssl 8:44 python3 /home/odoo/src/odoo/odoo-bin --database=my_oerp_master_11234451 --logfile=/home/odoo/logs/odoo.log
5899 ? RNs 0:01 /opt/odoo.sh/jupyterlab/bin/python3 /opt/odoo.sh/jupyterlab/bin/odoo-webshell --ip=0.0.0.0 --port=8889 -
5903 pts/0 SNs 0:00 /bin/bash -l
8730 ? SNsl 0:53 python3 /usr/local/bin/odoo-rpc --db_maxconn=2 --database=my_oerp_master_11234451
10738 ? SNsl 0:56 python3 /usr/local/bin/odoo-rpc --db_maxconn=2 --database=my_oerp_master_11234451
11263 ? SNsl 0:56 python3 /usr/local/bin/odoo-rpc --db_maxconn=2 --database=my_oerp_master_11234451
11334 pts/3 SNs+ 0:00 /bin/bash 12134 ? SNsl 0:56 python3 /usr/local/bin/odoo-rpc --database=my_oerp_master_11234451
12864 ? SNsl 0:56 python3 /usr/local/bin/odoo-rpc --db_maxconn=2 --database=my_oerp_master_11234451
13384 ? SNsl 0:49 python3 /usr/local/bin/odoo-rpc --db_maxconn=2 --database=my_oerp_master_11234451
13584 pts/0 RN+ 0:00 ps -ax
16281 ? SNsl 0:48 python3 /usr/local/bin/odoo-rpc --db_maxconn=2 --database=my_oerp_master_11234451
17130 ? SNsl 0:51 python3 /usr/local/bin/odoo-rpc --db_maxconn=2 --database=my_oerp_master_11234451
17882 ? SNsl 0:51 python3 /usr/local/bin/odoo-rpc --db_maxconn=2 --database=my_oerp_master_11234451
20516 ? SNsl 0:09 /opt/odoo.sh/jupyterlab/bin/python3 /opt/odoo.sh/jupyterlab/bin/jupyter-lab --debug --ip=0.0.0.0 --port=8888
29069 pts/1 SNs 0:00 /bin/bash
29079 pts/1 SN+ 0:02 less +F /home/odoo/logs/odoo.log
29094 pts/2 SNs+ 0:00 /bin/bash
30181 ? SNsl 0:56 python3 /usr/local/bin/odoo-rpc --db_maxconn=2 --database=my_oerp_master_11234451
my_oerp_master_11234451 [production/v13.0]:~$
AFTER ISSUE: running processes (px ax)
my_oerp_master_11234451 [production/v13.0]:~$ ps -ax
PID TTY STAT TIME COMMAND
1 ? Ss 2:29 ODOO.SH: [my_oerp_master_11234451 / production / 13.0]
8855 ? Ssl 8:44 python3 /home/odoo/src/odoo/odoo-bin --database=my_oerp_master_11234451 --logfile=/home/odoo/logs/odoo.log
10597 pts/0 SNs 0:00 /bin/bash -l
10601 ? SNsl 0:09 /opt/odoo.sh/jupyterlab/bin/python3 /opt/odoo.sh/jupyterlab/bin/jupyter-lab --debug --ip=0.0.0.0 --port=8888
11023 pts/1 RN+ 0:00 ps ax
23954 pts/2 SNs+ 0:00 /bin/bash
This hypothesis is confirmed too by the official Odoo documentation concerning the psycopg2-Error related to "the maximum connections parameter": https://odoo-development.readthedocs.io/en/latest/admin/db_maxconn.html
db_maxconn – specify the the maximum number of physical connections to posgresql per odoo process, but for all databases
To resolve it you need configure following parameters:
In odoo
- db_maxconn
- workers
- max_cron_threads
In posgresql
- max_connections
Those parameters must satisfy following condition:
(1 + workers + max_cron_threads) * db_maxconn < max_connections
For example, if you have following values:
workers = 1 (minimal value to make longpolling work)
max_cron_threads = 2 (default)
db_maxconn = 64 (default)
max_connections = 100 (default)
then (1 + 1 + 2) * 64 = 256 > 100, i.e. the condition is not satisfied and such deployment may face the error described above.
Ok, but which values are good for specific server and load conditions?
PostgreSQL’s max_connections should be set higher than db_maxconn * number_of_processes. You may need to tweak the kernel sysctl if you need max_connections higher than 1-2k.
For multi-processing mode, each HTTP worker handles a single request at a time, so theoretically db_maxconn=2 could work (some requests need 2 cursors, hence 2 db connections). However for multi-tenant this is not optimal because each request will need to reopen a new connection to a different db - setting it a bit higher is better. With lots of workers, 32 is a good trade-off, as 64 could make you reach kernel limits. Also keep in mind that the limit applies to the longpolling worker too, and you don’t want to delay chat messages too much because of a full connection pool, so don’t set it too low no matter what. Keeping the value in the 32-64 range usually seems a good choice.
For multi-thread mode, since there is only 1 process, this is the size of the global connection pool. To prevent errors, it should be set between 1x and 2x the expected number of concurrent requests at a time. Can be estimated based on the number of databases and the expected activity. Having a single process handle more than 20 request at a time on a single core (remember that multi-thread depends on the GIL) is unlikely to give good performance, so again, a setting in the 32-64 range will most likely work for a normal load.
- Accessing Python 3 type annotations for variables at runtime
- Identify and mark duplicates where any of them has a specific quality
- numpy.dtype size changed, may indicate binary incompatibility. Expected 96 from C header, got 88 from PyObject
- Can't install new packages for Python (Python 3.9.0, Windows 10)
- How can I set all form fields readonly in Odoo 16 depending on a field?
- Django - CreateView - Send a custom Error Message if model form is not valid
- How to rotate only Table, using borb (PDF library) python?
- Python unittest: how to run only part of a test file?
- add nested attributes to python
- How to Enumerate Threads using CreateToolhelp32Snapshot and Python ctypes?
- NotImplementedError YOLO ultralitycs
- Tuple matching using SQLAlchemy
- is there parallelism inside Ollama?
- Problem when adding dependencies to pyinstaller using poetry to manage dependencies
- The difference between <str:slug> and <slug:slug> in urls.py of Django application
- Visual Studio Code does not detect Virtual Environments
- Error print special character on results with Flask-SQLAlchemy
- Printing a list from a random sample using join()
- How to load a huggingface dataset from local path?
- Modify output from Python Pandas describe
- python Shift + enter not working in VScode with jupyter
- Logging Error: Failed to initialize logging system. Log messages may be missing.?
- Encountering connection problems with PostgreSQL when using psycopg2 on a port that has been forwarded
- Poetry stuck in infinite install/update
- How can one combine iterables keeping only the first element with each index?
- How to resolve a many-to-one-relation on insert in sqlalchemy 2.0?
- Widget with WA_TransparentForMouseEvents attribute in QSplitter. How to make it?
- WindowsError: [Error 126] The specified module could not be found
- How do I define a function with optional arguments?
- Add "b" prefix to python variable?