QUERY OPTIMIZATION IN A MULTI-TENANT DATABASE SYSTEM

• US 20100191719A1
• Filed: 03/31/2010
• Published: 07/29/2010
• Est. Priority Date: 09/23/2003
• Status: Active Grant

• Current Assignee: Salesforce.com, Inc.
• Original Assignee: Salesforce.com, Inc.
• Inventors: Weissman, Craig, Nakada, Paul, Wong, Simon, Moellenhoff, Dave
• Granted Patent: US 8,732,157 B2
• Time in Patent Office: Days
• US Class Current: 707/716
• 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 managing a multi-tenant database system, the method comprising:

providing access to data stored in a multi-tenant data structure, the multi-tenant data structure being implemented on a pool of computer servers, the multi-tenant data structure further being implemented by;

storing data for multiple tenants in the multi-tenant data structure;

defining the multi-tenant data structure to have a plurality of data columns and one or more index columns;

defining a first data field for a first tenant of the multi-tenant data structure, the first field indicating a first data type;

defining a second data field for a second tenant of the multi-tenant data structure, the second field indicating a second data type, wherein the second data type is different than the first data type;

when records having data values in the first and second fields are created by the first and second tenants, storing the data values of first and second fields to a single column in the data structure, wherein the single column includes data values having different data types for different tenants in the multi-tenant data structure;

receiving one or more requests to access data in the multi-tenant data structure;

monitoring operations of the pool of servers; and

distributing the one or more requests to access the multi-tenant data structure among the pool of computer servers in response to the monitored operations.

2. The method as recited in claim 1 wherein distributing further includes distributing requests for access to the information from the multiple users among multiple computer servers of the pool in response to a monitored response-time of the multi-tenant database.

3. The method as recited in claim 1 wherein distributing further includes distributing requests for access to the information from the multiple users among multiple computer servers of the pool in response to results of a load balancing algorithm.

4. The method as recited in claim 1 wherein monitoring further includes identifying multiple requests from a common user and distributing further includes distributing the multiple requests to different computer servers of the pool of computer servers.

5. The method as recited in claim 1 wherein monitoring further includes identifying multiple requests from first and second groups of the multiple users and distributing further includes distributing multiple requests from the first and second groups to a common server.

6. The method as recited in claim 1 wherein monitoring further includes identifying multiple requests from first and second groups of the multiple users and distributing further includes distributing a first set of multiple requests from the first group to a first computer server of the server pool and distributing a second set of multiple requests from the first group and the multiple requests of the second group to a common server.

7. The method as recited in claim 1 wherein providing further includes including in the pool of computer servers a set of virtual machines configured as application servers, and monitoring further includes identifying multiple requests from a common user and distributing further includes distributing the multiple requests to different virtual machines included in the pool of computer servers.

8. A machine-readable medium carrying one or more sequences of instructions for managing a multi-tenant database system when executed by one or more processors and cause the one or more processors to carry out steps of:

providing access to data stored in a multi-tenant data structure, the multi-tenant data structure being implemented on a pool of computer servers, the multi-tenant data structure further being implemented by;

storing data for multiple tenants in the multi-tenant data structure;

defining the multi-tenant data structure to have a plurality of data columns and one or more index columns;

defining a first data field for a first tenant of the multi-tenant data structure, the first field indicating a first data type;

defining a second data field for a second tenant of the multi-tenant data structure, the second field indicating a second data type, wherein the second data type is different than the first data type;

when records having data values in the first and second fields are created by the first and second tenants, storing the data values of first and second fields to a single column in the data structure, wherein the single column includes data values having different data types for different tenants in the multi-tenant data structure;

receiving one or more requests to access data in the multi-tenant data structure;

monitoring operations of the pool of servers; and

distributing the one or more requests to access the multi-tenant data structure among the pool of computer servers in response to the monitored operations.

9. The machine readable medium of claim 8 wherein the instructions to carry out the step of distributing further includes instructions to carry out a step of distributing requests for access to the information from the multiple users among multiple computer servers of the pool in response to a monitored response-time of the multi-tenant database.

