From user-return-682-apmail-trafodion-user-archive=trafodion.apache.org@trafodion.incubator.apache.org Mon Sep 12 06:22:02 2016 Return-Path: X-Original-To: apmail-trafodion-user-archive@minotaur.apache.org Delivered-To: apmail-trafodion-user-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id D2E9E191C3 for ; Mon, 12 Sep 2016 06:22:02 +0000 (UTC) Received: (qmail 98243 invoked by uid 500); 12 Sep 2016 06:22:02 -0000 Delivered-To: apmail-trafodion-user-archive@trafodion.apache.org Received: (qmail 98159 invoked by uid 500); 12 Sep 2016 06:22:02 -0000 Mailing-List: contact user-help@trafodion.incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@trafodion.incubator.apache.org Delivered-To: mailing list user@trafodion.incubator.apache.org Received: (qmail 98135 invoked by uid 99); 12 Sep 2016 06:22:02 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 12 Sep 2016 06:22:02 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id DFFA0C20CC; Mon, 12 Sep 2016 06:22:01 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.179 X-Spam-Level: * X-Spam-Status: No, score=1.179 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, RCVD_IN_DNSWL_LOW=-0.7, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd1-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 (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id 8AULC1VYyZcX; Mon, 12 Sep 2016 06:22:01 +0000 (UTC) Received: from mail-vk0-f41.google.com (mail-vk0-f41.google.com [209.85.213.41]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id 9ADB85F299; Mon, 12 Sep 2016 06:22:00 +0000 (UTC) Received: by mail-vk0-f41.google.com with SMTP id v189so120226359vkv.1; Sun, 11 Sep 2016 23:22:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:from:date:message-id:subject:to; bh=cHZzHBwSr8KWGD8/F85+toETZshSPj/q3oGb4Bp4Wuk=; b=HmqQVheoGfHqHDsXok4mJv8kNez4KliRy57Lf1wrO3I9C9OXZ3MbRALoWZ7colh3Is zh7Xk0bGhiYef99DJkNLoBOINIS/moy0mkN8GTAFo0cqelL5K+itIVCAwNHx5t/hscde KzS0O8HTnOCtyG+OIW4QHw2jETnjTjX0c4ExS7QMehYPAQKc44jDjx7Y6j3H8HmMYWWr 19gJN2O0trXOPzLTMM7ykdybCpbyDczM2Z7o6d7VP8OvlPOXO8aM1/yP4Jyb5G1BHtX7 opWaCVkk/FaIW1MB90ckZfyjGkrXU1wXt/Dwtl3JF6h3bSnUQNlxQDo5ALQnAPc6V1Zo h9KA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=cHZzHBwSr8KWGD8/F85+toETZshSPj/q3oGb4Bp4Wuk=; b=g8WRX3CFN2TnAEev1CU9kdo9dyp9SPNbtftHrnNYdrg3P8DUVWCtuEig7Q9ibejI8C Did8vx3uTnCUO4fmf6TbadIKzrNM4RSrVbkWlUl70V8QRZsKbXlzJ+02lST0AylhJL6l 2yzoItn52tMRngOXm+DvcWqx76+fh3+Ho9yIRk7o5v+P0RWcjdg99ZshKU9HtOxZiOaJ TwnmOf6Y/cj3FWAt2nQhreyk8wd+v9ZaLAz2/L9195PT+7dBdtJwBUzbWAiW5Ukf4KK+ ZWE9F1kriqa8CH5oX7yd6HJYjodcVU2tZ4dk5LiWw/gbFf3bDO4E92QTWFfsoR+U8bjk LOPg== X-Gm-Message-State: AE9vXwNbLCALsFHLeXBgcSYoXL3ub6YM7BDKS7CXFWxeveSshWtwkcGdni3LRwwHaq5wjG39m+VmMRo3sv38tQ== X-Received: by 10.31.65.210 with SMTP id o201mr7183650vka.0.1473661319396; Sun, 11 Sep 2016 23:21:59 -0700 (PDT) MIME-Version: 1.0 From: =?UTF-8?B?5LmU5b2m5YWL?= Date: Mon, 12 Sep 2016 06:21:48 +0000 Message-ID: Subject: trafodion query optimization To: "user@trafodion.incubator.apache.org" , "dev@trafodion.incubator.apache.org" Content-Type: multipart/alternative; boundary=001a114dd8dc76418f053c4983d8 --001a114dd8dc76418f053c4983d8 Content-Type: text/plain; charset=UTF-8 Hi all, I executed the sum and count query on my table where the cluster has three nodes. I found that the sum query is not well parallel executed(not all the three nodes get high load when executing the sum query) and the cpu load is very high while the memory load is very low(the machines have 16 cores and 16GB memory). My sum query on the 12 million data sets takes about 2 minutes and a half time. So my question is that is there any optimization advice that I can use to improve the query performance and maximize the usage of my machines, what ever the configuration or the table ddl. Any replies is appreciated. Thanks, Qiao --001a114dd8dc76418f053c4983d8 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi all,
=C2=A0 =C2=A0 =C2=A0I executed the sum and cou= nt query on my table where the cluster has three nodes. I found that the su= m query is not well parallel executed(not all the three nodes get high load= when executing the sum query) and the cpu load is very high while the memo= ry load is very low(the machines have 16 cores and 16GB memory). My sum que= ry on the 12 million data sets takes about 2 minutes and a half time.
=
=C2=A0 =C2=A0 So my question is that is there any optimization advice = that I can use to improve the query performance and maximize the usage of m= y machines, what ever the configuration or the table ddl.
=C2=A0 = =C2=A0 Any replies is appreciated.

Thanks,
Qiao
--001a114dd8dc76418f053c4983d8--