EPrints Technical Mailing List Archive

Message: #06410

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

Re: [EP-tech] Long Query Lock

Hi Zemy Azmira,

 

Which version of EPrints are you using? Does it look like you have the 'optimizer_search_depth' setting described here:

https://github.com/eprints/eprints/issues/120

http://www.eprints.org/tech.php/18677.html

 

If not, does adding this help?

If not, when you have a slow query running, what does the 'State' show for the query when you run (on the mysql server):

>SHOW FULL PROCESS LIST;

 

Cheers,

John

 

From: eprints-tech-bounces@ecs.soton.ac.uk [mailto:eprints-tech-bounces@ecs.soton.ac.uk] On Behalf Of ZEMY AZMIRA IIUM
Sent: 31 March 2017 16:45
To: eprints-tech@ecs.soton.ac.uk
Subject: [EP-tech] Long Query Lock

 

​​Hi All,

 

We have a problem with slow database connection due to long query. Something it locks out the system...my finding the query is something like below. I think it is indexing. We did customized the repository with new fields, quite a lot ...

 

How to handle this problem? When this query is in the connection, it will slow down eprints and sometimes, I will need to kill this process.

 

Hope to get advise from everybody who might faced the same problem. 

 

Thank you in advance for your help. Have a nice weekend.

 

Zemy Azmira

IIUM Library, Malaysia

 

          INSERT INTO `cache15972759`(`pos`, `eprintid`) SELECT @i:=@i+1, `eprintid` FROM (SELECT `eprint`.`eprintid` FROM `eprint` LEFT JOIN `eprint__ordervalues_en` ON `eprint`.`eprintid`=`eprint__ordervalues_en`.`eprintid`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='evaluation') AS `and_139772665284464_0`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='effect') AS `and_139772665284464_1`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='copper') AS `and_139772665284464_2`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='electroplating') AS `and_139772665284464_3`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='parameter') AS `and_139772665284464_4`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='adhesion') AS `and_139772665284464_5`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='using') AS `and_139772665284464_6`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='response') AS `and_139772665284464_7`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='surface') AS `and_139772665284464_8`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`='title' AND `eprint__rindex`.`word`='methodology') AS `and_139772665284464_9` WHERE `eprint`.`eprintid`=`and_139772665284464_0`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_1`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_2`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_3`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_4`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_5`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_6`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_7`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_8`.`eprintid` AND `eprint`.`eprintid`=`and_139772665284464_9`.`eprintid` AND (`eprint`.`metadata_visibility` = 'show' AND (`eprint`.`eprint_status` = 'buffer' OR `eprint`.`eprint_status` = 'archive')) GROUP BY `eprint`.`eprintid`, `eprint__ordervalues_en`.`date`, `eprint__ordervalues_en`.`creators_name`, `eprint__ordervalues_en`.`title` ORDER BY `eprint__ordervalues_en`.`date` DESC, `eprint__ordervalues_en`.`creators_name` ASC, `eprint__ordervalues_en`.`title` ASC LIMIT 2147483647) `S`

Select all. | Invert selection.