10. The machine readable medium of claim 8 wherein the instructions to carry out the step of distributing further includes instructions to carry out a step of distributing requests for access to the information from the multiple users among multiple computer servers of the pool in response to results of a load balancing algorithm.

11. The machine readable medium of claim 8 wherein the instructions to carry out the step of monitoring further includes instructions to carry out a step of identifying multiple requests from a common user and the instructions to carry out the step of distributing further includes instructions to carry out a step of distributing the multiple requests to different computer servers of the pool of computer servers.

12. The machine readable medium of claim 8 wherein the instructions to carry out the step of monitoring further includes instructions to carry out a step of identifying multiple requests from first and second groups of the multiple users and the instructions to carry out the step of distributing further includes instructions to carry out a step of distributing multiple requests from the first and second groups to a common server.

13. The machine readable medium of claim 8 wherein the instructions to carry out the step of monitoring further includes instructions to carry out a step of identifying multiple requests from first and second groups of the multiple users and the instructions to carry out the step of distributing further includes instructions to carry out a step of distributing a first set of multiple requests from the first group to a first computer server of the server pool and distributing a second set of multiple requests from the first group and the multiple requests of the second group to a common server.

14. A multi-tenant database system comprising:

a pool of servers, each have a processor; and

a memory space in data communication with the pool of servers and having a sequence of instructions which, when executed by the processor, cause the processor to carry out the steps of;

providing access to data stored in a multi-tenant data structure, the multi-tenant data structure being implemented on the pool of computer servers, the multi-tenant data structure further being implemented by;

storing data for multiple tenants in the multi-tenant data structure;

defining the multi-tenant data structure to have a plurality of data columns and one or more index columns;

defining a first data field for a first tenant of the multi-tenant data structure, the first field indicating a first data type;

defining a second data field for a second tenant of the multi-tenant data structure, the second field indicating a second data type, wherein the second data type is different than the first data type;

when records having data values in the first and second fields are created by the first and second tenants, storing the data values of first and second fields to a single column in the data structure, wherein the single column includes data values having different data types for different tenants in the multi-tenant data structure;

receiving one or more requests to access data in the multi-tenant data structure;

monitoring operations of the pool of servers; and

distributing the one or more requests to access the multi-tenant data structure among the pool of computer servers in response to the monitored operations.

15. The data center as recited in claim 14 wherein the sequence of instructions to carry of the step of distributing further includes a sub-routine to carry out a step of distributing requests for access to the information from the multiple users among multiple computer servers of the pool in response to a monitored response-time of the multi-tenant database.

16. The data center as recited in claim 14 wherein the sequence of instructions to carry of the step of distributing further includes a sub-routine to carry out a step of distributing requests for access to the information from the multiple users among multiple computer servers of the pool in response to results of a load balancing algorithm.

17. The data center as recited in claim 14 wherein the sequence of instructions to carry of the step of monitoring further includes a sub-routine to carry out a step of identifying multiple requests from a common user and the sequence of instructions to carry of the step of distributing further includes an additional sub-routine to carry out a step of distributing the multiple requests to different computer servers of the pool of computer servers.

18. The data center as recited in claim 14 wherein the sequence of instructions to carry of the step of monitoring further includes a sub-routine to carry out a step of identifying multiple requests from first and second groups of the multiple users and the sequence of instructions to carry of the step of distributing further includes an additional sub-routine to carry out a step of distributing multiple requests from the first and second groups to a common server.

19. The data center as recited in claim 14 wherein the sequence of instructions to carry out the step of monitoring further includes a sub-routine to carry out a step of identifying multiple requests from first and second groups of the multiple users and the sequence of instructions to carry out the step of distributing further includes an additional sub-routine to carry out a step of distributing a first set of multiple requests from the first group to a first computer server of the server pool and distributing a second set of multiple requests from the first group and the multiple requests of the second group to a common server.