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

[EP-tech] Eprints-tech Digest, Vol 103, Issue 4 -Long Query Lock



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