Hello everyone, we are having a problem on our server with the mysqld
process hanging. We first notice the problem because our max
connections is reached. A "show processlist" query shows 250 queries
with a status of "locked."
When we try to issue the command: mysqladmin -u root -p shutdown, it
does not work and locks up. When we view a list of processes, we see
that mysqld has been running very long (over 1 hour, the time our site
stops working) and continues to run. Nothing we do will terminate that
process (we have
tried: kill <pid>, kill -s 9 <pid>, everything that is supposed to
forcibly kill the process and nothing works).
If we switch to the MySQL data directory and do a directory listing
(to try and investigate the log files), this process also freezes. I'm
just including this info because raise a flag for someone.
Ultimately, we are forced to reboot the machine, and of course check
and repair the entire database (since it was not shut down properly).
Due to the size of the database, this takes 2+ hours, and is obviously
horrible for our site.
Our site is extremely busy (250,000+ queries per day) and has been for
several years. We have never had problems in the past. Is it possible
our server or MySQL just can't handle the load? Meaning, is this the
most likely cause? Or is it more likely that one specific query is
causing the "crash"?
Could logging have something to do with this (we were using both of
the logs, regular and update, when the crashes occurred - could
disabling them
help?) We use a Cobalt RaQ550 with 1 GB of RAM (which is almost never
completely used) and dual processors (the load balance on the machine
is rarely over 1, let alone 2).
We have one potential cause of the problem. This only seems to occur
when our server is sending emails. Here's an overview of this process:
We use cron to execute a PHP script every few minutes. The script
queries a table we have setup and retrieves details for the mailings
to send (a few hundred per execution). It loops through the result and
sends the email inside the loop. Is it possible that this loop is
somehow not finishing, leaving the result set open, and ultimately
causing mysqld to crash?
Ultimately, the question is, "Is it poor practice to execute code
inside a result set loop if there is a possibility of the loop never
finishing?"
Sample code below.
Current:
$result = mysql_query(query for list of mailings to send) while ($row
= mysql_fetch_array($result)) {
// do a bunch of stuff
// send the mailing
}
mysql_free_result($result)
Possible better code:
$result = mysql_query(query for list of mailings to send) while ($row
= mysql_fetch_array($result)) {
// add to data type (perhaps array)
}
mysql_free_result($result)
loop through array {
// send mailing here
}
I'm throwing this information out there with absolutely no idea
whether or not this has anything to do with our problem. Any
information would be greatly appreciated!
Hardware: Cobalt RaQ550, 1 GB RAM, dual (fast) proc MySQL version 3.23.38
Thank You,
Brian Erickson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
