[aur-dev] [PATCH 1/2] Fix performance issues with new PackageDepends lookups
We do a lookup by DepName in the package details view, but I made the silly mistake of forgetting this index addition in the upgrade steps. Signed-off-by: Dan McGee <dan@archlinux.org> --- Not sure if this fell through the cracks way back in April... UPGRADING | 6 ++++-- support/schema/aur-schema.sql | 1 + 2 files changed, 5 insertions(+), 2 deletions(-) diff --git a/UPGRADING b/UPGRADING index ad7ad96..51afad3 100644 --- a/UPGRADING +++ b/UPGRADING @@ -9,9 +9,11 @@ each AUR upgrade by running `make install` in the "po/" directory. 2. Remove the "NewPkgNotify" column from the "Users" table: ----- ALTER TABLE Users DROP COLUMN NewPkgNotify; ----- + +3. Fix up issues with depends performance on large dataset. + +ALTER TABLE PackageDepends ADD INDEX (DepName); 3. Rename "web/lib/config.inc" to "web/lib/config.inc.php". diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index e64ae82..88d074e 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -123,6 +123,7 @@ CREATE TABLE PackageDepends ( DepName VARCHAR(64) NOT NULL, DepCondition VARCHAR(20), INDEX (PackageID), + INDEX (DepName), FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE ) ENGINE = InnoDB; -- 1.7.5.4
We were doing some silly things here with an "ORDER BY Name, CategoryID" clause, due to the fact that Name is unique, and thus any additional ordering after Name will have no effect. Of course, the dumb as a box of rocks MySQL query optimizer doesn't realize this, leading to full table scans every time of ~30000 packages instead of using index scans for the ordering and only retrieving the first 50 rows. The biggest change is noted in the default sort order as it cuts down the columns to one, but we can remove the redundant sort from other orderings as well, even though those will still not be able to use an index-driven query plan. Signed-off-by: Dan McGee <dan@archlinux.org> --- This will directly address 2 of the top 5 queries in the sigurd MySQL slow query log. web/lib/pkgfuncs.inc.php | 8 ++++---- 1 files changed, 4 insertions(+), 4 deletions(-) diff --git a/web/lib/pkgfuncs.inc.php b/web/lib/pkgfuncs.inc.php index 46768f2..8cd1c61 100644 --- a/web/lib/pkgfuncs.inc.php +++ b/web/lib/pkgfuncs.inc.php @@ -472,23 +472,23 @@ function pkg_search_page($SID="") { $order = (isset($_GET["SO"]) && $_GET["SO"] == 'd') ? 'DESC' : 'ASC'; - $q_sort = "ORDER BY Name ".$order.", CategoryID DESC "; + $q_sort = "ORDER BY Name ".$order." "; $sort_by = isset($_GET["SB"]) ? $_GET["SB"] : ''; switch ($sort_by) { case 'c': $q_sort = "ORDER BY CategoryID ".$order.", Name ASC "; break; case 'v': - $q_sort = "ORDER BY NumVotes ".$order.", Name ASC, CategoryID DESC "; + $q_sort = "ORDER BY NumVotes ".$order.", Name ASC "; break; case 'w': if ($SID) { - $q_sort = "ORDER BY Voted ".$order.", Name ASC, CategoryID DESC "; + $q_sort = "ORDER BY Voted ".$order.", Name ASC "; } break; case 'o': if ($SID) { - $q_sort = "ORDER BY Notify ".$order.", Name ASC, CategoryID DESC "; + $q_sort = "ORDER BY Notify ".$order.", Name ASC "; } break; case 'm': -- 1.7.5.4
On Wed, Jun 22, 2011 at 10:04:11AM -0500, Dan McGee wrote:
We were doing some silly things here with an "ORDER BY Name, CategoryID" clause, due to the fact that Name is unique, and thus any additional ordering after Name will have no effect. Of course, the dumb as a box of rocks MySQL query optimizer doesn't realize this, leading to full table scans every time of ~30000 packages instead of using index scans for the ordering and only retrieving the first 50 rows.
The biggest change is noted in the default sort order as it cuts down the columns to one, but we can remove the redundant sort from other orderings as well, even though those will still not be able to use an index-driven query plan.
Signed-off-by: Dan McGee <dan@archlinux.org> ---
This will directly address 2 of the top 5 queries in the sigurd MySQL slow query log.
Ack, thanks.
On Wed, Jun 22, 2011 at 10:04:10AM -0500, Dan McGee wrote:
We do a lookup by DepName in the package details view, but I made the silly mistake of forgetting this index addition in the upgrade steps.
Signed-off-by: Dan McGee <dan@archlinux.org> ---
Not sure if this fell through the cracks way back in April...
Oh, yeah. I somehow missed that. Thanks for the reminder.
UPGRADING | 6 ++++-- support/schema/aur-schema.sql | 1 + 2 files changed, 5 insertions(+), 2 deletions(-)
diff --git a/UPGRADING b/UPGRADING index ad7ad96..51afad3 100644 --- a/UPGRADING +++ b/UPGRADING @@ -9,9 +9,11 @@ each AUR upgrade by running `make install` in the "po/" directory.
2. Remove the "NewPkgNotify" column from the "Users" table:
----- ALTER TABLE Users DROP COLUMN NewPkgNotify; ----- + +3. Fix up issues with depends performance on large dataset. + +ALTER TABLE PackageDepends ADD INDEX (DepName);
3. Rename "web/lib/config.inc" to "web/lib/config.inc.php".
Seems like you forgot to adjust the numbering when rebasing the patch. I'll fix that and push later.
diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index e64ae82..88d074e 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -123,6 +123,7 @@ CREATE TABLE PackageDepends ( DepName VARCHAR(64) NOT NULL, DepCondition VARCHAR(20), INDEX (PackageID), + INDEX (DepName), FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE ) ENGINE = InnoDB;
-- 1.7.5.4
participants (2)
-
Dan McGee
-
Lukas Fleischer