[aur-dev] Query Benchmarking

[Dan McGee]

On Wed, Sep 16, 2009 at 11:06 PM, Loui Chang <louipc.ist at 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[aur]@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[aur]@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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: slow-queries-processed.txt.gz
Type: application/x-gzip
Size: 1569 bytes
Desc: not available
URL: <http://mailman.archlinux.org/pipermail/aur-dev/attachments/20090916/005b9326/attachment.bin>