Server load and MYSQL

[gphillip at kaiser.aafp.org]

Are your doing much updating to MySql or just reading the database?  MySql
does not handle massive updates very well. I believe the standard install
of MySql locks the table whenever you do an update.  If you only do a few
updates its not a big deal.  If you do thousands of updates it can cause
alot of deadlocks and maybe some of your cgi processes to hang.  If you
are doing alot of updates you might want to use MySql-Max (from
mysql.com).  MySql-Max supports the InnoDb table type which allows row
level locking.

> 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
>
>