From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp-out2.suse.de (smtp-out2.suse.de [IPv6:2001:67c:2178:6::1d]) by sourceware.org (Postfix) with ESMTPS id 5179A3857414 for ; Fri, 29 Jul 2022 15:49:39 +0000 (GMT) DMARC-Filter: OpenDMARC Filter v1.4.1 sourceware.org 5179A3857414 Received: from imap2.suse-dmz.suse.de (imap2.suse-dmz.suse.de [192.168.254.74]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-521) server-digest SHA512) (No client certificate requested) by smtp-out2.suse.de (Postfix) with ESMTPS id 58B105BCD0 for ; Fri, 29 Jul 2022 15:49:38 +0000 (UTC) Received: from imap2.suse-dmz.suse.de (imap2.suse-dmz.suse.de [192.168.254.74]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-521) server-digest SHA512) (No client certificate requested) by imap2.suse-dmz.suse.de (Postfix) with ESMTPS id 4927513A8E for ; Fri, 29 Jul 2022 15:49:38 +0000 (UTC) Received: from dovecot-director2.suse.de ([192.168.254.65]) by imap2.suse-dmz.suse.de with ESMTPSA id /A3fEJIB5GJrBAAAMHmgww (envelope-from ) for ; Fri, 29 Jul 2022 15:49:38 +0000 From: Josef =?utf-8?B?xIxlamth?= To: "elfutils-devel@sourceware.org" Subject: [PATCH 1/1] debuginfod: create indexes to speed up grooming Date: Fri, 29 Jul 2022 17:49:37 +0200 Message-ID: <3605019.RUnXabflUD@dhcp107> Organization: SUSE L3 MIME-Version: 1.0 Content-Transfer-Encoding: 7Bit Content-Type: text/plain; charset="us-ascii" X-Spam-Status: No, score=-13.4 required=5.0 tests=BAYES_00, DKIM_SIGNED, DKIM_VALID, DKIM_VALID_AU, DKIM_VALID_EF, GIT_PATCH_0, SPF_HELO_NONE, SPF_PASS, TXREP autolearn=ham autolearn_force=no version=3.4.6 X-Spam-Checker-Version: SpamAssassin 3.4.6 (2021-04-09) on server2.sourceware.org X-BeenThere: elfutils-devel@sourceware.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: Elfutils-devel mailing list List-Unsubscribe: , List-Archive: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 29 Jul 2022 15:49:40 -0000 >From 32bd2715e2490fe7a56794b78563c799d456f4be Mon Sep 17 00:00:00 2001 From: Josef Cejka Date: Fri, 8 Jul 2022 14:57:07 +0200 Subject: [PATCH 1/1] debuginfod: create indexes to speed up grooming Create indexes on _r_de and _f_de tables to speed up delete operations called by groom() function. Primary keys of those tables are optimalized to search rows by buildids so delete by file and mtime attributes has to scan the whole table. On large database can single delete query take minutes and grooming will be aborted before completion by time limit. New indexes are compatible with current sqlite schema. Signed-off-by: Josef Cejka --- debuginfod/ChangeLog | 4 ++++ debuginfod/debuginfod.cxx | 4 ++++ 2 files changed, 8 insertions(+) diff --git a/debuginfod/ChangeLog b/debuginfod/ChangeLog index 029687ff..2ff63e22 100644 --- a/debuginfod/ChangeLog +++ b/debuginfod/ChangeLog @@ -1,3 +1,7 @@ +2022-07-29 Josef Cejka + + * debuginfod.cxx: Create db indexes for fast delete while grooming. + 2022-06-03 Michael Trapp * debuginfod.cxx (scan_source_info): New global. diff --git a/debuginfod/debuginfod.cxx b/debuginfod/debuginfod.cxx index 75e30e04..ce49caa4 100644 --- a/debuginfod/debuginfod.cxx +++ b/debuginfod/debuginfod.cxx @@ -174,6 +174,8 @@ static const char DEBUGINFOD_SQLITE_DDL[] = " foreign key (buildid) references " BUILDIDS "_buildids(id) on update cascade on delete cascade,\n" " primary key (buildid, file, mtime)\n" " ) " WITHOUT_ROWID ";\n" + // Index for faster delete by file identifier + "create index if not exists " BUILDIDS "_f_de_idx on " BUILDIDS "_f_de (file, mtime);\n" "create table if not exists " BUILDIDS "_f_s (\n" " buildid integer not null,\n" " artifactsrc integer not null,\n" @@ -196,6 +198,8 @@ static const char DEBUGINFOD_SQLITE_DDL[] = " foreign key (buildid) references " BUILDIDS "_buildids(id) on update cascade on delete cascade,\n" " primary key (buildid, debuginfo_p, executable_p, file, content, mtime)\n" " ) " WITHOUT_ROWID ";\n" + // Index for faster delete by archive file identifier + "create index if not exists " BUILDIDS "_r_de_idx on " BUILDIDS "_r_de (file, mtime);\n" "create table if not exists " BUILDIDS "_r_sref (\n" // outgoing dwarf sourcefile references from rpm " buildid integer not null,\n" " artifactsrc integer not null,\n" -- 2.35.3