[aur-dev] [PATCH] 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> --- Lukas- said missing index was the cause of the increased CPU usage/load on sigurd, it appears. I already created this index in production as I was the one who forgot it in my last set of patches, whoops! -Dan UPGRADING | 4 ++++ support/schema/aur-schema.sql | 1 + 2 files changed, 5 insertions(+), 0 deletions(-) diff --git a/UPGRADING b/UPGRADING index da85a8d..7878f63 100644 --- a/UPGRADING +++ b/UPGRADING @@ -7,6 +7,10 @@ From 1.8.2 to 1.9.0 1. Translation files are now gettext compatible and need to be compiled after each AUR upgrade by running `make install` in the "po/" directory. +2. Fix up issues with depends performance on large dataset. + +ALTER TABLE PackageDepends ADD INDEX (DepName); + From 1.8.1 to 1.8.2 ------------------- diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index d8c8560..da6a1f9 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -125,6 +125,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.4.4
On Mon, Apr 11, 2011 at 08:03:43PM -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> ---
Lukas- said missing index was the cause of the increased CPU usage/load on sigurd, it appears. I already created this index in production as I was the one who forgot it in my last set of patches, whoops!
How serious is this? I didn't check server load recently. If this one really stresses the server, we should probably put this into another maintainance release instead of 1.9.0 (although I'm not sure if anyone will care).
On Tue, Apr 12, 2011 at 11:23:38AM +0200, Lukas Fleischer wrote:
On Mon, Apr 11, 2011 at 08:03:43PM -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> ---
Lukas- said missing index was the cause of the increased CPU usage/load on sigurd, it appears. I already created this index in production as I was the one who forgot it in my last set of patches, whoops!
How serious is this? I didn't check server load recently. If this one really stresses the server, we should probably put this into another maintainance release instead of 1.9.0 (although I'm not sure if anyone will care).
Just had a look at the Munin stats for sigurd. Looks like the missing index makes CPU usage go up by an average of 30% which is quite much...
On Tue, Apr 12, 2011 at 4:36 AM, Lukas Fleischer <archlinux@cryptocrack.de> wrote:
On Tue, Apr 12, 2011 at 11:23:38AM +0200, Lukas Fleischer wrote:
On Mon, Apr 11, 2011 at 08:03:43PM -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> ---
Lukas- said missing index was the cause of the increased CPU usage/load on sigurd, it appears. I already created this index in production as I was the one who forgot it in my last set of patches, whoops!
How serious is this? I didn't check server load recently. If this one really stresses the server, we should probably put this into another maintainance release instead of 1.9.0 (although I'm not sure if anyone will care).
Just had a look at the Munin stats for sigurd. Looks like the missing index makes CPU usage go up by an average of 30% which is quite much...
I wouldn't worry about it unless you really want to spend time preparing a release- if you know one site running the AUR code that is at even 10% of the capacity of the main site, I'd be surprised. And they should be following this list anyway, and could apply the index at any time. :) -Dan
On Tue, Apr 12, 2011 at 09:10:13AM -0500, Dan McGee wrote:
On Tue, Apr 12, 2011 at 4:36 AM, Lukas Fleischer <archlinux@cryptocrack.de> wrote:
On Tue, Apr 12, 2011 at 11:23:38AM +0200, Lukas Fleischer wrote:
On Mon, Apr 11, 2011 at 08:03:43PM -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> ---
Lukas- said missing index was the cause of the increased CPU usage/load on sigurd, it appears. I already created this index in production as I was the one who forgot it in my last set of patches, whoops!
How serious is this? I didn't check server load recently. If this one really stresses the server, we should probably put this into another maintainance release instead of 1.9.0 (although I'm not sure if anyone will care).
Just had a look at the Munin stats for sigurd. Looks like the missing index makes CPU usage go up by an average of 30% which is quite much...
I wouldn't worry about it unless you really want to spend time preparing a release- if you know one site running the AUR code that is at even 10% of the capacity of the main site, I'd be surprised. And they should be following this list anyway, and could apply the index at any time. :)
Yeah. I also noticed that this wouldn't be easy without releasing new features (e.g. the gettext stuff that I pushed before) at the same time. We'd have to create a new maintainance branch and cherry-pick or rebase what is in master right now. On the other side, having separate master and maintainance branches might be a good thing anyway...
participants (3)
-
Dan McGee
-
Dan McGee
-
Lukas Fleischer