def solve_sonata_lp(Q, query_2_tables, cost_matrix, qid_2_R, sigma_max, width_max, bits_max, mode=6): """ :param Q: :param query_2_tables: :param cost_matrix: :param qid_2_R: :param sigma_max: :param width_max: :param bits_max: :param mode: :return: Mode: 1: All SP, i.e. no operation in the data plane 2: FILTER-ONLY, i.e. only perform filter operations in the data plane 3: PART-ONLY, i.e. naively execute stateful operations in the data plane w/o any refinement 4: FIXED-REF, i.e. in addition to naive partitioning, also refine the input query with a one refinement plan fits all refinement plan 5: Cache:, i.e. N-way LRU cache 6: Sonata """ name = "sonata" # Create a new model m = Model(name) I = {} D = {} Last = {} S = {} F = {} query_2_n = {} Sigma = {} var_name = "sigma" dp_sigma = m.addVar(lb=0, ub=sigma_max, vtype=GRB.INTEGER, name=var_name) m.addConstr(dp_sigma <= sigma_max) for qid in Q: I[qid] = {} D[qid] = {} Last[qid] = {} S[qid] = {} F[qid] = {} Sigma[qid] = {} query_2_n[qid] = {} for rid in qid_2_R[qid][1:]: # create indicator variable for refinement level rid for query qid var_name = "i_" + str(qid) + "_" + str(rid) I[qid][rid] = m.addVar(lb=0, ub=1, vtype=GRB.BINARY, name=var_name) if rid == qid_2_R[qid][1:][-1]: m.addConstr(I[qid][rid] == 1) D[qid][rid] = {} Last[qid][rid] = {} S[qid][rid] = {} F[qid][rid] = {} Sigma[qid][rid] = {} table_2_n = {} # add f variables for each previous refinement level for rid_prev in qid_2_R[qid]: # add f variable for previous refinement level rid_prev for table tid_new if rid_prev < rid: var_name = "f_" + str(qid) + "_" + str(rid_prev) + "_" + str(rid) F[qid][rid][rid_prev] = m.addVar(lb=0, ub=1, vtype=GRB.BINARY, name=var_name) if rid_prev > 0: m.addConstr(F[qid][rid][rid_prev] <= I[qid][rid_prev]) # sum of all f variables for each table is at max 1 f_over__qr = [F[qid][rid][rid_prev] for rid_prev in F[qid][rid].keys()] m.addConstr(sum(f_over__qr) >= I[qid][rid]) m.addConstr(sum(f_over__qr) <= 1) for tid in query_2_tables[qid]: tid_new = 1000000 * qid + 1000 * tid + rid # add stage variable var_name = "sigma_" + str(tid_new) Sigma[qid][rid][tid] = m.addVar(lb=0, ub=sigma_max, vtype=GRB.INTEGER, name=var_name) m.addConstr(dp_sigma >= Sigma[qid][rid][tid]) # add d variable for this table var_name = "d_" + str(tid_new) D[qid][rid][tid] = m.addVar(lb=0, ub=1, vtype=GRB.BINARY, name=var_name) S[qid][rid][tid] = {} for sid in range(1, sigma_max + 1): # add f variable for stage sid rid_prev for table tid_new var_name = "s_" + str(tid_new) + "_" + str(sid) S[qid][rid][tid][sid] = m.addVar(lb=0, ub=1, vtype=GRB.BINARY, name=var_name) m.addGenConstrIndicator(S[qid][rid][tid][sid], True, Sigma[qid][rid][tid] == sid) # a table can at most use one stage s_over_t = [S[qid][rid][tid][sid] for sid in range(1, sigma_max + 1)] m.addConstr(sum(s_over_t) >= D[qid][rid][tid]) m.addGenConstrIndicator(D[qid][rid][tid], True, sum(s_over_t) == 1) m.addGenConstrIndicator(D[qid][rid][tid], False, sum(s_over_t) == 0) # add last variable for this table var_name = "last_" + str(tid_new) Last[qid][rid][tid] = m.addVar(lb=0, ub=1, vtype=GRB.BINARY, name=var_name) # create number of out packets for each table var_name = "n_" + str(tid_new) table_2_n[tid_new] = m.addVar(lb=0, ub=GRB.INFINITY, vtype=GRB.INTEGER, name=var_name) n_over_tid = [F[qid][rid][rid_prev] * cost_matrix[qid][(rid_prev, rid)][tid][-1] for rid_prev in F[qid][rid].keys()] m.addGenConstrIndicator(Last[qid][rid][tid], True, table_2_n[tid_new] >= sum(n_over_tid)) m.addGenConstrIndicator(Last[qid][rid][tid], False, table_2_n[tid_new] == 0) # relate d and last variables for each query ind = 1 for tid in query_2_tables[qid]: tmp = [D[qid][rid][tid1] for tid1 in query_2_tables[qid][:ind]] m.addConstr(D[qid][rid][tid] >= Last[qid][rid][tid]) ind += 1 for (tid1, tid2) in zip(query_2_tables[qid][:-1], query_2_tables[qid][1:]): m.addConstr(D[qid][rid][tid1] >= D[qid][rid][tid2]) # inter-query dependency for (tid1, tid2) in zip(query_2_tables[qid][:-1], query_2_tables[qid][1:]): sigma1 = Sigma[qid][rid][tid1] sigma2 = Sigma[qid][rid][tid2] m.addGenConstrIndicator(D[qid][rid][tid2], True, sigma1 + 1 <= sigma2) # create a query (qid, rid) specific count for number of out packets var_name = "n_" + str(qid) + "_" + str(rid) query_2_n[qid][rid] = m.addVar(lb=0, ub=GRB.INFINITY, vtype=GRB.INTEGER, name=var_name) n_over_query = [table_2_n[tid_new] for tid_new in table_2_n.keys()] n_over_qr_no_dp = [] for rid_prev in F[qid][rid].keys(): tid_min = min(cost_matrix[qid][(rid_prev, rid)].keys()) n_over_qr_no_dp = [F[qid][rid][rid_prev] * cost_matrix[qid][(rid_prev, rid)][tid_min][0] for rid_prev in F[qid][rid].keys()] var_name = "ind_" + str(qid) + str(rid) tmp_ind = m.addVar(lb=0, ub=1, vtype=GRB.BINARY, name=var_name) m.addConstr(tmp_ind == 1 - sum([Last[qid][rid][tid] for tid in query_2_tables[qid]])) var_name = "qrn_" + str(qid) + "_" + str(rid) qrn = m.addVar(lb=0, ub=GRB.INFINITY, vtype=GRB.INTEGER, name=var_name) m.addGenConstrIndicator(tmp_ind, True, qrn >= sum(n_over_qr_no_dp)) m.addGenConstrIndicator(tmp_ind, False, qrn >= sum(n_over_query)) m.addGenConstrIndicator(I[qid][rid], True, query_2_n[qid][rid] >= qrn) # sum of all Last variables is 1 for each (qid, rid) l_over_query = [Last[qid][rid][tid] for tid in query_2_tables[qid]] m.addConstr(sum(l_over_query) <= 1) # apply the pipeline width constraint for sid in range(1, 1 + sigma_max): s_over_stage = [] for qid in Q: for rid in qid_2_R[qid][1:]: s_over_stage += [S[qid][rid][tid][sid] for tid in query_2_tables[qid]] m.addConstr(sum(s_over_stage) <= width_max) # apply the bits per stage constraint BS = {} All_BS = {} for sid in range(1, 1 + sigma_max): BS[sid] = {} All_BS[sid] = [] for qid in Q: BS[sid][qid] = {} for rid in qid_2_R[qid][1:]: BS[sid][qid][rid] = {} for tid in query_2_tables[qid]: var_name = "bs_" + str(sid) + "_" + str(qid) + "_" + str(rid) + "_" + str(tid) BS[sid][qid][rid][tid] = m.addVar(lb=0, ub=GRB.INFINITY, vtype=GRB.INTEGER, name=var_name) b_over_r = [F[qid][rid][rid_prev] * cost_matrix[qid][(rid_prev, rid)][tid][1] for rid_prev in F[qid][rid].keys()] m.addGenConstrIndicator(S[qid][rid][tid][sid], True, BS[sid][qid][rid][tid] == sum(b_over_r)) m.addGenConstrIndicator(S[qid][rid][tid][sid], False, BS[sid][qid][rid][tid] == 0) All_BS[sid].append(BS[sid][qid][rid][tid]) m.addConstr(sum(All_BS[sid]) <= bits_max) # define the objective, i.e. minimize the total number of packets to send to stream processor total_packets = [] for qid in Q: for rid in qid_2_R[qid][1:]: total_packets.append(query_2_n[qid][rid]) m.setObjective(sum(total_packets), GRB.MINIMIZE) # Apply mode-specific changes if mode in [1, 2]: # set all d variables to zero, i.e. no stateful operation in the data plane for qid in Q: for rid in qid_2_R[qid][1:]: if rid != qid_2_R[qid][-1]: m.addConstr(I[qid][rid] == 0) for tid in query_2_tables[qid]: m.addConstr(D[qid][rid][tid] == 0) elif mode in [3]: # deactivate all queries that run at coarser refinement level to zero, # i.e. only partitioning at finest refinement level and no iterative refinement for qid in Q: for rid in qid_2_R[qid][1:-1]: m.addConstr(I[qid][rid] == 0) elif mode in [4]: # activate all queries as all refinement levels to one, # i.e. each query uses all possible refinement levels for qid in Q: for rid in qid_2_R[qid][1:]: m.addConstr(I[qid][rid] == 1) m.write(name + ".lp") m.setParam(GRB.Param.OutputFlag, 0) m.setParam(GRB.Param.LogToConsole, 0) m.optimize() # for v in m.getVars(): # print(v.varName, v.x) # Print the Output out_table = [] refinement_levels = {} table_headers = ["Queries"] for sid in range(1, sigma_max + 1): table_headers.append(str(sid)) row_id = 0 for qid in Q: refinement_levels[qid] = "0" for rid in qid_2_R[qid][1:]: if I[qid][rid].x == 1: refinement_levels[qid] += "-->" + str(rid) out_table.append([]) out_table[row_id].append("Q(" + str(qid) + "," + str(rid) + ")") for sid in range(1, sigma_max + 1): flag = 0 for tid in query_2_tables[qid]: if int(S[qid][rid][tid][sid].x) == 1: for rid_prev in F[qid][rid].keys(): if int(F[qid][rid][rid_prev].x) == 1: out_table[row_id].append(cost_matrix[qid][(rid_prev, rid)][tid][1]) flag = 1 if flag == 0: out_table[row_id].append(0) row_id += 1 print("## Mode", mode) print("N(Tuples)", m.objVal) print(tabulate(out_table, headers=table_headers)) print(refinement_levels) print("==========================") return m
for va, vb in E: target_f = f.select(va, vb, '*', '*') for m in target_f[:-1]: for n in target_f[target_f.index(m) + 1:]: for α in range(round( lowest_common_multiple(T[m], T[n]) / T[m])): for β in range( round(lowest_common_multiple(T[m], T[n]) / T[n])): if β * T[n] > (α + 1) * T[m]: break if β * T[n] < (α - 1) * T[m]: continue σ = md.addVar() md.addGenConstrIndicator(σ, True, φ[m] - φ[n], GRB.LESS_EQUAL, -e2e[m] - α * T[m] + β * T[n], 'Link Constaints 1') md.addGenConstrIndicator(σ, False, φ[n] - φ[m], GRB.LESS_EQUAL, -e2e[n] - β * T[n] + α * T[m], 'Link Constaints 2') for m in target_f: for n in old_f.select(va, vb, '*', '*'): for α in range( round(lowest_common_multiple(T[m], old_T[n]) / T[m])): for β in range( round( lowest_common_multiple(T[m], old_T[n]) / old_T[n])): if β * old_T[n] > (α + 1) * T[m]: break
def solve_sonata_lp(): name = "sonata" try: # Create a new model m = Model(name) # create table tuples tables = {} table_2_qid = {} table_2_d = {} table_2_last = {} for qid in query_2_tables: table_2_last[qid] = {} table_2_d[qid] = {} for tid in query_2_tables[qid]: table_2_qid[tid] = qid # add a binary decision variable d var_name = "d_" + str(tid) table_2_d[qid][tid] = m.addVar(lb=0, ub=1, vtype=GRB.BINARY, name=var_name) # add a binary decision variable last var_name = "last_" + str(tid) table_2_last[qid][tid] = m.addVar(lb=0, ub=1, vtype=GRB.BINARY, name=var_name) for tid in table_2_bits.keys(): tables[tid] = {} qid = table_2_qid[tid] tables[tid]["qid"] = qid tables[tid]["d"] = table_2_d[qid][tid] tables[tid]["last"] = table_2_last[qid][tid] # print (tables) # satisfy the `last` variable constraint for each query qid_2_last = {} for qid in Q: var_name = "qid_last_" + str(qid) qid_2_last[qid] = m.addVar(lb=0, ub=1, vtype=GRB.BINARY, name=var_name) tmp = [table_2_last[qid][tid] for tid in query_2_tables[qid]] m.addConstr(sum(tmp) == qid_2_last[qid]) # relate d & last variables for qid in Q: ind = 1 for tid in query_2_tables[qid]: tmp = [ table_2_d[qid][tid1] for tid1 in query_2_tables[qid][:ind] ] m.addConstr(sum(tmp) >= table_2_last[qid][tid] * ind) ind += 1 # Enumerate powerset for queries G = list(powerset(Q))[:-1] print(G) # compute the cardinality of elements in G gid_2_doubleD = {} gid = 0 for g in G: output_set = set() for qid in g: output_set = output_set.union(query_2_D[qid]) gid_2_doubleD[gid] = output_set gid += 1 print(gid_2_doubleD) # create A variables A = {} # Add pipeline specific variables pipeline_2_I = {} pipeline_2_O = {} pipeline_2_A = {} for pid in Q: pipeline_2_O[pid] = {} pipeline_2_I[pid] = {} pipeline_2_A[pid] = {} # create A variables for gid in range(len(G)): var_name = "A_" + str(pid) + "_" + str(gid) pipeline_2_A[pid][gid] = m.addVar(lb=0, ub=1, vtype=GRB.BINARY, name=var_name) # create I variables for qid in Q: var_name = "I_" + str(pid) + "_" + str(qid) pipeline_2_I[pid][qid] = m.addVar(lb=0, ub=1, vtype=GRB.BINARY, name=var_name) # create O variables for qid1 in Q: pipeline_2_O[pid][qid1] = {} for qid2 in Q: if qid1 != qid2: var_name = "O_" + str(pid) + "_" + str( qid1) + "_" + str(qid2) pipeline_2_O[pid][qid1][qid2] = m.addVar( lb=0, ub=1, vtype=GRB.BINARY, name=var_name) # complimentary O constraint for pid in Q: for qid1 in Q: for qid2 in Q: if qid1 != qid2: m.addConstr(pipeline_2_O[pid][qid1][qid2] + pipeline_2_O[pid][qid2][qid1] <= 1) # if I1 & I2 then O12+O21 m.addConstr(pipeline_2_O[pid][qid1][qid2] + pipeline_2_O[pid][qid2][qid1] >= pipeline_2_I[pid][qid1] + pipeline_2_I[pid][qid2] - 1) # if (1-I1) or (1-I2) then !(O12+O21) m.addConstr(pipeline_2_O[pid][qid1][qid2] + pipeline_2_O[pid][qid2][qid1] <= 0.5 * (pipeline_2_I[pid][qid1] + pipeline_2_I[pid][qid2])) # create an indicator variable for pipelines pipeline_2_ind = {} for pid in Q: var_name = "P_ind_" + str(pid) pipeline_2_ind[pid] = m.addVar(lb=0, ub=1, vtype=GRB.BINARY, name=var_name) for qid in Q: m.addConstr(pipeline_2_I[pid][qid] <= pipeline_2_ind[pid]) I_for_P = [pipeline_2_I[pid][qid] for qid in Q] m.addConstr(pipeline_2_ind[pid] <= sum(I_for_P)) # # satisfy the constraint, sum(A) == sum(p_ind) # all_a = [A[gid] for gid in range(len(G))] # all_p = [pipeline_2_ind[pid] for pid in Q] # m.addConstr(sum(all_a) == sum(all_p)) for qid in Q: I_for_P = [pipeline_2_I[pid][qid] for pid in Q] m.addConstr(sum(I_for_P) <= 1) for gid in range(len(G)): A_for_P = [pipeline_2_A[pid][gid] for pid in Q] m.addConstr(sum(A_for_P) <= 1) # relate A and I variables for pid in Q: for gid in range(len(G)): g = G[gid] for qid in g: m.addGenConstrIndicator(pipeline_2_A[pid][gid], True, pipeline_2_I[pid][qid] == 1) for pid in Q: for qid in Q: tmp = [] for gid in range(len(G)): g = G[gid] if qid in g: tmp.append(pipeline_2_A[pid][gid]) m.addConstr(sum(tmp) == pipeline_2_I[pid][qid]) # objective objective_expr = ([ table_2_bits[tid] * tables[tid]["last"] for tid in table_2_bits.keys() ] + [1000 * (1 - qid_2_last[qid]) for qid in Q]) m.setObjective(sum(objective_expr), GRB.MINIMIZE) # satisfy the constraints on I variable for qid in Q: tmp = [pipeline_2_I[pid][qid] for pid in Q] m.addConstr(sum(tmp) == 1) # satisfy mirroring overhead constraint total_packets_expr_list = [] for pid in Q: for gid in range(len(G)): print(gid, gid_2_doubleD[gid], len(gid_2_doubleD[gid])) total_packets_expr_list.append(pipeline_2_A[pid][gid] * len(gid_2_doubleD[gid])) # Add cloned packet variable C = m.addVar(lb=0, ub=clone_max, vtype=GRB.INTEGER, name="Clone") m.addConstr(C == sum(total_packets_expr_list) - len(D)) m.addConstr(C <= clone_max) table_2_stageE = {} table_2_stageF = {} for pid in Q: table_2_stageE[pid] = {} table_2_stageF[pid] = {} for qid in Q: table_2_stageE[pid][qid] = {} table_2_stageF[pid][qid] = {} for tid in query_2_tables[qid]: var_name = "SE_" + str(pid) + "_" + str(qid) + "_" + str( tid) table_2_stageE[pid][qid][tid] = m.addVar(lb=0, ub=sigma_max, vtype=GRB.INTEGER, name=var_name) m.addGenConstrIndicator(pipeline_2_I[pid][qid], False, table_2_stageE[pid][qid][tid] <= 0) m.addGenConstrIndicator(pipeline_2_I[pid][qid], True, table_2_stageE[pid][qid][tid] >= 1) var_name = "SF_" + str(pid) + "_" + str(qid) + "_" + str( tid) table_2_stageF[pid][qid][tid] = m.addVar(lb=0, ub=sigma_max, vtype=GRB.INTEGER, name=var_name) m.addGenConstrIndicator(pipeline_2_I[pid][qid], False, table_2_stageF[pid][qid][tid] <= 0) m.addGenConstrIndicator(pipeline_2_I[pid][qid], True, table_2_stageF[pid][qid][tid] >= 1) m.addConstr(table_2_stageF[pid][qid][tid] <= table_2_stageE[pid][qid][tid]) # Apply intra-query dependencies for pid in Q: for qid in Q: for (tid1, tid2) in zip(query_2_tables[qid][:-1], query_2_tables[qid][1:]): #m.addConstr(table_2_stage[pid][qid][tid2] - 1 - table_2_stage[pid][qid][tid1] >= 0) m.addGenConstrIndicator( pipeline_2_I[pid][qid], True, table_2_stageF[pid][qid][tid2] - table_2_stageE[pid][qid][tid1] >= 1) # create sigma variables pid_2_sigma = {} for pid in Q: var_name = "sigma_" + str(pid) pid_2_sigma[pid] = m.addVar(lb=0, ub=sigma_max, vtype=GRB.INTEGER, name=var_name) # relate sigma with I for pid in Q: I_for_pid = [(len(query_2_tables[qid]) * pipeline_2_I[pid][qid]) for qid in Q] m.addConstr(sum(I_for_pid) == pid_2_sigma[pid]) # apply inter-query dependencies for pid in Q: for qid1 in Q: last_tid_1 = query_2_tables[qid1][-1] first_tid_1 = query_2_tables[qid1][0] for qid2 in Q: if qid1 != qid2: last_tid_2 = query_2_tables[qid2][-1] first_tid_2 = query_2_tables[qid2][0] m.addGenConstrIndicator( pipeline_2_O[pid][qid1][qid2], True, table_2_stageF[pid][qid2][first_tid_2] - table_2_stageE[pid][qid1][last_tid_1] >= 1) m.addGenConstrIndicator( pipeline_2_O[pid][qid2][qid1], True, table_2_stageF[pid][qid1][first_tid_1] - table_2_stageE[pid][qid2][last_tid_2] >= 1) # create W variable W = {} for pid in Q: W[pid] = {} for qid in Q: W[pid][qid] = {} for tid in query_2_tables[qid]: W[pid][qid][tid] = {} for sid in range(1, 1 + sigma_max): var_name = "W_" + str(pid) + "_" + str( qid) + "_" + str(tid) + "_" + str(sid) W[pid][qid][tid][sid] = m.addVar(lb=0, ub=table_2_bits[tid], vtype=GRB.INTEGER, name=var_name) m.addGenConstrIndicator(pipeline_2_I[pid][qid], False, W[pid][qid][tid][sid] == 0) # apply assignment constraint for qid in Q: for tid in query_2_tables[qid]: for pid in Q: tmp = [ W[pid][qid][tid][sid] for sid in range(1, 1 + sigma_max) ] var_name = "tmp_y_" + str(qid) + "_" + str(tid) + str(pid) tmp_y = m.addVar(lb=0, ub=1, vtype=GRB.BINARY, name=var_name) m.addConstr( tmp_y >= pipeline_2_I[pid][qid] + tables[tid]["d"] - 1) m.addGenConstrIndicator(tmp_y, True, sum(tmp) >= table_2_bits[tid]) # apply memory constraint for sid in range(1, 1 + sigma_max): W_for_S = [] for pid in Q: for qid in Q: for tid in query_2_tables[qid]: W_for_S.append(W[pid][qid][tid][sid]) m.addConstr(sum(W_for_S) <= bits_max) # relate W and stageE and stageF variables for pid in Q: for qid in Q: for tid in query_2_tables[qid]: for sid in range(1, 1 + sigma_max): # if W[pid][qid][tid][sid] > 0 then, # table_2_stageF[pid][qid][tid] <= sid and table_2_stageE[pid][qid][tid] >= sid # create a new indicator variable var_name = "fin_ind_" + str(pid) + "_" + str( qid) + "_" + str(tid) + "_" + str(sid) tmp_ind = m.addVar(lb=0, ub=1, vtype=GRB.BINARY, name=var_name) m.addGenConstrIndicator(tmp_ind, False, W[pid][qid][tid][sid] == 0) m.addGenConstrIndicator( tmp_ind, True, table_2_stageF[pid][qid][tid] <= sid) m.addGenConstrIndicator( tmp_ind, True, table_2_stageE[pid][qid][tid] >= sid) m.write(name + ".lp") m.optimize() print('Obj:', m.objVal) for v in m.getVars(): print(v.varName, v.x) out_str = "Stages" for sid in range(1, sigma_max + 1): out_str += "|" + str(sid) out_str += "\n" for pid in Q: out_str += "P" + str(pid) + "|" for sid in range(1, sigma_max + 1): out_sid = "" if pipeline_2_ind[pid].x > 0: for qid in Q: if pipeline_2_I[pid][qid].x > 0: for tid in query_2_tables[qid]: if W[pid][qid][tid][sid].x > 0: out_sid += "(S" + str(sid) + ",Q" + str( qid) + ",T" + str(tid) + ",B=" + str( W[pid][qid][tid][sid].x) + ")," out_sid = out_sid[:-1] if out_sid == "": out_sid = "XXXX" out_str += out_sid + "|" out_str += "\n" print(out_str) except GurobiError: print('Error reported', GurobiError.message)