This question has been flagged
2 Replies
5785 Views
odoo10
Avatar
Samo Arko

I'm running odoo 10 on a ubuntu 10.04 server with postgresql 9.5 database. Because multiple odoo instances and configured workers I have 300+ idle postgres tasks. This eats up nearly all my RAM. Now I'm trying to install pgbouncer. When I lunch the pgbouncer service the first time it works, but stop/start or just restart fails and so I can't run the pgbouncer.

I've installed it from git and from apt-get. Both times the same. Have googled for two days to try to fix the issue but nothing works.

Can someone explain me the order what you need to do when you install pgbouncer that it works. Do I need to install it before the db?! do I need to stop the odoo, postgres services when I install it?

The way I tried:

sudo apt-get update
sudo apt-get install pgbouncer

# write the users
sodu -u postgres psql
COPY (
SELECT '"' || rolname || '" "' ||
CASE WHEN rolpassword IS null THEN '' ELSE rolpassword END || '"'
FROM pg_authid
)
TO '/etc/pgbouncer/userlist.txt';

# sudo nano pgbouncer.ini
[databases]
;* = port=5432
postgres = host=localhost dbname=postgres
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
admin_users = postgres
pool_mode = session
server_reset_query = DISCARD ALL
default_pool_size=56
max_client_conn=1000


# and then change the db_port to 6432 in odoo.conf

when I started the first time it worked... all the other times I get ...fail!

 Hope someone can help! 

Avatar
Discard
Avatar
Stashell Esteban Charpentier Santamaria
Best Answer

Pgbouncer is quite tricky you have to keep checking the errors in the logs and clear them one by one.

test your setup first open your terminal and start with reducing your pgbouncer to the bare minimal:

[databases] 
* = port=5432 postgres = host=localhost dbname=postgres 

[pgbouncer] 
listen_port = 6432 
listen_addr = 127.0.0.1 
auth_file = /etc/pgbouncer/userlist.txt

save and try to activate the connection

$ /usr/sbin/pgbouncer  pgbouncer.ini

most probably you will have some errors post them will see what it is

if no errors show on the screen and pgbouncer is started do a connection test from a second terminal, following your settings type the following:

psql -U postgres -p 6432 -h localhost dbname=postgres

it should show you the connection problem and what needs to be fix, it could be 1 of 3 things or the 3 all together, the userlist, the IPs not properly configure or the authorization.

Once you have clear all the errors from the files and postgres configuration you can go ahead and start adding settings to pgbouncer, keep in mind this apps have a lot of interactions going on but the base needs to be clear to work out.

Avatar
Discard
Samo Arko
Author

thanks, but I gave up on pgbouncer.

Roland Lee

Try the Heimdall Proxy. Most Odoo users need statement-level pooling. PgBouncer cannot support this. It's also open source; you get what you pay for. The Heimdall Proxy also supports query caching and read/write split for ACID compliance.

Víctor Díaz

Heimdall proxy is not open source, you can download it and try it for 30 days but then it will disconnect you from database and you have to pay for the subscription. I tried it and it works but after 30 days you will be asked to buy a license

Roland Lee

@Victor, that is correct. The Heimdall Database Proxy is a commercial product. We get what you pay for :)

Avatar
Roland Lee
Best Answer

Have you tried out the Heimdall Proxy? It supports 1) Query caching, ACID-Compliant Read/Write splitting, and 3) Connection Pooling. www.heimdalldata.com/odoo

To be clear, pg-bouncer or pg-Pool-II will not work with Odoo as queries are in transactions and query splitting requires strong consistency. The Heimdall Proxy was specifically designed for the Odoo application.

Avatar
Discard
Muhammad Awais

we can specify the mode=transaction in pgbouncer

Roland Lee

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. With the Heimdall Proxy, it is common to see 100-1 reduction or greater on the number of connections to the database. Added to this, our proxy can to cache and read/write split, beyond what PgBouncer provides. We typically target larger-scale deployments with Postgres CPU cores of 48+.