[EP-tech] select and mysql 5.6.27
yurj at alfa.it
Mon Mar 7 09:37:33 GMT 2016
have you played with the query optimizer parameters?
Maybe they just changed from 5.1 to 5.6 or you forgot to apply the same
paramenters. In particular the |optimizer_search_depth|
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:
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!
> -----Original Message-----
> From: eprints-tech-bounces at ecs.soton.ac.uk [mailto:eprints-tech-bounces at ecs.soton.ac.uk] On Behalf Of Paolo Tealdi
> Sent: 07 March 2016 08:25
> To: eprints-tech at 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 ...
More information about the Eprints-tech