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?
