SQL query tuning and optimization: an example using AWS

Količina nastalih informacija svakim se danom povećava, a time rastu i zahtjevi za kapacitetom računalnih resursa kojima se one obrađuju i analiziraju. Rastuću ulogu u obradi podataka ima računarstvo u oblaku. Budući da se korištenje usluga u oblaku uobičajeno temelji na plaćanju po potrošnji, poznavanjem mogućnosti optimizacije i efikasnog korištenja resursa moguće je upravljati željenim performansama i troškovima. U ovom radu opisani su i prikazani na praktičnom primjeru različiti postupci koji se mogu provesti u svrhu optimizacije podataka i SQL upita, a prilikom korištenja usluga u oblaku za rad s velikim podacima. Opisani postupci uključuju odabir formata i provedbu kompresije podataka, ali i razne druge mogućnosti značajne za rad u oblaku, poput particioniranja podataka i planiranja strukture SQL upita. U praktičnom dijelu rada, nad podacima različitih obilježja proveden je niz SQL upita kako bi se usporedile performanse u odnosu na primijenjene postupke i obilježja upita. Temeljem navedenih postupaka i prikupljenih rezultata, može se zaključiti kako postoji veći broj mogućnosti putem kojih se može optimizirati provedba SQL upita. Važnost ovih postupaka sastoji se u njihovom doprinosu učinkovitom i isplativom korištenju usluga u oblaku prilikom rada s velikim podacima.Since the total amount of created data is increasing every day, requirements related to computing capacity needed to process data are also growing. Cloud computing is becoming a rapidly growing resource for data processing. Cloud usage is typically based on a pay-as-you-go model, which is the reason why different optimization possibilities represent a significant factor in performance and cost management. This paper describes and presents an example with various actions that can be performed for the purpose of optimizing data and SQL queries when using cloud services on big data. Described possibilities include choosing the appropriate data format or compression algorithm, but also other characteristics important for cloud environment, such as data partitioning and planning SQL queries structure. Presented practical example includes several SQL queries performed on differently processed data to compare performance against applied procedures and query characteristics. Based on the described features and obtained results, we can conclude there are multiple possibilities to optimize the implementation of SQL queries in cloud services. The importance of these actions arises from their contribution to the efficient and cost-effective usage of cloud services for big data

Machine learning enabled query re-optimization algorithms for cloud database systems

In cloud database systems, hardware configurations, data usage, and workload allocations are continuously changing. These changes make it difficult for the query optimizer to obtain an optimal query execution plan (QEP) for a query based on the data statistics collected before the query execution. In order to optimize a query with a more accurate cost estimation to achieve such a QEP, performing query re-optimizations during the query execution has been proposed in the literature. However, some of the re-optimizations may not provide any gain in terms of query response time or monetary cost and may also have negative impacts on the query performance due to their overheads. This raises the question of how to determine when a re-optimization is beneficial. In addition, a Service Level Agreement (SLA) is signed between users and the cloud. Thus, query re-optimization is multi-objective optimization that minimizes not only query execution time and monetary cost but also SLA violation. However, none of the existing query re-optimization algorithms considers all these three objectives together and none of them can predict when a re-optimization is beneficial. To fill the gap, in this dissertation, four novel query re-optimization algorithms, ReOpt, ReOptML, ReOptRL and SLAReOptRL are proposed. Extensive theoretical and experimental evaluations performed on our proposed techniques showed that each of them has better performance in terms of time, monetary cost, and SLA violation rate than state-of-the-art techniques when applied to the TPC-H database benchmark