Server load and MYSQL

Mon Oct 21 17:41:15 CDT 2002

Going out on a limb (knowing more about databases than I do about Linux) I
would recommend the following:

1) Buy more hard drives.

2) Buy more RAM.

Having run into problems before that were not entirely dissimilar, I would
venture a guess that your drives are overloaded.  Based on your listed
config, you probably have those poor drives mirrored, split into a couple
of partitions, and those 2 poor spindles have to handle all of the traffic
for your pagefiles, database files, database page files, operating system,
and serving your websites to boot.  Databases and operating systems have
timeout thresholds built into them and will start to get flaky if it takes
them too long to write something to the disk.  With everything that you
have going on in that system, I would guess that your system is simply
trying to push too much bandwidth through that controller and drives.  If
it were me (and if that controller had the channels), I would put at least
another 4 drives in the system: a mirrored set for the O/S, and a mirrored
set for your pagefiles.  Barring that, I would put one more drive in there
at least for both the Linux paging partition and for the database pagefiles
(but keeping the datafiles on a redundant drive).

On a sidenote, databases perform at their best when they are on a lot of
drives.  You can pull data from many drives far faster than you can pull it
from a few large ones.  Ideally, you would swap out those 18GB's for a set
of 4 new 9GB's so you could spread the workload over more spindles.  Even
if you have 10k RPM 18GB's going against 7k RPM 9GB's, the four 9GB's will
always have more raw throughput.  If you want to get hardcore, you could
tweak your data block allocation size so every read or write was spread
across at least 2 drives, but that is another story. :)

All of that having been said, if MySQL is anything like Oracle, your
performance is very closely tied to how efficiently you use your RAM.  I
would have a pro look at your settings.  I have seen processing times drop
by 90% with some operations when the memory was properly tweaked for the
application.  Similarly, if your queries are going against tables instead
of indexes (or going to unindexed tables) you will see a significant
dropoff.  If your system is bogged down in processing it can't send data to
the drives as fast.  I've seen even better performance gains from properly
written queries than I have from just about anything else.

So, if you're stuck with the memory allocations and the code but you still
need a boost.  Well, not to overstate the obvious, but reading from RAM is
still far faster than reading from drives, and the more of the database
that is in RAM, the faster you can get data from it.  RAM is also cheaper
than downtime in most cases.  In an ideal situation, you could throw enough
RAM in the box that you wouldn't even need a pagefile.

Onto your questions-

1) Stay away from writeback.

2) 28MB seems a little heavy-handed for a client session, but I wouldn't
throw it in the realm of ridiculous.  If you can reduce it, great, but that
might just slow things down somewhere else.

On a final note, I would run the full gamut of diags against your SCSI
controller.  Had a client one time with a dizzying array of seemingly
unconnected, unexplainable problems, and we spent weeks trying to chase it
down.  It all ended up being tied into a dirty cache in one of their
controllers.  It's one of the first things I check now.

Hope this helps!

Kevin Finch
Network Administrator
DST Systems, Inc.
816/435-6039
krfinch at dstsystems.com

                    "Aaron"                                                                         

                    <aaron at aarons.net>           To:     <kclug at kclug.org>                          

                    Sent by:                     cc:                                                

                    owner-kclug at marauder.i       Subject:     Server load and MYSQL                 

                    lliana.net                                                                      

                    10/21/2002 04:54 PM                                                             

Hi all.  I'm looking for a second opinion. (or a third, fourth, fifth....)

Here's the situation:  I'm running a server with the following specs:  Dual
P3 1.3Ghz, 1GB ECC Registered RAM, Dual 18GB SCSI Ultra 160 10K RPM drives,
Adaptec 29160 (the 64bit version)...  <-- Important specs. :)

This server is running RedHat 7.3 (with an upgraded kernel. 2.4.18-17smp)
and all the normal stuff. (apache, bind, mysql, php, etc etc etc) The web
site it serves gets 20,000 unique visitors a day and the databases are
roughly 6GB.

For the last two months I've had no end to the problems.  First, the
machine would crash randomly, once a day.  I figured out that it was a
Kernel bug, hence the reason for the upgrade.

Second, every once in a while (once every 3 or 4 days) Apache would crash
and I'l look at the load to find it at 300 - 400.  It wouldn't let me soft
reboot it so I'd have to shut it down.

I moved the databases and the domlogs to the second drive and that seems to
have relieved some of the load and stopped the /var and /usr partitions
from hitting 100% full every day, but it's still not uncommon for me to see
loads in the 8-9 range.  I'd like to get the machine a little more stable.

So my questions....

1.  Does anyone have an opinion on switching the journaling system to
writeback?  It looks like that's better for database writes but not so good
for data integrity.

2.  I saw something about MySQL taking 28MB of memory for each process it
spawns and that that could be reduced and would lighten the load, but I
can't get any details.  Also, in my my.cnf file it says it's set to 1MB.
Is there something I'm overlooking?

Any help, suggestions, opinions, etc would be greatly appreciated.

Aaron