MyBenchmark: generating databases for query workloads

Abstract

To evaluate the performance of database applications and database management systems (DBMSs), we usually execute workloads of queries on generated databases of different sizes and then benchmark various measures such as respond time and throughput. This paper introduces MyBenchmark, a parallel data generation tool that takes a set of queries as input and generates database instances. Users of MyBenchmark can control the characteristics of the generated data as well as the characteristics of the resulting workload. Applications of MyBenchmark include DBMS testing, database application testing, and application-driven benchmarking. In this paper, we present the architecture and the implementation algorithms of MyBenchmark. Experimental results show that MyBenchmark is able to generate workload-aware databases for a variety of workloads including query workloads extracted from TPC-C, TPC-E, TPC-H, and TPC-W benchmarks.

Appendix: Proofs

1.1 Proof of $k$-SAT-MATCH is $\mathcal {NP}$-complete

Theorem 1

Problem $k$-SAT-MATCH is $\mathcal {NP}$-complete.

We begin with proving $k$-SAT-MATCH is in $\mathcal {NP}$ and further show that it is $\mathcal {NP}$-hard by a reduction from the $\mathcal {NP}$-complete problem known as X3C (Exact Cover by 3-set).

Corollary 1

$k$-SAT-MATCH is in $\mathcal {NP}$.

Proof

Each “yes” instance has a polynomial-size proof, which consists of the set of edges in the matching, and the set of values for each variable. Thus, each “yes” instance can be verified in polynomial time. $\square$

Corollary 2

$k$-SAT-MATCH is $\mathcal {NP}$-hard.

Proof. Obviously, if we solely focus on the constraint satisfaction problem (i.e., the condition on satisfiability required in Definition 4), $k$-SAT-MATCH is definitely $\mathcal {NP}$-hard. However, as we want to show the difficulty of the matching problem itself (e.g., adding an edge to the matching set will induce some relationships that hinder the matching of the other nodes), we assume here the constraint satisfaction step is at no cost.

We are going to reduce X3C (Exact Cover by 3-Set) to the $k$-SAT-MATCH problem. The X3C problem [14] takes as input a set of elements $\mathcal {S}=\{S_1, S_2, \ldots , S_{3n}\}$ and a collection of 3-element set $\mathcal {C}=\{C_1, C_2, \ldots , C_m\}$ and asks whether there is a sub-collection of $\mathcal {C}$, whose size is $n$, such that it exactly covers all elements of $\mathcal {S}$. The reduction is to construct a constrained bipartite graph $G=(U,V,E)$ as follows.

• 1. For each 3-element set $C_i =\{S_j, S_k, S_\ell \}$, insert 3 constrained nodes $u_{i,j}$, $u_{i,k}$, and $u_{i,\ell }$ to constrained node set $U$. The propositional formulas that are associated with $u_{i,j}$, $u_{i,k}$, and $u_{i,\ell }$ would be [$$a_j$ $\le$ $w_i$], [$$a_k$ $\le$ $w_i$] and [$$a_\ell$ $\le$ $w_i$], respectively ($$a_j$, $$a_k$, $$a_\ell$ are symbols and $w_i$ is any unique value).

• 2. For each element $S_j$, insert a constrained node $v_j$ to constrained node set $V$. The propositional formula that is associated with $v_i$ would be [$$b_j$ $\ge$ $w$] (value $w$ would be the same for all elements).

• 3. Connect the nodes in $U$ and $V$ if they are created from the same element $S_j$. For instance, assume a 3-element set $C_2 = \{S_4, S_5, S_6\}$ has inserted 3 nodes $u_{2,4}$, $u_{2,5}$, and $u_{2,6}$ to $U$ in Step 1 and element $S_4$ has inserted a node $v_4$ into $V$ in Step 2. Then, nodes $u_{2,4}$ and $v_4$ should be connected as both of them are created from element $S_4$.

• 4. For each 3-element set $C_i$, insert a node $u_{C_i}$ with propositional formula [$$c_i$ $>$ $w_i$] to $U$ and insert a node $v_{C_i}$ with propositional formula [$$d_i$ $\le$ $w$] to $V$ and connect the two nodes with an edge.

The rest of the proof will establish:

Proposition 1

