On Tue, Apr 4, 2017 at 8:54 AM, <eprints-tech-bounces@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@ecs.soton.ac.uk
    Sent: Monday, April 3, 2017 4:43 PM
    To: eprints-tech@ecs.soton.ac.uk
    Subject: Eprints-tech Digest, Vol 103, Issue 4

- Ignored:
    Send Eprints-tech mailing list submissions to
        eprints-tech@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@ecs.soton.ac.uk

    You can reach the person managing the list at
        eprints-tech-owner@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@leeds.ac.uk>
    Subject: Re: [EP-tech] Long Query Lock
    To: "eprints-tech@ecs.soton.ac.uk" <eprints-tech@ecs.soton.ac.uk>
    Message-ID:
        <DB6PR0301MB2311F9504FB5D8EAD3D89A5FC4080@DB6PR0301MB2311.eurprd03.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@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. 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@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@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@iium.edu.my>
To: "eprints-tech-request@ecs.soton.ac.uk" <eprints-tech-request@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 for Windows 10

 

From: eprints-tech-request@ecs.soton.ac.uk
Sent: Monday, April 3, 2017 4:43 PM
To: eprints-tech@ecs.soton.ac.uk
Subject: Eprints-tech Digest, Vol 103, Issue 4

 

Send Eprints-tech mailing list submissions to

              eprints-tech@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@ecs.soton.ac.uk

 

You can reach the person managing the list at

              eprints-tech-owner@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@leeds.ac.uk>

Subject: Re: [EP-tech] Long Query Lock

To: "eprints-tech@ecs.soton.ac.uk" <eprints-tech@ecs.soton.ac.uk>

Message-ID:

              <DB6PR0301MB2311F9504FB5D8EAD3D89A5FC4080@DB6PR0301MB2311.eurprd03.prod.outlook.com" target="_blank">DB6PR0301MB2311F9504FB5D8EAD3D89A5FC4080@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@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`, (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@ecs.soton.ac.uk

http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech

 

 

End of Eprints-tech Digest, Vol 103, Issue 4

********************************************