On 10/10/07, Ben Walton <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 > > [EMAIL PROTECTED] > > www.engelschall.com > > > > > > > > _______________________________________________ > > Monotone-devel mailing list > > Monotone-devel@nongnu.org > > http://lists.nongnu.org/mailman/listinfo/monotone-devel > > > > > -- > --------------------------------------------------------------------------------------------------------------------------- > Ben Walton <[EMAIL PROTECTED]> > > 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 > Monotone-devel@nongnu.org > http://lists.nongnu.org/mailman/listinfo/monotone-devel > -- Justin Patrin _______________________________________________ Monotone-devel mailing list Monotone-devel@nongnu.org http://lists.nongnu.org/mailman/listinfo/monotone-devel