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

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