Unbalanced CPU consumption
Hello,
I recently setup a 3 nodes cluster, created my data model, without applying any particular tuning of any sort, just using all default database settings, no additional projections.
I am now running regular queries on this cluster, and I am observing that, out of my 3 nodes, only 2 seems quite heavily busy (between 400% and 500% CPU) when queries are running, while the 3rd one is almost idle (never more than 100%).
This is has been true for the last 12 hours.
I used to run this same set of queries on this same data model before, on other clustesr, and never observed this.
Any idea about possible reasons for this ?
Thans for helping
0
Comments
Hello,
This could be bacause of data not been arranged properly. It seems the queries you are passing have the data only in 2 nodes. you can try creating comprehensive projections if you are using ad-hoc queries. otherwise you can try creating query specific projection with all node segments.
Regards,
Raj
I just tried to run the database designer, in order to optimize the most time consuming queries.
I am not observing any changes.
You said that data are not arranged properly, how can I check this, and how can I fix it ?
Hi again
I have now optimized all the queries I could, and checked that all projections have actually been created by the DB Designer.
First observation: no change in CPU consumption, still 2 nodes doing 90% of the job.
Second: Strictly now improvement in query execution time.
Additionnal question: since the initial full projections are still here, how can I be sure that the new projections are actually used ?
Hi Luc_Lecoq,
Are you able to profile the query through MC? The Query Plan tool will highlight which nodes are doing more work, and can tell you which projections have been used.
For instance, to check for disparity between nodes, you can see if one of the nodes is scanning more than the others:
For large plans, the analysis tool will automatically point out the skews for you:
The 'View Projection Metadata' button will show you all the projections used, and if any of those are skewed:
Here is the result for one of the queries:
Obviously, for this one, node_0002 (the one that "does not work") looks less busy.
Other queries are showing similar results.
Not sure what is the conclusion here ...?
Then, regarding the projection in use, this looks OK
Sorry, previous one was incomplete.
What kind of conclusion can we get from this ??
Hi Luc_Lecocq,
I am unable to see the images you're inserted (they all come out as the warning triangles to me). Can you try adding them as attachments instead?
Looking info your execution plan , you can see that node 1 do less proccesing for part of the tables .
If your data modle is star schema , you need to make sure you dim tables projections are of unsegmented (duplicated cross the nodes) ,this can improved your cluster unlization
Hi,
Here are the images.