Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eUE6p-0005ZD-69 for pgsql-performance@arkaria.postgresql.org; Wed, 27 Dec 2017 15:59:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eUE6m-0006XP-CX for pgsql-performance@arkaria.postgresql.org; Wed, 27 Dec 2017 15:59:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eUE6m-0006XF-0C for pgsql-performance@lists.postgresql.org; Wed, 27 Dec 2017 15:59:08 +0000 Received: from aibo.runbox.com ([91.220.196.211]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eUE6j-0006zI-5a for pgsql-performance@postgresql.org; Wed, 27 Dec 2017 15:59:07 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=runbox.com; s=rbselector1; h=Content-Type:MIME-Version:Message-ID:Date:Subject: In-Reply-To:References:To:From; bh=kylMWeP/uY2k6XDPrlTw+k17OQExXYLWRGDRd9vMing=; b=glK3s3WFneg+lO1y9YkgIwNbL8 Y7hiqlgGeEdYAk8nAMX/Bp1s8dTFFVG/WCQGyFeySeSupL9szqUbqQuQO6tP/dMIdO7BLLNYCj265 LDIR9R4uTRJGMipfsaVN7gOYKEmIpDv623OYsThEMwlokzp7RKhnR1THhyizL+m7OEWxcfNWAyU1T 57bNn9C5Rcap3VD7lglL4kMHvtQ/ceNuFf+NgVDIY/A+0JjSvncpYXpXYYhI7mOXvUjwevNYtBzTo fDiIUXJcD2vUfFtgMqIGX5YuFr7bUyJbY2af+KUgOrBIezrW1quHQD7ktxv/BxuUAoOsMSQOtyCT5 NJh8GsIQ==; Received: from [10.9.9.212] (helo=mailfront12.runbox.com) by mailtransmit02.runbox with esmtp (Exim 4.86_2) (envelope-from ) id 1eUE6i-0003B7-22 for pgsql-performance@postgresql.org; Wed, 27 Dec 2017 16:59:04 +0100 Received: from cpe-66-27-125-35.san.res.rr.com ([66.27.125.35] helo=seasyslap4) by mailfront12.runbox.com with esmtpsa (uid:561468 ) (TLS1.2:RSA_AES_256_CBC_SHA256:256) (Exim 4.82) id 1eUE6L-0008W8-Gy for pgsql-performance@postgresql.org; Wed, 27 Dec 2017 16:58:41 +0100 From: "Mike Sofen" To: References: In-Reply-To: Subject: RE: Batch insert heavily affecting query performance. Date: Wed, 27 Dec 2017 07:58:38 -0800 Message-ID: <00d901d37f2b$9071ba50$b1552ef0$@runbox.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00DA_01D37EE8.82502800" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQKVBFIp7oUjc1kY6fmexFcEABEuuAJR49SrAMKZmkACNSPHeQHWJC6HoZtICOA= Content-Language: en-us List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_00DA_01D37EE8.82502800 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi Jean, =20 I=E2=80=99ve used Postgres on a regular EC2 instance (an m4.xlarge), = storing complex genomic data, hundreds of millions of rows in a table = and =E2=80=9Cnormal=E2=80=9D queries that used an index returned in = 50-100ms, depending on the query=E2=80=A6so this isn=E2=80=99t a = Postgres issue per se. =20 =20 Your table and index structures look ok, although in PG, use the = =E2=80=9Ctext=E2=80=9D datatype instead of varchar, it is the optimized = type for storing string data of any size (even a 2 char country code). = Since you have 2 such columns that you=E2=80=99ve indexed and are = querying for, there is a chance you=E2=80=99ll see an improvement. =20 =20 I have not yet used Aurora or RDS for any large data=E2=80=A6it sure = seems like the finger could be pointing there, but it isn=E2=80=99t = clear what mechanism in Aurora could be creating the slowness. =20 Is there a possibility of you creating the same db on a normal EC2 = instance with PG installed and running the same test? There is nothing = else obvious about your data/structure that could result in such = terrible performance. =20 Mike Sofen =20 From: Jean Baro [mailto:jfbaro@gmail.com]=20 Sent: Wednesday, December 27, 2017 7:14 AM Hello, =20 We are still seeing queries (by UserID + UserCountry) taking over 2 = seconds, even when there is no batch insert going on at the same time. =20 Each query returns from 100 to 200 messagens, which would be a 400kb pay = load, which is super tiny. =20 I don't know what else I can do with the limitations (m4.large), 167MM = rows, almost 500GB database and 29GB of indexes (all indexes). =20 I am probably to optimistic, but I was expecting queries (up to 50 = queries per second) to return (99th) under 500ms or even less, as the = index is simple, there is no aggregation or join involves. =20 Any suggestion? =20 The table structure: CREATE TABLE public.card ( id character(36) NOT NULL, user_id character varying(40) NOT NULL, user_country character(2) NOT NULL, user_channel character varying(40), user_role character varying(40), created_by_system_key character(36) NOT NULL, created_by_username character varying(40), created_at timestamp with time zone NOT NULL, last_modified_at timestamp with time zone NOT NULL, date_start timestamp with time zone NOT NULL, date_end timestamp with time zone NOT NULL, payload json NOT NULL, tags character varying(500), menu character varying(50), deleted boolean NOT NULL, campaign character varying(500) NOT NULL, correlation_id character varying(50), PRIMARY KEY (id) ); =20 CREATE INDEX idx_user_country ON public.card USING btree (user_id COLLATE pg_catalog."default", user_country COLLATE = pg_catalog."default"); =20 CREATE INDEX idx_last_modified_at ON public.card USING btree (last_modified_at ASC NULLS LAST); =20 CREATE INDEX idx_campaign ON public.card USING btree (campaign ASC NULLS LAST) =20 The EXPLAIN =20 'Index Scan using idx_user_country on card (cost=3D0.57..1854.66 = rows=3D460 width=3D922)' ' Index Cond: (((user_id)::text =3D '4684'::text) AND (user_country =3D = 'BR'::bpchar))' =20 =20 =20 Em 25 de dez de 2017 01:10, "Jean Baro" > escreveu: Thanks for the clarification guys. =20 It will be super useful. After trying this I'll post the results! =20 Merry Christmas!=20 =20 ------=_NextPart_000_00DA_01D37EE8.82502800 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Hi = Jean,

 

I=E2=80=99ve used Postgres on a regular EC2 instance = (an m4.xlarge), storing complex genomic data, hundreds of millions of = rows in a table and =E2=80=9Cnormal=E2=80=9D queries that used an index = returned in 50-100ms, depending on the query=E2=80=A6so this = isn=E2=80=99t a Postgres issue per se.=C2=A0

 

Your table = and index structures look ok, although in PG, use the = =E2=80=9Ctext=E2=80=9D datatype instead of varchar, it is the optimized = type for storing string data of any size (even a 2 char country = code).=C2=A0 Since you have 2 such columns that you=E2=80=99ve indexed = and are querying for, there is a chance you=E2=80=99ll see an = improvement.=C2=A0

 

I have not = yet used Aurora or RDS for any large data=E2=80=A6it sure seems like the = finger could be pointing there, but it isn=E2=80=99t clear what = mechanism in Aurora could be creating the slowness.

 

Is there a = possibility of you creating the same db on a normal EC2 instance with PG = installed and running the same test?=C2=A0 There is nothing else obvious = about your data/structure that could result in such terrible = performance.

 

Mike Sofen

 

From: Jean Baro = [mailto:jfbaro@gmail.com]
Sent: Wednesday, December 27, 2017 = 7:14 AM

Hello,

 

We are still seeing = queries  (by UserID + UserCountry) taking over 2 seconds, even when = there is no batch insert going on at the same = time.

 

Each query returns from 100 = to 200 messagens, which would be a 400kb pay load, which is super = tiny.

 

I don't know what else I = can do with the limitations (m4.large), 167MM rows, almost 500GB = database and 29GB of indexes (all indexes).

 

I am probably to = optimistic, but I was expecting queries (up to 50 queries per second) to = return  (99th) under 500ms or even less, as the index is simple, = there is no aggregation or join involves.

 

Any = suggestion?

 

The table = structure:

<= div>

CREATE TABLE = public.card

(

    id character(36) NOT = NULL,

    user_id character = varying(40) NOT NULL,

    user_country character(2) = NOT NULL,

    user_channel character = varying(40),

    user_role character = varying(40),

    created_by_system_key = character(36) NOT NULL,

    created_by_username = character varying(40),

    created_at timestamp with = time zone NOT NULL,

    last_modified_at timestamp = with time zone NOT NULL,

    date_start timestamp with = time zone NOT NULL,

    date_end timestamp with time = zone NOT NULL,

    payload json NOT = NULL,

    tags character = varying(500),

    menu character = varying(50),

    deleted boolean NOT = NULL,

    campaign character = varying(500) NOT NULL,

    correlation_id character = varying(50),

    PRIMARY KEY = (id)

);

 

CREATE INDEX = idx_user_country

    ON public.card USING = btree

    (user_id COLLATE = pg_catalog."default", user_country COLLATE = pg_catalog."default");

 

CREATE INDEX = idx_last_modified_at

    ON public.card USING = btree

    (last_modified_at ASC NULLS = LAST);

 

CREATE INDEX = idx_campaign

    ON public.card USING = btree

    (campaign ASC NULLS = LAST)

 

The = EXPLAIN

 

'Index Scan using idx_user_country on = card  (cost=3D0.57..1854.66 rows=3D460 = width=3D922)'

'  Index Cond: (((user_id)::text =3D = '4684'::text) AND (user_country =3D = 'BR'::bpchar))'

 

 

 

Em 25 de dez de 2017 01:10, = "Jean Baro" <jfbaro@gmail.com> = escreveu:

Thanks for the clarification = guys.

 

It will be super useful. = After trying this I'll post the results!

 

Merry Christmas! =

 

=
------=_NextPart_000_00DA_01D37EE8.82502800--