Trouble with plugin "MySQL Index Search Plugin"  "timeout "

Open Harvester Systems support questions and answers, bug reports, and development issues.

Moderators: jmacgreg, michael, John

Forum rules
The Public Knowledge Project Support Forum is moving to http://forum.pkp.sfu.ca

This forum will be maintained permanently as an archived historical resource, but all new questions should be added to the new forum. Questions will no longer be monitored on this old forum after March 30, 2015.
franciscogarrido
Posts: 53
Joined: Fri May 11, 2012 12:05 pm

Trouble with plugin "MySQL Index Search Plugin"  "timeout "

Postby franciscogarrido » Thu May 15, 2014 12:59 pm

I have an OHS installation with a very large database.
When I search across all repositories the browser throws the following error:"timeout " ("Se agotó el tiempo de espera"). Is this a documented problem? I have the 2.3.2.0 OHS version and the 1.0.0.0 mysqlIndex version.

Watching for your reply.

regards

asmecher
Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm
Contact:

Re: Trouble with plugin "MySQL Index Search Plugin"  "timeo

Postby asmecher » Thu May 15, 2014 1:04 pm

Hi franciscogarrido,

I'd suggest using the MySQL slow query log to find out what query is causing trouble, then using SQL's "explain" to examine what that query is doing. It might be that you're missing an index.

Regards,
Alec Smecher
Public Knowledge Project Team

franciscogarrido
Posts: 53
Joined: Fri May 11, 2012 12:05 pm

Re: Trouble with plugin "MySQL Index Search Plugin"  "timeo

Postby franciscogarrido » Mon May 19, 2014 8:37 am

Alec Smecher.

The problem occurs when you call the query generated in /plugins/generic/mysqlIndex/SearchDAO.inc.php "function &getPhraseResults"


Watching for your reply.

regards

franciscogarrido
Posts: 53
Joined: Fri May 11, 2012 12:05 pm

Re: Trouble with plugin "MySQL Index Search Plugin"  "timeo

Postby franciscogarrido » Mon May 19, 2014 9:03 am

Alec Smecher.


this is the query:


SELECT o.record_id AS record_id, MAX(o.raw_field_id) AS raw_field_id, COUNT(*) AS count, a.title
FROM records r LEFT JOIN archives a ON (r.archive_id = a.archive_id and a.continente= "Latinoamericano"),
search_objects o NATURAL JOIN search_object_keywords o0 NATURAL JOIN search_keyword_list k0
WHERE k0.keyword_text = ? AND
(r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ?
OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ?
OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ?
OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ?
OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ?
OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ?
OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ?
OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ?
OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ?
OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ? OR r.archive_id = ?
OR r.archive_id = ? OR r.archive_id = ?)
AND r.record_id = o.record_id
AND a.enabled = 1
GROUP BY o.record_id
ORDER BY count DESC LIMIT 500

regards

asmecher
Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm
Contact:

Re: Trouble with plugin "MySQL Index Search Plugin"  "timeo

Postby asmecher » Tue May 20, 2014 8:28 am

Hi franciscogarrido,

Have you customized this installation? The distribution doesn't have a "continente" column on the archives table.

Regards,
Alec Smecher
Public Knowledge Project Team

franciscogarrido
Posts: 53
Joined: Fri May 11, 2012 12:05 pm

Re: Trouble with plugin "MySQL Index Search Plugin"  "timeo

Postby franciscogarrido » Tue May 20, 2014 9:05 am

Alec Smecher.

yes, add a "continente" column to the table archives.

regards

asmecher
Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm
Contact:

Re: Trouble with plugin "MySQL Index Search Plugin"  "timeo

Postby asmecher » Tue May 20, 2014 9:35 am

Hi franciscogarrido,

