[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[EP-tech] select and mysql 5.6.27


  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| 
and |optimizer_prune_level| 

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!
> Cheers,
> John
> -----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 ...