Query optimization in a multi-tenant database system

• US 8,732,157 B2
• Filed: 03/31/2010
• Issued: 05/20/2014
• Est. Priority Date: 09/23/2003
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Weissman, Craig, Moellenhoff, Dave, Wong, Simon, Nakada, Paul
• Primary Examiner(s): PHAM, KHANH B
• Application Number:
  US12/752,098
• Publication Number:
  US 20100191719A1
• Time in Patent Office: 1,511 Days
• Field of Search: 707790-793, 707/713
• US Class Current: 707/713
• CPC Class Codes: G06F 16/2453 Query optimisation
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries
• CPC Class Codes: G06F 21/6218 to a system of files or objects, e.g. local or distributed file system or database
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99939 Privileged access

Abstract

The present invention provides techniques for managing a multi-tenant database system. In one embodiment the method includes providing access to data stored in a multi-tenant data structure that is implemented by storing data for multiple tenants on a pool of computer servers defining the multi-tenant data structure that having a plurality of data columns and one or more index columns. The one or more index columns define a first data field for a first tenant of the multi-tenant data structure and a second data field for a second tenant of the multi-tenant data structure. The first field indicates a first data type and the second field indicates a second data type, wherein the second data type is different than the first data type. Once created, records having data values in the first and second fields are stored to a single column in the data structure. The single column includes data values having different data types for different tenants in the multi-tenant data structure. Operations are monitored so that one or more requests to access the multi-tenant data structure among are distributing the pool of computer servers to maintain load balancing among the servers of the pool.

Claims

1. A method for load sharing incoming requests received at a Multitenant Database System, the method comprising:

generating tenant-level statistics for each of a plurality of tenants having data stored within a database system of the Multitenant Database System, wherein the database system includes one or more data tables to store the data, each data table having one or more columns defining data categories and one or more rows associated with one or more tenants among the plurality of tenants having data stored within the data tables;

receiving the incoming requests from user systems over a public Internet network at a web-based customer relationship management (CRM) interface of the Multitenant Database System;

load sharing the requests received amongst a plurality of servers within the Multitenant Database System to balance loads via distribution of the requests among the plurality of servers within the Multitenant Database System; and

wherein the distribution of the requests among the plurality of servers comprises each one of the plurality of servers having been distributed one of the requests performing at least the following operations;

(i) generating one or more queries designed to access the information requested, and

(ii) optimizing the one or more queries based on the tenant-level statistics to increase system performance for an individual tenant among the plurality of tenants whose data is being accessed.

2. The method of claim 1, wherein each of the plurality of servers within the Multitenant Database System has access to tenant data stored by the Multitenant Database System for at least the user systems having requests received at the web-based CRM interface of the Multitenant Database System.

3. The method of claim 1, wherein tenant data is arranged so that data of one tenant is kept separate from tenant data of other tenants, wherein the one tenant does not have access to another tenant'"'"'s data unless the tenant data of the one tenant is expressly shared.

4. The method of claim 1:

wherein each of the user systems is one of a desktop personal computer, a workstation, a laptop, a Personal Digital Assistant (PDA), a cell phone, or a wireless device;

wherein each of the user systems communicate with the web-based CRM interface of the Multitenant Database System via HTTP requests via one of a browser, an HTTP client, or an Internet client; and

wherein load sharing the requests received comprises a load balancer of the Multitenant Database System to balance loads and distribute the HTTP requests evenly over a plurality of application servers.

5. The method of claim 4, wherein each of the user systems communicate through a browsing program running as a client of the user system.

6. The method of claim 1, further comprising:

providing, via the web-based CRM interface, web pages, forms, data, and media content to the user systems to support access by the user systems to the Multitenant Database System as tenants of the Multitenant Database System.

7. The method of claim 6, wherein providing the web pages, forms, data, and media content to the user systems further comprises providing security mechanisms to keep each tenant'"'"'s data separate unless shared.

8. The method of claim 1, wherein receiving communications from user systems comprises using more than one Multitenant Database System located in close proximity to one another within a server farm, within a single building, or within a campus.

9. The method of claim 8, wherein load sharing requests comprises load sharing requests among the more than one Multitenant Database System.

10. The method of claim 1:

wherein receiving communications from user systems comprises using more than one Multitenant Database System distributed at locations remote from one another, each within different cities; and

wherein load sharing requests comprises load sharing requests among the more than one Multitenant Database System.

11. The method of claim 1:

wherein the incoming requests comprise HTTP requests; and

wherein the distribution of the requests among the plurality of servers comprises distributing the HTTP requests via HTTP request distributors to the plurality of servers.

12. The method of claim 1, wherein the Multitenant Database System includes a tenant database divided into individual tenant storage areas via a physical arrangement or a logical arrangement, and wherein the individual tenant storage areas comprises user storage allocated for each respective user.

13. The method of claim 1, wherein load sharing requests comprises implementing a load balancing function to distribute the requests to the plurality of servers within the Multitenant Database System via one of:

