EPrints Technical Mailing List Archive

Message: #06430

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

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@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

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