Data store optimizer

• US 10,282,350 B1
• Filed: 06/21/2013
• Issued: 05/07/2019
• Est. Priority Date: 06/21/2013
• Status: Active Grant

• Current Assignee: Amazon Technologies, Inc. (Amazon.com, Inc.)
• Original Assignee: Amazon Technologies, Inc. (Amazon.com, Inc.)
• Inventors: Pathak, Rahul Sharma
• Primary Examiner(s): Lu, Charles E
• Application Number:
  US13/924,271
• Time in Patent Office: 2,146 Days
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/2453 Query optimisation

Abstract

A method is described for monitoring data store performance and recommending a data store structure for a data store. The method may include analyzing a data store structure to determine whether the data store structure corresponds to a set of criteria, analyzing a query executed against the data store structure to determine performance of the query with respect to the data store structure and providing a proposed data store structure to a customer based at least on part on the analysis of the data store structure and analysis of the query.

Claims

1. A computer implemented method, comprising:

performing a plurality of data store queries on data stored in a data store having a first data store structure;

determining whether the first data store structure comprises at least one of;

a table, row, column, data store cluster, or data store cluster node;

determining that at least one of a distribution key, a sort key, a primary key, or a foreign key is not set for the first data store structure;

analyzing the first data store structure to identify a candidate column or row for setting as the at least one of the distribution key, the sort key, the primary key, or the foreign key;

determining query data selected in a majority of the plurality of data store queries on the data stored in the data store; and

implementing a second data store structure for the data store based at least in part on the query data selected in the majority of the plurality of data store queries and based at least in part on at least one constraint of the first data store structure, wherein the second data store structure includes a modification of at least one data store constraint of the first data store structure selected from the group consisting of;

the distribution key, the sort key, the primary key and the foreign key, and wherein implementing the second data store structure improves efficiency of data management.

2. The method as in claim 1, further comprising modifying the distribution key for a data store table of the second data store structure, wherein a column from the data store table is selected as the distribution key.

3. The method as in claim 1, further comprising modifying the sort key for a data store table of the second data store structure, wherein a column from the data store table is selected as the sort key.

4. The method as in claim 1, further comprising modifying compression encoding for at least one column in the second data store structure.

5. The method as in claim 1, further comprising determining whether data is substantially distributed evenly across a distributed data store.

6. The method as in claim 1, further comprising creating the second data store structure using a structured query language (SQL) statement that is generated and executed upon acceptance of a customer.

7. The method as in claim 1, further comprising monitoring data ingested into the data store to determine whether the data adheres to a table definition and space allocated for the data in the data store.

8. The method as in claim 1, further comprising monitoring a table definition for a data store table to determine whether changes made to the table definition correspond to a data store structure criteria.

9. The method as in claim 1, further comprising periodically analyzing a data store structure to determine whether the data store structure is based at least in part on data store structure criteria.

10. The method as in claim 1, further comprising periodically analyzing a data store query to determine whether a data store structure is efficient in relation to execution of the data store query.

11. The method as in claim 1, wherein the data store is a data warehouse or a distributed data store.

12. The method as in claim 1, further comprising:

determining that data compression is not enabled for any columns of the first data store structure;

determining that the data compression is not enabled for any columns of the first data store structure;

analyzing the first data store structure to identify a first data type of data stored in a first one of the columns to identify a first recommended data compression encoding suitable for the first data type; and

analyzing the first data store structure to identify a second data type of data stored in a second one of the columns to identify a second recommended data compression encoding suitable for the second data type;

wherein implementing the second data store structure further comprises implementing the first recommended data compression encoding for the first one of the columns and implementing the second recommended data compression encoding for the second one of the columns.

13. The method as in claim 1, further comprising:

identifying a threshold for columns used in join operations to be frequently used columns;

determining a frequency of how often columns in the first data store structure are used in join operations;

determining whether the frequently used columns have been designated as distribution keys;

designating the frequently used columns as distribution keys as part of implementing the second data store structure when the frequently used columns have not been designated as distribution keys; and

removing designation of columns as distribution keys as part of implementing the second data store structure when the columns are designated in the first data store structure as distribution keys and are not determined to be the frequently used columns.

14. The method as in claim 1, further comprising:

examining a table definition defining what type of data is placed in a column of the first data store structure;

comparing the table definition to data that is ingested into the column to determine whether the data ingested into the column matches the type of data defined in the table definition;

modifying the type of data defined in the table definition to match the data ingested into the column as part of implementing the second data store structure when the data ingested into the column does not match the type of data defined in the table definition; and

re-allocating memory for the column based on modifying the table definition.

15. The method as in claim 1, further comprising:

determining that blocks of data of the first data store structure are frequently moved around between nodes;

determining that query performance will improve by replicating the blocks of data on the nodes;

recommending replicating the blocks of data on the nodes; and

implementing the replicating as part of implementing the second data store structure.

16. The method as in claim 1, further comprising providing a list to a customer specifically identifying queries that will be benefited by implementation of the proposed data store structure, wherein benefits include reducing a number of I/O operations and minimizing memory used to process the queries.

17. The method as in claim 1, wherein the second data store structure includes a modification of at least one data store constraint of the first data store structure selected from the group consisting of:

the sort key, the primary key and the foreign key.

18. A system for adaptively improving data store performance, comprising:

one or more computing nodes, each of which comprises at least one processor and a memory, wherein the one or more computing nodes are configured to collectively implement;

a distributed data store;

a service manager configured to manage a plurality of nodes for the distributed data store;

a performance module configured to execute logic, comprising;

logic that determines whether a data store structure for the distributed data store corresponds to data store structure criteria, wherein the data store structure includes at least one of;

a table, row, column, cluster, or cluster node;

logic that determines that at least one of a distribution key, a sort key, a primary key, or a foreign key is not set for the data store structure;

logic that analyzes the data store structure to identify a candidate column or row for setting as the at least one of the distribution key, the sort key, the primary key, or the foreign key;

logic that determines query data selected in a majority of a plurality of data store queries corresponding to the distributed data store in relation to the data store structure; and

logic that implements a proposed data store structure for the distributed data store based at least in part on the query data selected in the majority of the plurality of data store queries and based at least in part on at least one constraint of the data store structure, wherein the proposed data store structure includes a modification of at least one data store constraint of the data store structure selected from the group consisting of;

the distribution key, the sort key, the primary key and the foreign key, and wherein implementing the proposed data store structure improves efficiency of data management.

19. The system as in claim 18, further comprising:

a client interface module configured to provide a customer with a customer interface to the distributed data store; and

a simulation module configured to execute a computer simulation of the proposed data store structure as applied to the distributed data store and results of the computer simulation are provided to a customer, wherein the computer simulation comprises a graph to display to a customer through the customer interface as a visual representation of improvements available through the proposed data store structure prior to implementation of the structure; and

wherein the performance module further comprises executing logic that builds a SQL statement which, when executed, implements the proposed data store structure upon approval of the proposed data store structure through the customer interface.