From 9802b2e6509bfc67f979f742e93b35340af62af8 Mon Sep 17 00:00:00 2001 From: Joshua Watt Date: Tue, 23 Jul 2019 09:16:37 -0500 Subject: bitbake: hashserv: SQL Optimizations Implements a number of optimizations to the SQL used in the hash equivalence server: 1) Two indexes are created for the two methods (method, taskhash and method outhash) by which rows are found in order to speed up the lookup 2) An extra SELECT to lookup the just inserted row was removed. This SELECT is unnecessary since all of the information about the newly inserted row is already available. 3) A uniqueness constraint was added to the table. This should allow the server to be multithreaded in the future since duplicate inserts can be detected (and ignored). This change requires bumping the database version to '2', since a uniqueness constraint can't be added to an existing table. 4) Some comments are added to clarify the trick SELECT statement used when inserting new equivalent hashes (Bitbake rev: 7aec8632e67b4f0ab7b72692c40a42f6926608c3) Signed-off-by: Joshua Watt Signed-off-by: Richard Purdie --- bitbake/lib/hashserv/__init__.py | 37 +++++++++++++++++++++++++++---------- 1 file changed, 27 insertions(+), 10 deletions(-) diff --git a/bitbake/lib/hashserv/__init__.py b/bitbake/lib/hashserv/__init__.py index fdc9ced9f2..768c5504cf 100644 --- a/bitbake/lib/hashserv/__init__.py +++ b/bitbake/lib/hashserv/__init__.py @@ -1,4 +1,4 @@ -# Copyright (C) 2018 Garmin Ltd. +# Copyright (C) 2018-2019 Garmin Ltd. # # SPDX-License-Identifier: GPL-2.0-only # @@ -32,7 +32,7 @@ class HashEquivalenceServer(BaseHTTPRequestHandler): d = None with contextlib.closing(self.db.cursor()) as cursor: - cursor.execute('SELECT taskhash, method, unihash FROM tasks_v1 WHERE method=:method AND taskhash=:taskhash ORDER BY created ASC LIMIT 1', + cursor.execute('SELECT taskhash, method, unihash FROM tasks_v2 WHERE method=:method AND taskhash=:taskhash ORDER BY created ASC LIMIT 1', {'method': method, 'taskhash': taskhash}) row = cursor.fetchone() @@ -63,15 +63,29 @@ class HashEquivalenceServer(BaseHTTPRequestHandler): with contextlib.closing(self.db.cursor()) as cursor: cursor.execute(''' - SELECT taskhash, method, unihash FROM tasks_v1 WHERE method=:method AND outhash=:outhash + -- Find tasks with a matching outhash (that is, tasks that + -- are equivalent) + SELECT taskhash, method, unihash FROM tasks_v2 WHERE method=:method AND outhash=:outhash + + -- If there is an exact match on the taskhash, return it. + -- Otherwise return the oldest matching outhash of any + -- taskhash ORDER BY CASE WHEN taskhash=:taskhash THEN 1 ELSE 2 END, created ASC + + -- Only return one row LIMIT 1 ''', {k: data[k] for k in ('method', 'outhash', 'taskhash')}) row = cursor.fetchone() + # If no matching outhash was found, or one *was* found but it + # wasn't an exact match on the taskhash, a new entry for this + # taskhash should be added if row is None or row['taskhash'] != data['taskhash']: + # If a row matching the outhash was found, the unihash for + # the new taskhash should be the same as that one. + # Otherwise the caller provided unihash is used. unihash = data['unihash'] if row is not None: unihash = row['unihash'] @@ -88,18 +102,17 @@ class HashEquivalenceServer(BaseHTTPRequestHandler): if k in data: insert_data[k] = data[k] - cursor.execute('''INSERT INTO tasks_v1 (%s) VALUES (%s)''' % ( + cursor.execute('''INSERT INTO tasks_v2 (%s) VALUES (%s)''' % ( ', '.join(sorted(insert_data.keys())), ', '.join(':' + k for k in sorted(insert_data.keys()))), insert_data) logger.info('Adding taskhash %s with unihash %s', data['taskhash'], unihash) - cursor.execute('SELECT taskhash, method, unihash FROM tasks_v1 WHERE id=:id', {'id': cursor.lastrowid}) - row = cursor.fetchone() self.db.commit() - - d = {k: row[k] for k in ('taskhash', 'method', 'unihash')} + d = {'taskhash': data['taskhash'], 'method': data['method'], 'unihash': unihash} + else: + d = {k: row[k] for k in ('taskhash', 'method', 'unihash')} self.send_response(200) self.send_header('Content-Type', 'application/json; charset=utf-8') @@ -120,7 +133,7 @@ def create_server(addr, db, prefix=''): with contextlib.closing(db.cursor()) as cursor: cursor.execute(''' - CREATE TABLE IF NOT EXISTS tasks_v1 ( + CREATE TABLE IF NOT EXISTS tasks_v2 ( id INTEGER PRIMARY KEY AUTOINCREMENT, method TEXT NOT NULL, outhash TEXT NOT NULL, @@ -134,9 +147,13 @@ def create_server(addr, db, prefix=''): PV TEXT, PR TEXT, task TEXT, - outhash_siginfo TEXT + outhash_siginfo TEXT, + + UNIQUE(method, outhash, taskhash) ) ''') + cursor.execute('CREATE INDEX IF NOT EXISTS taskhash_lookup ON tasks_v2 (method, taskhash)') + cursor.execute('CREATE INDEX IF NOT EXISTS outhash_lookup ON tasks_v2 (method, outhash)') logger.info('Starting server on %s', addr) return HTTPServer(addr, Handler) -- cgit 1.2.3-korg