[EP-tech] Eprints critical block on cache insert

[Paolo Tealdi]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: select_bloccata_07022013.sql
Type: text/x-sql
Size: 117581 bytes
Desc: not available
Url : http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20130212/87fb1978/attachment-0001.bin