question Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN in Support Questions

Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

AcharkiMed — Fri, 16 Sep 2022 12:25:49 GMT

Hi,

In NameNode when I run the query via odbc script (php/perl or python), I can fetchAll results (9.2M) in a variable in about 30 seconds, but when I tried with the same script/query on another remote 2 servers, the execution time was in first server 28 min and in second 17 min.

To exclude the assuming that it's a network speed issue, I fetch the result on a file and then I copied it with scp command to the first remote server, and it finished in ~40 seconds.

What I observe in Query info in CM that there is a big different between the *Threads: Network Send Wait Time* values in the 3 queries :

in NN query: 9.40s
in 1st remote server: 16.7m
in 2nd remote server: 26.8m

And also I try an java script with Impala JDBC, but the results in NN are already not stimulate to continue.

But so far I cann't find where is the problem and how can I resolve it.

NB: I'm working on CDH 5.12.0/Impala 2.9.0, I installed Impala ODBC 2.5.37.1014.

Hope you can respode to me ASAP, because this issue is a realy an obstacle of using a cluster that we made a several months to make it.
Thanks in advance.

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

EricL — Wed, 25 Oct 2017 10:55:39 GMT

How big is the file? And if you scp the result file within the cluster, how long does it take?

This is to confirm if there are speed differences between within the cluster and across to the outside of cluster. If there is, how much different.

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

AcharkiMed — Wed, 25 Oct 2017 12:06:56 GMT

Thanks to your reply @EricL

I fetched same result of query on a file (about 500Mb), then I copied it with scp command to the first remote server, and it finished in ~40 seconds.

To elimine the probability that this issue is caused by a network speed, I have been copied the same results (9.2M) in a postgres table and test the fetchAll with postgres driver via unixODBC and it takes about 50 secs.

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

EricL — Thu, 26 Oct 2017 01:41:12 GMT

Hmm, For JDBC and ODBC drivers, there is a setting called RowsFetchedPerBlock, the default value is 10,000. For 9.2M rows, this back and forth might cause the delay. Can you try to increase this value to see if it can help?

See Doc below:
http://www.cloudera.com/documentation/other/connectors/impala-jdbc/latest/Cloudera-JDBC-Driver-for-Impala-Install-Guide.pdf

and search for RowsFetchedPerBlock.

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

AcharkiMed — Thu, 26 Oct 2017 12:06:37 GMT

Hi @EricL

I already increase the RowsFetchedPerBlock and also TSaslTransportBufSize and no improvement :'(
In Additionally, the test of ODBC with postgresql driver is runed with the same value of this proprieties and give a good results (50 secs).

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

AcharkiMed — Tue, 31 Oct 2017 10:42:33 GMT

Remark: I observe in ClouderaODBCDriverforImpala_connection_0.log file, that the PrepareTResultForDataRetrieval in the 2 cases repeats ~150 times, even if the RowsFetchedPerBlock is 10000 (it look like it fixed on 1024) !!!

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

EricL — Wed, 01 Nov 2017 03:24:45 GMT

Are you able to share the full PROFILE output of the query for review? That queries through OBDC driver?

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

EricL — Wed, 01 Nov 2017 04:29:35 GMT

Can you also share the ClouderaODBCDriverforImpala_connection_0.log file?

If PROFILE and log files are too big, are you able to share through Dropbox or other online tools? They should help us understand more on the issue.

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

AcharkiMed — Wed, 01 Nov 2017 17:54:34 GMT

Hi @EricL,

Here is the profile of query and ODBC log files:

NN - ODBC logs - query 200k - 10s - 1.73s without log
Remote Server - ODBC logs - query 200k - 48s - 41s without log

Thanks in advance.

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

AcharkiMed — Wed, 18 Apr 2018 09:27:34 GMT

Hi all,

Finaly and after almost 6 months I have found the solution!

It was always about my 1024 limitition remark, the row batch limitation was from BATCH_SIZE max value (1024), in the last versions (CDH 5.14/Impala 2.11) we have a new effective range is 1-65536.
1-1024: https://www.cloudera.com/documentation/enterprise/5-12-x/topics/impala_batch_size.html
1-65536: https://www.cloudera.com/documentation/enterprise/5-14-x/topics/impala_batch_size.html

