Characterizing business intelligence workloads
View Patent ↗One or more embodiments characterize workloads in a data warehouse system. A set of accounting data associated with a data warehouse system comprising at least one database is collected. A set of query concurrency information associated with the database is determined determining based on the set of accounting data. The set of query concurrency information identifies a plurality of queries executed on the database simultaneously and a duration of this execution. A set of processor utilization distribution information associated with the plurality of queries is determined based on the set of accounting data. A set of data access information indicating a quantity of data accessed by each query in the plurality of queries is determined. A workload profile associated with the database is generated based on the set of query concurrency information, the set of processor utilization distribution information, and the set of data access information.
1. A method for characterizing workloads in a data warehouse system, the method comprising:
executing with a processor on an information processing system the following:
collecting a set of accounting data associated with a data warehouse system comprising at least one database;
determining, based on the set of accounting data, a set of query concurrency information associated with the database, the set of query concurrency information identifying, for a plurality of queries and for each of a plurality of time units over a period of time, a number of queries executed on the database simultaneously and a duration of execution of such queries, wherein the set of query concurrency information is determined based on at least comparing a start and end time of each query in the plurality of queries against each a start and end time of each remaining query in the plurality of queries;
determining, based on the set of accounting data, a set of processor utilization distribution information associated with the plurality of queries;
determining, based on the set of accounting data, a set of data access information indicating a quantity of data accessed by each query in the plurality of queries; and
generating a workload profile associated with the database based on the set of query concurrency information, the set of processor utilization distribution information, and the set of data access information.
2. The method of claim 1 , wherein determining the set of query concurrency information further comprises:
counting a number of queries with start times smaller than a given first time unit and with end times larger than the first time unit.
3. The method of claim 1 , wherein determining the set of processor utilization distribution information comprises:
determining, for each query, a processor access time associated with such query; and
creating a sorted list comprising each of the processor access times, wherein each position of the sorted list corresponds to a processor utilization distribution percentile.
4. The method of claim 1 , wherein determining the set of data access information comprises at least:
recording a set of page request counts for each query associated with the database;
multiplying each page request count by a page size of a buffer pool associated with that page;
summing a result of the multiplying across all buffer pools;
determining a page size of each table in the database; and
multiplying a number of active pages in each table by the page size associated with that table.
5. The method of claim 1 , wherein the set of accounting data is collected during a predefined time of day.
6. The method of claim 1 , further comprising:
generating a graph illustrating the set of query concurrency information.
7. The method of claim 1 , wherein determining the set of query concurrency information comprises:
sorting a set of start and end times associated with the plurality of queries.
8. An information processing system for characterizing workloads in a data warehouse system, the system comprising:
a memory;
a processor communicatively coupled to the memory; and
a workload characterizer communicatively coupled to the memory and the processor, the workload characterizer configured to perform a method comprising:
collecting a set of accounting data associated with a data warehouse system comprising at least one database;
determining, based on the set of accounting data, a set of query concurrency information associated with the database, the set of query concurrency information identifying, for a plurality of queries, a number of queries executed on the database simultaneously and a duration of execution of such queries;
determining, based on the set of accounting data, a set of processor utilization distribution information associated with the plurality of queries, wherein determining the set of processor utilization distribution information comprises
determining, for each query, a processor access time associated with such query; and
creating a sorted list comprising each of the processor access times, wherein each position of the sorted list corresponds to a processor utilization distribution percentile;
determining, based on the set of accounting data, a set of data access information indicating a quantity of data accessed by each query in the plurality of queries; and
generating a workload profile associated with the database based on the set of query concurrency information, the set of processor utilization distribution information, and the set of data access information.
9. The information processing system claim 8 , wherein determining the set of query concurrency information comprises:
counting a number of queries with start times smaller than a given first time unit and with end times larger than the first time unit.
10. The information processing system claim 8 , wherein determining the set of data access information comprises at least:
recording a set of page request counts for each query associated with the database;
multiplying each page request count by a page size of a buffer pool associated with that page;
summing a result of the multiplying across all buffer pools;
determining a page size of each table in the database; and
multiplying a number of active pages in each table by the page size associated with that table.
11. The information processing system claim 8 , the method further comprising:
generating a graph illustrating the set of query concurrency information.
12. The information processing system claim 8 , wherein determining the set of query concurrency information comprises:
sorting a set of start and end times associated with the plurality of queries.
13. A computer program product for characterizing workloads in a data warehouse system, the computer program product comprising:
a non-transitory storage medium readable by a processing circuit and storing instructions for execution by the processing circuit for performing a method comprising:
collecting a set of accounting data associated with a data warehouse system comprising at least one database;
determining, based on the set of accounting data, a set of query concurrency information associated with the database, the set of query concurrency information identifying, for a plurality of queries and for each of a plurality of time units over a period of time, a number of queries executed on the database simultaneously and a duration of execution of such queries, wherein the set of query concurrency information is determined based on at least comparing a start and end time of each query in the plurality of queries against each a start and end time of each remaining query in the plurality of queries;
determining, based on the set of accounting data, a set of processor utilization distribution information associated with the plurality of queries;
determining, based on the set of accounting data, a set of data access information indicating a quantity of data accessed by each query in the plurality of queries; and
generating a workload profile associated with the database based on the set of query concurrency information, the set of processor utilization distribution information, and the set of data access information.
14. The computer program product of claim 13 , wherein determining the set of query concurrency information further comprises:
counting a number of queries with start times smaller than a given first time unit and with end times larger than the first time unit.
15. The computer program product of claim 13 , wherein determining the set of processor utilization distribution information comprises:
determining, for each query, a processor access time associated with such query; and
creating a sorted list comprising each of the processor access times, wherein each position of the sorted list corresponds to a processor utilization distribution percentile.
16. The computer program product of claim 13 , wherein determining the set of data access information comprises at least:
recording a set of page request counts for each query associated with the database;
multiplying each page request count by a page size of a buffer pool associated with that page;
summing a result of the multiplying across all buffer pools;
determining a page size of each table in the database; and
multiplying a number of active pages in each table by the page size associated with that table.
17. The computer program product of claim 13 , wherein the set of accounting data is collected during a predefined time of day.
18. The computer program product of claim 13 , the method further comprising:
generating a graph illustrating the set of query concurrency information.
19. The computer program product of claim 13 , wherein determining the set of query concurrency information comprises:
sorting a set of start and end times associated with the plurality of queries.