public inbox for elfutils@sourceware.org
 help / color / mirror / Atom feed
* [PATCH 1/1] debuginfod: create indexes to speed up grooming
@ 2022-07-29 15:49 Josef Čejka
  2022-07-29 16:15 ` Frank Ch. Eigler
  2022-07-30 13:07 ` Frank Ch. Eigler
  0 siblings, 2 replies; 3+ messages in thread
From: Josef Čejka @ 2022-07-29 15:49 UTC (permalink / raw)
  To: elfutils-devel

From 32bd2715e2490fe7a56794b78563c799d456f4be Mon Sep 17 00:00:00 2001
From: Josef Cejka <jcejka@suse.de>
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 <jcejka@suse.de>
---
 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 <jcejka@suse.de>
+
+	* debuginfod.cxx: Create db indexes for fast delete while grooming.
+
 2022-06-03  Michael Trapp <michael.trapp@sap.com>
 
 	* 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





^ permalink raw reply	[flat|nested] 3+ messages in thread

* Re: [PATCH 1/1] debuginfod: create indexes to speed up grooming
  2022-07-29 15:49 [PATCH 1/1] debuginfod: create indexes to speed up grooming Josef Čejka
@ 2022-07-29 16:15 ` Frank Ch. Eigler
  2022-07-30 13:07 ` Frank Ch. Eigler
  1 sibling, 0 replies; 3+ messages in thread
From: Frank Ch. Eigler @ 2022-07-29 16:15 UTC (permalink / raw)
  To: Josef Čejka; +Cc: elfutils-devel

Hi -

> 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.

Pushed, thanks!

- FChE


^ permalink raw reply	[flat|nested] 3+ messages in thread

* Re: [PATCH 1/1] debuginfod: create indexes to speed up grooming
  2022-07-29 15:49 [PATCH 1/1] debuginfod: create indexes to speed up grooming Josef Čejka
  2022-07-29 16:15 ` Frank Ch. Eigler
@ 2022-07-30 13:07 ` Frank Ch. Eigler
  1 sibling, 0 replies; 3+ messages in thread
From: Frank Ch. Eigler @ 2022-07-30 13:07 UTC (permalink / raw)
  To: Josef Čejka; +Cc: elfutils-devel

Hi -

> 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. [...]

By the way, another possible approach to this is could be to create
those indexes only for the duration of a groom operation: Create at
start, drop at end.  It'd speed up a set of delete's almost as well,
and also save disk space during normal operations.  OTOH, the peak
disk space requirement would be the same, so if the server's running
low on index storage disk, it'll fail either way, so probably not
worth doing this.

- FChE


^ permalink raw reply	[flat|nested] 3+ messages in thread

end of thread, other threads:[~2022-07-30 13:07 UTC | newest]

Thread overview: 3+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2022-07-29 15:49 [PATCH 1/1] debuginfod: create indexes to speed up grooming Josef Čejka
2022-07-29 16:15 ` Frank Ch. Eigler
2022-07-30 13:07 ` Frank Ch. Eigler

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).