I'm using CKAN v2.7.2(docker). And getting "Error - <class 'sqlalchemy.exc.TimeoutError'>: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30" error due to which HTTP-500 error are coming and request could not be completed.
It seems, after sometime ckan has no connection left to serve a new request that is why this error has occured. Is my understanding correct? If so why it is consuming all the connections?
Are these values(pool_size & max_overflow) are load dependent(resource data or size of resource files)?
Is pool_size value 5 and max_overflow value 10, is not sufficient?
Should we only increase max_overflow value or only pool_size value or we should increase both values to avoid this "HTTP-500 error(sqlalchemy.exc.TimeoutError)".
What should be appropriate value or how we should choose the appropriate value?
datapusher.error.log
Fetching from: https://url/ckan/dataset/8a723839-5ea4-938b-bfa9-7637743dfe12/resource/0797383e-bac0-4114-943b-87c74d2677a9/download/Filelist1.csv
Error notifying listener
Traceback (most recent call last):
File "/usr/lib/ckan/datapusher/lib/python2.7/site-packages/apscheduler/scheduler.py", line 239, in _notify_listeners
cb(event)
File "/usr/lib/ckan/datapusher/lib/python2.7/site-packages/ckanserviceprovider/web.py", line 184, in job_listener
db.mark_job_as_errored(job_id, error_object)
File "/usr/lib/ckan/datapusher/lib/python2.7/site-packages/ckanserviceprovider/db.py", line 413, in mark_job_as_errored
_update_job(job_id, update_dict)
File "/usr/lib/ckan/datapusher/lib/python2.7/site-packages/ckanserviceprovider/db.py", line 348, in _update_job
job_dict["error"] = json.dumps(job_dict["error"])
File "/usr/lib/python2.7/json/__init__.py", line 243, in dumps
return _default_encoder.encode(obj)
File "/usr/lib/python2.7/json/encoder.py", line 207, in encode
chunks = self.iterencode(o, _one_shot=True)
File "/usr/lib/python2.7/json/encoder.py", line 270, in iterencode
return _iterencode(o, 0)
File "/usr/lib/python2.7/json/encoder.py", line 184, in default
raise TypeError(repr(o) + " is not JSON serializable")
TypeError: <Response [500]> is not JSON serializable
Job "push_to_datastore (trigger: RunTriggerNow, run = True, next run at: None)" raised an exception
Traceback (most recent call last):
File "/usr/lib/ckan/datapusher/lib/python2.7/site-packages/apscheduler/scheduler.py", line 512, in _run_job
retval = job.func(*job.args, **job.kwargs)
File "/usr/lib/ckan/datapusher/src/datapusher/datapusher/jobs.py", line 417, in push_to_datastore
existing = datastore_resource_exists(resource_id, api_key, ckan_url)
File "/usr/lib/ckan/datapusher/src/datapusher/datapusher/jobs.py", line 221, in datastore_resource_exists
response.status_code, search_url, response,
HTTPError: Error getting datastore resource. status=500 url=https://url/ckan/api/3/action/datastore_search response=<Response [500]>
ckan_http.error.log
Error - <class 'sqlalchemy.exc.TimeoutError'>: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30
URL: https://url/dataset/dataset1/resource/0797383e-bac0-4114-943b-87c74d2677a9
File '/usr/lib/ckan/default/lib/python2.7/site-packages/weberror/errormiddleware.py', line 171 in __call__
app_iter = self.application(environ, sr_checker)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/webob/dec.py', line 147 in __call__
resp = self.call_func(req, *args, **self.kwargs)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/webob/dec.py', line 208 in call_func
return self.func(req, *args, **kwargs)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/fanstatic/publisher.py', line 234 in __call__
return request.get_response(self.app)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/webob/request.py', line 1053 in get_response
application, catch_exc_info=False)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/webob/request.py', line 1022 in call_application
app_iter = application(self.environ, start_response)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/webob/dec.py', line 147 in __call__
resp = self.call_func(req, *args, **self.kwargs)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/webob/dec.py', line 208 in call_func
return self.func(req, *args, **kwargs)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/fanstatic/injector.py', line 54 in __call__
response = request.get_response(self.app)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/webob/request.py', line 1053 in get_response
application, catch_exc_info=False)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/webob/request.py', line 1022 in call_application
app_iter = application(self.environ, start_response)
File '/usr/lib/ckan/default/src/ckan/ckan/config/middleware/pylons_app.py', line 250 in inner
result = application(environ, start_response)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/beaker/middleware.py', line 73 in __call__
return self.app(environ, start_response)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/beaker/middleware.py', line 156 in __call__
return self.wrap_app(environ, session_start_response)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/routes/middleware.py', line 131 in __call__
response = self.app(environ, start_response)
File '/usr/lib/ckan/default/src/ckan/ckan/config/middleware/common_middleware.py', line 80 in __call__
return self.app(environ, start_response)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/pylons/wsgiapp.py', line 125 in __call__
response = self.dispatch(controller, environ, start_response)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/pylons/wsgiapp.py', line 324 in dispatch
return controller(environ, start_response)
File '/usr/lib/ckan/default/src/ckan/ckan/lib/base.py', line 212 in __call__
res = WSGIController.__call__(self, environ, start_response)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/pylons/controllers/core.py', line 221 in __call__
response = self._dispatch_call()
File '/usr/lib/ckan/default/lib/python2.7/site-packages/pylons/controllers/core.py', line 172 in _dispatch_call
response = self._inspect_call(func)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/pylons/controllers/core.py', line 107 in _inspect_call
result = self._perform_call(func, args)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/pylons/controllers/core.py', line 60 in _perform_call
return func(**args)
File '/usr/lib/ckan/default/src/ckan/ckan/controllers/package.py', line 1123 in resource_read
return render(template, extra_vars=vars)
File '/usr/lib/ckan/default/src/ckan/ckan/lib/base.py', line 177 in render
return cached_template(template_name, render_template)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/pylons/templating.py', line 249 in cached_template
return render_func()
File '/usr/lib/ckan/default/src/ckan/ckan/lib/base.py', line 131 in render_template
return render_jinja2(template_name, globs)
File '/usr/lib/ckan/default/src/ckan/ckan/lib/base.py', line 88 in render_jinja2
return template.render(**extra_vars)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/jinja2/environment.py', line 989 in render
return self.environment.handle_exception(exc_info, True)
File '/usr/lib/ckan/default/lib/python2.7/site-packages/jinja2/environment.py', line 754 in handle_exception
reraise(exc_type, exc_value, tb)
File '/usr/lib/ckan/default/src/ckanext-fiware_theme_basic/ckanext/fiware_theme_basic/templates/package/resource_read.html', line 1 in top-level template code
{% ckan_extends %}
TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30
question from:
https://stackoverflow.com/questions/65951912/ckan-datapusher-http500-error-due-to-error-class-sqlalchemy-exc-timeouterro 
