List:General Discussion« Previous MessageNext Message »
From:Jay Pipes Date:March 2 2007 9:33pm
Subject:Re: Heavily-loaded MySQL: Resource temporarily unavavailable?
View as plain text  
Chris Boot wrote:
> 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.

Yeah, don't use pconnect.

>> 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?).

The thread_cache (I believe you mean *thread_cache_size*?)  is the 
number of THD* cleared instances that the server keeps internally so 
that the memory allocation of creating a new THD (connection class 
instance) is mitigated.  But, unless you have very high concurrent 
connections, this likely will not make much of a difference.  Look at 
the difference between Connections and Threads_created status variables 
to see what percentage of your connections are being created from 
scratch (as opposed to partial recycling via the thread_cache)

thread_concurrency won't do anything on Linux.  It's for Solaris.

However, innodb_thread_concurrency is different.  It's the maximum (or 
infinite if set to 0 or more than 20) number of operating system threads 
that InnoDB can use in its queueing system.  I wouldn't recommend 
changing this.

> 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?

You may want to try reverting that and simply turning off networking 
entirely, choosing to use unix sockets for everything:

--skip-networking

This should provide a good connection time reduction.

See here for more information on that option:

http://dev.mysql.com/doc/refman/5.0/en/dns.html

Other variables you may want to look at is ensuring that your 
table_cache (or table_open_cache if 5.1+) has enough room to deal with 
600 connections * the number of average tables in a typical SQL 
expression executed against the server.  If this variable value is 
really low, you could be experiencing file descriptor swap/thrashing as 
so many threads are opening and then closing file descriptors rapidly.

Cheers,

Jay
> 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
>>
> 
> 

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