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

[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>
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
>     Sent: Monday, April 3, 2017 4:43 PM
>     To: 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
>
>     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
>
>     You can reach the person managing the list at
>         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>
>     Subject: Re: [EP-tech] Long Query Lock
>     To: "eprints-tech at ecs.soton.ac.uk" <eprints-tech at ecs.soton.ac.uk>
>     Message-ID:
>         <DB6PR0301MB2311F9504FB5D8EAD3D89A5FC4080 at DB6PR0301MB2311.eu
> rprd03.prod.ou=
>     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] On Behalf Of ZEMY AZMIRA IIUM
>     Sent: 31 March 2017 16:45
>     To: 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'ele
> ctropl=
>     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'adh
> esion'=
>     ) 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_139
> 77266528446=
>     4_2`.`eprintid` AND `eprint`.`eprintid`=3D`and_139
> 772665284464_3`.`eprintid=
>     ` AND `eprint`.`eprintid`=3D`and_139772665!
>      284464_4`.`eprintid` AND `eprint`.`eprintid`=3D`and_139
> 772665284464_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/attach
> ments/2017=
>     0403/39013dae/attachment.html=20
>
>     ------------------------------
>
>     _______________________________________________
>     Eprints-tech mailing list
>     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>
> To: "eprints-tech-request at ecs.soton.ac.uk" <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
> *Sent: *Monday, April 3, 2017 4:43 PM
> *To: *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
>
>
>
> 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
>
>
>
> You can reach the person managing the list at
>
>               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>
>
> Subject: Re: [EP-tech] Long Query Lock
>
> To: "eprints-tech at ecs.soton.ac.uk" <eprints-tech at ecs.soton.ac.uk>
>
> Message-ID:
>
>               <DB6PR0301MB2311F9504FB5D8EAD3D89A5FC4080 at DB6PR0301MB2311.eu
> rprd03.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 e
> cs.soton.ac.uk] On Behalf Of ZEMY AZMIRA IIUM
>
> Sent: 31 March 2017 16:45
>
> To: 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/attach
> ments/20170403/39013dae/attachment.html
>
>
>
> ------------------------------
>
>
>
> _______________________________________________
>
> Eprints-tech mailing list
>
> 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/a434a4a7/attachment-0001.html