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

[Ben Walton]

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.

-Ben

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
>                                        www.engelschall.com
>
>
>
> _______________________________________________
> Monotone-devel mailing list
> address@hidden
> http://lists.nongnu.org/mailman/listinfo/monotone-devel
>


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

---------------------------------------------------------------------------------------------------------------------------