Reply
Explorer
Posts: 34
Registered: ‎07-17-2017

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

[ Edited ]

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.

Cloudera Employee
Posts: 212
Registered: ‎03-23-2015

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

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.
Explorer
Posts: 34
Registered: ‎07-17-2017

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

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.

Cloudera Employee
Posts: 212
Registered: ‎03-23-2015

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

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-I...

and search for RowsFetchedPerBlock.
Explorer
Posts: 34
Registered: ‎07-17-2017

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

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).

Explorer
Posts: 34
Registered: ‎07-17-2017

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

[ Edited ]

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) !!!

Cloudera Employee
Posts: 212
Registered: ‎03-23-2015

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

Are you able to share the full PROFILE output of the query for review? That queries through OBDC driver?
Cloudera Employee
Posts: 212
Registered: ‎03-23-2015

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

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.
Explorer
Posts: 34
Registered: ‎07-17-2017

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

Announcements