What do you get by running an EXPLAIN statement on the above query in MySQL? (You may have to replace the ? placeholders with actual numerical values; it doesn't matter what you use.)

Regards,
Alec Smecher
Public Knowledge Project Team

franciscogarrido
Posts: 53
Joined: Fri May 11, 2012 12:05 pm

Re: Trouble with plugin "MySQL Index Search Plugin"  "timeo

Postby franciscogarrido » Tue May 20, 2014 11:30 am

Alec Smecher.

Associate image with the query result.

Image


Regards

asmecher
Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm
Contact:

Re: Trouble with plugin "MySQL Index Search Plugin"  "timeo

Postby asmecher » Tue May 20, 2014 12:05 pm

Hi franciscogarrido,

Hmm, that appears to use indexes throughout and should be reasonably quick. What happens when you run that query outright -- does it take long?

Regards,
Alec Smecher
Public Knowledge Project Team

franciscogarrido
Posts: 53
Joined: Fri May 11, 2012 12:05 pm

Re: Trouble with plugin "MySQL Index Search Plugin"  "timeo

Postby franciscogarrido » Tue May 20, 2014 12:16 pm

Alec Smecher.

The response time is very high so high that the lansa browser timeout error and loses the connection to the server.

In the example that sends the query is only a word but when many words are entered the error occurs because so used the system generates a query differently for each word you enter in the search term if full title is entered is even worse.
The response time is about 2 minutes for a search of a word.


Regards

asmecher
Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm
Contact:

Re: Trouble with plugin "MySQL Index Search Plugin"  "timeo

Postby asmecher » Tue May 20, 2014 12:52 pm

Hi franciscogarrido,

What about the specific query you ran through the EXPLAIN? If you run that in MySQL directly, does it take a long time?

Regards,
Alec Smecher
Public Knowledge Project Team

franciscogarrido
Posts: 53
Joined: Fri May 11, 2012 12:05 pm

Re: Trouble with plugin "MySQL Index Search Plugin"  "timeo

Postby franciscogarrido » Tue May 20, 2014 12:55 pm

Alec Smecher.


The picture above is put more specifcally what happens with the query and run the query directly in mysql 2 minutes delay


Regards

asmecher
Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm
Contact:

Re: Trouble with plugin "MySQL Index Search Plugin"  "timeo

Postby asmecher » Tue May 20, 2014 1:26 pm

Hi franciscogarrido,

This would be much easier to test/debug if I had a copy of the database -- but I suspect that's fairly large. Is it possible for you to put the database, gzipped, onto a Dropbox or something similar? Or, failing that, if you're harvesting open repositories, can you let me know their OAI URLs?

Regards,
Alec Smecher
Public Knowledge Project Team

franciscogarrido
Posts: 53
Joined: Fri May 11, 2012 12:05 pm

Re: Trouble with plugin "MySQL Index Search Plugin"  "timeo

Postby franciscogarrido » Tue May 20, 2014 2:17 pm

Alec Smecher.


harvesting list

-www.bibna.gub.uy/
-biiacs-dspace.cide.edu/
-www.conicyt.cl/
-www.alice.cnptia.embrapa.br/
-bdigital.zamorano.edu/
-bibdigital.epn.edu.ec/
-dspace.espoch.edu.ec/
-www.dspace.espol.edu.ec/
-www.flacsoandes.org/
-www.fjp.gov.br/
-www.arca.fiocruz.br/
-repositoriodigitalonemi.cl/web/
-repositorio.ibict.br/
-repositorio.iaen.edu.ec/
-repositorio.eppetroecuador.ec/
-bibliotecadigital.indh.cl
-www.museomedicina.cl:8080/dspace/
-repositorio.uc.cl
-repository.javeriana.edu.co/
-www.scielo.cl/
-bibliotecadigital.academia.cl/
-repositorio.uasb.edu.ec/
-biblioteca.unab.cl/client/inicio
-cybertesis.uach.cl/
-eprints.uanl.mx/
-bdigital.uao.edu.co/
-bibliotecadigital.uca.edu.ar/greenstone/cgi-bin/library.cgi
-repository.ucatolica.edu.co/
-repositoriodigital.uct.cl:8080/xmlui
-saber.ucv.ve/jspui/
-www.dspace.uce.edu.ec/
-ri.agro.uba.ar/cgi-bin/library.cgi
-www.captura.uchile.cl
-www.revistas.uchile.cl
-www.tesis.uchile.cl
-www.kerwa.ucr.ac.cr/
-dspace.ucuenca.edu.ec/
-ri.ues.edu.sv/
-www.universidad.edu.uy/
-repository.lasalle.edu.co/
-revistas.userena.cl/index.php/index/index
-www.saber.ula.ve/que_es_repositorio.jsp
-ri.bib.udo.edu.ve/
-repositorio.upr.edu:8080/jspui/
-repositorio.udesa.edu.ar/jspui/
-dspace.utalca.cl/index.jsp
-repository.urosario.edu.co
-repositorio.uned.ac.cr/reuned/
-www.revistas.una.ac.cr/
-www.bdigital.unal.edu.co/
-rdu.unc.edu.ar/
-www.repositorio.una.ac.cr/
-bdigital.uncu.edu.ar
-cybertesis.uni.edu.pe/sdx/uni/
-sedici.unlp.edu.ar
-www.memoria.fahce.unlp.edu.ar/
-nulan.mdp.edu.ar
-rephip.unr.edu.ar/
-ediblio.unsa.edu.ar/
-bibliotecavirtual.unl.edu.ar:8180/publicaciones/
-bibliotecadigital.uns.edu.ar/scielo.php
-ateneo.unmsm.edu.pe
-repositorio.usfq.edu.ec/
-repositorio.utn.edu.ec/
-repositorio.utp.edu.co/dspace/
-cdigital.uv.mx/
-repositorio.bce.unb.br/
-www.producao.usp.br/
-ri.uepg.br:8080/riuepg
-www.acervodigital.unesp.br/
- https://repositorio.ufba.br/ri/
-repositorio.ufsc.br/
-repositorio.ufes.br/
-www.repositorio.ufpa.br/jspui/
-repositorio.furg.br
-repositorio.ufrn.br:8080/jspui/
-www.lume.ufrgs.br/
-repositorio.uscs.edu.br/
-repositorio.utfpr.edu.br/jspui/
-www.manioc.org/
-mord.mona.uwi.edu/

Regards

asmecher
Posts: 10015
Joined: Wed Aug 10, 2005 12:56 pm
Contact:

Re: Trouble with plugin "MySQL Index Search Plugin"  "timeo

Postby asmecher » Tue May 20, 2014 2:21 pm

Hi franciscogarrido,

Can you give me the full OAI URLs rather than the home pages?

Regards,
Alec Smecher
Public Knowledge Project Team


Return to “Open Harvester Systems Support and Development”

Who is online

Users browsing this forum: No registered users and 1 guest