I'm running mysql 3.23.41 on a PIII 800 Mhz machine with 512MB RAM,
linux kernel 2.4.7, dedicated machine (ie, not running anything other
than MySQL). I've been running mysql in a production environment for
nearly 2 years, moderately hit (500,000 - 800,000 database connects per
day) with no problems until very recently, and I'm trying to figure out
what's going on.
I just installed the 3.23.41 from source about 2 weeks ago, and fairly
shortly thereafter I started seeing my load levels increase
dramatically. This was on a faster machine with 1GB RAM...last week
the hard drive developed bad sectors on a part of the disk where one of
my larger and more frequently written tables lived, and that machine
died a horrible death. So I chalked the load level increase up to bad
hard drive, put the above-described machine in its place, and the load
levels went thru the roof again.
Ran isamchk and myisamchk -r on all problem tables. Verified the disk
integrity...but yesterday the *new* disk failed at a block containing
another big and heavily hit table started with the bad blocks...caught
this one in time and moved the table someplace else. Two disks in one
week makes me think of power fluctuations, and I'm wondering if this
could be causing me to think the problem is MySQL and really it's a
power spike during a table write?? And I wouldn't be seeing the same
thing on the web server because it's not written to very often, unlike
the mysql server...
I'm having a lot of trouble tracing the origin of the problem --
sometimes it seems that accessing a particular large table is causing
it, sometimes it seems that a combination of factors is causing it.
Regardless, what I observe is that within 1 minute my load level climbs
from between 2 and 4 to over 100, which I have never seen on any *nix
system before. The RAM utilization is high, but not over 85%, and the
CPU utilization fluctuates of course but stays below 40% user until
whatever is causing my problems happens, and then it jumps to 100% and
doesn't come down until I kill mysqld and let everything close.
The database is being accessed almost entirely by PHP calls from
php4.0.6 running with apache 1.3.20, using PHP's built in mysql
functions. The web server lives on a different machine. Does anyone
know of any problems with PHP and the newer versions of MySQL? What
about large table handling on linux? I realise that if the tables
aren't constructed properly they could cause problems, but these tables
aren't *that* big...the data files are no more than 200Mb. And they
seem to be using indexes correctly, and they've been running fine for
nearly 2 years. These are mostly ISAM tables, if that makes a
difference.
This has been going on for the better part of a week now and I'm about
out of guesses. We have combed our code for places where database
connections aren't closed properly, we've split large tables off and
checked indexing, we've even moved a high-volume read-only database onto
the webserver itself to help lessen the load, and I don't know what
else to do. If anyone can help, it will be greatly appreciated.
TIA
jenn
---------------------------------------------------------------------
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
