Hi, I'm responsible for a large, dynamic website that gets from 100,000 -150,000 hits per day on average. We're using PHP/MySQL along with Apache on Mac OS X 10.1.3 to serve the site and overall it works very well. Sporadically though, we've been encountering a very strange problem where some of MySQL's threads seem to lose track of where the data directory is.
A typical example is, as follows: I'm alerted by my server monitoring scripts that a test page is not coming up. I check the main page via web browser and it still seems to be available. When I try to log in via the phpmyadmin web page, it doesn't see the usual databases that should be there, but instead displays a "database" that can't be accessed: zBooterMnt Connecting to the server via SSH, and launching the MySQL command-line client, I type show databases; and get the same result which I have discovered is actually the contents of the /private/var/tmp as opposed to the /usr/local/mysql/data directory. It's worth noting that after I noticed this behavior, I started explicitly setting the datadir value in the script where the database is launched which unfortunately, does not stop the behavior. If we leave the server in this state, eventually it stops serving data requests to the web server. Shutting the webserver down with mysqladmin -u root -p shutdown and restarting the daemon fixes the problem, temporarily, until it happens again The problem is not easily replicable, but seems to happen only after large amounts of traffic have been served. There are no spikes in traffic at the actual times it happens; they seem to be fairly random. Here's my theory. We're using persistent connections with PHP. Over time, they expire and bring up new ones. Eventually the problem occurs, and the pointer for datadir is overwritten with the pointer to tempdir. The persistent connections that were created before the problem (ie the ones associated with the most active apache children) continue serving the website with no problems for a while, because they have the old value for datadir stored in memory. Any new connections (such as those created by the infrequently used phymyadmin or mysql client) load the new corrupted value into their memory space and thus have the error. Over time, the old, good persistent connections expire and cause the website to stop responding. The error that appears in the logs is "Error in accept: Resource temporarily unavailable". I've activated query-logging as well, but there does not seem to be any one particular query that causes it, just an eerie period of silence in the logs once it happens. The results from a "variables" command after the event are at: http://homepage.mac.com/tonyniesz/variables.txt The results from "extended-status" are at: http://homepage.mac.com/tonyniesz/extended-status.txt The two data dumps refer to 3.23.46, but I've upgraded to 3.23.49a and the problem persists. Any help would be much appreciated. Tony Niesz --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
