List:General Discussion« Previous MessageNext Message »
From:Chris Boot Date:March 2 2007 7:56pm
Subject:Re: Heavily-loaded MySQL: Resource temporarily unavavailable?
View as plain text  
Rolando Edwards wrote:
> Also consider
> 
> wait_timeout in my.ini

This is set to 28800. I don't consider this a problem since I've hardly ever got 
anywhere near my connection limit.

> ----- Original Message -----
> From: "Rolando Edwards" <redwards@stripped>
> To: "Chris Boot" <bootc@stripped>
> Cc: mysql@stripped
> Sent: Friday, March 2, 2007 2:19:38 PM (GMT-0500) Auto-Detected
> Subject: Re: Heavily-loaded MySQL: Resource temporarily unavavailable?
> 
> I hope I am not asking dumb questions
> but please consider the following:
> 
> 1) Do you mysql_close every connection when rendering a page ?
> 2) Have you ever tried mysql_pconnect (which does not require doing a mysql_close to
> any persistent connection) ?

I have, and these suck up my connections like you wouldn't imagine. There are 
lots of different sites hosted on the server, and using persistent connections 
would require thousands of connections.

> 3) Do you have interactive_timeout set (in seconds) in my.ini (default is 28800 [8
> hours]) ?

It's set to the default. Once again I don't consider this an issue since 
connections don't stay open long enough for this to even be useful.

> 4) Do you have 'max_user_connections' set to zero (default anyway) in my.ini ?

I don't, this is set to 0.

I've done a few things since my first post that seem to have helped, though I'm 
not sure quite yet. I've upped the back_log from 50 to 128, upped thread_cache 
from 20 to 32, thread_concurrency from 4 to 32 (does this do anything on Linux?).

Those in themselves didn't seem to make much difference, however I have now made 
some of the busier sites connect to 127.0.0.1 (using TCP instead of UNIX 
sockets) which has either slowed connections enough to make the problem go away, 
or is making better use of back_log which I'm not sure is used for UNIX sockets. 
Any ideas?

Thanks again,
Chris

> 
> ----- Original Message -----
> From: "Chris Boot" <bootc@stripped>
> To: mysql@stripped
> Sent: Friday, March 2, 2007 1:34:40 PM (GMT-0500) Auto-Detected
> Subject: Heavily-loaded MySQL: Resource temporarily unavavailable?
> 
> All,
> 
> I'm running a loaded MySQL server and every so often MySQL seems to refuse
> connections in batches, which manifests itself as the following errors in PHP:
> 
> mysql_connect() [<a
> href='function.mysql-connect'>function.mysql-connect</a>]:
> Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'
> (11) [path to file] on line [x]
> 
> I've got a carefully tuned my.ini and Apache configuration, which can
> theoretically process 400 connections at a time and this works most of the time.
> MySQL is set to accept 600 simultaneous connections though this is never reached
> (according to phpMyAdmin's stats anyway). I've upped the open files limit on
> MySQL and Apache (PHP runs as a module).
> 
> At this stage I'm completely out of ideas as to what I can do to fix my problem.
> Any ideas? What extra information can I provide that could help?
> 
> Many thanks,
> Chris
> 


-- 
Chris Boot
bootc@stripped
http://www.bootc.net/
Thread
Heavily-loaded MySQL: Resource temporarily unavavailable?Chris Boot2 Mar
  • Re: Heavily-loaded MySQL: Resource temporarily unavavailable?Rolando Edwards2 Mar
    • Re: Heavily-loaded MySQL: Resource temporarily unavavailable?Rolando Edwards2 Mar
      • Re: Heavily-loaded MySQL: Resource temporarily unavavailable?Chris Boot2 Mar
        • Re: Heavily-loaded MySQL: Resource temporarily unavavailable?Jay Pipes2 Mar
          • Re: Heavily-loaded MySQL: Resource temporarily unavavailable?Chris Boot2 Mar
            • Re: Heavily-loaded MySQL: Resource temporarily unavavailable?Jay Pipes2 Mar
              • Re: Heavily-loaded MySQL: Resource temporarily unavavailable?Chris Boot2 Mar
        • Need sql optimization helpBryan Cantwell3 Mar
          • Re: Need sql optimization helpPeter Brawley3 Mar
            • RE: Need sql optimization helpBryan Cantwell3 Mar
              • Re: Need sql optimization helpMichael Dykman3 Mar
                • Re: Need sql optimization helpPeter Brawley3 Mar