[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Monotone-devel] Monotone speedup by adding additional database indi

From: Ben Walton
Subject: Re: [Monotone-devel] Monotone speedup by adding additional database indices?
Date: Wed, 10 Oct 2007 14:20:31 -0400

Indexes speed up read operations but slow down writes.  Which do we do
more of?  I'd optimize for the case that would benefit most.  I
suspect we do a fair amount of both.  In this case, maybe adding
indexes for mostly-read tables would be the way to go.


On 10/10/07, Ralf S. Engelschall <address@hidden> wrote:
> Some Monotone operations really operate slower than what one would
> expect in the first spot. Hence, I've today looked at the run-time of
> a simple "mtn update" in a workspace which *is already* at h:n.v.m.
> This "no-operation" command internally performs a dozend times the
> following SQL queries:
>   SELECT id, name, value, keypair, signature
>          FROM revision_certs WHERE id = ? AND name = ? AND value = ?
>   SELECT keydata FROM public_keys WHERE id = ?
>   SELECT id FROM public_keys WHERE id = ?
> The problem is that "revision_certs" and "public_keys" have not the
> proper indices for those queries and hence full-table scans seem to
> be performed. I did a quick test and added the following to indices
> manually:
>   CREATE INDEX revision_certs__id_name_value ON
>                revision_certs (id, name, value);
>   CREATE INDEX public_keys__id ON
>                public_keys (id);
> This dropped down the total execution time of the mentioned "mtn update"
> command by over 80%! A "time mtn update" showed 0.450s on average before
> and 0.080s on average afterwards. And this was really not any type of
> in-depth analysis of the situation. I just created two obvious indices
> for the most prominent queries which "mtn --debug update" showed me.
> What do we think? Should we investigate further and especially add
> additional indices like the above to the Monotone database schema? Or is
> there consensus that this type of speed optimization is just the root of
> furthcoming evil and at least at this time should be still ignored at
> all...
>                                        Ralf S. Engelschall
>                                        address@hidden
> _______________________________________________
> Monotone-devel mailing list
> address@hidden

Ben Walton <address@hidden>

When one person suffers from a delusion, it is called insanity. When
many people suffer from a delusion it is called Religion.
Robert M. Pirsig, Zen and the Art of Motorcycle Maintenance


reply via email to

[Prev in Thread] Current Thread [Next in Thread]