Re: [Phplib-users] MySQL Overload

[nathan r. h. <na...@ds...>]

On Fri, 29 Mar 2002, Richard Archer wrote:

> At 9:45 PM +0200 28/3/02, <pis...@al...> wrote:
>
> >I'm using a RedHat Linux, Apache+PHP4+MySql on a P2/500 with 256 MB RAM.
>
> More RAM never hurts. Upgrade that machine to the max it can handle
> (probably only 768 MB).
>

Word.  This probably won't fix the crashing problem, but it certianly can
*never* hurt. This would also help with something I mention below.

Faster drives can also make a huge difference, sometimes (depending on
usage) more than a faster processor.  If you're using plain ol vanialla 33
or even 66 IDE drives, you need to step up to UltraATA 100 or SCSI 160.
Even the SCSI-2 machines I have (max sustained rate 10 MB/sec) show better
throughput and performance than my ATA 33 drives. (and my SCSI-160 RAID 5
array just pretty much screams :)

Again, this is a performance suggestion and probably wouldn't help your
crashing  problem.

>
> >The MySQL crashes very often, and i need to /etc/rc.d/init.d/mysqld
> >stop-start about 5 times a day.
>
> Sounds like you have a corrupt table there.
>

Could very well be.

> Are you running the latest MySQL?
>
> Did you compile it yourself or install from a package?
>  (hint: you almost always have to compile your MySQL, Apache, PHP
>  and kernel yourself to get the options and optimizations you want)
>

Even without custom compiling apache or php, a tweaked kernel can be a
world of help.  Using one of the newer memory managemebnt patches floating
out there would also be good (Ingo's o(2) scheduler, or the AA VM)  At a
minimum, don't run a SMP kernel if you have a single processor.  It would
also behoove you to update to 2.4.18 (or 2.4.19-pre1) which has been very
stable and wonderful for me. (The -ac series has been looking a bit to
rough for production use of late)

Also, general system admin stuff... turn off services you don't need, make
sure you have updates for everything, looks at top and ps, see what's
eating the memory and cpu.  Try turinging off pconnect() in phplib, tune
your MinSpareServers and MaxSpareServers and other Apache tuning.  Got a
lot of .htaccess files?  Put their directives in httpd.conf and save the
extra disk I/O.

You also mention this machine serves webmail. If you can, split mail
(sendmail / imap / pop)  onto  another machine and repoint your webmail
stuff to it.  That should lessen the load a good bit (just make sure the
two machines have a fast connection to each other - best if you can put an
addtional 100MBs card in each and run a crossover cable twixt the two or
buy a very very nice switch - or get the extra cards and a nice hub,
yaddah yaddah)

If you have the cash, space and brainshare to do it, you could also do
some sort of cluster using LVS+mon+heartbeat, redhat's pirannah, or VA's
ultraMonkey.  Can be cheaper than you think as you don't need screamers
behind the load balancers, that's why there are load balancers :)  (Of
course you could always shell out the bucks for a Cisco or Checkpoint LB..
but where's the fun in that?)

> After you stop MySQL, do you run a verification/repair on all the
> databases?
>
> See the manual, section 4.4.6. This should do the trick (assuming
> all your tables are MyISAM format):
>
> myisamchk --silent --force --fast --update-state -O key_buffer=64M
> -O sort_buffer=64M -O read_buffer=1M -O write_buffer=1M
> /path/to/datadir/*/*.MYI
>


Yes, do that :)

Also, someone mentioned caching, which can really save the day.  I dunno
what people are using, but I've been turned on to jpcache which is very
nice and easy to implement.  File and DB based storage, I'd say use the
Files method and with all that extra memory you have, set the files cache
to live on a ramdisk or shm segment (/dev/shm on 2.4 kernels)  Whee..
that's fast.  jpcache will also use gzip compression on the output so
hopefully it'll not only cut down on the load, but possibly lessen your
connection load. as well.

http://www.weirdpier.com/jpcache/

To the troll about using a "real database" MySQL can by fully ACID if you
want, more specficly, I doubt throwing Oracle at the situation would help
matter any in terms of performance and ACIDity has nothing to do with
the crashing issues the original poster is having.  Before we all jump on
the Great DB flamfest again, lets all take a breath and remember "to each
their own."  This isn't the forum for debating ACIDity issues or what DB
rocks more.

-n
------
nathan hruby
na...@ds...
------