EPrints Technical Mailing List Archive

Message: #05470

< Previous (by date) | Next (by date) > | < Previous (in thread) | Next (in thread) > | Messages - Most Recent First | Threads - Most Recent First

Re: [EP-tech] select and mysql 5.6.27

Dear all,

thank you for your help.
Probably mysql 5.1 (anf 5.5) manages differently the the optimizer_search_depth set to 0 than mysql 5.6 does. Anyway i found the solution by myself setting to a low but NOT zero the above variable. Putting it directly in the mysql code is much better. So the other mysql instances do not suffer for low optimized queries. 

Best regards,
Paolo Tealdi


On 07/03/2016 11:57, John Salter wrote:

We're also on 3.3.10, and  have our optimizer_search_depth set to 3 (code below) which seems to work well for us.
This is useful: https://mariadb.com/blog/setting-optimizer-search-depth-mysql - and explains what a setting of 0 does too.

Cheers,
John

In: ~/perl_lib/EPrints/Database/mysql.pm
sub connect
{
         my( $self ) = @_;

         my $rc = $self->SUPER::connect();

         if( $rc )
         {
                 # always try to reconnect
                 $self->{dbh}->{mysql_auto_reconnect} = 1;

                 $self->do("SET NAMES 'utf8'");
                 # JLRS 2014-03-18
                 # See: https://github.com/eprints/eprints/issues/120
                 # See: http://www.eprints.org/tech.php/18677.html
                 $self->do('SET @@session.optimizer_search_depth = 3;');
         }
         elsif( $DBI::err == 1040 )
         {
                 EPrints->abort( "Error connecting to MySQL server: $DBI::errstr. To fix this increase max_connections in my.cnf:\n\n[mysqld]\nmax_connections=300\n" );
         }

         return $rc;
}



-----Original Message-----
From: eprints-tech-bounces@ecs.soton.ac.uk [mailto:eprints-tech-bounces@ecs.soton.ac.uk] On Behalf Of Paolo Tealdi
Sent: 07 March 2016 10:33
To: eprints-tech@ecs.soton.ac.uk
Subject: Re: [EP-tech] select and mysql 5.6.27

On 07/03/2016 09:25, Paolo Tealdi wrote:

Dear all,

After 5 year of normal use with mysql 5.1.XX, after the upgrade to mysql 5.6.27,
we noticed that our eprints sometimes (1 time every two day more or less)
suddendly blocks itself.
Analyzing the problem we noticed that one of the mysql connections from eprints
server (we are using a centralized version of mysql) is hanging in "statistics"
status on a very big select (in attach).
All the other processes are waiting for table lock.
After  the Kill of that processs,   all the other processes flushed immediately
and the server returns to life.

Have you ever noticed this BIG issue ? Any idea ?


In attach one of the sql commands.

Our optimizer_search_depth is set to 0.

Best regards,
Paolo Tealdi

P.S. One of the  problems probably is due to the eprint__rindex size ... 37M of
records ...



Hi all.

Thank for your answers.
I'll study more deeply all your answers.
What i want to  say to you is that :

-) the problem persists if i set optimizer_search_depth  to '62' (the server
default). I'll try with other values.
-) the problem disappears when i try it on a copy of my eprints production
database on my linux ubuntu workstation with a 5.5.47 vanilla mysql. :-(
(simply copying and pasting the query in mysql character client). That query
return an answer in 2 seconds, while on the production server had been killed
after 1 hour of down and mysql process at 100% cpu.
-) The base search is set on xapian search (almost it should be). I'll check it
-) the advanced search is on sql search
-) My eprints production  server is a modified (but not in the search engine
parts) version of 3.3.10 version. It's on since 2011 without many changes. None
in the indexing part.
-) I'm now comparing the system variable between the two mysql server. I'm
noticing that there are many new variables in the optimization section between
5.5 and 5.6.

I'll keep all you updated.


Best regards and thanks again,
Paolo

P.S. grrrrr



--
Ing. Paolo Tealdi         Area IT - Politecnico Torino
Telefono/Phone : +39-011-0906714 , FAX : +39-011-0906625
Indirizzo/Address : C.so Duca degli Abruzzi,  24 - 10129 Torino - ITALY
Skype : tealdi.paolo
Please consider your environmental responsibility before printing this e-mail