[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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



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 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/
>
>
>
> *** 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/
>


-- 
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