How large can the catalog size can be without impacting performance?

Moderator: NorbertKrupa

Post Reply
bbhushan
Newbie
Newbie
Posts: 2
Joined: Thu Sep 03, 2015 9:19 am

How large can the catalog size can be without impacting performance?

Post by bbhushan » Thu Sep 03, 2015 9:25 am

We are trying to implement multi-tenancy in Vertica. One of the options that we are looking at is to create Multiple Schemas: One Schema per Tenant. However, these schemas will only contain the views, which would reference the tables in the unified foundation schema.

Vertica documentation says that "A large number of tenants with their schemas and tables result in a large catalog. A large catalog size results in long catalog lock hold times that affect all the database operations." So how large can the catalog size be without impacting the performance? can adding memory reduce the lock hold times?

Note that any point of time the table updates will only be happening in 1 schema. Can this also result in catalog locks which would cause performance issues with the queries?

Thanks in advance for your help.

Regards,
BBT

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: How large can the catalog size can be without impacting performance?

Post by NorbertKrupa » Thu Sep 03, 2015 2:31 pm

There have been organizations that could not start Vertica because the catalog was too large (multi-tenant). From what you describe, you're only using a single core schema and building views in different schemas for a pseudo multi-tenant environment. It may become a problem if you have too many tables, but otherwise I don't see it being an issue.
Checkout vertica.tips for more Vertica resources.

bbhushan
Newbie
Newbie
Posts: 2
Joined: Thu Sep 03, 2015 9:19 am

Re: How large can the catalog size can be without impacting performance?

Post by bbhushan » Tue Sep 08, 2015 10:51 am

Thank you. This is helpful.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: How large can the catalog size can be without impacting performance?

Post by nnani » Tue Sep 22, 2015 7:55 am

Hi,

The recommended size by Vertica is 4-5 GB per node.

If your catalog goes beyond this limit, you may start experiencing Slowness on the database.

The reason is, Vertica has a single huge Catalog which keeps a track of every single change in the database.The changes include data about objects / system / epochs. Every transaction has to write the commit information to Catalog, if the catalog is large it will take time to write this information.

As you go ahead increasing the catalog size the time taken to write info about the transactions also increases.

Also, similar catalog(with some differences) is present in all nodes.
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1823
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How large can the catalog size can be without impacting performance?

Post by JimKnicely » Thu Oct 15, 2015 7:24 pm

From my understanding, it's recommended to keep the catalog size less than 2GB ;)

If you have a very large catalog, then you may want to take a look at the database design. The catalog size is largely dependent upon the number of tables. Try to to reduce the number of tables in your design by implementing an effective purge/archive strategy, de-normalization and/or table concatenation. Also, I've read where clients report that running "Analyze Statistics" greatly increases the catalog size (Good news here is there is fix for this in Excavator).

With large catalogs you will see issues when restarting the database on a node (as Norbert pointed out) and and more work being required by the locking subsystem (as nnani pointed out).

P.S. I have to confess, one of my first DB designs for Vertica was for a multi-tenant environment. Each tenant (i.e. client) was to have their own schema where I would duplicate all of the objects (tables, views, sequences, etc.) in each schema. We planned to have hundreds of clients in one DB. Wow! That would have been a disaster! Luckily, we were only able to acquire one client :lol:
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “General”