{"thread":{"id":"6007","subject":"[PATCH] Use column indexes in git-cvsserver where necessary.","startedAt":"2006-10-23T05:09:35Z","lastAt":"2006-10-23T06:02:01Z","messageCount":2,"participants":["Shawn Pearce","Martin Langhoff (CatalystIT)"],"isPatch":true,"patchVersion":1,"patchTotal":null},"messages":[{"id":"29700","messageId":"20061023050934.GA25018@spearce.org","threadId":"6007","inReplyTo":null,"subject":"[PATCH] Use column indexes in git-cvsserver where necessary.","fromName":"Shawn Pearce","fromEmail":"spearce@spearce.org","sentAt":"2006-10-23T05:09:35Z","receivedAt":"2006-10-23T05:09:35Z","isPatch":true,"sender":{"key":"spearce@spearce.org","avatar":"https://avatars.githubusercontent.com/u/34844?v=4"},"body":"Tonight I found a git-cvsserver instance spending a lot of time in\ndisk IO while trying to process operations against a Git repository\nwith >30,000 objects contained in it.\n\nBlowing away my SQLLite database and rebuilding all tables with\nindexes on the attributes that git-cvsserver frequently runs queries\nagainst seems to have resolved the issue quite nicely.\n\nSince the indexes shouldn't hurt performance on small repositories\nand always helps on larger repositories we should just always create\nthem when creating the revision storage tables.\n\nSigned-off-by: Shawn O. Pearce <spearce@spearce.org>\n---\n git-cvsserver.perl |   14 +++++++++++++-\n 1 files changed, 13 insertions(+), 1 deletions(-)\n\ndiff --git a/git-cvsserver.perl b/git-cvsserver.perl\nindex 08ad831..8817f8b 100755\n--- a/git-cvsserver.perl\n+++ b/git-cvsserver.perl\n@@ -2118,9 +2118,17 @@ sub new\n                 mode       TEXT NOT NULL\n             )\n         \");\n+        $self->{dbh}->do(\"\n+            CREATE INDEX revision_ix1\n+            ON revision (name,revision)\n+        \");\n+        $self->{dbh}->do(\"\n+            CREATE INDEX revision_ix2\n+            ON revision (name,commithash)\n+        \");\n     }\n \n-    # Construct the revision table if required\n+    # Construct the head table if required\n     unless ( $self->{tables}{head} )\n     {\n         $self->{dbh}->do(\"\n@@ -2134,6 +2142,10 @@ sub new\n                 mode       TEXT NOT NULL\n             )\n         \");\n+        $self->{dbh}->do(\"\n+            CREATE INDEX head_ix1\n+            ON head (name)\n+        \");\n     }\n \n     # Construct the properties table if required\n-- \n1.4.3.g4e51\n"},{"id":"29702","messageId":"453C5AD9.4030008@catalyst.net.nz","threadId":"6007","inReplyTo":"20061023050934.GA25018@spearce.org","subject":"Re: [PATCH] Use column indexes in git-cvsserver where necessary.","fromName":"Martin Langhoff (CatalystIT)","fromEmail":"martin@catalyst.net.nz","sentAt":"2006-10-23T06:02:01Z","receivedAt":"2006-10-23T06:02:01Z","isPatch":true,"sender":{"key":"martin@laptop.org","avatar":null},"body":"Shawn Pearce wrote:\n> Tonight I found a git-cvsserver instance spending a lot of time in\n> disk IO while trying to process operations against a Git repository\n> with >30,000 objects contained in it.\n> \n> Blowing away my SQLLite database and rebuilding all tables with\n> indexes on the attributes that git-cvsserver frequently runs queries\n> against seems to have resolved the issue quite nicely.\n> \n> Since the indexes shouldn't hurt performance on small repositories\n> and always helps on larger repositories we should just always create\n> them when creating the revision storage tables.\n >\n > Signed-off-by: Shawn O. Pearce <spearce@spearce.org>\n\nAck.\n\nI am thinking we need a lightweight schema versioning mechanism to \ndecide whether the DB schema needs changes such as this. Too much work \nfor this though. ;-)\n\nWe have a simple one in Moodle I could port when the time comes.\n\ncheers,\n\n\nmartin\n-- \n-----------------------------------------------------------------------\nMartin @ Catalyst .Net .NZ  Ltd, PO Box 11-053, Manners St,  Wellington\nWEB: http://catalyst.net.nz/           PHYS: Level 2, 150-154 Willis St\nOFFICE: +64(4)916-7224                              MOB: +64(21)364-017\n       Make things as simple as possible, but no simpler - Einstein\n-----------------------------------------------------------------------\n"}]}