10mi rows with 5 self join takes 57s in free trial license

Help Query Tuning
1

I am using the free licenses with 4 units used (which provides only 1 aggregator and 1 lead node), and I have put 128gb of RAM in total and 48 CPUs. I have a table with 10mi rows and I scan that 5 times with self join and it takes around 57s for the results to come, and I see that the CPU usage is only 24% when the query runs. I am trying to get the performance in milli seconds if possible. Please advise on what are the changes that I should consider.

1 Like
2

Hi @anurag246 . I would recommend you to change the cluster to have 4 leaf nodes with 1 license unit per node. If possible, also use 2 aggregators. If you can share mode details about the schema and the query being executed, that can help to identify the issue.

2 Likes
3

Thank you @vkruoso for responding. Do you know if we can create a singlestore instance with 2 aggregator and 4 leaf nodes with the below Docker - We are trying to install on Linux box using the Free License - 4 Units Capacity that Singlestore provides. We can directly install on Linux box instead of Docker, but thought that we will need to do that to make sure we are able to install with a reduced CPU and RAM that is allowed for this free license, as the host has got 96 CPUs and 380 GB RAM.

4

The docs for cluster in a box:

If you want to extract performance, it is not recommended to use the cluster in a box approach. This is for fast prototype and use in CI environments. If you have such large host, I would suggest creating VMs with exactly the unit size, or creating multiple smaller hosts instead of this large one.

3 Likes
5

@vkruoso That’s a great suggestion. I will try creating VMs of exactly size, and even creating multiple smaller hosts. Would you have some documentation which will help me create that, I am new to Singlestore and creating the host environment, so it would be helpful.

Also, Should I plan to allocate all the 96 CPS and 380 GB RAM to singlestore, for e.g. if there are 2 aggregator and 4 lead nodes, so do I distribute the infrastructure evenly across these 6 instances - say 16 CPS and 60 GB RAM each?

6

As per the docs:

The license unit is 8 vCPU or less and 32 GB RAM or less.

Note that the aggregators do not count on the license. Only the leafs. I would recommend 4 nodes of 8 CPUs ad 32Gb each. The aggregators can be half of that.

For installation help follow the guidelines here:

2 Likes
7

Hi,
I’ve done some benchmarks using SSDB cluster in a box and was getting great performance, all queries under 3 seconds. Can you share your tables DDL? I’m curious what keys you’ve used, how you’re joining.

1 Like
8

Thank You, I will try changing the number of aggregator/leaf configurations as per these suggestions. Right now I changed the table function to mpsql (stored procedure) and changed column storage to row storage and I was able to get the expected performance as I was able to change 4 full table scan on a table of 10mi rows to a single full table scan and 3 index scan and it reduced the query run time from 50s to <2s. However I need to connect to singlestore using mysql, and it keeps giving me permission denied even though I’m using the singlestore root password in this command. (SingleStoreDB Cloud · SingleStore Documentation - See documentation for “Third Party Client Application”)

cslucano@baremetal02:/var/lib$ mysql -u root -h 127.0.0.1 -P 3306 --prompt="memsql> " -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
cslucano@baremetal02:/var/lib$ ls -ltrd docker
drwxrwxrwx 13 root root 4096 Dec  7 23:47 docker

I am showing the above screenshot as I thought the issue was with the permission in the docker folder, and I changed the permission to 777, but that also didn’t help. Please advise.

9

Thanks for the additional information anurag246. I’m sorry you are experiencing this complication. I have escalated this, and we are looking for an expert to assist you. Thank you for your patience.

10

@MariaSilverhardt Any help would be highly appreciated, Thank You!

11

Hey Anurag!
Based on the error message, it looks like you can connect to the DB but it’s likely a password issue.

  1. Please check your settings.
    See what you set ${ROOT_PASSWORD} variable to by doing echo ${ROOT_PASSWORD} .
  2. Once you do that, please retry with the password.

If the password fix doesn’t work. Here’s a previous forum post that could help. Unable to connect after changing the Root password

Does this resolve the issue?