EPrints Technical Mailing List Archive

See the EPrints wiki for instructions on how to join this mailing list and related information.

Message: #01680

< Previous (by date) | Next (by date) > | < Previous (in thread) | Next (in thread) > | Messages - Most Recent First | Threads - Most Recent First

[EP-tech] Re: Eprints critical block on cache insert

Better yet, instead of producing monster (sub)queries EPrints should use multiple SELECT INTOs with temporary tables to store intermediate results to obtain acceptable performance in general case. 

Tim Brody wrote:

Hi,

These are normally coming out of a very big search query, where you get
a load of table joins for each term.

Perhaps the internal search should have a sanity check to throw an error
(or truncate) if too many terms are sent at it.

/Tim.

On Fri, 2013-03-08 at 13:56 +0100, Paolo Tealdi wrote:

On 03/08/2013 01:05 PM, Lee Paton wrote:

Hi Lee.

No. I changed again the global parameter setting it to 0 and mysql is working like a charme since the changing. Considering the freezing frequency, i think that
it should already have happened at least one time. I controlled the search a few times and the system answers more or less in 8-9 seconds.


Best regards,
Paolo Tealdi




Hi Paolo

I've been experiencing the same problem and your solution has solved it on our dev system

Have you run into any issues on your server since you made the change?

Thanks

Lee

Lee Paton
Information Services
Cardiff University
40-42 Park Place
Cardiff
CF10 3BB



From: Paolo Tealdi<paolo.tealdi@polito.it>
To: "<eprints-tech@ecs.soton.ac.uk>"<eprints-tech@ecs.soton.ac.uk>
Date: 12/02/2013 08:38
Subject: [EP-tech]  Eprints critical block on cache insert
Sent by: eprints-tech-bounces@ecs.soton.ac.uk
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



Dear all,

i'm debugging some misterious mysql hanging that sometimes happen on our server (2-3 times a week). They happen during the night and when i can check the server i find the eprint server almost swaped out with a
VERY big number of httpd process, all the mysql connection used  and tipically a huge cache insert (the attachment is the last insert found) apparently blocking the mysql server.
Doing a SHOW FULL PROCESSLIST\G i found the older active thread

       Id: 961782
     User: eprints
     Host: giasone.polito.it:35530
       db: eprints3310
Command: Query
     Time: 50077
    State: statistics
     Info:  ... the select in attachment ...

You can notice that the process state is in "statistics" status.

Googling i found that it seems that, if in a select there are involved a big number of tables, mysql query optimizer can block itself indefinitely analyzing that transaction, in the statistic state.
It seems that setting  optimizer_search_depth variable  to a low value this problem disappear.

The default is

optimizer_search_depth = 62

i put it to

set global optimizer_search_depth = 5;

and seems to resolve the blocking issue.
A side effect seems to be a slight speedup in general when you're doing advanced searches (those query can be "important") .

Anybody has found this problem ?

Best regards,
Paolo Tealdi

--
Ing. Paolo Tealdi         Area IT - Politecnico Torino
Telefono/Phone : +39-011-0906714 , FAX : +39-011-0906799
Indirizzo/Address : C.so Duca degli Abruzzi,  24 - 10129 Torino - ITALY
Skype : tealdi.paolo
Please consider your environmental responsibility before printing this e-mail
[attachment "select_bloccata_07022013.sql" deleted by Lee Paton/scolgp/CardiffUniversity] *** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
*** Archive: http://www.eprints.org/tech.php/
*** EPrints community wiki: http://wiki.eprints.org/



*** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
*** Archive: http://www.eprints.org/tech.php/
*** EPrints community wiki: http://wiki.eprints.org/








*** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
*** Archive: http://www.eprints.org/tech.php/
*** EPrints community wiki: http://wiki.eprints.org