[EP-tech] Eprints-tech Digest, Vol 103, Issue 4 -Long Query Lock
John Salter
J.Salter at leeds.ac.uk
Mon Apr 10 09:23:05 BST 2017
Excellent news!
- and thank you for realising the message had not got through and re-sending it.
Cheers,
John
From: eprints-tech-bounces at ecs.soton.ac.uk [mailto:eprints-tech-bounces at ecs.soton.ac.uk] On Behalf Of ZEMY AZMIRA IIUM
Sent: 10 April 2017 07:33
To: eprints-tech at ecs.soton.ac.uk
Subject: Re: [EP-tech] Eprints-tech Digest, Vol 103, Issue 4 -Long Query Lock
Hi All/Hi John,
I thought my email went through but it was bounced.
I think the additional line is working perfectly to solved this problem. I did not see the long script since last week.
For record we are using 3.3.15.
Thank You very much for your help.
Zemy
IIUM Library
On Tue, Apr 4, 2017 at 8:54 AM, <eprints-tech-bounces at ecs.soton.ac.uk<mailto:eprints-tech-bounces at ecs.soton.ac.uk>> wrote:
The results of your email command are provided below. Attached is your
original message.
- Results:
Ignoring non-text/plain MIME parts
- Unprocessed:
Thank you for your reply.
We are using 3.3.15=20
I will test adding the line in our file.
For this information=20
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;
The state is STATISTIC.
I will update when I test adding the line. TQ
Zemy
Sent from Mail for Windows 10
From: eprints-tech-request at ecs.soton.ac.uk<mailto:eprints-tech-request at ecs.soton.ac.uk>
Sent: Monday, April 3, 2017 4:43 PM
To: eprints-tech at ecs.soton.ac.uk<mailto:eprints-tech at ecs.soton.ac.uk>
Subject: Eprints-tech Digest, Vol 103, Issue 4
- Ignored:
Send Eprints-tech mailing list submissions to
eprints-tech at ecs.soton.ac.uk<mailto:eprints-tech at ecs.soton.ac.uk>
To subscribe or unsubscribe via the World Wide Web, visit
http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
or, via email, send a message with subject or body 'help' to
eprints-tech-request at ecs.soton.ac.uk<mailto:eprints-tech-request at ecs.soton.ac.uk>
You can reach the person managing the list at
eprints-tech-owner at ecs.soton.ac.uk<mailto:eprints-tech-owner at ecs.soton.ac.uk>
When replying, please edit your Subject line so it is more specific
than "Re: Contents of Eprints-tech digest..."
Today's Topics:
1. Re: Long Query Lock (John Salter)
----------------------------------------------------------------------
Message: 1
Date: Mon, 3 Apr 2017 08:39:58 +0000
From: John Salter <J.Salter at leeds.ac.uk<mailto:J.Salter at leeds.ac.uk>>
Subject: Re: [EP-tech] Long Query Lock
To: "eprints-tech at ecs.soton.ac.uk<mailto:eprints-tech at ecs.soton.ac.uk>" <eprints-tech at ecs.soton.ac.uk<mailto:eprints-tech at ecs.soton.ac.uk>>
Message-ID:
<DB6PR0301MB2311F9504FB5D8EAD3D89A5FC4080 at DB6PR0301MB2311.eurprd03.prod.ou=
tlook.com<http://tlook.com>>
=09
Content-Type: text/plain; charset=3D"utf-8"
Hi Zemy Azmira,
Which version of EPrints are you using? Does it look like you have the 'opt=
imizer_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 at ecs.soton.ac.uk<mailto:eprints-tech-bounces at ecs.soton.ac.uk> [mailto:eprints-tech-bounces at ecs<mailto:eprints-tech-bounces at ecs>=
.soton.ac.uk<http://soton.ac.uk>] On Behalf Of ZEMY AZMIRA IIUM
Sent: 31 March 2017 16:45
To: eprints-tech at ecs.soton.ac.uk<mailto:eprints-tech at ecs.soton.ac.uk>
Subject: [EP-tech] Long Query Lock
??Hi All,
We have a problem with slow database connection due to long query. Somethin=
g 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, qu=
ite a lot ...
How to handle this problem? When this query is in the connection, it will s=
low 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:=3D at i+1,=
`eprintid` FROM (SELECT `eprint`.`eprintid` FROM `eprint` LEFT JOIN `eprin=
t__ordervalues_en` ON `eprint`.`eprintid`=3D`eprint__ordervalues_en`.`eprin=
tid`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rin=
dex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=3D`eprint__rindex`.`epri=
ntid` AND `eprint__rindex`.`field`=3D'title' AND `eprint__rindex`.`word`=3D=
'evaluation') AS `and_139772665284464_0`, (SELECT `eprint`.`eprintid` AS `e=
printid` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`=
.`eprintid`=3D`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`=3D't=
itle' AND `eprint__rindex`.`word`=3D'effect') AS `and_139772665284464_1`, (=
SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS=
`eprint__rindex` WHERE `eprint`.`eprintid`=3D`eprint__rindex`.`eprintid` A=
ND `eprint__rindex`.`field`=3D'title' AND `eprint__rindex`.`word`=3D'copper=
') AS `and_139772665284464_2`, (SELE!
CT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `e=
print__rindex` WHERE `eprint`.`eprintid`=3D`eprint__rindex`.`eprintid` AND =
`eprint__rindex`.`field`=3D'title' AND `eprint__rindex`.`word`=3D'electropl=
ating') AS `and_139772665284464_3`, (SELECT `eprint`.`eprintid` AS `eprinti=
d` FROM `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`epri=
ntid`=3D`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`=3D'title' =
AND `eprint__rindex`.`word`=3D'parameter') AS `and_139772665284464_4`, (SEL=
ECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `e=
print__rindex` WHERE `eprint`.`eprintid`=3D`eprint__rindex`.`eprintid` AND =
`eprint__rindex`.`field`=3D'title' AND `eprint__rindex`.`word`=3D'adhesion'=
) AS `and_139772665284464_5`, (SELECT `eprint`.`eprintid` AS `eprintid` FRO=
M `eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=
=3D`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`=3D'title' AND `=
eprint__rindex`.`word`=3D'using') AS `a!
nd_139772665284464_6`, (SELECT `eprint`.`eprintid` AS `eprinti!
d` FROM=20
`eprint`, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=3D=
`eprint__rindex`.`eprintid` AND `eprint__rindex`.`field`=3D'title' AND `epr=
int__rindex`.`word`=3D'response') AS `and_139772665284464_7`, (SELECT `epri=
nt`.`eprintid` AS `eprintid` FROM `eprint`, `eprint__rindex` AS `eprint__ri=
ndex` WHERE `eprint`.`eprintid`=3D`eprint__rindex`.`eprintid` AND `eprint__=
rindex`.`field`=3D'title' AND `eprint__rindex`.`word`=3D'surface') AS `and_=
139772665284464_8`, (SELECT `eprint`.`eprintid` AS `eprintid` FROM `eprint`=
, `eprint__rindex` AS `eprint__rindex` WHERE `eprint`.`eprintid`=3D`eprint_=
_rindex`.`eprintid` AND `eprint__rindex`.`field`=3D'title' AND `eprint__rin=
dex`.`word`=3D'methodology') AS `and_139772665284464_9` WHERE `eprint`.`epr=
intid`=3D`and_139772665284464_0`.`eprintid` AND `eprint`.`eprintid`=3D`and_=
139772665284464_1`.`eprintid` AND `eprint`.`eprintid`=3D`and_13977266528446=
4_2`.`eprintid` AND `eprint`.`eprintid`=3D`and_139772665284464_3`.`eprintid=
` AND `eprint`.`eprintid`=3D`and_139772665!
284464_4`.`eprintid` AND `eprint`.`eprintid`=3D`and_139772665284464_5`.`ep=
rintid` AND `eprint`.`eprintid`=3D`and_139772665284464_6`.`eprintid` AND `e=
print`.`eprintid`=3D`and_139772665284464_7`.`eprintid` AND `eprint`.`eprint=
id`=3D`and_139772665284464_8`.`eprintid` AND `eprint`.`eprintid`=3D`and_139=
772665284464_9`.`eprintid` AND (`eprint`.`metadata_visibility` =3D 'show' A=
ND (`eprint`.`eprint_status` =3D 'buffer' OR `eprint`.`eprint_status` =3D '=
archive')) GROUP BY `eprint`.`eprintid`, `eprint__ordervalues_en`.`date`, `=
eprint__ordervalues_en`.`creators_name`, `eprint__ordervalues_en`.`title` O=
RDER BY `eprint__ordervalues_en`.`date` DESC, `eprint__ordervalues_en`.`cre=
ators_name` ASC, `eprint__ordervalues_en`.`title` ASC LIMIT 2147483647) `S`
Select all. | Invert selection.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/2017=
0403/39013dae/attachment.html=20
------------------------------
_______________________________________________
Eprints-tech mailing list
Eprints-tech at ecs.soton.ac.uk<mailto:Eprints-tech at ecs.soton.ac.uk>
http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
End of Eprints-tech Digest, Vol 103, Issue 4
********************************************
- Done.
---------- Forwarded message ----------
From: <azmira at iium.edu.my<mailto:azmira at iium.edu.my>>
To: "eprints-tech-request at ecs.soton.ac.uk<mailto:eprints-tech-request at ecs.soton.ac.uk>" <eprints-tech-request at ecs.soton.ac.uk<mailto:eprints-tech-request at ecs.soton.ac.uk>>
Cc:
Bcc:
Date: Tue, 4 Apr 2017 08:54:07 +0800
Subject: RE: Eprints-tech Digest, Vol 103, Issue 4 -Long Query Lock
Hi John,
Thank you for your reply.
We are using 3.3.15
I will test adding the line in our file.
For this information
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;
The state is STATISTIC.
I will update when I test adding the line. TQ
Zemy
Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10
From: eprints-tech-request at ecs.soton.ac.uk<mailto:eprints-tech-request at ecs.soton.ac.uk>
Sent: Monday, April 3, 2017 4:43 PM
To: eprints-tech at ecs.soton.ac.uk<mailto:eprints-tech at ecs.soton.ac.uk>
Subject: Eprints-tech Digest, Vol 103, Issue 4
Send Eprints-tech mailing list submissions to
eprints-tech at ecs.soton.ac.uk<mailto:eprints-tech at ecs.soton.ac.uk>
To subscribe or unsubscribe via the World Wide Web, visit
http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
or, via email, send a message with subject or body 'help' to
eprints-tech-request at ecs.soton.ac.uk<mailto:eprints-tech-request at ecs.soton.ac.uk>
You can reach the person managing the list at
eprints-tech-owner at ecs.soton.ac.uk<mailto:eprints-tech-owner at ecs.soton.ac.uk>
When replying, please edit your Subject line so it is more specific
than "Re: Contents of Eprints-tech digest..."
Today's Topics:
1. Re: Long Query Lock (John Salter)
----------------------------------------------------------------------
Message: 1
Date: Mon, 3 Apr 2017 08:39:58 +0000
From: John Salter <J.Salter at leeds.ac.uk<mailto:J.Salter at leeds.ac.uk>>
Subject: Re: [EP-tech] Long Query Lock
To: "eprints-tech at ecs.soton.ac.uk<mailto:eprints-tech at ecs.soton.ac.uk>" <eprints-tech at ecs.soton.ac.uk<mailto:eprints-tech at ecs.soton.ac.uk>>
Message-ID:
<DB6PR0301MB2311F9504FB5D8EAD3D89A5FC4080 at DB6PR0301MB2311.eurprd03.prod.outlook.com<mailto:DB6PR0301MB2311F9504FB5D8EAD3D89A5FC4080 at DB6PR0301MB2311.eurprd03.prod.outlook.com>>
Content-Type: text/plain; charset="utf-8"
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 at ecs.soton.ac.uk<mailto:eprints-tech-bounces at ecs.soton.ac.uk> [mailto:eprints-tech-bounces at ecs.soton.ac.uk<mailto:eprints-tech-bounces at ecs.soton.ac.uk>] On Behalf Of ZEMY AZMIRA IIUM
Sent: 31 March 2017 16:45
To: eprints-tech at ecs.soton.ac.uk<mailto:eprints-tech at 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`, (SELE!
CT `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 `a!
nd_139772665284464_6`, (SELECT `eprint`.`eprintid` AS `eprinti!
d` 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_139772665!
284464_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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20170403/39013dae/attachment.html
------------------------------
_______________________________________________
Eprints-tech mailing list
Eprints-tech at ecs.soton.ac.uk<mailto:Eprints-tech at ecs.soton.ac.uk>
http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
End of Eprints-tech Digest, Vol 103, Issue 4
********************************************
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20170410/b4ed0581/attachment-0001.html
More information about the Eprints-tech
mailing list