構築したばかりのResashにて、試しに80万行のテーブルに対して全件SELECTをしたところ、 Error running query: failed communicating with server. Please check your Internet connection and try again. と表示されました。
こちらの解決方法をご教示いただければと思います。
構築先インスタンス
AWS東京リージョン上に公式AMIから構築したインスタンス上で動かしています。
インスタンスタイプ：r5a.large （CPU:2 Memory:16GiB）
docker-compose.yml：以下のとおりです。
version: “2”
x-redash-service: &redash-service
image: redash/redash:8.0.0.b32245
depends_on:
- postgres
- redis
env_file: /opt/redash/env
restart: always
services:
server:
<<: *redash-service
command: server
ports:
- “5000:5000”
environment:
REDASH_WEB_WORKERS: 4
REDASH_DATE_FORMAT: “YYYY-MM-DD”
REDASH_ENABLED_QUERY_RUNNERS: ‘redash.query_runner.google_spreadsheets,redash.query_runner.mysql,redash.query_runner.dynamodb_sql,redash.query_runner.google_analytics,redash.query_runner.query_results’
REDASH_ADDITIONAL_QUERY_RUNNERS: redash.query_runner.python
REDASH_HOST: “https://example.com”
REDASH_FEATURE_SHOW_PERMISSIONS_CONTROL: “true”
QUERY_RESULTS_CLEANUP_MAX_AGE: 2
scheduler:
<<: *redash-service
command: scheduler
environment:
QUEUES: “celery”
WORKERS_COUNT: 1
scheduled_worker:
<<: *redash-service
command: worker
environment:
QUEUES: “scheduled_queries,schemas”
WORKERS_COUNT: 1
REDASH_ENABLED_QUERY_RUNNERS: ‘redash.query_runner.google_spreadsheets,redash.query_runner.mysql,redash.query_runner.dynamodb_sql,redash.query_runner.google_analytics,redash.query_runner.query_results’
REDASH_ADDITIONAL_QUERY_RUNNERS: redash.query_runner.python
adhoc_worker:
<<: *redash-service
command: worker
environment:
QUEUES: “queries”
WORKERS_COUNT: 2
REDASH_ENABLED_QUERY_RUNNERS: ‘redash.query_runner.google_spreadsheets,redash.query_runner.mysql,redash.query_runner.dynamodb_sql,redash.query_runner.google_analytics,redash.query_runner.query_results’
REDASH_ADDITIONAL_QUERY_RUNNERS: redash.query_runner.python
redis:
image: redis:5.0-alpine
restart: always
postgres:
image: postgres:9.6-alpine
env_file: /opt/redash/env
volumes:
- /opt/redash/postgres-data:/var/lib/postgresql/data
restart: always
nginx:
image: redash/nginx:latest
ports:
- “80:80”
depends_on:
- server
links:
- server:redash
restart: always
postgres
postgresのメモリ割り当ては以下の設定でした。
postgres=# select name, setting, unit from pg_settings where name = ‘work_mem’;
name | setting | unit
----------±--------±-----
work_mem | 4096 | kB
(1 row)
エラー時のログ
adhoc_worker_1
adhoc_worker_1 | [2020-05-26 07:33:09,168][PID:11][ERROR][ForkPoolWorker-2] Task redash.tasks.execute_query[c14f78ad-318c-4c78-98d8-b1d102b03289] raised unexpected: InternalError(’(psycopg2.InternalError) invalid memory alloc request size 1073741824\n’,)
adhoc_worker_1 | Traceback (most recent call last):
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/celery/app/trace.py”, line 385, in trace_task
adhoc_worker_1 | R = retval = fun(*args, **kwargs)
adhoc_worker_1 | File “/app/redash/worker.py”, line 84, in call
adhoc_worker_1 | return TaskBase.call(self, *args, **kwargs)
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/celery/app/trace.py”, line 648, in protected_call
adhoc_worker_1 | return self.run(*args, **kwargs)
adhoc_worker_1 | File “/app/redash/tasks/queries.py”, line 436, in execute_query
adhoc_worker_1 | scheduled_query).run()
adhoc_worker_1 | File “/app/redash/tasks/queries.py”, line 391, in run
adhoc_worker_1 | run_time, utcnow())
adhoc_worker_1 | File “/app/redash/models/init.py”, line 314, in store_result
adhoc_worker_1 | for q in queries:
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py”, line 2994, in iter
adhoc_worker_1 | self.session._autoflush()
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py”, line 1444, in _autoflush
adhoc_worker_1 | util.raise_from_cause(e)
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py”, line 265, in raise_from_cause
adhoc_worker_1 | reraise(type(exception), exception, tb=exc_tb, cause=cause)
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py”, line 1434, in _autoflush
adhoc_worker_1 | self.flush()
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py”, line 2254, in flush
adhoc_worker_1 | self._flush(objects)
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py”, line 2380, in _flush
adhoc_worker_1 | transaction.rollback(_capture_exception=True)
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py”, line 66, in exit
adhoc_worker_1 | compat.reraise(exc_type, exc_value, exc_tb)
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py”, line 2344, in _flush
adhoc_worker_1 | flush_context.execute()
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py”, line 391, in execute
adhoc_worker_1 | rec.execute(self)
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py”, line 556, in execute
adhoc_worker_1 | uow
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py”, line 181, in save_obj
adhoc_worker_1 | mapper, table, insert)
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py”, line 866, in _emit_insert_statements
adhoc_worker_1 | execute(statement, params)
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py”, line 948, in execute
adhoc_worker_1 | return meth(self, multiparams, params)
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py”, line 269, in _execute_on_connection
adhoc_worker_1 | return connection._execute_clauseelement(self, multiparams, params)
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py”, line 1060, in _execute_clauseelement
adhoc_worker_1 | compiled_sql, distilled_params
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py”, line 1200, in _execute_context
adhoc_worker_1 | context)
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py”, line 1413, in _handle_dbapi_exception
adhoc_worker_1 | exc_info
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py”, line 265, in raise_from_cause
adhoc_worker_1 | reraise(type(exception), exception, tb=exc_tb, cause=cause)
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py”, line 1193, in _execute_context
adhoc_worker_1 | context)
adhoc_worker_1 | File “/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py”, line 509, in do_execute
adhoc_worker_1 | cursor.execute(statement, parameters)
adhoc_worker_1 | InternalError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (psycopg2.InternalError) invalid memory alloc request size 1073741824
すでに試したこと
- EC2インスタンスタイプ変更：当初t2.smallで構築しましたが、CPU100%張り付きのため、インスタンスタイプ変更。現在r5a.largeです
- swapfile作成：16GiBでも不足しているかと考えswapfileを作成。