Bug 15406 - Very SLOW OAI server performance
Summary: Very SLOW OAI server performance
Status: RESOLVED DUPLICATE of bug 15108
Alias: None
Product: Koha
Classification: Unclassified
Component: Web services (show other bugs)
Version: 3.20
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on: 15108
Blocks: 15342
  Show dependency treegraph
 
Reported: 2015-12-22 11:07 UTC by grharry
Modified: 2017-05-05 14:51 UTC (History)
7 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description grharry 2015-12-22 11:07:08 UTC
I noticed a very slow performance on oai server koha version 3.22 ! 

In particular the query 

(
SELECT biblioitems.biblionumber, biblioitems.timestamp, marcxml 
FROM biblioitems JOIN oai_sets_biblios ON biblioitems.biblionumber = oai_sets_biblios.biblionumber  
WHERE timestamp >= '1970-01-01 00:00:00' AND timestamp <= '2015-12-22 23:59:59'  AND oai_sets_biblios.set_id = '4' 
)       
UNION 
(
SELECT deletedbiblio.biblionumber, null as marcxml, timestamp 
FROM deletedbiblio JOIN oai_sets_biblios ON deletedbiblio.biblionumber = oai_sets_biblios.biblionumber  
WHERE DATE(timestamp) >= '1970-01-01T00:00:00Z' AND DATE(timestamp) <= '2015-12-22T23:59:59Z'  AND oai_sets_biblios.set_id = '4' 
) 
ORDER BY biblionumber LIMIT 51 OFFSET 0

As constructed by oai.pl 

Requires about a minute to get results for each fetch! 

So according to my rough calculations 
a 500K marcs catalogue would require about 6 days to be harvested!

Harry!
Comment 1 Frédéric Demians 2016-02-02 07:44:20 UTC
Bug 15106 contains the solution. Especially, an index must be added to biblioitems/deletebiblioitems timestamp field:

KEY `timestamp_bibno` (`timestamp`, `biblionumber`)

You can confirm that this solution works for you by altering your DB schema:

ALTER TABLE biblioitems ADD KEY `timestamp_bibno` (`timestamp`, `biblionumber`);
ALTER TABLE deletedbiblioitems ADD KEY `timestamp_bibno` (`timestamp`, `biblionumber`)
Comment 2 Jonathan Druart 2016-02-02 09:17:53 UTC
(In reply to Frédéric Demians from comment #1)
> Bug 15106 contains the solution.

Must be a typo, bug 15106 is about batch patron modification.
Comment 3 Frédéric Demians 2016-02-02 10:20:26 UTC
Yes, sorry. It's bug 15108.
Comment 4 Marcel de Rooy 2016-12-09 10:35:10 UTC
Frederic,
Can you confirm that 15108 would actually resolve this one?
Comment 5 Frédéric Demians 2016-12-09 12:08:00 UTC
(In reply to Marcel de Rooy from comment #4)
> Frederic,
> Can you confirm that 15108 would actually resolve this one?

Yes. Even I haven't re-check recently.
Comment 6 Olli-Antti Kivilahti 2017-03-14 13:58:32 UTC
Then please go sign it off!
Comment 7 Tomás Cohen Arazi 2017-05-05 14:51:54 UTC

*** This bug has been marked as a duplicate of bug 15108 ***