Big Tables and Partitioning
5 Machines Cluster
16 GB of RAM and 4 CPU’s on each node.
Storage – regular attached NFS disks. (with enough disk apace)
I have created two big FACT & DIM tables for Multi Tenants usage...
those tables are generic tables for all Tenants Usage.
Two main fields distinct the Tenants : TenentId & EntityId.
Also I've created a simulator that fill-up this tables (of course we have metadata layer as well) the logical schema is sales + and it dimension Products+Stores+Regions….etc
The goals of this POC are:
· MOST Queries response time should be around 1 second
· Find out, the best segmentation and partitioning design for the BIG Table design?
· Find out, how many tenants and rows per tenant can hold one set of FACT+DIM?
· How to decide when to open a new set of FACT+DIM?
· How large amount of long string effect results, and how to solve it?
The FACT table definition include 100 Dates , 100 Numeric(35,6) and 100 Varchar(4096) , and ID (AUTO_INCREMENT) as Primary Key
segmentation by hash(TenantId)
partition by (TenantId||EntityId)
I have 2 main projections for the FACT table (super projection that has all the columns, and small one that include only the relevant columns according to the queries)
Now, starting with 5 Tenants and only one logical table and 1,000,000 per Tenant the results are inconclusive and I have some questions:
1. Is it recommend to have same fields for Segmentation and Partitioning ?
2. Partitioning , partitioning upon the super projection has 1 partition per tenant which is fine.
3. The explain plane do not show the partitioning usage, even if I use TenantId||EntityId in the WHERE Clause , WHY ?
4. However, for the other small projection, it created 1 or 2 partitions per node. – is this normal? I would expect 1 partition per Tenant||Entity?
5. There are 3 aggregated SQL’s , and the response time are 1.5 , 0.5 , 0.39 (second)
6. Adding additional 10Millions rows per Tenant to the FACT increased the number of partitions to 240 and response time to above 2 minutes…WHY?
---------------------------------
Gil Peretz
HP Big Data, DBA
Phone: +972 3 5397019
Cell : +972 54 5597107
Comments
Hi,
To answer your questions ,
1. Is it recommend to have same fields for Segmentation and Partitioning ?
NO, those are completely different properties. Segmentation is a property of a projection and it is a way to distribute the data across nodes. Chose something with high cardinality so the data is not skewed (Vertica is a fast as the slowest node, if one node have more data than other it may take longer to answer the queries). Also chose something that you may do joins with other tables so you have equally segmented tables and the join happen in the node without need to distribute the data. For the same table you can have different projection with different segmentations depending what you need.
Partition is a property of the table and every projection in the table will have the same partition. A partition help vertica to organize the files form the table. The main goal of a partition is (1) remove data in the table (2) partition pruning. Normally a partition is a month of a date field in the table. What helps is if you want to remove the data from table that is older than xx months, you just do drop partition what is very efficient as for Vertica is just go and rm the file that belongs to that partition. Also chose a date that you use in predicates, if you have a where date = xxx. vertica because of the partition can identify the files that may have data and ignore the others what makes a very efficient scan of records.
Those are key elements of design and can really affect the database performance, I will recommend to look more into details in the documentation.
2. Partitioning , partitioning upon the super projection has 1 partition per tenant which is fine.
I am not sure what you mean with that/
3. The explain plane do not show the partitioning usage, even if I use TenantId||EntityId in the WHERE Clause , WHY ?
Not sure what do you mean with that, but anyway the explain plan does not show the use of partition. You can check a table query_events and you should have events types "PARTITIONS_ELIMINATED"
4. However, for the other small projection, it created 1 or 2 partitions per node. – is this normal? I would expect 1 partition per Tenant||Entity?
I am not sure how you check the partitions, but first Tentant|Entity is not a good choice, as I explain in point 1, the partition should be a date field. When you load data vertica will always create a new file then the merge out try to combine those files in one. This is why you may see many files per partition as mergeout may had not combine them yet. I say mergeout "try" to combine files because it will combine in one file the data of inactive partition. The data of active partition, vertica combine the files using an strata algorithm, meaning combine the files that are similar in size. An active partition is the partition that you load data last. This can be confusing but it is important that you do the proper design as a wrong partition key could lead to bad performance and many unnecessary mergeouts.
5. There are 3 aggregated SQL’s , and the response time are 1.5 , 0.5 , 0.39 (second)
What are the aggregates?
6. Adding additional 10Millions rows per Tenant to the FACT increased the number of partitions to 240 and response time to above 2 minutes...WHY?
I think that you have the wrong partition key, but anyway, having too many files does not take advantage of encoding and compression. Remove the partition key that you have and chose a date one or leave it without partition and run DBD on the query that you are executing so DBD recommends you a good order by for the query that you are executing and the query should not be affected for more data. However, it will depend of what data do you have.
This is too much info for just one posting but I hope it helps.
Eugenia