EPrints Technical Mailing List Archive

Message: #05462

< 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

Paolo,

 have you played with the query optimizer parameters?

http://dev.mysql.com/doc/refman/5.6/en/controlling-optimizer.html
Maybe they just changed from 5.1 to 5.6 or you forgot to apply the same paramenters. In particular the |optimizer_search_depth| <http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_optimizer_search_depth> and |optimizer_prune_level| <http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_optimizer_prune_level> variables.
The real solution, anyway, is to switch to Xapian and forgot sql queries for search. Also saves a lot of mysql tables and if there are problems, only the search functionality is affected: 

http://getting-started-with-xapian.readthedocs.org/en/latest/concepts/indexing/databases.html

"Backends
Xapian databases store data in custom formats which allow searches to be performed extremely quickly; Xapian does not use a relational database as its datastore." 

Il 07/03/2016 10:07, John Salter ha scritto:

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 ...