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

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