Posts: 1
Registered: ‎11-22-2019

DB Structure for Large Scale SAAS CRM

We are working on an early stage CRM Platform and we are currently suffering from slow request and database overload on bussiness inteligence and reporting pages,

I don't think we can continue working doing live request on database on potentially over 200K Leads and show results on real time.

What are the best practices for scaling? do we absolutly need to precalculate nightly all the reports?

Any help will be apreciated,

Thank you

Posts: 1
Registered: ‎08-12-2020

Re: DB Structure for Large Scale SAAS CRM

Look at implementing a star schema. It's a dimensional data model. This is typically used for BI and reporting because it's as fast as can be for response time.

The trade off is it is slower with inserts and the code doing the inserts has to be more complex than the code for inserts into a normalized data model.

As soon as you said business intelligence, I wanted you off of a normalized schema. An analyst running iterative queries based on the prior query's results to build some understanding of the market needs the fastest response time possible or they lose their train of thought (along with their patience!).

Before you go there, be sure your SQL is not crap SQL. Enable the slow_log and look at the explain plans for the top five slowest queries. Your problem could be as simple as a missing index.

Then, if your database is being updated constantly, check for wait lock contention. If the db responds fast to a well tuned, complex query in a non-production environment but shitty slow in production, it could be the mixed load.

A cheap way to remediate that is to set up a read only slave and run all your reports on that.