Debian Codegraph @

mysql-test/suite/innodb_stress/t/load_generator.py

raw
1 import io
2 import hashlib
3 import mysql.connector
4 import os
5 import random
6 import signal
7 import sys
8 import threading
9 import time
10 import string
11
12 CHARS = string.ascii_letters + string.digits
13
14 def sha1(x):
15 return hashlib.sha1(str(x).encode("utf-8")).hexdigest()
16
17 # Should be deterministic given an idx
18 def get_msg(do_blob, idx):
19 random.seed(idx);
20 if do_blob:
21 blob_length = random.randint(1, 24000)
22 else:
23 blob_length = random.randint(1, 255)
24
25 if random.randint(1, 2) == 1:
26 # blob that cannot be compressed (well, compresses to 85% of original size)
27 return ''.join([random.choice(CHARS) for x in range(blob_length)])
28 else:
29 # blob that can be compressed
30 return random.choice(CHARS) * blob_length
31
32 class PopulateWorker(threading.Thread):
33 global LG_TMP_DIR
34 def __init__(self, con, start_id, end_id, i):
35 threading.Thread.__init__(self)
36 self.con = con
37 con.autocommit = False
38 self.log = open('%s/populate-%d.log' % (LG_TMP_DIR, i), 'a')
39 self.num = i
40 self.start_id = start_id
41 self.end_id = end_id
42 self.exception = None
43 self.start_time = time.time()
44 self.start()
45
46 def run(self):
47 try:
48 self.runme()
49 print("ok", file=self.log)
50 except Exception as e:
51 self.exception = e
52 try:
53 cursor = self.con.cursor()
54 cursor.execute("INSERT INTO errors VALUES (%s)", (e,))
55 except mysql.connector.Error as e2:
56 print("caught while inserting error (%s)" % e2,)
57 print("caught (%s)" % e)
58 finally:
59 self.finish()
60
61 def finish(self):
62 print("total time: %.2f s" % (time.time() - self.start_time), file=self.log)
63 self.log.close()
64 self.con.commit()
65 self.con.close()
66
67 def runme(self):
68 print("populate thread-%d started" % self.num, file=self.log)
69 cur = self.con.cursor()
70 stmt = None
71 for i in range(self.start_id, self.end_id):
72 msg = get_msg(do_blob, i)
73 stmt = """
74 INSERT INTO t1(id,msg_prefix,msg,msg_length,msg_checksum) VALUES (%d,'%s','%s',%d,'%s')
75 """ % (i+1, msg[0:255], msg, len(msg), sha1(msg))
76 try:
77 cur.execute(stmt)
78 if i % 100 == 0:
79 self.con.commit()
80 except mysql.connector.Error as e2:
81 print("caught in insert stmt: (%s)" % e2)
82
83 def populate_table(con, num_records_before, do_blob, log):
84 con.autocommit = False
85 cur = con.cursor()
86 stmt = None
87 workers = []
88 N = num_records_before // 10
89 start_id = 0
90 for i in range(10):
91 # We use raw data as long strings of digits were interpretted by connector as
92 # a float and cast to float value of "inf".
93 w = PopulateWorker(mysql.connector.connect(user=user, host=host, port=port, db=db, ssl_disabled=True, raw=True),
94 start_id, start_id + N, i)
95 start_id += N
96 workers.append(w)
97
98 for i in range(start_id, num_records_before):
99 msg = get_msg(do_blob, i)
100 stmt = """
101 INSERT INTO t1(id,msg_prefix,msg,msg_length,msg_checksum) VALUES (%d,'%s','%s',%d,'%s')
102 """ % (i+1, msg[0:255], msg, len(msg), sha1(msg))
103 cur.execute(stmt)
104
105 con.commit()
106 for w in workers:
107 w.join()
108 if w.exception:
109 print("populater thead %d threw an exception" % w.num)
110 return False
111 return True
112
113 def get_update(msg, idx):
114 return """
115 UPDATE t1 SET msg_prefix='%s',msg='%s',msg_length=%d,msg_checksum='%s' WHERE id=%d""" % (
116 msg[0:255], msg, len(msg), sha1(msg), idx)
117
118 def get_insert_on_dup(msg, idx):
119 return """
120 INSERT INTO t1 (msg_prefix,msg,msg_length,msg_checksum,id) VALUES ('%s','%s',%d,'%s',%d)
121 ON DUPLICATE KEY UPDATE
122 msg_prefix=VALUES(msg_prefix),
123 msg=VALUES(msg),
124 msg_length=VALUES(msg_length),
125 msg_checksum=VALUES(msg_checksum),
126 id=VALUES(id)""" % (
127 msg[0:255], msg, len(msg), sha1(msg), idx)
128
129 def get_insert(msg, idx):
130 return """
131 INSERT IGNORE INTO t1 (msg_prefix,msg,msg_length,msg_checksum,id) VALUES ('%s','%s',%d,'%s',%d)""" % (
132 msg[0:255], msg, len(msg), sha1(msg), idx)
133
134 def get_insert_null(msg):
135 return """
136 INSERT IGNORE INTO t1 (msg_prefix,msg,msg_length,msg_checksum,id) VALUES ('%s','%s',%d,'%s',NULL)""" % (
137 msg[0:255], msg, len(msg), sha1(msg))
138
139 class ChecksumWorker(threading.Thread):
140 global LG_TMP_DIR
141 def __init__(self, con, checksum):
142 threading.Thread.__init__(self)
143 self.con = con
144 con.autocommit = False
145 self.log = open('%s/worker-checksum.log' % LG_TMP_DIR, 'a')
146 self.checksum = checksum
147 print("given checksum=%d" % checksum, file=self.log)
148 self.start()
149
150 def run(self):
151 try:
152 self.runme()
153 print("ok", file=self.log)
154 except Exception as e:
155 try:
156 cursor = self.con.cursor()
157 cursor.execute("INSERT INTO errors VALUES (%s)", (repr(e),))
158 con.commit()
159 except mysql.connector.Error as e2:
160 print("caught while inserting error (%s)" % repr(e2))
161
162 print("caught (%s)" % repr(e))
163 finally:
164 self.finish()
165
166 def finish(self):
167 print("total time: %.2f s" % (time.time() - self.start_time), file=self.log)
168 self.log.close()
169 self.con.close()
170
171 def runme(self):
172 print("checksum thread started", file=self.log)
173 self.start_time = time.time()
174 cur = self.con.cursor()
175 cur.execute("CHECKSUM TABLE t1")
176 checksum = cur.fetchone()[1]
177 self.con.commit()
178 if checksum != self.checksum:
179 print("checksums do not match. given checksum=%d, calculated checksum=%d" % (self.checksum, checksum))
180 self.checksum = checksum
181 else:
182 print("checksums match! (both are %d)" % checksum, file=self.log)
183
184
185 class Worker(threading.Thread):
186 global LG_TMP_DIR
187
188 def __init__(self, num_xactions, xid, con, server_pid, do_blob, max_id, fake_changes, secondary_checks):
189 threading.Thread.__init__(self)
190 self.do_blob = do_blob
191 self.xid = xid
192 con.autocommit = False
193 self.con = con
194 self.num_xactions = num_xactions
195 cur = self.con.cursor()
196 self.rand = random.Random()
197 self.rand.seed(xid * server_pid)
198 self.loop_num = 0
199 self.max_id = max_id
200
201 self.num_primary_select = 0
202 self.num_secondary_select = 0
203 self.num_secondary_only_select = 0
204 self.num_inserts = 0
205 self.num_deletes = 0
206 self.num_updates = 0
207 self.time_spent = 0
208 self.log = open('%s/worker%02d.log' % (LG_TMP_DIR, self.xid), 'a')
209 self.secondary_checks = secondary_checks
210 self.start()
211
212 def finish(self):
213 print("loop_num:%d, total time: %.2f s" % (
214 self.loop_num, time.time() - self.start_time + self.time_spent), file=self.log)
215 print("num_primary_select=%d,num_secondary_select=%d,num_secondary_only_select=%d" %\
216 (self.num_primary_select, self.num_secondary_select, self.num_secondary_only_select), file=self.log)
217 print("num_inserts=%d,num_updates=%d,num_deletes=%d,time_spent=%d" %\
218 (self.num_inserts, self.num_updates, self.num_deletes, self.time_spent), file=self.log)
219 self.log.close()
220
221 def validate_msg(self, msg_prefix, msg, msg_length, msg_checksum, idx):
222
223 prefix_match = msg_prefix == msg[0:255]
224
225 checksum = sha1(msg)
226 checksum_match = checksum == msg_checksum
227
228 len_match = len(msg) == msg_length
229
230 if not prefix_match or not checksum_match or not len_match:
231 errmsg = "id(%d), length(%s,%d,%d), checksum(%s,%s,%s), prefix(%s,%s,%s)" % (
232 idx,
233 len_match, len(msg), msg_length,
234 checksum_match, checksum, msg_checksum,
235 prefix_match, msg_prefix, msg[0:255])
236 print(errmsg)
237
238 cursor = self.con.cursor()
239 cursor.execute("INSERT INTO errors VALUES (%s)", (errmsg,))
240 cursor.execute("COMMIT")
241 raise Exception('validate_msg failed')
242 else:
243 print("Validated for length(%d) and id(%d)" % (msg_length, idx), file=self.log)
244
245 # Check to see if the idx is in the first column of res_array
246 def check_exists(self, res_array, idx):
247 for res in res_array:
248 if res[0] == idx:
249 return True
250 return False
251
252 def run(self):
253 try:
254 self.runme()
255 print("ok, with do_blob %s" % self.do_blob, file=self.log)
256 except Exception as e:
257 try:
258 cursor = self.con.cursor()
259 cursor.execute("INSERT INTO errors VALUES (%s)", (repr(e),))
260 cursor.execute("COMMIT")
261 except mysql.connector.Error as e2:
262 print("caught while inserting errors: (%s)\nThe message to store: %s" % (repr(e2), repr(e)))
263
264 print("caught (%s)" % repr(e))
265 finally:
266 self.finish()
267
268 def runme(self):
269 self.start_time = time.time()
270 cur = self.con.cursor(buffered=True)
271 print("thread %d started, run from %d to %d" % (
272 self.xid, self.loop_num, self.num_xactions), file=self.log)
273
274 while not self.num_xactions or (self.loop_num < self.num_xactions):
275 idx = self.rand.randint(0, self.max_id)
276 insert_or_update = self.rand.randint(0, 3)
277 self.loop_num += 1
278
279 try:
280 stmt = None
281
282 msg = get_msg(self.do_blob, idx)
283
284 # Query primary key 70%, secondary key lookup 20%, secondary key only 10%
285 r = self.rand.randint(1, 10)
286 if r <= 7:
287 cur.execute("SELECT msg_prefix,msg,msg_length,msg_checksum FROM t1 WHERE id=%d" % idx)
288 res = cur.fetchone()
289 self.num_primary_select += 1
290 elif r <= 9:
291 cur.execute("SELECT msg_prefix,msg,msg_length,msg_checksum FROM t1 WHERE msg_prefix='%s'" % msg[0:255])
292 res = cur.fetchone()
293 self.num_secondary_select += 1
294 # Query only the secondary index
295 else:
296 cur.execute("SELECT id, msg_prefix FROM t1 WHERE msg_prefix='%s'" % msg[0:255])
297 res = cur.fetchall()
298 self.num_secondary_only_select += 1
299 # have to continue to next iteration since we arn't fetching other data
300 continue
301 if res:
302 self.validate_msg(res[0].decode(), res[1].decode(), int(res[2], 10), res[3].decode(), idx)
303
304 insert_with_index = False
305 if insert_or_update:
306 if res:
307 if self.rand.randint(0, 1):
308 stmt = get_update(msg, idx)
309 else:
310 stmt = get_insert_on_dup(msg, idx)
311 insert_with_index = True
312 self.num_updates += 1
313 else:
314 r = self.rand.randint(0, 2)
315 if r == 0:
316 stmt = get_insert(msg, idx)
317 insert_with_index = True
318 elif r == 1:
319 stmt = get_insert_on_dup(msg, idx)
320 insert_with_index = True
321 else:
322 stmt = get_insert_null(msg)
323 self.num_inserts += 1
324 else:
325 stmt = "DELETE FROM t1 WHERE id=%d" % idx
326 self.num_deletes += 1
327
328 cur.execute(stmt)
329
330 # 10% probability of checking to see the key exists in secondary index
331 if self.secondary_checks and self.rand.randint(1, 10) == 1:
332 cur.execute("SELECT id, msg_prefix FROM t1 WHERE msg_prefix='%s'" % msg[0:255])
333 res_array = cur.fetchall()
334 if insert_or_update:
335 if insert_with_index:
336 if not self.check_exists(res_array, idx):
337 print("Error: Inserted row doesn't exist in secondary index")
338 raise Exception("Error: Inserted row doesn't exist in secondary index")
339 else:
340 if self.check_exists(res_array, idx):
341 print("Error: Deleted row still exists in secondary index")
342 raise Exception("Error: Deleted row still exists in secondary index")
343
344
345 if (self.loop_num % 100) == 0:
346 print("Thread %d loop_num %d: result: %s" % (self.xid,
347 self.loop_num,
348 stmt), file=self.log)
349
350 # 30% commit, 10% rollback, 60% don't end the trx
351 r = self.rand.randint(1,10)
352 if r < 4:
353 try:
354 self.con.commit()
355 except mysql.connector.Error as e:
356 print("Error: error during commit: %s" % e)
357 elif r == 4:
358 try:
359 self.con.rollback()
360 except mysql.connector.Error as e:
361 print("Error: error during rollback: %s" % e)
362
363 except mysql.connector.Error as e:
364 if e.args[0] in [2006, 2013, 2055]: # server is killed
365 print("mysqld down, transaction %d" % self.xid)
366 return
367 elif e.args[0] in [1213, 1205]: # deadlock or lock wait timeout, ignore
368 return
369 else:
370 try:
371 cursor = self.con.cursor()
372 cursor.execute("INSERT INTO errors VALUES (%s)", ("%s -- %s" % (e, stmt),))
373 except mysql.connector.Error as e2:
374 print("caught while inserting error (%s)" % e2)
375 print("mysql error for stmt(%s) %s" % (stmt, e))
376
377 try:
378 self.con.commit()
379 except Exception as e:
380 print("commit error %s" % repr(e))
381
382 if __name__ == '__main__':
383 global LG_TMP_DIR
384
385 pid_file = sys.argv[1]
386 kill_db_after = int(sys.argv[2])
387 num_records_before = int(sys.argv[3])
388 num_workers = int(sys.argv[4])
389 num_xactions_per_worker = int(sys.argv[5])
390 user = sys.argv[6]
391 host = sys.argv[7]
392 port = int(sys.argv[8])
393 db = sys.argv[9]
394 do_blob = int(sys.argv[10])
395 max_id = int(sys.argv[11])
396 LG_TMP_DIR = sys.argv[12]
397 fake_changes = int(sys.argv[13]) # not used
398 checksum = int(sys.argv[14])
399 secondary_checks = int(sys.argv[15])
400
401 checksum_worker = None
402 workers = []
403 server_pid = int(open(pid_file).read())
404 log = open('%s/main.log' % LG_TMP_DIR, 'a')
405
406 # print "kill_db_after = ",kill_db_after," num_records_before = ", \
407 #num_records_before, " num_workers= ",num_workers, "num_xactions_per_worker =",\
408 #num_xactions_per_worker, "user = ",user, "host =", host,"port = ",port,\
409 #" db = ", db, " server_pid = ", server_pid
410
411 if num_records_before:
412 print("populate table do_blob is %d" % do_blob, file=log)
413 # We use raw data as long strings of digits were interpretted by connector as
414 # a float and cast to float value of "inf".
415 con = mysql.connector.connect(user=user, host=host, port=port, db=db, ssl_disabled=True, raw=True)
416 if not populate_table(con, num_records_before, do_blob, log):
417 sys.exit(1)
418 con.close()
419
420 if checksum:
421 print("start the checksum thread", file=log)
422 # We use raw data as long strings of digits were interpretted by connector as
423 # a float and cast to float value of "inf".
424 checksum_worker = ChecksumWorker(mysql.connector.connect(user=user, host=host, port=port, db=db,
425 ssl_disabled=True), checksum, raw=True)
426 workers.append(checksum_worker)
427
428 print("start %d threads" % num_workers, file=log)
429 for i in range(num_workers):
430 # We use raw data as long strings of digits were interpretted by connector as
431 # a float and cast to float value of "inf".
432 worker = Worker(num_xactions_per_worker, i,
433 mysql.connector.connect(user=user, host=host, port=port, db=db, ssl_disabled=True, raw=True),
434 server_pid, do_blob, max_id, fake_changes, secondary_checks)
435 workers.append(worker)
436
437 if kill_db_after:
438 print("kill mysqld", file=log)
439 time.sleep(kill_db_after)
440 if hasattr(signal, 'SIGKILL'):
441 os.kill(server_pid, signal.SIGKILL)
442 else:
443 # On Windows only SIGTERM is available.
444 os.kill(server_pid, signal.SIGTERM)
445
446 print("wait for threads", file=log)
447 for w in workers:
448 w.join()
449
450 if checksum_worker and checksum_worker.checksum != checksum:
451 print("checksums do not match. given checksum=%d, calculated checksum=%d" % (checksum, checksum_worker.checksum), file=log)
452 sys.exit(1)
453
454 print("all threads done", file=log)
455