Low query performance - load interconnect interface

sergey_h

Hi,
Need help, I can not find the cause of abnormal behavior.
I have two same 6 nodes cluster :
1 cluster - Vertica 8.1.1.2 CentOS 6.9
2 cluster - Vertica 8.1.1.9 CentOS 7.3
I run test query, result for 1 cluster ~6 sec, for 2 cluster ~ 67 sec.
EXPLAIN for sql does not differ.

In the monitoring system, when I run the query, I see that for the 2 cluster there is a load on the network 30-70MB/s on the interconnect interface.
On 1 cluster this behavior is not observed.
How can I find the reason for this behavior?

Jim_Knicely

Hi,

What do you get when you run the following query on each cluster?

SELECT controlmode FROM vs_global_settings;

Are the underlying table projections the same on each cluster (sort order / segmentation)?

sergey_h

Hi,
all cluster - broadcast
Yes, on all cluster same sort order / segmentation

Sharon_Cutter

The network observation may be a red herring.

• Are all queries slower on the 2nd cluster or just this test query?

• What sort of query are you executing? Simple select with predicates? Joins? Group Bys?

• Are these timing results consistent on multiple runs? Are both clusters idle - no other queries running when you execute the test query?

• Confirm that there isn't any queueing for the query on the 2nd cluster (RESOURCE_ACQUISITIONS)

• Are the resource pool settings the same? (Check RESOURCE_POOLS and RESOURCE_POOL_STATUS)

• Is the time for executing EXPLAIN the same on both clusters?

• PROFILE the query on both systems and compare the output. I'd start with comparing the "execution time (us)" and "clock time (us)" values and the number of threads for each operator.

  --Sharon