Open-source relational database systems have become increasingly popular in the cloud era. However, practitioners are often beset with query performance issues. Thus a general-purpose database performance tuning tool independent of the various DBMS kernels becomes desired to lower the bar of using these systems. The first mandatory step in developing such a tool is to design an effective sampling method that collects representative records from different tables. Although one could leverage standard SQL statements and indexes to achieve this, sampling performance and statistical efficiency are not guaranteed when the underlying tables are frequently updated, especially for Sparse Tables where the range of index values is significantly greater than the table size.To this end, we propose a novel Active Sampling algorithm that queries regions more likely to contain data records from Sparse Tables. It relies on Gaussian process regression to characterize the probability density of whether a data record is non-null at a given index value. With the help of this estimated density function, the proposed method achieves efficient sampling by actively querying records with adaptive resolutions of interval lengths and provides an unbiased estimator for histogram construction. Comprehensive experiments on synthetic and real-world datasets demonstrate that the proposed Active Sampling method can effectively improve the estimation accuracy and use less query cost than other commonly used sampling methods.

Deploying database services on cloud systems has gained increasing popularity and has become a common practice in the industry. However, the complicated cloud environments make performance issues inevitable, which could violate the service level guarantee if not addressed in a timely manner. Among the various problems, anomalies in SQL queries are the most commonly reported sources that cause performance issues in database applications. These anomalous queries can be divided into High-impact SQLs (H-SQLs) and Root Cause SQLs (R-SQLs), representing the related SQLs that are correlated with the anomalies and the ones that are the root causes of the performance issue, respectively. In the presence of a large number of queries, to pinpoint the R-SQLs is far more difficult than to identify the H-SQLs. To address this challenge, we aim at automatically pinpointing the R-SQLs to resolve performance issues in cloud databases. This paper introduces PinSQL, an autonomous diagnosing system for Alibaba Cloud, which has four modules that are executed sequentially, including data collection and pre-processing, anomaly detection, root cause analysis, and repairing actions. First, the related performance metrics and query logs from monitored cloud database instances are collected and aggregated as the data sources. Then, based on these inputs, efficient anomaly detection is conducted in real-time. Upon the detection of an anomaly, the root cause SQLs are pinpointed through tracking the propagation chain of the involved SQLs. Finally, repairing actions are suggested and then executed on R-SQLs to address the anomalies. Extensive experiments on an Alibaba production system show that PinSQL can achieve an 80% accuracy for pinpointing the top-1 R-SQLs and successfully resolve the database performance issues resultantly.