EPrints Technical Mailing List Archive

Message: #03980

< Previous (by date) | Next (by date) > | < Previous (in thread) | Next (in thread) > | Messages - Most Recent First | Threads - Most Recent First

[EP-tech] Re: DB slow down.

Hi there,

more info on the db slow down. Looking at a trace from the DB, it isn't the commit that seems to be taking the time but a select over the event_queue which gets triggered on a regular basis and is taking somewhere just short of a minute to run. The select from the log is:

SELECT "event_queue"."eventqueueid" FROM "event_queue", (SELECT "event_queue"."eventqueueid" AS "eventqueueid" FROM "event_queue", (SELECT "event_queue"."eventqueueid" AS "eventqueueid" FROM "event_queue" WHERE "event_queue"."status" = 'waiting' UNION SELECT "event_queue"."eventqueueid" AS "eventqueueid" FROM "event_queue" WHERE "event_queue"."status" = 'inprogress') AS "or_64363808" WHERE "event_queue"."eventqueueid"="or_64363808"."eventqueueid") AS "and_64365552_0" WHERE "event_queue"."eventqueueid"="and_64365552_0"."eventqueueid" AND "event_queue"."hash" = 'b6b50f1eeaa27f7737d6844b4ab13a76' GROUP BY "event_queue"."eventqueueid"

in this case it takes about 37secs but that's one of the 'quicker' ones.
The event_queue table has a little over 4 million rows.

Any thoughts?

Cheers,

Ray.



________________________________________
From: eprints-tech-bounces@ecs.soton.ac.uk <eprints-tech-bounces@ecs.soton.ac.uk> on behalf of Field A.N. <af05v@ecs.soton.ac.uk>
Sent: 25 February 2015 11:35
To: eprints-tech@ecs.soton.ac.uk
Subject: [EP-tech] Re: DB slow down.

Hi Ray

        A first step would be to check for database corruption.  Run mysqlcheck and see if if finds anything wrong...

--
Adam Field
Business Relationship Manager and Community Lead
EPrints Services




On 25 Feb 2015, at 10:34, CARRICK Ray wrote:

> Hi folks,
>
> we have a problem with DB slow down. This is running on 3.2 - we plan to test on 3.3 to see if there's any difference but haven't done so yet. The problem occurs when importing and from putting tracing into the code seems to be happening with a commit.
>
> - We have a QA setup which has about 6,500 eprints and a live server which has 650,000.
>
> - An import which takes 2-3 mins on the QA server takes 25mins on the live.
>
> - The time seems to be going on 'commit' - that's eprints commit (I haven't dug into it any further yet).
>
> - On the QA server it takes fractional time but on the live server this takes about 1 min.
>
> So if we have a main record plus, say, 25 associated documents, the time is approx 1min for the main record plus 1 min per document, hence abut 25 mins. The records we are loading typically have somewhere in the region of 10 to 40 associated documents.
>
> Has anyone come up against this before?
> Any thoughts on how to proceed?
>
> Thanks,
>
> Ray.
>
> The University of Edinburgh is a charitable body, registered in
> Scotland, with registration number SC005336.
> *** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
> *** Archive: http://www.eprints.org/tech.php/
> *** EPrints community wiki: http://wiki.eprints.org/
> *** EPrints developers Forum: http://forum.eprints.org/


*** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
*** Archive: http://www.eprints.org/tech.php/
*** EPrints community wiki: http://wiki.eprints.org/
*** EPrints developers Forum: http://forum.eprints.org/

-- 
The University of Edinburgh is a charitable body, registered in
Scotland, with registration number SC005336.