EPrints Technical Mailing List Archive

Message: #01674

< 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

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/