System and method for enhancing storage performance in analytical database applications
A system and method for enhancing data throughput in data warehousing environments by connecting multiple servers having local storages with designated external storage systems, such as, for example, those provided by SANs. The system and method may preserve a full reference copy of the data in a protected environment (e.g., on the external storage system) that is fully available. The system and method may enhance overall I/O potential performance and reliability for efficient and reliable system resource utilization.
1. A database system comprising:
a first electronic storage that holds a complete copy of a database, the first electronic storage corresponding to an external storage system having a first data transfer rate to a database server;
a second electronic storage that is separate from the first electronic storage, the second electronic storage holding at least a portion of the database, the second electronic storage corresponding to a local storage of the database server, the second electronic storage having a second data transfer rate to the database server, the second data transfer rate being faster than the first data transfer rate; and
the database server configured to:
receive a database query;
read data requested in the database query in parallel from the first electronic storage and the second electronic storage;
responsive to receipt of the database query, determine a size of a first portion of a set of result data for the database query from the first electronic storage and a size of a second portion of the set of result data for the database query from the second electronic storage based on a data throughput between the database server and the first electronic storage and a data throughput between the database server and the second electronic storage such that a retrieval, by the database server, of the first portion from the first electronic storage and the second portion from the second electronic storage in parallel takes approximately the same time;
configure a first request for the first portion and a second request for the second portion;
send the first request to the first electronic storage and the second request to the second electronic storage, retrieve the first portion from the first electronic storage and the second portion from the second electronic storage in parallel and send a response to the database query based on the first portion and the second portion.
2. The system of claim 1 , wherein the data throughput between the database server and the first electronic storage is measured at or near receipt of the database query.
3. The system of claim 1 , wherein the proportion of the portion of the database stored on the second electronic storage to the whole database is adjusted to account for a change in utilization of the first electronic storage.
4. The system of claim 1 , further comprising:
a second database server remote from the external storage system that is in communication with the first electronic storage to perform transactions with the database copy held on the external storage system;
third electronic storage that is logically and physically separate and discrete from each of the first electronic storage and the second electronic storage, the third electronic storage holding at least a portion of the database,
wherein the portion of the database held on the second electronic storage is determined based on the data throughput between the database server and the first electronic storage and the data throughput between the database server and the second electronic storage, and wherein the portion of the database held on the third electronic storage is determined based on a data throughput between the second database server and the first electronic storage and a data throughput between the second database server and the third electronic storage.
5. The system of claim 4 , wherein the database server determines the portion of the database that is held on the second electronic storage autonomously, and wherein the second database server determines the portion of the database that is held on the third electronic storage autonomously.
6. The system of claim 5 , wherein the database server determines the portion of the database that is held on the second electronic storage further based on one or more of storage capacity of the second electronic storage, performance of the second electronic storage, or availability of the first electronic storage, and wherein the second database server determines the portion of the database that is held on the third electronic storage further based on one or more of storage capacity of the third electronic storage, performance of the third electronics storage, or availability of the first electronic storage.
7. The system of claim 1 , wherein the first electronic storage is associated with a Storage Area Network.
8. The system of claim 1 , wherein the first electronic storage is available to one or more other database servers, and wherein the second electronic storage is direct-attached storage to the database server.
9. A database system comprising:
a database server configured to retrieve data of a database from a first electronic storage and a second electronic storage, the first electronic storage separate from the second electronic storage, the first electronic storage holding a complete copy of the database, and the second electronic storage holding at least a portion of the database, the first electronic storage corresponding to an external storage system having a first data transfer rate to a database server, the second electronic storage corresponding to a local storage of the database server, the second electronic storage having a second data transfer rate to the database server, the second data transfer rate being faster than the first data transfer rate;
the database server configured to:
receive a database query;
read data requested in the database query in parallel from the first electronic storage and the second electronic storage;
responsive to receipt of the database query, determine a size of a first portion of a set of result data for the database query from the first electronic storage and a size of a second portion of the set of result data for the database query from the second electronic storage based on a data throughput between the database server and the first electronic storage and a data throughput between the database server and the second electronic storage such that a retrieval, by the database server, of the first portion from the first electronic storage and the second portion from the second electronic storage in parallel takes approximately the same time;
configure a first request for the first portion and a second request for the second portion;
send the first request to the first electronic storage and the second request to the second electronic storage, retrieve the first portion from the first electronic storage and the second portion from the second electronic storage in parallel and send a response to the database query based on the first portion and the second portion.
10. The system of claim 9 , wherein the database server is further configured to measure the data throughput between the database server and the first electronic storage at or near receipt of the database query.
11. The system of claim 9 , wherein the database server is further configured to adjust the proportion of the portion of the database stored on the second electronic storage to the whole database to account for a change in utilization of the first electronic storage.
12. The system of claim 9 , wherein the first electronic storage is associated with a Storage Area Network.
13. The system of claim 9 , wherein the first electronic storage is available to one or more other database servers, and wherein the second electronic storage is direct-attached storage to the database server.
14. A method for retrieving data from a database in response to a database query comprising:
receiving, by a database server, a database query;
configuring a first request for a first portion of a set of result data for the database query from a first electronic storage that holds a complete copy of the database, the first electronic storage corresponding to an external storage system having a first data transfer rate to a database server;
configuring a second request for a second portion of the set of result data for the database query from a second electronic storage that is separate from the first electronic storage, the second electronic storage holding at least a portion of the database, the second electronic storage corresponding to a local storage of the database server, the second electronic storage having a second data transfer rate to the database server, the second data transfer rate being faster than the first data transfer rate;
responsive to receipt of the database query, determining a size of the first portion and a size of the second portion based on a data throughput between the database server and the first electronic storage and a data throughput between the database server and the second electronic storage such that a retrieval of the first portion from the first electronic storage and the second portion from the second electronic storage in parallel takes approximately the same time;
sending the first request to the first electronic storage and the second request to the second electronic storage, retrieving the first portion from the first electronic storage and the second portion from the second electronic storage in parallel, and sending a response to the database query based on the first portion and the second portion.
15. The method of claim 14 , wherein the data throughput between the database server and the first electronic storage is measured at or near receipt of the database query.
16. The method of claim 14 , wherein the proportion of the portion of the database stored on the second electronic storage to the whole database is adjusted to account for a change in utilization of the first electronic storage.
17. The method of claim 14 , wherein the database server determines the portion of the database that is held on the second electronic storage autonomously.
18. The method of claim 14 , wherein the database server determines the portion of the database that is held on the second electronic storage further based on one or more of storage capacity of the second electronic storage, performance of the second electronic storage, or availability of the first electronic storage.