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

[EP-tech] Too many connections



Hi John and Jonathan - Thanks to you both for the detailed replies.

John: You've definitely given me something to investigate here as I think we are on MyISAM - thanks.  Time to perform some checks! :)

Cheers

George

From: eprints-tech-bounces at ecs.soton.ac.uk [mailto:eprints-tech-bounces at ecs.soton.ac.uk] On Behalf Of John Salter
Sent: 15 August 2016 10:51
To: eprints-tech at ecs.soton.ac.uk
Subject: Re: [EP-tech] Too many connections

Hi George,
My understanding is that the database max_connections is something that should be tuned in relation to your Apache config (and both should be tuned to the server resources available).

Recently we had been suffering with some performance issues.
We were hitting our Apache MaxClients - but the root cause seems to have been the database:
We were using MyISAM tables (the MySQL default when we implemented EPrints).
The following sequence of events caused us problems:

1.       Slow SELECT query (possibly from generate_views, but there were a few other possibilities - a paper with >2,500 authors...)

2.       Update on table included in above SELECT (whilst it was still running)

3.       SELECT on above table

MyISAM can handle parallel SELECT queries on the tables without any problem *until* you throw an UPDATE into the equation. MyISAM uses table-level locking, and the UPDATE will wait for the slow SELECT to complete, and the other SELECTs get queued behind the UPDATE.

If you have a MySQL session open when you hit a max_connections issue, do a:
mysql>SHOW PROCESSLIST\G
You might see a query in the 'sending' state (that's been running for a while) and a lot of 'waiting for table lock' entries.
If you do, you may well be seeing the same issue we had.

We have altered our database tables to use InnoDB (which is the default MySQL engine since 5.5.5), but you'll need to understand what you're doing when updating it if you go down this route...

I posted recently on this list about a [dataset]__index tables missing a primary key - I discovered this as part of the planning to move to InnoDB.

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] On Behalf Of George Macgregor
Sent: 15 August 2016 10:20
To: eprints-tech at ecs.soton.ac.uk<mailto:eprints-tech at ecs.soton.ac.uk>
Subject: [EP-tech] Too many connections

Hello colleagues.

Occasionally our EPrints suffers the "too many connections" MySQL connection error.  In the past when EPrints has suffered this error my assumption has been that there is a query hanging which then has a knock on effect on other queries as they can't write to a table.  We currently have max_connections set to 300 (which seems like a lot) but I can observe from previous chatter on the list that some go up to 1000.  I was therefore wondering what the community considered to be an appropriate connection limit?  Given all the traffic I can observe it is conceivable - however unlikely - that 300 is insufficient.  Have others observed something similar?

Thanks in advance!

George

--
George Macgregor
Information Services Directorate | University of Strathclyde
Curran Building | 101 St James Road
Glasgow G4 ONS
Tel: 0141 548 3496
Email: george.macgregor at strath.ac.uk<mailto:george.macgregor at strath.ac.uk>
Web: http://personal.strath.ac.uk/george.macgregor/
Twitter: @g3om4c<https://twitter.com/g3om4c>
[iD icon]orcid.org/0000-0002-8482-3973<http://orcid.org/0000-0002-8482-3973>
--
Ensure that your research outputs are eligible for submission in the next REF.  Authors' accepted manuscripts of journal articles and conference proceedings accepted after 01 April 2016 must be deposited in PURE as soon as possible after acceptance for publication - email openaccess at strath.ac.uk<mailto:openaccess at strath.ac.uk> for further information or visit http://www.strath.ac.uk/openaccess.

[twitter logo]<https://twitter.com/StrathclydeOA>
--
The University of Strathclyde is a charitable body, registered in Scotland, with registration number SC015263

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20160815/c9e97cf9/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 1261 bytes
Desc: image001.png
Url : http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20160815/c9e97cf9/attachment.png 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.png
Type: image/png
Size: 2196 bytes
Desc: image002.png
Url : http://mailman.ecs.soton.ac.uk/pipermail/eprints-tech/attachments/20160815/c9e97cf9/attachment-0001.png