So when I increase it throgh a odbc.ini with SSP_BATCH_SIZE I can benifit from increasing the other odbc parameters (RowsFetchedPerBlock / TSaslTransportBufSize) and the rows can be fetched in a seconds (~45 secs) instead of tens of minutes.

Remark: I have been recreated the cluster in 3 different server providers and tested the connections from almost 5 others with different ODBC/JDBC releases etc.. and always I have the same slowness until this update came.
I can not understand why I'm the only on declared this big issue and why no one can answer me, kowning that it's realy depressed to have a good quering engine but a veeeery slow fetch rows!

Any way, thanks all for your replies.

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

Tim Armstrong — Wed, 18 Apr 2018 21:31:36 GMT

In Impala 2.11 we actually capped the max batch_size setting. Before that you could set it to an arbitrarily high value, which could have strange consequences. It's still a bit of a use-at-your-own-risk setting since it can have consequences for memory consumption and performance.

The real fix for this would be https://issues.apache.org/jira/browse/IMPALA-1618. Setting batch_size is just a workaround that may or may not work for you.

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

Bishnup — Fri, 28 Sep 2018 06:29:32 GMT

Can you tell me the way to set the BATCH_SIZE for impala jdbc connection? I tried but it is not working for me.

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

AcharkiMed — Sat, 29 Sep 2018 19:25:55 GMT

Hi @Bishnup

ConfiguringServer-SideProperties
When connecting to a server that is running Impala 2.0 or later, you can use the driver to apply configuration properties to the server by setting the properties in the connection URL.
https://www.cloudera.com/documentation/other/connectors/impala-jdbc/latest/Cloudera-JDBC-Driver-for-Impala-Install-Guide.pdf

Good luck.

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

Bishnup — Mon, 01 Oct 2018 09:30:22 GMT

Hi @AcharkiMed

I tried setting the Batch size in the connection URL but I didn't get any performance boost in the query fetching time. I have posted my usecase in the cloudera forum. Kindly answer my questions :

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

AcharkiMed — Mon, 01 Oct 2018 12:03:14 GMT

Hi,

Please try to change all these 3 params:

TSaslTransportBufSize=4000;
RowsFetchedPerBlock=60536;
SSP_BATCH_SIZE=60536;

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

Bishnup — Mon, 01 Oct 2018 12:23:04 GMT

Hi @AcharkiMed

As you suggested me to set

TSaslTransportBufSize=4000;
RowsFetchedPerBlock=60536;
SSP_BATCH_SIZE=60536;

in the connection URL. I did the changes but i am getting these errors

java.sql.SQLException: [Simba][ImpalaJDBCDriver](500051) ERROR processing query/ statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS, sqlState:H Y000, errorMessage:Invalid query option: SSP_BATCH_SIZE
), Query: SET SSP_BATCH_SIZE=60536.
        at com.cloudera.hivecommon.api.HS2Client.executeStatementInternal(Unknow n Source) ~[Impala-JDBC-41-1.0.0.jar!/:na]

and

java.sql.SQLException: [Simba][ImpalaJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:Invalid query option: TSaslTransportBufSize
), Query: SET TSaslTransportBufSize=4000.

Help me set up the property.

Thank You,

Bishnu

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

AcharkiMed — Sat, 09 Feb 2019 12:29:42 GMT

Hi @Bishnup

If you still have the same problem please try to share with us your URL string.

Re: Impala ODBC/JDBC bad performance - rows fetch is very slow from a remote server compared with NN

AcharkiMed — Sat, 09 Feb 2019 12:47:55 GMT

Hi @Tim Armstrong

While IMPALA-1618 steel open and unresolved, I confirmed that this "workaround" is safe and efficient (I'm using it on a large scope and during more than 9 months) so that this is the only solution I find to solve or -get around- this big problem.

Hope that the main problem will be fixed ASAP.
Thanks for the remark.