There is an exact cover of $\mathcal {S}$ if and only if the size of maximum satisfiable matching of $G$ is exactly $3n+(m-n)$.

Firstly, if the node $u_{i,j}$ appears in the MSM, it must be matched with the node $v_j$, so that it will induce the total-order relationship $w_i \ge w$. On the other hand, if $u_{C_i}$ appears in the MSM, it must be matched with will $v_{C_i}$, so that it will induce the total-order relationship $w > w_i$. Thus, if either $u_{i,j}$, $u_{i,k}$, or $u_{i,\ell }$ appear in the MSM, we cannot have $u_{C_i}$ in the MSM at the same time.

Suppose we denote $z$ to be the number of $i$’s such that $u_{i,j}$, $u_{i,k}$, or $u_{i,\ell }$ appear in the MSM. Then, the size of MSM is at most $3z + (m-z)$, which in turn is at most $3n + (m-n)$ since $z \le n$.

The “only-if” direction. Next, suppose there is an exact cover of $\mathcal {S}$. In that case, let $C_{i_1}, C_{i_2}, \ldots , C_{i_n}$ be the 3-sets such that they exactly cover $\mathcal {S}$. This implies the elements in these 3-sets must be distinct from each other. Then, consider the following matching in $G$:

• 1. For each $i \in \{i_1, i_2, \ldots , i_n\}$, the corresponding nodes of $C_{i_t}$, i.e., $u_{i_t,j}, u_{i_t,k}, u_{i_t,\ell }$, are matched to $v_j$, $v_k$, and $v_\ell$, respectively.

• 2. For each $i \notin \{i_1, i_2, \ldots , i_n\}$, $u_{C_i}$ is matched to $v_{C_i}$.

The above matching is also satisfiable because the edges induce total-order relationships of the form $w_i \ge w$ when $i \in \{i_1,i_2,\ldots ,i_n\}$, and of the form $w_i < w$ for other choice of $i$. Thus, all edges can be satisfied simultaneously. Finally, it is easy to check that the above matching has $3n+(m-n)$ edges, so that it is a maximum satisfiable matching.

The “if” direction. If the size of MSM is exactly $3n+(m-n)$, we claim that $z$, which is the number of $i$’s such that $u_{i,j}$, $u_{i,k}$, or $u_{i,\ell }$ appear in the MSM, must be exactly $n$; in addition, for each such $i$, all $u_{i,j}, u_{i,k}, u_{i,\ell }$ must appear in the matching. If this claim is true, it will immediately imply the corresponding 3-sets $C_i$’s (in total $n$ of them) will cover exactly $\mathcal {S}$.

Now, it remains to prove the claim. We first show that $z = n$. If $z < n$, then the matching can contain at most $3z$ edges connecting some $u_{i,r}$ with $v_r$, and at most $m-z$ edges connecting some $u_{C_s}$ with $v_{C_s}$, so that the number of edges is at most $3z + (m-z)$, which is less than $3n+(m-n)$. On the other hand, if $z > n$, then the matching can contain at most $3n$ edges connecting some $u_{i,r}$ with $v_r$ (because $v_r$ is limited), and at most $m-z$ edges connecting some $u_{C_s}$ with $v_{C_s}$, so that the number of edges is at most $3n + (m-z)$, which again is less than $3n+(m-n)$. Thus, if the size of MSM is $3n+(m-n)$, we must have $z=n$.

Given $z=n$, there are at most $m-n$ edges connecting $u_{C_s}$ with $v_{C_s}$. Thus, at least $3n$ edges must be connecting some $u_{i,r}$ with $v_r$. However, since there are only $n$ values of $i$ with $u_{i,j}$, $u_{i,k}$, or $u_{i,\ell }$ appear in the MSM, the previous statement is possible unless for each such $i$, all $u_{i,j}, u_{i,k}, u_{i,\ell }$ appear in the matching. Thus, the proof of the claim completes, and so do the proofs of the Proposition 1 and Corollary 2.

1.2 Proof sketch of the optimal integration plan problem

Proof Sketch. Given an instance of a cross product optimization [13], we create a corresponding symbolic database such that the matching size between two databases is always equal to the size of the cartesian product of the databases plus the size of the two databases. Thus, finding the maximum (satisfiable) matching equals to finding the optimal join ordering, which is $\mathcal {NP}$-hard. $\square$