From user-return-11166-apmail-drill-user-archive=drill.apache.org@drill.apache.org Tue Jun 2 13:09:10 2020 Return-Path: X-Original-To: apmail-drill-user-archive@www.apache.org Delivered-To: apmail-drill-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [207.244.88.153]) by minotaur.apache.org (Postfix) with SMTP id 4A85C19E03 for ; Tue, 2 Jun 2020 13:09:08 +0000 (UTC) Received: (qmail 30418 invoked by uid 500); 2 Jun 2020 13:09:06 -0000 Delivered-To: apmail-drill-user-archive@drill.apache.org Received: (qmail 30372 invoked by uid 500); 2 Jun 2020 13:09:06 -0000 Mailing-List: contact user-help@drill.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@drill.apache.org Delivered-To: mailing list user@drill.apache.org Received: (qmail 30360 invoked by uid 99); 2 Jun 2020 13:09:06 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 02 Jun 2020 13:09:06 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id B9A9A180C6F for ; Tue, 2 Jun 2020 13:09:05 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -0.099 X-Spam-Level: X-Spam-Status: No, score=-0.099 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, DKIM_VALID_EF=-0.1, RCVD_IN_DNSWL_NONE=-0.0001, SPF_HELO_NONE=0.001, SPF_PASS=-0.001, URIBL_BLOCKED=0.001, URI_HEX=0.1] autolearn=disabled Authentication-Results: spamd3-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-he-de.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id 1n1w2BsQEHDU for ; Tue, 2 Jun 2020 13:09:02 +0000 (UTC) Received-SPF: Pass (mailfrom) identity=mailfrom; client-ip=2607:f8b0:4864:20::843; helo=mail-qt1-x843.google.com; envelope-from=cgivre@gmail.com; receiver= Received: from mail-qt1-x843.google.com (mail-qt1-x843.google.com [IPv6:2607:f8b0:4864:20::843]) by mx1-he-de.apache.org (ASF Mail Server at mx1-he-de.apache.org) with ESMTPS id 84B407DCA8 for ; Tue, 2 Jun 2020 13:09:01 +0000 (UTC) Received: by mail-qt1-x843.google.com with SMTP id e16so10553915qtg.0 for ; Tue, 02 Jun 2020 06:09:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:content-transfer-encoding:mime-version:subject:date:references :to:in-reply-to:message-id; bh=1bgHfH58Mh23dO81uHbhG3lzo2xdJkqiGfvTLqUkEuc=; b=pRiGbm/usyX0HFeBpjlX4Q+QoELttP9KOrP3vI/WpgtTWje1F2tgqWUvkFQopVLkjf aPuEvJ34oMs1MZTxstV85GqCgjBO7hcyZ2Zwj/uXp/PV9a4/fUcJzhQwwmNfFAy17y5g PRaFtzsZMDZdXLP+EQbYmt4RhI8FXC3Dp8jrqLo+lT1QB8u4i7vspcsLAVAvPoVGCv/4 3Pd+XUp5Aj+VjvPk5w3ZdKODKpmbGWeTRH/rvgTVNeqvfbaMDxvF+XYHcEWwl+iRU1+A TxCBWGXma9kXG6EmgBlaWSZ//rX9Bc5qYcxAu0vGm6JqlFsbkWWdGph2770uUeORNGt0 fEPA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:content-transfer-encoding:mime-version :subject:date:references:to:in-reply-to:message-id; bh=1bgHfH58Mh23dO81uHbhG3lzo2xdJkqiGfvTLqUkEuc=; b=KQyHXts4sqQ0SDWbYWyrRTD2ffymRUGXSffp3sxgVpHPKEYziprhYt65C6cjyJyZBy MLYepg6AGa/6pfgLseBiv3aIn0MKxAV8u6A0yWq0YcBJapmWYAi5S93qNtA3xhDGmRMm ddCtQMHnjt7bN/lgGUnHEc5v0wZ0LZnrwYZBxjP5VylPnoPyC4EVnYkKmw+yByD43PJ6 7W5YZMZqfnOcu7Fmi9fg8BH2JmRKUXh4+cDBdHgpGGMPAtDCkKXoyETRJgs9z2DnJj/T csmahsoe6myFRoJ2L61lB+eX/K170JurpSVyVlYpCFcuSvb1gyco38n3IF8IXdUR/7Wr Aj/g== X-Gm-Message-State: AOAM532PIs49Glw4s5ArHLzefEnTNzynChM+FZPwJ36OUhXZ9trlLlbI 66E9rTIvq3UF+UqAEAy8e+91fhCruiM= X-Google-Smtp-Source: ABdhPJwDfnWAfCbvzzEkPY8+TVzu5eyWqClz8GcI3VFOrDvZ7DNil+EP7iK+onFEHPev5ClNhyUWOw== X-Received: by 2002:aed:38e4:: with SMTP id k91mr12731796qte.376.1591103339747; Tue, 02 Jun 2020 06:08:59 -0700 (PDT) Received: from [192.168.1.26] (pool-173-64-76-175.bltmmd.fios.verizon.net. [173.64.76.175]) by smtp.gmail.com with ESMTPSA id w10sm2658897qtc.15.2020.06.02.06.08.56 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 02 Jun 2020 06:08:58 -0700 (PDT) From: Charles Givre Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.80.23.2.2\)) Subject: Re: Excesive Execution Time querying RDBMS Date: Tue, 2 Jun 2020 09:08:55 -0400 References: <3fa13856029b1c8229fe78327490eedf2bd3ebce.camel@iti.es> <8ef3ef5c321698dfbce940e27e71fc391a1cdda8.camel@iti.es> <66acb79d06606734bb0387f663d1c501309bfd6a.camel@iti.es> To: user@drill.apache.org In-Reply-To: Message-Id: <2412A7C9-7587-4617-A7ED-774CEEEBA163@gmail.com> X-Mailer: Apple Mail (2.3608.80.23.2.2) Hi Marc,=20 I'm wondering if the limit is not being pushed down to the database or = there is something wrong with the mechanism for that. =20 A few questions: 1. What database are you querying? 2. Can you run the following queries and share the results: EXPLAIN PLAN FOR SELECT * FROM db_scott_centria.public.centria_raw LIMIT = 4 (or any small number) EXPLAIN PLAN FOR SELECT * FROM db_scott_centria.public.centria_raw LIMIT = 10000 (or any large number) Also, if you have access to the query logs from the database, can share = what query Drill actually is transmitting to the database? =20 Thanks, -- C > On Jun 2, 2020, at 1:26 AM, Marc Sole Fonte wrote: >=20 > Hello, >=20 > I am updating this as I have still not received any answer. I hope you = can help me. >=20 > Thank you very much, > Marc >=20 > On Mon, 2020-05-25 at 14:39 +0200, Marc Sol=C3=A9 wrote: > Hello, >=20 > So we finally updated to 1.17.0 and it improved a lot our times but a = new problem appeared. It looks like it is related to limits. >=20 > If we execute a simple query it now takes a lot of less time. It is = acceptable and it is not a problem anymore. However, if you use a limit = in that query (+4) it starts to take a lot of execution time. It is just = that weird. It looks like if we use big limits (like 10000) then times = are acceptable again. Here is a table: >=20 > Time User Query State Duration Foreman > 05/25/2020 11:16:50 anonymous SELECT * FROM = db_scott_centria.public.centria_raw LIMIT = 10000 Succeeded 1.990 sec = 6669a55236df > 05/25/2020 11:15:31 anonymous SELECT * FROM = db_scott_centria.public.centria_raw LIMIT = 4 Succeeded 29.999 sec = 6669a55236df > 05/25/2020 11:15:22 anonymous SELECT * FROM = db_scott_centria.public.centria_raw LIMIT = 3 Succeeded 1.332 sec = 6669a55236df > 05/25/2020 11:14:41 anonymous SELECT * FROM = db_scott_centria.public.centria_raw LIMIT = 4 Succeeded 29.612 sec = 6669a55236df > 05/25/2020 11:13:50 anonymous SELECT * FROM = db_scott_centria.public.centria_raw LIMIT = 5 Succeeded 30.446 sec = 6669a55236df > 05/25/2020 11:12:58 anonymous SELECT * FROM = db_scott_centria.public.centria_raw LIMIT = 7 Succeeded 30.343 sec = 6669a55236df > 05/25/2020 11:12:43 anonymous SELECT * FROM = db_scott_centria.public.centria_raw LIMIT = 3 Succeeded 0.519 sec = 6669a55236df > 05/25/2020 11:12:23 anonymous SELECT * FROM = db_scott_centria.public.centria_raw LIMIT = 2 Succeeded 0.527 sec = 6669a55236df > 05/25/2020 11:11:55 anonymous SELECT * FROM = db_scott_centria.public.centria_raw LIMIT = 1 Succeeded 0.622 sec = 6669a55236df > 05/25/2020 11:11:10 anonymous SELECT * FROM = db_scott_centria.public.centria_raw LIMIT = 10 Succeeded 32.111 sec = 6669a55236df >=20 > It just does not make sense for us. When duration is big we are = talking about execution time. Database works correctly if accedesed from = without making use of Drill. >=20 > Any idea about how to solve it? >=20 > Thank you again, > Marc >=20 > On Thu, 2020-05-14 at 14:39 +0000, Marc Sole Fonte wrote: > Hello, >=20 > Still unable to answer to your asnwers. I think I already fixed it for = the following ones. >=20 > I am going to try to use 1.17.0, it is not exactly easy for us, as it = is part of a bigger project, but I can try. >=20 > Anything else I can have in mind to check? I have seen there are a lot = of tunning options. >=20 > Thank you, > Marc >=20 > On Thu, 2020-05-14 at 15:39 +0200, Marc Sol=C3=A9 wrote: > Hello, >=20 > I have seen an answer but I am not able to answer it directly. The = Drill version I am using is 1.16.0. >=20 > The transcript for the images is more or less the following one: >=20 > Planning Queued Execution Total > 0.219 sec 0.070 sec 15.166 sec 15.455 sec > 0.186 sec 0.036 sec 16.944 sec 17.166 sec >=20 > If you need more data, feel free to ask for it. Thank you for your = help. >=20 > Marc >=20 >=20 > On Thu, 2020-05-14 at 12:50 +0200, Marc Sol=C3=A9 wrote: > Hello, >=20 > First of all, I hope you and your families are all safe and healthy. = Also, thank you in advance for your support. >=20 > I have a problem with Apache Drill conected to CockroachDB = (PostgreSQL) using the RDBMS Storage Plugin. It works, but it is = extremely slow. My experience with Drill has showed me a lot of = scenarios where the first query is slow, due to planification, but the = next ones are acceptable, assuming the time consumed by the man in the = middle. However, this does not seem normal at all. >=20 > Running a small and simple query like this one: SELECT * FROM = db_scott_centria.public.centria_raw LIMIT 1, execution times are = completely over the limit. It takes about 15 seconds of Execution Time. = Both Apache Drill and the database are in the same network, and a query = from terminal from the same takes only some milliseconds. Where is the = problem? What can I do to improve that time? >=20 > Here are some pictures with examples: >=20 > [cid:97eb2ea0977350cc3ca8583db031c2da9c332c32.camel@iti.es] >=20 > [cid:75d46626b4961aace77f136325391a8fb7002d04.camel@iti.es] >=20 > Thank you very much for your help, I hope you can help me. >=20 > Marc