Skip to Main Content
  • Questions
  • Multi-tenancy database vs. limited disk read throughput

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Martin.

Asked: February 27, 2017 - 5:36 pm UTC

Last updated: March 07, 2017 - 2:14 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi AskTom team,

I have relatively small SaaS app with "poor man" multi-tenancy database architecture, there is an tenantId field in each table and application server properly generates SQL queries with tenantId in the where clause.

There are thousands of tenants and the largest table has millions of rows.

The database is running on the IaaS cloud platform with limited disk read throughput ~30 MBps.

Now, I have ran into (I think) plan optimization problems. For some tenants (about tens of thousands of rows) ALL_ROWS based optimizer chooses FULL SCAN plan, that may makes sense because plan based on tenantId index has a bit more cost. But FULL SCAN causes much more disk reads and many of these concurrently running SQL queries usually increase read throughput to the platform limit.

I have tried to use RULE based optimizer, which works better but it's not optimal too, it increases CPU usage and the plan isn't good in many other cases.

What's your advice in this scenario?

Thanks a lot.

Martin

and Connor said...

Some options to consider

a) If its just a few SQL's with poor plans, lock them down with sql plan management or profiles.

b) Are you able to use partitioning to get a physical separation between tenants

c) If not (a), then partitioning views to separate perhaps just the largest tenants out into their own segments.

d) Switching to first_rows_n to lean more strongly toward index scans

e) Manipulating system stats (dbms_stats.set_system_stats) to lean more strongly toward index scans


For me, (a) seems a relatively low risk stop gap solution, and (b) seems a potential longer course of action given your current setup, but that has licensing implications. All options would need to be carefully benchmarked/tested to ensure against regression

Others welcome to add their views.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Random ramblings

Racer I., February 28, 2017 - 12:05 pm UTC

Assuming the tables have some additional key, say customer or order, they should have indexes like (tenant,customer) or (tenant,order) and queries should provide both. I think per default the analyzer will provide histograms per column, so there will be big and small tenants, but you can tell it to compute extended heuristics (multi-column). Then all queries should be considered small (say orders per customer) and it should always go for the index.

https://blogs.oracle.com/optimizer/entry/how_does_the_method_opt

exec dbms_stats.gather_tables_stats('user', 'table', method_op => 'FOR ALL COLUMNS SIZE 254 FOR COLUMNS SIZE 254 (tenant,customer)');

Caveat : never tried it myself.

Alternatively you can gather statistics without a histogram on the tenant column. Of course Oracle can then decide to use FULL SCANS for all tentants.

Regards,

Racer I.

Cheating with statistics worked like a charm

Martin Schayna, March 06, 2017 - 12:26 pm UTC

After some poking around (except partitioning due to licensing issues), I found cheating statistics as a super useful. I ended up with:

- create additional index per tenantId + timeStart (very common column in the where clause of almost all queries)

- disable histogram for tenantId index, i.e.

dbms_stats.gather_table_stats('USER', 'TABLE', method_opt => 'FOR ALL COLUMNS SIZE 254 FOR COLUMNS SIZE 1 TENANTID'
);

All queries have meaningful plan and performance, disk read throughput decreased and users are happy now.

Many thanks to both of you!

Martin

Connor McDonald
March 07, 2017 - 2:14 am UTC

Glad to hear about the good outcome

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.