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

[Justin Patrin]

On 10/10/07, Ben Walton <address@hidden> wrote:
> 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.
>

The size increase on the DB should also be investigated here. How much
did your DB increase in size when the indexes were added?

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


-- 
Justin Patrin