Continue Out Of Memory of simple query


We are encountring a lot of SQL oom error when using redshift jdbc driver:
compile group: '', name: 'redshift-jdbc42', version: ''
and redshift is dc2.large. (2cpu and 15GB mem)

Below is a table structure and with about 15K records.
CREATE TABLE if not exists datatable (
id bigint not null,
ingest_created_at timestamp with time zone,
ingest_updated_at timestamp with time zone,
ingest_synced_at timestamp with time zone,
ingest_state int default 0,
MerchantID int not null,
merchant_location_id int not null,
guarantor_id character varying(128),
customer_name character varying(128),
office_id varchar(255),
firstname varchar(255),
lastname varchar(255),
preferred_name varchar(255),
birthdate character varying(64),
status character varying(64),
gender varchar(32),
practice_id varchar(64),
address_line1 character varying(255),
address_line2 character varying(255),
city character varying(255),
state character varying(255),
zipcode character varying(255),
email character varying(128),
cell character varying(512),
billing_Type varchar(64),
preferred_contact varchar(64),
phone varchar(32),
workphone varchar(32))
INTERLEAVED sortkey(MerchantID,merchant_location_id,guarantor_id,ingest_state,id);

and our query is like:
SELECT "firstname", "birthdate", "gender", "city", "merchant_location_id", "cell", "ingest_created_at", "billing_type", "office_id", "guarantor_id", "workphone", "address_line1", "address_line2", "preferred_name", "state", "id", "preferred_contact", "email", "practice_id", "ingest_updated_at", "ingest_state", "customer_name", "lastname", "zipcode", "ingest_synced_at", "phone", "merchantid", "status" FROM datatable WHERE "guarantor_id"= '456' AND "merchantid"= 123 AND "office_id"= 'abc' AND "practice_id"= '1' LIMIT 1

and the oom is like:
Caused by: [Amazon](500310) Invalid operation: Out of Memory

error: Out of Memory
code: 1020
context: From OomGuard
query: 0
location: oom_guard.cpp:311
process: sched [pid=1175]

I also checked the table : svl_query_summary (but the diskbased is false).
and now the redshift avg select time is about 2 seconds and 200 ms per update query (which the app is host in ec2). Is this a acceptable response time for redshift? I also limit the concurrent connections to 30 but not working.

Edited by: egao91 on Jan 28, 2021 7:33 PM

Edited by: egao91 on Jan 28, 2021 7:34 PM

asked 2 years ago267 views
3 Answers

Hi egao91,

Is your issue with the OOM errors or Redshift SELECT and UPDATE statement execution time?

Clearly the OOM error is an issue. I'd recommend you direct that to AWS Support if you have a support plan higher than basic forum based support.

WRT your statement response times, Redshift, as an analytic column-store SQL database, is not built to perform a lot of small narrow queries (like your SELECT) or do a lot of small UPDATE statements (you didn't provide your UPDATE statement so I'm assuming a single row update) like an OLTP SQL database with row-store storage is built to do. Instead small narrow query and DML statement response time, it's tuned for overall large complex query workload throughput and fast loads (COPY command) of huge amounts of data.

I hope this helps you at least a little bit,

answered 2 years ago

Thanks Kurt. My first issue is the oom error. I tried to limit the connection pool to 10. and then it happened rarely. I don't know what's cause the OOM. I checked the WLM and didn't find any helpful messages. because the table is small enough. and the database may provide some mechanism to avoid oom (eg using a cache file ) for this kind of simple select and update.
after I limited the connection pool to 10 (a really small pool), the query (a very selective query with where conditions) cost about 10 seconds. and update (also similar with select) cost 700 ms. but when I stopped some tasks (which will use redshift), the time is recovered to a reasonable value about 70ms.

Edited by: egao91 on Jan 31, 2021 10:26 PM

answered 2 years ago

Hi egao91,

Glad to see you're making some progress on your own. I think that's all useful information for AWS Redshift Support to help them diagnose the 2 issues. I think you need their help from here. I don't know of anyone the Redshift customer community here that help diagnose this further and get a fix.


answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions