Hi!
>>>>> "Erik" == Erik Liljencrantz <[EMAIL PROTECTED]> writes:
Erik> Hi, I'm running an ancient version of mySQL (3.20.29 on Solaris 2.5.1) that
Erik> has worked well for a long time, but in recent months I've seen the "Too
Erik> many connections" message four times and had to kill the mysqld process and
Erik> restart with safe_mysqld.
Try starting mysqld with -O max_connections=200.
Erik> This last time I managed to do mysqladmin proc just before every new
Erik> connection were blocked and I saw this:
Erik> ...
Erik> | 35985 | someuser | somehost | somedb | Locked | insert...
Erik> | 36250 | webuser | localhost | somedb | Query | select...
Erik> | 36251 | webuser | localhost | somedb | Query | select...
Erik> | 36303 | webuser | localhost | somedb | Locked | select...
Erik> | 36304 | webuser | localhost | somedb | Locked | select...
Erik> | 36305 | webuser | localhost | somedb | Locked | select...
Erik> | 36306 | webuser | localhost | somedb | Locked | select...
Erik> | 36307 | webuser | localhost | somedb | Locked | select...
Erik> ...the list goes on with more locked selects, all on the same table as the
Erik> insert is waiting for...
Erik> It looks like the insert is waiting for the two running selects to finish
Erik> but the deamon is somehow stuck in a deadlock situation and new selects
Erik> keep coming, gets locked and the "Too many connections" message will
Erik> eventually show up...
The above is actually ok; If you have an SELECT running and execute
an INSERT followed with a new SELECT, the last select will wait
wait until the INSERT is completed before continuing.
The real question is why the select marked with 'query' takes a long time.
Erik> I know it's time to upgrade this old mySQL, I'm just looking for some
Erik> encouragement. Has similar problems occured before in older versions of
Erik> mySQL and been solved in newer versions? What about the problems reported
Erik> by Brian and Darren with mySQL 3.22.14 on Solaris 2.5.1 last week (Too many
Erik> connections in subject)?
MySQL 3.22 supports HIGH_PRIORITY SELECT's that will execute even if
there are inserts waiting for another SELECT. (MySQL also support
LOW_PRIORITY INSERT's with will allow SELECT to take precedence)
Erik> Are there specific logic errors that can cause deadlocks when connecting a
Erik> database to a webserver and performing many selects and some inserts? (I
Erik> never call "lock table" or similar)
I don't know of anything that could cause a deadlock; In the above
case you may only have to wait until the SELECT threads are ready.
Note that in MySQL 3.22 there is an extra connection available for a
user with the 'process' privilege; This will allow the user to
examine the state of the mysqld process even if other clients gets the
'too many connections' error message.
Regards,
Monty
-----------------------------------------------------------
Send a mail to [EMAIL PROTECTED] with
unsubscribe mysql [EMAIL PROTECTED]
in the body of the message to unsubscribe from this list.
