Unexpected drop of performance under low concurrency
At my company we have our own analytical web service that reads requests from frontend web applications and translate them to SQL queries. We have been testing Vertica as a possible underlying analytical database.
For that we took an example use case with two cubes composed by a fact table with all the dimensions degenerated and a joined date dimension.
The fact tables have around 5 million rows and 20 columns (dimensions). For our test we took 30 frontend request that generates about 300 SQL queries. Each thread executes the 300 queries ten times.
We set up on AWS a three node cluster with r3.4xlarge instances with default Vertica configuration:
Prior to this cluster we tested our use case in a single r3.4xlarge instance.
We were expecting some major improvement in a single thread test execution. That was not the case. "Maybe our queries are very simple and 5M is not that much, so all is happening in ram, hence maintaining the results. Lets go multithread" - we thought.
After running five threads executing the same queries in random order, we registered a 30% improvement comparing single instance vs three instances cluster.
Not bad but wait... comparing single thread vs 5 thread we had a 50% drop in response times in the 3 nodes cluster.
10 threads generated a 250% drop in performance relative to single thread.
I find these results disappointing since we have a three nodes, 16 core each, system and at most 10 concurrent threads (I will not even mention RAM since it was never used more than 6GB).
Questions:
For that we took an example use case with two cubes composed by a fact table with all the dimensions degenerated and a joined date dimension.
The fact tables have around 5 million rows and 20 columns (dimensions). For our test we took 30 frontend request that generates about 300 SQL queries. Each thread executes the 300 queries ten times.
We set up on AWS a three node cluster with r3.4xlarge instances with default Vertica configuration:
- 16 vCores,
- 122 GB RAM,
- 200 GB SSD (4*50GB RAID0 + 15GB System)
Prior to this cluster we tested our use case in a single r3.4xlarge instance.
We were expecting some major improvement in a single thread test execution. That was not the case. "Maybe our queries are very simple and 5M is not that much, so all is happening in ram, hence maintaining the results. Lets go multithread" - we thought.
After running five threads executing the same queries in random order, we registered a 30% improvement comparing single instance vs three instances cluster.
Not bad but wait... comparing single thread vs 5 thread we had a 50% drop in response times in the 3 nodes cluster.
10 threads generated a 250% drop in performance relative to single thread.
I find these results disappointing since we have a three nodes, 16 core each, system and at most 10 concurrent threads (I will not even mention RAM since it was never used more than 6GB).
Questions:
- Considering this scenario should Vertica's performance be affected that significantly as we increase concurrency?
- Can the drop in performance be related with all the queries hitting the same tables at the same time?
0
Comments
I didn't find anything wrong though.
The vcpuperf results look fine to me.
The vnetperf results seem to show some bytes aren't received in udp-throughput above 256MB/s. For a 2048 MB/s rate limit in udp-throughput, there's only an average of 91138730 bytes received out of 843644928 bytes sent. Vertica uses UDP for spread transmission. Have you looked at your retransmit rates in dc_spread_monitor? The tcp-throughput looked acceptable.
The vioperf results are only reporting node0001.
What does your test look like? Are the tests being load balanced, and are they just all SELECT statements?
I'd recommend taking a glance at the scalability and concurrency documentation.
Let's simplify the use case. We did register a drop in performance in a single node environment when we increased concurrency from one user to five users (the response time increased by 100%). No spread/upd/network related issues there.
Is this drop expected given the previous data?