<font size=2 face="sans-serif">Hi Paolo</font>
<br>
<br><font size=2 face="sans-serif">I've been experiencing the same problem
and your solution has solved it on our dev system</font>
<br>
<br><font size=2 face="sans-serif">Have you run into any issues on your
server since you made the change?</font>
<br>
<br><font size=2 face="sans-serif">Thanks</font>
<br>
<br><font size=2 face="sans-serif">Lee</font>
<br>
<br><font size=2 face="sans-serif">Lee Paton <br>
Information Services<br>
Cardiff University<br>
40-42 Park Place<br>
Cardiff<br>
CF10 3BB</font>
<br>
<br>
<br>
<br><font size=1 color=#5f5f5f face="sans-serif">From:
</font><font size=1 face="sans-serif">Paolo Tealdi <paolo.tealdi@polito.it></font>
<br><font size=1 color=#5f5f5f face="sans-serif">To:
</font><font size=1 face="sans-serif">"<eprints-tech@ecs.soton.ac.uk>"
<eprints-tech@ecs.soton.ac.uk></font>
<br><font size=1 color=#5f5f5f face="sans-serif">Date:
</font><font size=1 face="sans-serif">12/02/2013 08:38</font>
<br><font size=1 color=#5f5f5f face="sans-serif">Subject:
</font><font size=1 face="sans-serif">[EP-tech] Eprints
critical block on cache insert</font>
<br><font size=1 color=#5f5f5f face="sans-serif">Sent by:
</font><font size=1 face="sans-serif">eprints-tech-bounces@ecs.soton.ac.uk</font>
<br>
<hr noshade>
<br>
<br>
<br><tt><font size=2>Dear all,<br>
<br>
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<br>
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.<br>
Doing a SHOW FULL PROCESSLIST\G i found the older active thread<br>
<br>
Id: 961782<br>
User: eprints<br>
Host: giasone.polito.it:35530<br>
db: eprints3310<br>
Command: Query<br>
Time: 50077<br>
State: statistics<br>
Info: ... the select in attachment ...<br>
<br>
You can notice that the process state is in "statistics" status.<br>
<br>
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.<br>
It seems that setting optimizer_search_depth variable to a
low value this problem disappear.<br>
<br>
The default is<br>
<br>
optimizer_search_depth = 62<br>
<br>
i put it to<br>
<br>
set global optimizer_search_depth = 5;<br>
<br>
and seems to resolve the blocking issue.<br>
A side effect seems to be a slight speedup in general when you're doing
advanced searches (those query can be "important") .<br>
<br>
Anybody has found this problem ?<br>
<br>
Best regards,<br>
Paolo Tealdi<br>
<br>
-- <br>
Ing. Paolo Tealdi Area IT - Politecnico Torino<br>
Telefono/Phone : +39-011-0906714 , FAX : +39-011-0906799<br>
Indirizzo/Address : C.so Duca degli Abruzzi, 24 - 10129 Torino -
ITALY<br>
Skype : tealdi.paolo<br>
Please consider your environmental responsibility before printing this
e-mail<br>
[attachment "select_bloccata_07022013.sql" deleted by Lee Paton/scolgp/CardiffUniversity]
*** Options: </font></tt><a href="http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech"><tt><font size=2>http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech</font></tt></a><tt><font size=2><br>
*** Archive: </font></tt><a href=http://www.eprints.org/tech.php/><tt><font size=2>http://www.eprints.org/tech.php/</font></tt></a><tt><font size=2><br>
*** EPrints community wiki: </font></tt><a href=http://wiki.eprints.org/><tt><font size=2>http://wiki.eprints.org/</font></tt></a><tt><font size=2><br>
</font></tt>
<br>