search

code-for-charlottesville / housinghub

GNU General Public License v3.0
14 stars 23 forks source link

Increase local DB connection pool size #96

Closed thinkharderdev closed 4 years ago

thinkharderdev commented 4 years ago

Fixes #93

This shouldn't be a problem in general outside of the local dev environment. In the AWS env we are actually using the RDS Data API which queries the Aurora Serverless DB through an HTTP API so connections are pooled at the AWS API level.

thinkharderdev commented 4 years ago

still able to make this crash by spamming the search endpoint:

housinghub-api_1  |
housinghub-api_1  | 172.20.0.3 - - [30/Jul/2020 14:47:30] "POST /property/search HTTP/1.1" 500 -
housinghub-api_1  | [2020-07-30 14:47:31,167] ERROR in property_handlers: Unexpected error getting property: $Traceback (most recent call last):
housinghub-api_1  |   File "/usr/housinghub/src/property_handlers.py", line 47, in get_property
housinghub-api_1  |     _property = app.services.property_service().get_property(payload)
housinghub-api_1  |   File "/usr/housinghub/src/services/property.py", line 22, in get_property
housinghub-api_1  |     if (self.db_session.query(Property).all() == None):
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3244, in all
housinghub-api_1  |     return list(self)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3403, in __iter__
housinghub-api_1  |     return self._execute_and_instances(context)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3425, in _execute_and_instances
housinghub-api_1  |     querycontext, self._connection_from_session, close_with_result=True
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3440, in _get_bind_args
housinghub-api_1  |     mapper=self._bind_mapper(), clause=querycontext.statement, **kw
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3418, in _connection_from_session
housinghub-api_1  |     conn = self.session.connection(**kw)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1133, in connection
housinghub-api_1  |     execution_options=execution_options,
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1139, in _connection_for_bind
housinghub-api_1  |     engine, execution_options
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 432, in _connection_for_bind
housinghub-api_1  |     conn = bind._contextual_connect()
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2251, in _contextual_connect
housinghub-api_1  |     self._wrap_pool_connect(self.pool.connect, None),
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2285, in _wrap_pool_connect
housinghub-api_1  |     return fn()
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 363, in connect
housinghub-api_1  |     return _ConnectionFairy._checkout(self)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 773, in _checkout
housinghub-api_1  |     fairy = _ConnectionRecord.checkout(pool)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
housinghub-api_1  |     rec = pool._do_get()
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 131, in _do_get
housinghub-api_1  |     code="3o7r",
housinghub-api_1  | sqlalchemy.exc.TimeoutError: QueuePool limit of size 20 overflow 10 reached, connection timed out, timeout 30 (Background on this error at: http://sqlalche.me/e/3o7r)
housinghub-api_1  |
housinghub-api_1  | 172.20.0.3 - - [30/Jul/2020 14:47:31] "POST /property/search HTTP/1.1" 500 -
housinghub-api_1  | [2020-07-30 14:47:31,326] ERROR in property_handlers: Unexpected error getting property: $Traceback (most recent call last):
housinghub-api_1  |   File "/usr/housinghub/src/property_handlers.py", line 47, in get_property
housinghub-api_1  |     _property = app.services.property_service().get_property(payload)
housinghub-api_1  |   File "/usr/housinghub/src/services/property.py", line 40, in get_property
housinghub-api_1  |     _properties = _properties.all()
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3244, in all
housinghub-api_1  |     return list(self)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3403, in __iter__
housinghub-api_1  |     return self._execute_and_instances(context)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3425, in _execute_and_instances
housinghub-api_1  |     querycontext, self._connection_from_session, close_with_result=True
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3440, in _get_bind_args
housinghub-api_1  |     mapper=self._bind_mapper(), clause=querycontext.statement, **kw
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3418, in _connection_from_session
housinghub-api_1  |     conn = self.session.connection(**kw)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1133, in connection
housinghub-api_1  |     execution_options=execution_options,
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1139, in _connection_for_bind
housinghub-api_1  |     engine, execution_options
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 432, in _connection_for_bind
housinghub-api_1  |     conn = bind._contextual_connect()
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2251, in _contextual_connect
housinghub-api_1  |     self._wrap_pool_connect(self.pool.connect, None),
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2285, in _wrap_pool_connect
housinghub-api_1  |     return fn()
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 363, in connect
housinghub-api_1  |     return _ConnectionFairy._checkout(self)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 773, in _checkout
housinghub-api_1  |     fairy = _ConnectionRecord.checkout(pool)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
housinghub-api_1  |     rec = pool._do_get()
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 131, in _do_get
housinghub-api_1  |     code="3o7r",
housinghub-api_1  | sqlalchemy.exc.TimeoutError: QueuePool limit of size 20 overflow 10 reached, connection timed out, timeout 30 (Background on this error at: http://sqlalche.me/e/3o7r)
housinghub-api_1  |
housinghub-api_1  | 172.20.0.3 - - [30/Jul/2020 14:47:31] "POST /property/search HTTP/1.1" 500 -
housinghub-api_1  | [2020-07-30 14:47:31,338] ERROR in property_handlers: Unexpected error getting property: $Traceback (most recent call last):
housinghub-api_1  |   File "/usr/housinghub/src/property_handlers.py", line 47, in get_property
housinghub-api_1  |     _property = app.services.property_service().get_property(payload)
housinghub-api_1  |   File "/usr/housinghub/src/services/property.py", line 40, in get_property
housinghub-api_1  |     _properties = _properties.all()
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3244, in all
housinghub-api_1  |     return list(self)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3403, in __iter__
housinghub-api_1  |     return self._execute_and_instances(context)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3425, in _execute_and_instances
housinghub-api_1  |     querycontext, self._connection_from_session, close_with_result=True
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3440, in _get_bind_args
housinghub-api_1  |     mapper=self._bind_mapper(), clause=querycontext.statement, **kw
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3418, in _connection_from_session
housinghub-api_1  |     conn = self.session.connection(**kw)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1133, in connection
housinghub-api_1  |     execution_options=execution_options,
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1139, in _connection_for_bind
housinghub-api_1  |     engine, execution_options
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 432, in _connection_for_bind
housinghub-api_1  |     conn = bind._contextual_connect()
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2251, in _contextual_connect
housinghub-api_1  |     self._wrap_pool_connect(self.pool.connect, None),
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2285, in _wrap_pool_connect
housinghub-api_1  |     return fn()
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 363, in connect
housinghub-api_1  |     return _ConnectionFairy._checkout(self)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 773, in _checkout
housinghub-api_1  |     fairy = _ConnectionRecord.checkout(pool)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
housinghub-api_1  |     rec = pool._do_get()
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 131, in _do_get
housinghub-api_1  |     code="3o7r",
housinghub-api_1  | sqlalchemy.exc.TimeoutError: QueuePool limit of size 20 overflow 10 reached, connection timed out, timeout 30 (Background on this error at: http://sqlalche.me/e/3o7r)
housinghub-api_1  |
housinghub-api_1  | 172.20.0.3 - - [30/Jul/2020 14:47:31] "POST /property/search HTTP/1.1" 500 -
housinghub-api_1  | [2020-07-30 14:47:31,391] ERROR in property_handlers: Unexpected error getting property: $Traceback (most recent call last):
housinghub-api_1  |   File "/usr/housinghub/src/property_handlers.py", line 47, in get_property
housinghub-api_1  |     _property = app.services.property_service().get_property(payload)
housinghub-api_1  |   File "/usr/housinghub/src/services/property.py", line 22, in get_property
housinghub-api_1  |     if (self.db_session.query(Property).all() == None):
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3244, in all
housinghub-api_1  |     return list(self)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3403, in __iter__
housinghub-api_1  |     return self._execute_and_instances(context)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3425, in _execute_and_instances
housinghub-api_1  |     querycontext, self._connection_from_session, close_with_result=True
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3440, in _get_bind_args
housinghub-api_1  |     mapper=self._bind_mapper(), clause=querycontext.statement, **kw
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3418, in _connection_from_session
housinghub-api_1  |     conn = self.session.connection(**kw)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1133, in connection
housinghub-api_1  |     execution_options=execution_options,
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 1139, in _connection_for_bind
housinghub-api_1  |     engine, execution_options
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/session.py", line 432, in _connection_for_bind
housinghub-api_1  |     conn = bind._contextual_connect()
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2251, in _contextual_connect
housinghub-api_1  |     self._wrap_pool_connect(self.pool.connect, None),
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2285, in _wrap_pool_connect
housinghub-api_1  |     return fn()
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 363, in connect
housinghub-api_1  |     return _ConnectionFairy._checkout(self)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 773, in _checkout
housinghub-api_1  |     fairy = _ConnectionRecord.checkout(pool)
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/base.py", line 492, in checkout
housinghub-api_1  |     rec = pool._do_get()
housinghub-api_1  |   File "/usr/local/lib/python3.6/site-packages/sqlalchemy/pool/impl.py", line 131, in _do_get
housinghub-api_1  |     code="3o7r",
housinghub-api_1  | sqlalchemy.exc.TimeoutError: QueuePool limit of size 20 overflow 10 reached, connection timed out, timeout 30 (Background on this error at: http://sqlalche.me/e/3o7r)

I can increase the size of the connection pool more but it will always be possible in principle to cause an error this way by exhausting the pool. This should only be a problem in the local dev environment though.