a least connections algorithm to route the requests to the plurality of servers;

a round robin load balancing algorithm to route the requests to the plurality of servers; and

an observed response time load balancing algorithm to route the requests to the plurality of servers.

14. The method of claim 13, wherein the load balancing function routes multiple consecutive requests from a same user to different servers among the plurality of servers.

15. The method of claim 1:

wherein each server among the plurality of servers is configured to handle requests for any user or organization;

wherein no server affinity for a user or organization exists for any of the plurality of servers; and

wherein the lack of server affinity supports adding and removing any of the plurality of servers from a server pool at any time and for any reason.

16. A non-transitory medium storing program code that, when executed by a central processing unit of a Multitenant Database System, the program code causes the Multitenant Database System to perform operations for load sharing incoming requests received at the Multitenant Database System, the operations including:

generating tenant-level statistics for each of a plurality of tenants having data stored within a database system of the Multitenant Database System, wherein the database system includes one or more data tables to store the data, each data table having one or more columns defining data categories and one or more rows associated with one or more tenants among the plurality of tenants having data stored within the data tables;

receiving the incoming requests from user systems over a public Internet network at a web-based customer relationship management (CRM) interface of the Multitenant Database System;

load sharing the requests received amongst a plurality of servers within the Multitenant Database System to balance loads via distribution of the requests among the plurality of servers within the Multitenant Database System; and

wherein the distribution of the requests among the plurality of servers comprises each one of the plurality of servers having been distributed one of the requests performing at least the following operations;

(i) generating one or more queries designed to access the information requested, and

(ii) optimizing the one or more queries based on the tenant-level statistics to increase system performance for an individual tenant among the plurality of tenants whose data is being accessed.

17. The non-transitory medium storing program code of claim 16:

wherein each of the user systems is one of a desktop personal computer, a workstation, a laptop, a Personal Digital Assistant (PDA), a cell phone, or a wireless device;

wherein each of the user systems communicate with the web-based CRM interface of the Multitenant Database System via HTTP requests via one of a browser, an HTTP client, or an Internet client; and

wherein load sharing the requests received comprises a load balancer of the Multitenant Database System to balance loads and distribute the HTTP requests evenly over a plurality of application servers.

18. The non-transitory medium storing program code of claim 16, wherein load sharing requests comprises implementing a load balancing function to distribute the requests to the plurality of servers within the Multitenant Database System via one of:

a least connections algorithm to route the requests to the plurality of servers;

a round robin load balancing algorithm to route the requests to the plurality of servers; and

an observed response time load balancing algorithm to route the requests to the plurality of servers.

19. The non-transitory medium storing program code of claim 18, wherein each server among the plurality of servers is configured to handle requests for any user or organization;

wherein no server affinity for a user or organization exists for any of the plurality of servers; and

wherein the lack of server affinity supports adding and removing any of the plurality of servers from a server pool at any time and for any reason.

20. A Multitenant Database System, comprising:

a database system including one or more data tables to store data for a plurality of tenants, each data table having one or more columns defining data categories and one or more rows associated with one or more tenants among the plurality of tenants having data stored within the data tables;

a plurality of servers to store data to and retrieve data from the database system;

a statistics generating module to generate tenant-level statistics for each of the plurality of tenants having data stored within the database system of the Multitenant Database System;

a web-based customer relationship management (CRM) interface to receive the incoming requests from user systems over a public Internet network;

a load balancer to distribute the requests received at the web-based CRM interface amongst the plurality of servers; and

wherein the load balancer to distribute the requests among the plurality of servers comprises each one of the plurality of servers having been distributed one of the requests to perform at least the following operations;

(i) generate one or more queries designed to access the information requested, and

(ii) optimize the one or more queries based on the tenant-level statistics to increase system performance for an individual tenant among the plurality of tenants whose data is being accessed.

21. The Multitenant Database System of claim 20:

wherein each of the user systems is one of a desktop personal computer, a workstation, a laptop, a Personal Digital Assistant (PDA), a cell phone, or a wireless device;

wherein each of the user systems communicate with the web-based CRM interface of the Multitenant Database System via HTTP requests via one of a browser, an HTTP client, or an Internet client; and

wherein the load balancer to distribute the requests comprises the load balancer to balance loads and distribute the HTTP requests evenly over a plurality of application servers.

22. The Multitenant Database System of claim 20:

wherein the load balancer to distribute the requests comprises the load balancer to implement a load balancing function to distribute the requests to the plurality of servers within the Multitenant Database System via one of;

(i) a least connections algorithm to route the requests to the plurality of servers, (ii) a round robin load balancing algorithm to route the requests to the plurality of servers, and (iii) an observed response time load balancing algorithm to route the requests to the plurality of servers;

wherein each server among the plurality of servers is configured to handle requests for any user or organization with no server affinity by any user or any organization to any one of the plurality of servers; and

wherein the lack of server affinity supports adding and removing any of the plurality of servers from a server pool at any time and for any reason.