[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[EP-tech] New system, crashing periodically b/c MySQL Locking
This should do it:
SELECT
TABLE_NAME,
ENGINE
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'doras';
If you've migrated EPrints config, or migrated databases, there might be some old-format tables hanging around.
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 15:38
To: eprints-tech at ecs.soton.ac.uk
Subject: Re: [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<https://emea01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmysql.pm&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=nv0oW7a9m%2Fj9My4gmIRHiqn8ashklcNfWdbUSUvBn9U%3D&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<mailto: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&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=nv0oW7a9m%2Fj9My4gmIRHiqn8ashklcNfWdbUSUvBn9U%3D&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&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=CkkyQTHyZjaNUrY%2FUJHWWuxLZMX88Gueoh6QH%2BnIBhg%3D&reserved=0<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Feprints%2Feprints%2Fissues%2F120&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=CkkyQTHyZjaNUrY%2FUJHWWuxLZMX88Gueoh6QH%2BnIBhg%3D&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> [mailto: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<mailto: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&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=03a5667TFNavTsnIJorIARCp5roRje1I8355p9sTVEg%3D&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&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=EnnB6X47eC9696OVY6QzyS8SxufrKvNFDMEMObXQ5Uw%3D&reserved=0>
[Image removed by sender.]
[Image removed by sender.]<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.facebook.com%2FDCU%2F&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=Pjj3s4VgVvLmZvo0P3jfKD%2BmfJOtEJeWnSDDjbxdb3U%3D&reserved=0>[Image removed by sender.]<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2FDublinCityUni&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=8I8jwLu6agRBM9mamtOVbcaSjLYVpCxZE0k0WwNka9Q%3D&reserved=0>[Image removed by sender.]<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Fdublin-city-university&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=%2F2IoHRbRpj7rFG6WKZrLoHfbshlyI%2FemjRrOz7uZ1Tk%3D&reserved=0>[Image removed by sender.]<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.instagram.com%2Fdublincityuniversity%2F%3Fhl%3Den&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=%2BR4bkjbCkeAdYxiv1dXuGmAosgRlbRPVzSylkvJI8Dc%3D&reserved=0>[Image removed by sender.]<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.youtube.com%2Fuser%2FDublinCityUniversity&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=7GpJRT0g3Wylguk7%2Bd%2FzeTqxd4feMH67u5Auffgdipk%3D&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&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=03a5667TFNavTsnIJorIARCp5roRje1I8355p9sTVEg%3D&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&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=EnnB6X47eC9696OVY6QzyS8SxufrKvNFDMEMObXQ5Uw%3D&reserved=0>
[Image removed by sender.]
[Image removed by sender.]<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.facebook.com%2FDCU%2F&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=Pjj3s4VgVvLmZvo0P3jfKD%2BmfJOtEJeWnSDDjbxdb3U%3D&reserved=0>[Image removed by sender.]<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2FDublinCityUni&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=8I8jwLu6agRBM9mamtOVbcaSjLYVpCxZE0k0WwNka9Q%3D&reserved=0>[Image removed by sender.]<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Fdublin-city-university&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=%2F2IoHRbRpj7rFG6WKZrLoHfbshlyI%2FemjRrOz7uZ1Tk%3D&reserved=0>[Image removed by sender.]<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.instagram.com%2Fdublincityuniversity%2F%3Fhl%3Den&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=%2BR4bkjbCkeAdYxiv1dXuGmAosgRlbRPVzSylkvJI8Dc%3D&reserved=0>[Image removed by sender.]<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.youtube.com%2Fuser%2FDublinCityUniversity&data=01%7C01%7Ceprints-tech%40ecs.soton.ac.uk%7Cc399a01927574c230f3d08d692945459%7C4a5378f929f44d3ebe89669d03ada9d8%7C1&sdata=7GpJRT0g3Wylguk7%2Bd%2FzeTqxd4feMH67u5Auffgdipk%3D&reserved=0>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20190214/c139636f/attachment-0001.html
-------------- next part --------------
A non-text attachment was scrubbed...
Name: ~WRD107.jpg
Type: image/jpeg
Size: 823 bytes
Desc: ~WRD107.jpg
Url : http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20190214/c139636f/attachment-0001.jpg