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

[EP-tech] New system, crashing periodically b/c MySQL Locking



Hi John,

Thanks for the very prompt feedback. I don't have a line in mysql.pm so
I'll try setting that (to 3 or 4 as suggested). Interestingly the ideal
solution is to "produce better SQL" but isn't it only eprints producing the
SQL?

As for MyISAM I thought it was deprecated (or at least supplanted by
InnoDB) the system was only built 6 months ago. Can anybody tell me how I
can determine if it's using MyISAM?

Thanks again,
Fran


On Thu, 14 Feb 2019 at 15:28, John Salter <J.Salter at leeds.ac.uk> wrote:

> Hi Fran,
>
> A couple of things to look at:
>
> In ~/perl_lib/EPrints/Database/mysql.pm, do you have a line that
> references 'optimizer_search_depth'?
>
> If not, this might be useful:
> https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Feprints%2Feprints%2Fissues%2F120&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=hfSVWQXycG0wCsOiUPTGA26VwtAiUbvxGMpv39%2F88ME%3D&amp;reserved=0
>
>
>
>
>
> If you already have that, what about the storage engines the tables are
> using?
>
> MyISAM uses table-level locking, but InnoDB use row-level locking.
>
> I did some investigations a while ago, and found that some queries that
> looked like they shouldn't lock a table were doing so for some reason (I
> cannot recall the exact details - but I don't think it's an 'eprints' thing
> - I could recreate the scenario in the DB directly).
>
>
>
> Any of that help?
>
> Cheers,
>
> John
>
>
>
>
>
> *From:* eprints-tech-bounces at ecs.soton.ac.uk [mailto:
> eprints-tech-bounces at ecs.soton.ac.uk] *On Behalf Of *Fran Callaghan via
> Eprints-tech
> *Sent:* 14 February 2019 14:50
> *To:* eprints-tech at ecs.soton.ac.uk
> *Subject:* [EP-tech] New system, crashing periodically b/c MySQL Locking
>
>
>
> Hi Everybody,
>
>
>
> We have a (reasonably) new install that has been grinding to a halt every
> week or two. When I look at 'top' on the command line (Red Hat) mysqld is
> hogging 100% CPU. When I use mysqladmin processlist I can see a number of
> sessions queuing behind this...
>
>
>
> | 104558 | doras | localhost | doras | Query   | 1587 |
> statistics                   | INSERT INTO `cache17312`(`pos`, `eprintid`)
> SELECT @i:=@i+1, `eprintid` FROM (SELECT `eprint`.`eprin | 0.000    |
>
> The other processes (queued) are all waiting for table level lock and look
> like this...
>
>
>
> | 105134 | doras | localhost | doras | Query   | 487  | Waiting for table
> level lock | UPDATE `eprint` SET `edit_lock_user`='1037',
> `edit_lock_since`='1550139932', `edit_lock_until`='1550 | 0.000    |
>
> | 105135 | doras | localhost | doras | Query   | 486  | Waiting for table
> level lock | SELECT
> `eprintid`,`rev_number`,`eprint_status`,`userid`,`importid`,`source`,`dir`,`datestamp_year`,`
> | 0.000
>
>
>
> Eventually the max http processes is reached and the system crashes. The
> only remedy is to restart httpd and mariadb.
>
>
>
> Any ideas what this insert into cache is and why it's locking the the
> system?
>
> Thanks for any guidance
>
> Fran Callaghan
>
> Dublin City University
>
>
>
> *S?anadh R?omhphoist/Email Disclaimer*
>
> *T? an r?omhphost seo agus aon chomhad a sheoltar leis faoi r?n agus is
> lena ?s?id ag an seola? agus sin amh?in ?. Is f?idir tuilleadh a l?amh
> anseo.
> <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww4.dcu.ie%2Fiss%2Fseanadh-riomhphoist.shtml&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=gUq%2FNqdmMw0d4IaQNdOVHdJfMIcr7fGUc1rBWuHhObs%3D&amp;reserved=0>*
>
> *This e-mail and any files transmitted with it are confidential and are
> intended solely for use by the addressee. Read more here.
> <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww4.dcu.ie%2Fiss%2Femail-disclaimer.shtml&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=bZlDTTC34sPdEh9eBPFDEgPo4iRWiXF2CE8dqM%2Fu8kc%3D&amp;reserved=0>*
>
>
>
>
>
>
> [image: Image removed by sender.]
>
> [image: Image removed by sender.]
> <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.facebook.com%2FDCU%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=ILYmp0%2BaDNGnjUBUldUgRvOjOLZuSum2sBA7q8ufz50%3D&amp;reserved=0>[image:
> Image removed by sender.]
> <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2FDublinCityUni&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=QglXFwvEhzgUXtKi859RvBrl9LJKEUYYpSoVXWLE0bk%3D&amp;reserved=0>[image:
> Image removed by sender.]
> <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Fdublin-city-university&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=o6bswHaZjbiQF2pOP3mVuhIljzIzqo25ToSxDdvL0bQ%3D&amp;reserved=0>[image:
> Image removed by sender.]
> <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.instagram.com%2Fdublincityuniversity%2F%3Fhl%3Den&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=IY9vepv2L4ws5oyFgMgoRXZrbmheNLzOjzwR2EBiUJo%3D&amp;reserved=0>[image:
> Image removed by sender.]
> <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.youtube.com%2Fuser%2FDublinCityUniversity&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=SQ6AhhH1t5fOZEgdcnMX6%2FjpP0WUW7lw8edB6B%2B4I4k%3D&amp;reserved=0>
>

-- 
*

*S?anadh R?omhphoist/Email Disclaimer*

*T? an r?omhphost seo agus aon 
chomhad a sheoltar leis faoi r?n agus is lena ?s?id ag an seola? agus sin 
amh?in ?. Is f?idir tuilleadh a l?amh anseo.? 
<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww4.dcu.ie%2Fiss%2Fseanadh-riomhphoist.shtml&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=gUq%2FNqdmMw0d4IaQNdOVHdJfMIcr7fGUc1rBWuHhObs%3D&amp;reserved=0>*

*This e-mail and any 
files transmitted with it are confidential and are intended solely for use 
by the addressee. Read more here. 
<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww4.dcu.ie%2Fiss%2Femail-disclaimer.shtml&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=bZlDTTC34sPdEh9eBPFDEgPo4iRWiXF2CE8dqM%2Fu8kc%3D&amp;reserved=0>*



*

-- 

 <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.facebook.com%2FDCU%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=ILYmp0%2BaDNGnjUBUldUgRvOjOLZuSum2sBA7q8ufz50%3D&amp;reserved=0> <https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2FDublinCityUni&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=QglXFwvEhzgUXtKi859RvBrl9LJKEUYYpSoVXWLE0bk%3D&amp;reserved=0> 
<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Fdublin-city-university&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=o6bswHaZjbiQF2pOP3mVuhIljzIzqo25ToSxDdvL0bQ%3D&amp;reserved=0> 
<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.instagram.com%2Fdublincityuniversity%2F%3Fhl%3Den&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=IY9vepv2L4ws5oyFgMgoRXZrbmheNLzOjzwR2EBiUJo%3D&amp;reserved=0> 
<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.youtube.com%2Fuser%2FDublinCityUniversity&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C06d391cd309c4b45364208d6929273dc%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=SQ6AhhH1t5fOZEgdcnMX6%2FjpP0WUW7lw8edB6B%2B4I4k%3D&amp;reserved=0>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20190214/ed69b815/attachment-0001.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: ~WRD358.jpg
Type: image/jpeg
Size: 823 bytes
Desc: not available
Url : http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20190214/ed69b815/attachment-0001.jpg