I'm working on a busy server, trying to get everything to run
smoothly. I think that I have it all going well, but a couple of days
ago I had an unexplained MySQL crash. It happened late at night when
the server wasn't heavily loaded, but the number of threads spiked
from 1 to 53.32.

Luckily, I was right there to restart MySQL, and haven't had a problem
since. I had made several changes to my.cnf and httpd.conf, and have
made several more changes since (like, increasing thread_cache_size to
4 instead of the default 0), but since I was never able to duplicate
the error, I don't know if I've fixed the problem or not.

Now, I worry that it might crash when I'm not here, and that I
wouldn't know to restart it for several hours. I was thinking of
setting up a simple cron to run every 5 minutes:

if (!$dbh = @mysql_connect("localhost", "username", "password")) {
mail('hostmaster (AT) mydomain (DOT) com','mysql restarted',mysql_error());
exec('/etc/rc.d/init.d/mysql restart');
}

I figure that this will test for a connection, and if I have a problem
then it will restart automatically within 5 minutes, and then alert me
by email to the problem.

My question is this: when I'm having a high server load, is this going
to cause the load to be even higher? Or, since I'm not running any
queries, would it just utilize any persistent connection already made
(having no impact on the load)?

TIA,

Jason

Jason Carlton wrote:
Could it be that your optimization uses more memory than you have on the
server? It can bee good to calculate how much you need, here is the formula
that MySQL seems to suggest from time to time ir running out of ram:

key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K
bytes of memory


Why not just check if the mysql process is running, there is a pid file which
contains the process id, if you don't find it, then restart mysql (your
example would restart the mysql server when all threads would have been used
and make all ongoing processes accessing the database to fail).

You could combine the two methods, taking a check that the mysql is still
responding either by random or by a algorithm of yours.



Of course it will append to the load that you have.


--

//Aho

Hi,

do you have any entries in your MySQL errorlog?

..../mysql/<hostname>.err

You may want to play with scripts like:

mytop, innotop
tuning-primer: http://gert.sos.be/demo/mysqlar

Check if you system starts heavy swapping,

What does that mean? obviously a typo?

kind regards,

Toni

On 7 Feb, 11:14, Jason Carlton <jwcarl... (AT) gmail (DOT) com> wrote:

On Mon, 8 Feb 2010 05:49:42 -0800 (PST), Captain Paralytic wrote:
Average over time.

--
How about an Australian-language version? 'Your program just attempted
an illegal instruction. No worries, mate.'
-- Paul Tomblin

On 8 Feb, 14:01, "Peter H. Coffin" <hell... (AT) ninehells (DOT) com> wrote:
Not if the "number of threads spiked". A spike is not an average.