EPrints Technical Mailing List Archive

See the EPrints wiki for instructions on how to join this mailing list and related information.

Message: #05461

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

Re: [EP-tech] select and mysql 5.6.27

Hi Paolo,
As John says Xapian will do a much better job handling such searches. Other options without switching to Xapian are:
1. Remove 'documents' field from simple search as this by far has the most entries in the index. It is unusual for a user to actual need full text search and they can always use advanced search if they do.
2. Change the way that MySQL optimises queries. This can be done by add the following line at line 244 of /opt/eprints3/perl_lib/EPrints/Database/mysql.pm 

$self->do('SET @@session.optimizer_search_depth = 3;');
I am not exactly sure this line number is right, as I do not know which version of EPrints you are running but this should come before the MySQL query itself is constructed. 

Regards

David Newman
EPrints Services

On 07/03/2016 09:07, John Salter wrote:

Hi Paolo,
I don’t think this is related to the version of MySQL (I may be wrong!).

I think I may have seen something similar on our server (older version of MySQL) when someone pastes a long citation into the 'simple search' box, or a search field that references a few eprint fields (in your case it's documents/title/abstract/creators_name/note/authors_string).
Reading through the SQL, the search was for something like:
"policy per l attivazione di contratti per ricercatore a tempo determinate ex art 24 comma 3 lettera a della legge 240"

As you can see, this generates quite a big SQL statement.

I don't have a solution for this - but possibly restricting the number of search elements that are used in the search (e.g. take the first 10 words) might be a possible way forward.

I think that the Xapian search works better for this - but we haven't got it on our live server - so can't confirm from experience.

Hope that helps a bit - if you get a good resolution, let me know!
Cheers,
John

-----Original Message-----
From: eprints-tech-bounces@ecs.soton.ac.uk [mailto:eprints-tech-bounces@ecs.soton.ac.uk] On Behalf Of Paolo Tealdi
Sent: 07 March 2016 08:25
To: eprints-tech@ecs.soton.ac.uk
Subject: [EP-tech] select and mysql 5.6.27

Dear all,

After 5 year of normal use with mysql 5.1.XX, after the upgrade to mysql 5.6.27,
we noticed that our eprints sometimes (1 time every two day more or less)
suddendly blocks itself.
Analyzing the problem we noticed that one of the mysql connections from eprints
server (we are using a centralized version of mysql) is hanging in "statistics"
status on a very big select (in attach).
All the other processes are waiting for table lock.
After  the Kill of that processs,   all the other processes flushed immediately
and the server returns to life.

Have you ever noticed this BIG issue ? Any idea ?


In attach one of the sql commands.

Our optimizer_search_depth is set to 0.

Best regards,
Paolo Tealdi

P.S. One of the  problems probably is due to the eprint__rindex size ... 37M of
records ...