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" <[EMAIL PROTECTED]>
To: "Chris Boot" <[EMAIL PROTECTED]>
Cc: mysql@lists.mysql.com
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" <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
