On Wed, Sep 16, 2009 at 11:06 PM, Loui Chang <louipc....@gmail.com> wrote: > On Wed 16 Sep 2009 22:48 -0500, Dan McGee wrote: >> OMG, query benchmarking? I've never seen this in the AUR before. :P > > Hah, why not. There's kind of a tradition of being picky with the JSON > interface. > >> I would just do it right and return a maintainer name. Worry about >> performance later; there really is little to no penalty here for this. >> I already told you one query that needs optimizing the other day that >> is much more prevalent than this one. > > Which other day? I thought you fixed all of them. > Please refresh my memory. Thanks.
Attached is the processed full query from sigurd, generated like so: $ sudo mysqldumpslow -s t /var/lib/mysql/sigurd-slow.log | gzip -9 > slow-queries-processed.txt.gz It is sorted by total time, so those queries that run the most (as opposed to take the most time per query) will bubble to the top, as those are the best ones to attack. Here are the top two entries in that file, for example: Count: 49 Time=2.19s (107s) Lock=0.02s (1s) Rows=26.5 (1300), aur[a...@localhost SELECT SQL_CALC_FOUND_ROWS Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY Name ASC, LocationID ASC, CategoryID DESC LIMIT N, N Count: 50 Time=2.01s (100s) Lock=0.00s (0s) Rows=25.0 (1250), aur[a...@localhost SELECT SQL_CALC_FOUND_ROWS CommentNotify.UserID AS Notify, PackageVotes.UsersID AS Voted, Users.Username AS Maintainer, PackageCategories.Category, PackageLocations.Location, Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, Packages.ID, Packages.OutOfDate FROM Packages LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) LEFT JOIN PackageVotes ON (Packages.ID = PackageVotes.PackageID AND PackageVotes.UsersID = N) LEFT JOIN CommentNotify ON (Packages.ID = CommentNotify.PkgID AND CommentNotify.UserID = N) LEFT JOIN PackageCategories ON (Packages.CategoryID = PackageCategories.ID) LEFT JOIN PackageLocations ON (Packages.LocationID = PackageLocations.ID) WHERE Packages.DummyPkg = N ORDER BY Name ASC, LocationID ASC, CategoryID DESC LIMIT N, N These two queries (for 99 total slow queries) are generated from the package view page. One has votes and one does not; that may be logged in vs. not logged in. For something like this, the best bet is to run an explain on the query, see why it sucks (I can already tell you it is extremely inefficient here at using the right indexes), and try to improve it. In all honesty, the AUR can keep up with this stuff just fine, and the server isn't overloaded, so it probably isn't a big deal, but it is a good exercise if you are interested in performance. -Dan
slow-queries-processed.txt.gz
Description: GNU Zip compressed data
