Hi,
Does anybody know how we can use pgPool and pgBoucner for helping load balancing in Odoo
Odoo is the world's easiest all-in-one management software.
It includes hundreds of business apps:
- CRM
- e-Commerce
- Accounting
- Inventory
- PoS
- Project
- MRP
This question has been flagged
4
Replies
18868
Views
Pg-bouncer or Pg-Pool-II does not work for Odoo because queries are in transactions.
You can try the Heimdall Proxy (https://www.heimdalldata.com/odoo). The Heimdall Proxy was specifically designed for Odoo to handle transactions and data consistency. It can support connection pooling, query caching, automated Postgres failover, and Read/Write split with Strong Consistency. Query caching will give you the biggest performance/scale boost. We have seen cache hit rates up to 90% on Odoo.
Any statement without data is just an opinion. PgBouncer is just working fine with odoo, it saved us many times,and it's free open source.
Zahid, here is the data:
PgBouncer is limited in the Odoo functionality for two reasons: 1) Odoo already supports connection pooling internally, so the base pooling of PgBouncer doesn't provide significant benefits, and 2) Odoo uses transactions for all units of work, preventing statement pooling from operating.
It is the inability to operate pooling at the statement level that we say it is "broken", as this is what many customers are looking for--the ability to dramatically reduce the number of connections coming from their application to the database.
For Heimdall Proxy, it is common to see a 10:1 reduction or greater for connections to the database. Additionally, we uniquely support query caching and read/write split to scale Postgres.--This is way beyond what PgBouncer provides, in particular in large-scale deployments 48+ Postgres CPU cores. Heimdall Data offers a free 30-day download trial.
http://girders.org/blog/2012/09/29/scaling-postgresql-with-pgpool-and-pgbouncer/
Hi vasanth thanks for your reply. Did you use this process for load balancing in odoo db?
no.I am did not use this process yet.i heared about it
Hey! I tried connecting odoo with PostgreSQL via pgbouncer. I'll just give you a brief connections configurations! Just try this!
Say suppose I have two servers:
1.192.168.1.1 (Which has Odoo & Pgbouncer running on it)
2. 192.168.2.12 (Which has Postgresql running on it)
Postgresql Config on 192.168.2.12
pg_hba.conf: add these lines to your pg_hba.conf file
# IPv4 local connections:
host all all 192.168.1.1/32 trust
posgresql.conf: make PostgreSQL listen to all the IPs
# Connection Settings -
listen_addresses = '*'
Creating User Database Password on PostgreSQL
$ sudo -i -u postgres
$ psql
$ CREATE USER user_pg WITH PASSWORD '1234';
$ CREATE DATABASE db_pg WITH OWNER user_pg;
$ ALTER ROLE user_pg WITH CREATEDB CREATEROLE;
Pgbouncer Config on 192.168.1.1
/etc/pgbouncer/pgbouncer.ini: Edit the following in your pgbouncer.ini
[DATABASES]
*= host=192.168.2.12 port=5432 db_name=db_pg
listening address= *
listening port= 6432
auth_type=trust
/etc/pgbouncer/userlist.txt: Add the following in your userlist.txt
"user_pg" "1234"
Running Odoo:
$ ./odoo-bin --database=db_pg --db_host=127.0.0.1 --db_port=6432 --db_user=user_pg --db_password=1234
Hi there,
I am getting the following error
psycopg2.OperationalError: could not connect to server: Connection refused
Is the server running on host "127.0.0.1" and accepting
TCP/IP connections on port 6432?
I cannot find how to solve?
If someone can help
Thanks
add this line as well to the pg_hba.conf file:
host all all 127.0.0.1/32 trust
I have the following in pg_hba.conf
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
Firstly, did you follow the sample configuration which was mentioned above. I think you are making some mistakes because the ones that I wrote there are best of my knowledge and working solution. Try that again and let me know what difficulties are you facing.
Yes I followed the sample configuration
The difference is that everything is on the same server for me.
I have to following configuration:
pg_hba.conf:
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
posgresql.conf
listen_addresses = '*'
/etc/pgbouncer/pgbouncer.ini
[databases]
*= host=127.0.0.1 port=5432 db_name=db_pg
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
/etc/pgbouncer/userlist.txt
"user_pg" "1234"
In my odoo conf file I have the following:
db_host = 127.0.0.1
db_port = 6432
db_user = user_pg
db_password = 1234
When I start the odoo server I have the following error message
odoo.sql_db: Connection to the database failed
Traceback (most recent call last):
File "/usr/lib/python2.7/dist-packages/werkzeug/serving.py", line 177, in run_wsgi
execute(self.server.app)
File "/usr/lib/python2.7/dist-packages/werkzeug/serving.py", line 165, in execute
application_iter = app(environ, start_response)
File "/opt/aslane/aslane-server/odoo/service/wsgi_server.py", line 186, in application
return application_unproxied(environ, start_response)
File "/opt/aslane/aslane-server/odoo/service/wsgi_server.py", line 172, in application_unproxied
result = handler(environ, start_response)
File "/opt/aslane/aslane-server/odoo/http.py", line 1308, in __call__
return self.dispatch(environ, start_response)
File "/opt/aslane/aslane-server/odoo/http.py", line 1282, in __call__
return self.app(environ, start_wrapped)
File "/usr/lib/python2.7/dist-packages/werkzeug/wsgi.py", line 588, in __call__
return self.app(environ, start_response)
File "/opt/aslane/aslane-server/odoo/http.py", line 1446, in dispatch
self.setup_db(httprequest)
File "/opt/aslane/aslane-server/odoo/http.py", line 1368, in setup_db
httprequest.session.db = db_monodb(httprequest)
File "/opt/aslane/aslane-server/odoo/http.py", line 1530, in db_monodb
dbs = db_list(True, httprequest)
File "/opt/aslane/aslane-server/odoo/http.py", line 1498, in db_list
dbs = odoo.service.db.list_dbs(force)
File "/opt/aslane/aslane-server/odoo/service/db.py", line 333, in list_dbs
with closing(db.cursor()) as cr:
File "/opt/aslane/aslane-server/odoo/sql_db.py", line 635, in cursor
return Cursor(self.__pool, self.dbname, self.dsn, serialized=serialized)
File "/opt/aslane/aslane-server/odoo/sql_db.py", line 177, in __init__
self._cnx = pool.borrow(dsn)
File "/opt/aslane/aslane-server/odoo/sql_db.py", line 518, in _locked
return fun(self, *args, **kwargs)
File "/opt/aslane/aslane-server/odoo/sql_db.py", line 586, in borrow
**connection_info)
File "/usr/lib/python2.7/dist-packages/psycopg2/__init__.py", line 164, in connect
conn = _connect(dsn, connection_factory=connection_factory, async=async)
OperationalError: could not connect to server: Connection refused
Is the server running on host "127.0.0.1" and accepting
TCP/IP connections on port 6432?
Try passing arguments instead from config file. like this
$ ./odoo-bin --database=db_pg --db_host=127.0.0.1 --db_port=6432 --db_user=user_pg --db_password=1234
any tutorial on implementing pgBouncer with odoo?
Enjoying the discussion? Don't just read, join in!
Create an account today to enjoy exclusive features and engage with our awesome community!
Sign up| Related Posts | Replies | Views | Activity | |
|---|---|---|---|---|
|
1
Feb 25
|
532 | ||
|
2
Feb 25
|
1001 | ||
|
2
Jan 25
|
1170 | ||
|
1
Dec 24
|
2109 | ||
|
1
Oct 24
|
871 |
