why query profiled twice
Hi Team,
I have experiencing huge performance issue for concurrent user and as well as by increasing the number of nodes to our existing 6 node cluster.
Simple query which is running for 20 to 25 sec's for single user is taking around 5 times(60-70 sec's) for the 5 concurrent Users and more than 20 time(430- 450 sec's) for 10 concurrent users. And sometimes out of 10 users, more than 5 users will get a error message "Join did not fit in memory"
Then I got to know that, vertica 7 has a bug where the query is profiled twice to get the complete result. And sometime if the query profiled more than 2 times will get a error message Join did not fit in memory.
Any help will be appreciate.
Regards
--Noor
I have experiencing huge performance issue for concurrent user and as well as by increasing the number of nodes to our existing 6 node cluster.
Simple query which is running for 20 to 25 sec's for single user is taking around 5 times(60-70 sec's) for the 5 concurrent Users and more than 20 time(430- 450 sec's) for 10 concurrent users. And sometimes out of 10 users, more than 5 users will get a error message "Join did not fit in memory"
Then I got to know that, vertica 7 has a bug where the query is profiled twice to get the complete result. And sometime if the query profiled more than 2 times will get a error message Join did not fit in memory.
Any help will be appreciate.
Regards
--Noor
0
Comments
JOIN didn't fit in memory error does come in case of a JOIN operation where HASH join is performed. If inner table in HASH join is not able to fit in the available memory, you see such type of errors. JOINS are spilled to Disk in such case automatically for next execution of the query, which can degrade performance on such joins.
In other case if statistics of involved tables in JOIN are not upto date, vertica can get wron information regarding size of a table & can put bigger table as inner table in HASH join, which automatically will cause such errors.
On other hand queries taking time with Higher Concurrency of users can be because of wrong resource allocation for RESOURCE POOLS in your db. Check for PLANNED CONCURRENCY settings in RESOURCE_POOLS table & set it according to your planning for concurrent users those will access the system.
PLANNED CONCURRENCY settings will budget memory accordingly , out of available memory to each concurrent user for a resource pool. This value also is dependent on number of CPU cores present in each node & should not be more than that , to get appropriate performance.
Refer to below doc link for more understanding on RESOURCE POOL parameter settings:
https://my.vertica.com/docs/6.1.x/HTML/index.htm#14133.htm
I hope it might help
Regards'
Abhishek