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

[Lee Paton]

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 at polito.it>
To:     "<eprints-tech at ecs.soton.ac.uk>" <eprints-tech at ecs.soton.ac.uk>
Date:   12/02/2013 08:38
Subject:        [EP-tech]  Eprints critical block on cache insert
Sent by:        eprints-tech-bounces at 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/

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20130308/a1ce0164/attachment.html