EPrints Technical Mailing List Archive

Message: #05460

< 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

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



-- 
Ing. Paolo Tealdi         Area IT - Politecnico Torino
Telefono/Phone : +39-011-0906714 , FAX : +39-011-0906625
Indirizzo/Address : C.so Duca degli Abruzzi,  24 - 10129 Torino - ITALY
Skype : tealdi.paolo
Please consider your environmental responsibility before printing this e-mail