Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 492E6200D16 for ; Tue, 10 Oct 2017 14:59:03 +0200 (CEST) Received: by cust-asf.ponee.io (Postfix) id 47C9A1609E5; Tue, 10 Oct 2017 12:59:03 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id 3EE8A160BE0 for ; Tue, 10 Oct 2017 14:59:02 +0200 (CEST) Received: (qmail 77255 invoked by uid 500); 10 Oct 2017 12:59:01 -0000 Mailing-List: contact user-help@ignite.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@ignite.apache.org Delivered-To: mailing list user@ignite.apache.org Received: (qmail 77245 invoked by uid 99); 10 Oct 2017 12:59:01 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd2-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 10 Oct 2017 12:59:01 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd2-us-west.apache.org (ASF Mail Server at spamd2-us-west.apache.org) with ESMTP id 7A3D01A5316 for ; Tue, 10 Oct 2017 12:59:00 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd2-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.38 X-Spam-Level: ** X-Spam-Status: No, score=2.38 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, KAM_SHORT=0.001, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, RCVD_IN_SORBS_SPAM=0.5, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd2-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd2-us-west.apache.org [10.40.0.9]) (amavisd-new, port 10024) with ESMTP id 8uk1js44gLqq for ; Tue, 10 Oct 2017 12:58:58 +0000 (UTC) Received: from mail-ua0-f174.google.com (mail-ua0-f174.google.com [209.85.217.174]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id 903B15F6C6 for ; Tue, 10 Oct 2017 12:58:57 +0000 (UTC) Received: by mail-ua0-f174.google.com with SMTP id f46so16640893uae.1 for ; Tue, 10 Oct 2017 05:58:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to; bh=yIpCsKG4xDPuWGv7XgqaJCgWgpK3qL249BJvNHYjxpE=; b=Nr3JueO3xNkeK769E4iuR/qIi2l4gn3/QDtThukdxS3vJj00IA94u8j50dKfUn7i2h tB87HipzWRelKoFpql8ihOxA6F+ha1f0v9S4U1EQiNc3MHBUmT8llmSh1ZJYEaFFpXI3 210t311UzRy9tsrGywDAZdSj0bZVGmnWIdKUrMGdbmCxTjT752fem96obKLsZzVaHtUX hgomfCvoBELVaYvCPsmxR3CvRimD40AOgIhTeYgKITWmMVjDFoCtYHrv6C817yKrQU6c VmcSXe2Gzg2PgxHRg4ce5aC355EGh3Lb8Gh5Y4x0Wh6xnqz2Fih5od+vttL8gbyqfzJz Gi4Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to; bh=yIpCsKG4xDPuWGv7XgqaJCgWgpK3qL249BJvNHYjxpE=; b=OqbQVGcpz/biBzYuh62auGgNT4UyOLAuHHxIc7Eu3NMme2fAVjlx4IzrY8U7D0l1o+ exNpSxp3e1uPvNkF/FuSw+ElRebGT3da4+ffrSFhow6fVrLE/wtOVxq03UPffrOqQq2n YvkwyhTjrUT6eqhvXCXmrJC02y1sUijm9K+mxLM4sZ6DdvUPNGxNQjuE1Obfpww4pd06 HkDrT1A3gAscEvIxURZs4DMsFqZrYZUcGNhJTdpp+QeCBIeoYdvISCPUNzqHqiCnqfaN y+WNZLhP0i9mV0nV9dc9oB+ymonIlHDH8/mS1ZTwd/pdynT+sygVNLp/VRbPp2JQGR24 hhfA== X-Gm-Message-State: AMCzsaV/mjETfq7qwXVA51VMyaIIvBqL5Vh7cQ8LoaJGCvEIlnJQnuzk tquChewi4ptk1X9DlPNrMZUOSo4tuau3A5KQ24g= X-Google-Smtp-Source: AOwi7QAVk21+GdIw5csQe1qGo/GFA0DtBEla9tTOBbGU6zwxawseGz6x/3eWHBa89Idu1sSEWirC2+Op0RrCiHpC+ME= X-Received: by 10.176.75.195 with SMTP id b3mr5354014uag.51.1507640336242; Tue, 10 Oct 2017 05:58:56 -0700 (PDT) MIME-Version: 1.0 Received: by 10.176.21.206 with HTTP; Tue, 10 Oct 2017 05:58:55 -0700 (PDT) In-Reply-To: <64bb2185.dc3c.15f061d8ff7.Coremail.wanxing987@163.com> References: <12ecf329.2a54.15e833afeb1.Coremail.wanxing987@163.com> <4f67ea5e.2905.15e8973bd54.Coremail.wanxing987@163.com> <5440524.6225.15e93a55020.Coremail.wanxing987@163.com> <220e76d5.df9.15e97e3d1ef.Coremail.wanxing987@163.com> <64bb2185.dc3c.15f061d8ff7.Coremail.wanxing987@163.com> From: Andrey Mashenkov Date: Tue, 10 Oct 2017 15:58:55 +0300 Message-ID: Subject: Re: Re: Fetched result use too much time To: user@ignite.apache.org Content-Type: multipart/alternative; boundary="f403045f8ab6b0de3b055b30defb" archived-at: Tue, 10 Oct 2017 12:59:03 -0000 --f403045f8ab6b0de3b055b30defb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Lucky, Looks like your query selectivity is poor and even with GroupBy large amount of data shoud be fetched to reduce node. 1. Is it possiblt to coolocate data on field used in OrderBy clause? 2. Looks weird that queryParallelizm cause wrong results. Looks like you have a single node grid and there is a bug in queryParallelizm feature. Also I can find what ignite version you use. Would you try to switch to the latest one? On Tue, Oct 10, 2017 at 2:48 PM, Lucky wrote: > Andrey Mashenkov > Thank you very much! > 1.query parallelism:this will cause a problem: fetch wrong reslut. > I set it to 10,and have table a with 150,000 records, table b with > 12,000,000 records. > when I query single table,the result is correct. > but when the sql is like this: > select a.id from a inner join b on a.id =3D b.tid > it got the wrong result. The result should be 11,000,000;but it jus= t > return 380,000 records. > when I remove query parallelism setting,it return correctly. > > 2. I have modified ths property,and restart the server.for the record > is too large, it need 4 hours to load data to ignite.So I have to wait. > 3.Actually, if I remove the group by clause and having condition, it > took more time! > 4 and 5: I have try them before ,but it did not work. > Thanks again. > Lucky > > > > > At 2017-09-21 21:28:40, "Andrey Mashenkov" > wrote: > > Lucky, > > > 1. Looks like it make no sense to set query parallelism level higher > number of available CPU on node. > > 2. Map query use index for field FASSIGCUID type of String and seems > values are 16 chars length strings (32 bytes) > By default, values with size < 10 bytes can be inlined in index, so Ignit= e > doesn't need to lookup a data page for value data. > You can try to increase it up to 32 via* > cacheConfiguration.setSqlIndexMaxInlineSize(32) *or JVM property > *-DIGNITE_MAX_INDEX_PAYLOAD_SIZE=3D32*. > > 3. Ignite doesn't know whether your data is collocated by FDATABASEDID > (group by clause) or not collocated. > So, Ignite can't apply HAVING condition instantly on map phase and have t= o > load and merge all groups from all nodes before check for HAVING. > If it possible to collocate data on GROUP BY condition, you can hint > Ignite with setting query flag: *sqlFieldsQuery.setCollocated(true).* > However, I'm not sure it will help much and H2 will be able to make any > optimization here. > > 4. Also, you can force Ignite to use different index. E.g. group index on > FDATABASEDID and FASSIGCUID and same fields in different order. > > 5. Sometimes, Ignite change join order and it can cause unexcpected > slowdown. You can try to change join order by changing tables positions i= n > query string. > To preserve Ignite join order optimization you may use a flag: > *sqlFieldsQuery.setEnforceJoinOrder(true).* > > > Hope, this will help you. > > > > =E3=80=90=E7=BD=91=E6=98=93=E8=87=AA=E8=90=A5=E3=80=91=E5=A5=BD=E5=90=83= =E5=88=B0=E7=88=86=EF=BC=81=E9=B2=9C=E9=A6=99=E5=BC=B9=E6=BB=91=E5=8A=A0=E7= =83=AD=E5=8D=B3=E9=A3=9F=EF=BC=8C=E7=BB=8F=E5=85=B813=E9=A6=99/=E9=BA=BB=E8= =BE=A3=E5=B0=8F=E9=BE=99=E8=99=BE=E4=BB=8575=E5=85=833=E6=96=A4>> > > > --=20 Best regards, Andrey V. Mashenkov --f403045f8ab6b0de3b055b30defb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Lucky,

Looks like your query selecti= vity is poor and even with GroupBy large amount of data shoud be fetched to= reduce node.

1. Is it possiblt to coolocate data = on field used in OrderBy clause?
2. Looks weird that queryParalle= lizm cause wrong results. Looks like you have a single node grid and there = is a bug in queryParallelizm feature.
=C2=A0Also I can find what = ignite version you use. Would you try to switch to the latest one?

On Tue, Oct 10= , 2017 at 2:48 PM, Lucky <wanxing987@163.com> wrote:
=C2=A0 =C2=A0 Thank you very much!
=C2=A0 =C2=A0 1.query= parallelism:this will cause a problem: fetch wrong reslut.=C2=A0
=C2=A0 =C2=A0 =C2=A0 = =C2=A0I set it to 10,and have table a with 150,000 records, table b with 12= ,000,000 records.
=C2=A0 =C2=A0 =C2=A0 =C2=A0when I query single table,the result is cor= rect.
=C2=A0 = =C2=A0 =C2=A0 =C2=A0but when the sql is like this:
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0select a.id from a inne= r join b on a.id =3D b.tid=C2= =A0
=C2=A0 = =C2=A0 =C2=A0 it got the wrong result. The result should be 11,000,000;but = it just return 380,000 records.
=C2=A0 =C2=A0 =C2=A0 when I remove=C2=A0query parallelis= m setting,it return correctly.=C2=A0

=C2=A0 =C2=A0 2. I have modified ths property,and restart t= he server.for the record is too large, it need 4 hours to load data to igni= te.So I have to wait.
=C2=A0 =C2=A0 3.Actually, if I remove the group by clause and havi= ng condition, it took more time!
=C2=A0 =C2=A0 4 =C2=A0and 5: I have try them before ,bu= t it did not work.
Thanks again.
Lucky=C2=A0




At 2017-09-21 21:28:40, = "Andrey Mashenkov" <andrey.mashenkov@gmail.com> wrote:
Lucky,=


1. Looks like it make no sense to set qu= ery parallelism level higher number of available CPU on node.
2. Map query use index for field FASSIGCUID type of String and = seems values are 16 chars length strings (32 bytes)
By default, v= alues with size < 10 bytes can be inlined in index, so Ignite doesn'= t need to lookup a data page for value data.=C2=A0
You can try to= increase it up to 32 via cacheConfiguration.setSqlIndexMaxInlineSi= ze(32)=C2=A0or JVM property -DIGNITE_MAX_INDEX_PAYLOAD_SIZ= E=3D32.

3. Ignite doesn't know whether you= r data is collocated by FDATABASEDID (group by clause) or not collocated.
So, Ignite can't apply HAVING condition instantly on map phase= and have to load and merge all groups from all nodes before check for HAVI= NG.
If it possible to collocate data on GROUP BY condition, you c= an hint Ignite with setting query flag:=C2=A0 =C2=A0sqlFieldsQuery.setCo= llocated(true).
However, I'm not sure it will help m= uch and H2 will be able to make any optimization here.

=
4. Also, you can force Ignite to use different index. E.g. group index= on FDATABASEDID and FASSIGCUID and same fields in different order.

5. Sometimes, Ignite change join order and it can cause u= nexcpected slowdown. You can try to change join order by changing tables po= sitions in query string.
To preserve Ignite join order optimizati= on you may use a flag:=C2=A0=C2=A0sqlFieldsQuery.setEnforceJoinOrde= r(true).


Hope, this will help y= ou.





<= div>
--
Best regards,
Andrey V. Mashenkov
<= /div>
--f403045f8ab6b0de3b055b30defb--