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

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



Hi John/All,

One more question. If I find MyISAM tables in my DB is it a simple matter
of ALTER TABLE isam_table_name ENGINE = innodb ?

Fran Callaghan


On Thu, 14 Feb 2019 at 15:47, Fran Callaghan via Eprints-tech <
eprints-tech at ecs.soton.ac.uk> wrote:

> Hi John,
>
> Thanks for the very prompt feedback. I don't have a line in mysql.pm
> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmysql.pm&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=20OFdG%2B34QxYZHG8cc8SgtL8YgJswSZMGiQGxZDJ6CU%3D&amp;reserved=0>
> 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
>> <https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmysql.pm&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=20OFdG%2B34QxYZHG8cc8SgtL8YgJswSZMGiQGxZDJ6CU%3D&amp;reserved=0>,
>> 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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=W5KhCao2ccVyEFYhLC3CPgxnrj0RM0Pg6W%2B4veuOv28%3D&amp;reserved=0
>> <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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=W5KhCao2ccVyEFYhLC3CPgxnrj0RM0Pg6W%2B4veuOv28%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=Q%2F%2BlQR6Pb9NX7vuNJ%2FIfHUOaIwlVcKqyUvNU%2F%2B6qPOA%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=Wy%2Bc8biERk8OYXcF0z8odm5lZpny5E2BbtMkkNLdl%2B8%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=4CeH0DQgJzpFc1D8OAY7pFVJqlwm64MJSk9Zo1nzuCQ%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=9NUbUDMs5qA%2FvHrRrP2IVyQIuNBFLlKbnK%2BZ6bTwz%2Bo%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=UzOgVXx0fTffgWm%2F1LjtoPtzV1d25nJntLM9gD5EOm4%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=4s1a4OWspBxMk1D4mXkUnkh1YOxVJbo9%2B4DzHm9YiYg%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=4j27LPb1Tr%2BC5%2FEzgPGwECP4O7coUzxkO070N5mJthc%3D&amp;reserved=0>
>>
>
>
>
> *S?anadh R?omhphoist/Email DisclaimerT? 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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=Q%2F%2BlQR6Pb9NX7vuNJ%2FIfHUOaIwlVcKqyUvNU%2F%2B6qPOA%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=Wy%2Bc8biERk8OYXcF0z8odm5lZpny5E2BbtMkkNLdl%2B8%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=4CeH0DQgJzpFc1D8OAY7pFVJqlwm64MJSk9Zo1nzuCQ%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=9NUbUDMs5qA%2FvHrRrP2IVyQIuNBFLlKbnK%2BZ6bTwz%2Bo%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=UzOgVXx0fTffgWm%2F1LjtoPtzV1d25nJntLM9gD5EOm4%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=4s1a4OWspBxMk1D4mXkUnkh1YOxVJbo9%2B4DzHm9YiYg%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=4j27LPb1Tr%2BC5%2FEzgPGwECP4O7coUzxkO070N5mJthc%3D&amp;reserved=0>
> *** Options: http://mailman.ecs.soton.ac.uk/mailman/listinfo/eprints-tech
> *** Archive: https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.eprints.org%2Ftech.php%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=Lja03vB%2FkpWr6%2FQQki7ZyglaSUeh%2BFWxqLJ%2FonELfnM%3D&amp;reserved=0
> *** EPrints community wiki: https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwiki.eprints.org%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=9ut4WS5ETH5se3iRibggiEgiO6aNocHTT%2FnWh79SQxU%3D&amp;reserved=0
> *** EPrints developers Forum: https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fforum.eprints.org%2F&amp;data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=UvsyN1dJtgFcLQ5zVnfdZFy9XEXE39Nbi7EGAJIYkRY%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=Q%2F%2BlQR6Pb9NX7vuNJ%2FIfHUOaIwlVcKqyUvNU%2F%2B6qPOA%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=Wy%2Bc8biERk8OYXcF0z8odm5lZpny5E2BbtMkkNLdl%2B8%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=4CeH0DQgJzpFc1D8OAY7pFVJqlwm64MJSk9Zo1nzuCQ%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=9NUbUDMs5qA%2FvHrRrP2IVyQIuNBFLlKbnK%2BZ6bTwz%2Bo%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=UzOgVXx0fTffgWm%2F1LjtoPtzV1d25nJntLM9gD5EOm4%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=4s1a4OWspBxMk1D4mXkUnkh1YOxVJbo9%2B4DzHm9YiYg%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%7C6b20b54b4e694f2437f408d693458c22%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&amp;sdata=4j27LPb1Tr%2BC5%2FEzgPGwECP4O7coUzxkO070N5mJthc%3D&amp;reserved=0>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20190215/2cacb997/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/20190215/2cacb997/attachment-0001.jpg