psycopg2.pool.PoolError: connection pool exhausted
autra opened this issue · 5 comments
autra commented
autra commented
original comment:
Hello,
I cannot display correctly "lyon" example data with lopocs due to this error:
psycopg2.pool.PoolError: connection pool exhausted
As i'm not a programmer and I had to do some changes to make lopocs works on Debian (see at the bottom), I wonder if it can be due to my modifications or if there are some settings to change in the pool side
Following, part of the log:
[2020-03-11 20:03:48,493] ERROR in app: Exception on /3dtiles/public.lyon.points/read.pnts [GET]
Traceback (most recent call last):
File "/opt/lopocs/venv/lib/python3.7/site-packages/flask/app.py", line 1949, in full_dispatch_request
rv = self.dispatch_request()
File "/opt/lopocs/venv/lib/python3.7/site-packages/flask/app.py", line 1935, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "/opt/lopocs/venv/lib/python3.7/site-packages/flask_restplus/api.py", line 313, in wrapper
resp = resource(*args, **kwargs)
File "/opt/lopocs/venv/lib/python3.7/site-packages/flask/views.py", line 89, in view
return self.dispatch_request(*args, **kwargs)
File "/opt/lopocs/venv/lib/python3.7/site-packages/flask_restplus/resource.py", line 44, in dispatch_request
resp = meth(*args, **kwargs)
File "/opt/lopocs/lopocs/app.py", line 159, in get
args.get('lod')
File "/opt/lopocs/lopocs/threedtiles.py", line 58, in ThreeDTilesRead
[tile, npoints] = get_points(session, box, lod, offsets, pcid, scales, schema)
File "/opt/lopocs/lopocs/threedtiles.py", line 125, in get_points
sql = sql_query(session, box, pcid, lod)
File "/opt/lopocs/lopocs/threedtiles.py", line 186, in sql_query
patch_size = session.patch_size
File "/opt/lopocs/lopocs/database.py", line 234, in patch_size
return self.query(sql)[0][0]
File "/opt/lopocs/lopocs/database.py", line 466, in query
with cls._execute(query, parameters) as cursor:
File "/usr/lib/python3.7/contextlib.py", line 112, in __enter__
return next(self.gen)
File "/opt/lopocs/lopocs/database.py", line 450, in _execute
with cls._conn() as conn:
File "/usr/lib/python3.7/contextlib.py", line 112, in __enter__
return next(self.gen)
File "/opt/lopocs/lopocs/database.py", line 442, in _conn
conn = cls.pool.getconn()
File "/opt/lopocs/venv/lib/python3.7/site-packages/psycopg2/pool.py", line 168, in getconn
return self._getconn(key)
File "/opt/lopocs/venv/lib/python3.7/site-packages/psycopg2/pool.py", line 91, in _getconn
raise PoolError("connection pool exhausted")
psycopg2.pool.PoolError: connection pool exhausted
192.168.0.102 - - [11/Mar/2020 20:03:48] "GET /3dtiles/public.lyon.points/read.pnts?v=0.0&lod=2&bounds=[4440967.31950011,375461.372910486,4547185.11007563,4441167.6970001105,375719.772910486,4547372.6850756295] HTTP/1.1" 500 -
192.168.0.102 - - [11/Mar/2020 20:03:48] "GET /3dtiles/public.lyon.points/read.pnts?v=0.0&lod=2&bounds=[4440967.31950011,375719.772910486,4547185.11007563,4441167.6970001105,375978.172910486,4547372.6850756295] HTTP/1.1" 200 -
192.168.0.102 - - [11/Mar/2020 20:03:49] "GET /3dtiles/public.lyon.points/read.pnts?v=0.0&lod=2&bounds=[4440967.31950011,375202.97291048605,4547185.11007563,4441167.6970001105,375461.372910486,4547372.6850756295] HTTP/1.1" 200 -
192.168.0.102 - - [11/Mar/2020 20:03:49] "GET /3dtiles/public.lyon.points/read.pnts?v=0.0&lod=2&bounds=[4441368.07450011,374944.572910486,4546809.96007563,4441568.4520001095,375202.97291048605,4546997.53507563] HTTP/1.1" 200 -
192.168.0.102 - - [11/Mar/2020 20:03:49] "GET /3dtiles/public.lyon.points/read.pnts?v=0.0&lod=2&bounds=[4440967.31950011,374944.572910486,4547185.11007563,4441167.6970001105,375202.97291048605,4547372.6850756295] HTTP/1.1" 200 -
and in the attached image what I see
My configuration:
VMWare Player Virtal machine, 40 giga hd, 4096MB of memory for the VM (32 Giga for the host)
Debian 10 stable
PostgreSQL 11 (repository)
PDAL (from GIT to have lazperf and laszip)
I did some changes in the following files:
setup.py
-- 'pyproj==1.9.5.1',
'pyproj==1.9.6',
-- downgrade
'werkzeug==0.16.1'
lopocs/cli.py
-- offset_x = summary['bounds']['X']['min'] + (summary['bounds']['X']['max'] - summary['bounds']['X']['min']) / 2
-- offset_y = summary['bounds']['Y']['min'] + (summary['bounds']['Y']['max'] - summary['bounds']['Y']['min']) / 2
-- offset_z = summary['bounds']['Z']['min'] + (summary['bounds']['Z']['max'] - summary['bounds']['Z']['min']) / 2
offset_x = summary['bounds']['minx'] + (summary['bounds']['maxx'] - summary['bounds']['minx']) / 2
offset_y = summary['bounds']['miny'] + (summary['bounds']['maxy'] - summary['bounds']['miny']) / 2
offset_z = summary['bounds']['minz'] + (summary['bounds']['maxz'] - summary['bounds']['minz']) / 2
-- xmin, ymin, zmin = transform(pini, pout, summary['bounds']['X']['min'], summary['bounds']['Y']['min'], summary['bounds']['Z']['min'])
-- xmax, ymax, zmax = transform(pini, pout, summary['bounds']['X']['max'], summary['bounds']['Y']['max'], summary['bounds']['Z']['max'])
xmin, ymin, zmin = transform(pini, pout, summary['bounds']['minx'], summary['bounds']['miny'], summary['bounds']['minz'])
xmax, ymax, zmax = transform(pini, pout, summary['bounds']['maxx'], summary['bounds']['maxy'], summary['bounds']['maxz'])
lopocs/database.py
server_version = full_server_version.split()[0] # Keep only "X.X.X"
-- server_version = server_version_full.split()[0] # Keep only "X.X.X"
(venv) root@sdi:/opt/lopocs# lopocs demo --sample lyon --work-dir demos --server-url http://192.168.0.120:5000 --cesium
ln -s /opt/lopocs/demos/ /var/www/html/lopocs
(venv) root@sdi:/opt/lopocs# lopocs serve --host 192.168.0.120
pizzuga commented
Partially solved modifying row 193 in lopocs/database.py, and putting a different maxconn number in ThreadedConnectionPool
# cls.pool = ThreadedConnectionPool(1, cpu_count(), query_con)
cls.pool = ThreadedConnectionPool(1, 50, query_con)
But I really don't know what kind of consequences it could arise
autra commented
@pizzuga can you copy the content of your conf/lopocs.yml or whatever file your LOPOCS_SETTINGS env variable points to? It will help me diagnose the root cause. You shouldn't have to modify the code to make it work.
pizzuga commented
Here it is
flask:
DEBUG: False
PG_HOST: localhost
PG_NAME: lopocs
PG_PORT: 5432
PG_USER: gis
PG_PASSWORD: sig
CACHE_DIR: /home/user/.cache/lopocs
../conf/lopocs.yml (END)
Sorry see now, maybe I shuold have change CACHE_DIR: /home/user/.cache/lopocs ?
delhomer commented