The Answer Gang

By Jim Dennis, Jason Creighton, Chris G, Karl-Heinz, and... (meet the Gang) ... the Editors of Linux Gazette... and You!

---

Optimizing Apache2 and MySQL install

From Suramya Tomar

Answered By: Thomas Adam, Yan-Fa Li, Ramon van Alteren

Hi Everyone,

I have another problem and I was hoping that you will be able to help me solve this one too. I am hosting my website on a Debian Sarge system on a LVS (Linux Virtual Server) with 64 MB RAM and 64MB swap using Apache, PHP and MySQL. The problem I have is that every so often the Apache and MySQL process start taking 100% of the CPU and RAM and after a few mins of which the system runs out of RAM and the kernel starts killing processes (MySQL is usually the victim) which means my site goes down till I restart that process.

Is there some way I can optimize MySQL and Apache and tell it not to use so much RAM/CPU? I tried searching on the web but didn't find anything relevant. I am running Apache2, PHP 5, MySQL 4.1.10 on this box.

Apache was compiled with:

--enable-ssl --enable-cgi --with-ssl --enable-module=so

PHP With:

--with-bz2 --enable-ftp --with-gd --enable-exif --with-jpeg-dir
--with-png-dir --with-ttf --with-freetype-dir
--with-imap=/usr/local/imap --with-mcrypt --with-mysql=/usr/local/mysql/
--with-openssl --with-cpdflib --enable-soap --with-zlib
--with-apxs2=/usr/local/apache2/bin/apxs

and MySQL with:

--with-mysqld-user=mysqld --with-openssl

The website gets about a 1000 - 2000 hits a day and I have 2 blogs(Using Serendipity Ver 0.8.1) running on this system. I also run Sendmail on the box but is only used locally to send mails from Serendipity and my guestbook. I don't allow any external connections to port 25.

The error I see in the /var/log/messages when a process is killed is:

Jun 21 13:44:21 wyrm kernel: __alloc_pages: 0-order allocation failed
(gfp=0x1d2/0)

Output of free:

wyrm:/home/suramya# free
              total       used       free     shared    buffers     cached
Mem:         60500      59092       1408          0        660      27324
-/+ buffers/cache:      31108      29392
Swap:        65528      28648      36880

Output of top right now:

   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
740 apache    14   0 13400 9908 2992 R 57.8 16.4   0:28.18 httpd
809 mysql     20   0 11676 2416 2204 R 35.5  4.0   0:01.20 mysqld
459 mysql      9   0 11676 2416 2204 S  1.9  4.0   0:04.74 mysqld
808 root       9   0  1044 1044  852 R  1.3  1.7   0:00.09 top
   4 root       9   0     0    0    0 S  0.3  0.0   0:22.18 kswapd
798 apache     9   0  6928 3660 3056 S  0.3  6.0   0:06.91 httpd
   1 root       9   0   100   72   72 S  0.0  0.1   0:00.21 init
   2 root       9   0     0    0    0 S  0.0  0.0   0:00.00 keventd
  3 root      19  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd_CPU0
  5 root       9   0     0    0    0 S  0.0  0.0   0:00.00 bdflush
  6 root       9   0     0    0    0 S  0.0  0.0   0:00.00 kupdated
  7 root       9   0     0    0    0 S  0.0  0.0   0:00.00 jfsIO
  8 root       9   0     0    0    0 S  0.0  0.0   0:00.00 jfsCommit
  9 root       9   0     0    0    0 S  0.0  0.0   0:00.00 jfsSync
10 root       9   0     0    0    0 S  0.0  0.0   0:00.00 xfsbufd
11 root       9   0     0    0    0 S  0.0  0.0   0:00.00 xfslogd/0

Any suggestions on how to reduce the load and prevent the system from dying every few hours?

[Thomas] Well, there is. If it is really a problem, you could compile your own kernel, and not compiling the OOM-killer into it. That would stop the kernel's divine intervention, at any rate.

More practical purposes though would be to limit the amount of RAM that the processes can use. Recent versions of PAM allow for this through the file: /etc/security/limits.conf For example, on Debian, Apache runs via the user 'www-data'. In that file, you could add an entry thus:

www-data  soft     cpu       6000
www-data  hard     memlock   700000

The difference between hard and soft is almost analogous to how quota enforces them -- soft is a graceful enforce, while hard is a complete cut-off. The values above are examples only -- you should change them to suit your needs, at any rate.

The use of limits.conf via PAM is really much more applicable to daemon-based tools, although it can be applicable to anything. It used to be the case that you'd have to do:

ulimit -l <some_value>

.. and you still can. It's great to do that for login shells, etc. The problem with that is for daemon processes that do not spawn a shell when the process starts, makes it invalid. Not to mention the user that the daemon runs at doesn't have a shell.

You can apply a similar methodology for MySQL.

[Yan] Just an observation, stop being so miserly with your RAM. 64MB is very little ram for a DB and a web server and your scripts,

[Thomas] Really? I refute that, given the tools available to limit things. I have 64MB on a P166 which runs: Exim, Squid, Apache, MySqL, Ruby-on-Rails. It has no problems whatsoever. 64MB is not miserly at all -- and there's nothing to say one needs $MASSIVE_RAM for applications to run -- if you believe that, you'll believe anything.

[Yan] Really, interesting and you're right. But this isn't an embedded system we're talking about, it's a linux VLS, which is running on a real Linux box, so he must have access to more resources causes he's already paying the overhead of VLS and it's already thrashing the CPU so it's stealing cycles from the rest of the system because it's thrashing.

Its an LVS being run by a hosting company. If I want to increase the available RAM I have to pay extra and thats something I want to avoid till I have exausted all other options. Its not that much more expensive but why pay more when some small config changes will fix the problems, right? :)

[Yan] MySQL 4.1 is the latest greatest and it's a bit of pig, usually eats up 16-32mb. What are you running 3.23 ? That's miserly in comparison. Squid in non caching mode is pretty small agreed, but then it's only a proxy. Apache 1 or 2, Apache 2 takes up more room, just did a quick check on my system and it's resident at Virtual 20MB, resident at 9MB. What version of Linux 2.2 or 2.4 ? 2.4 is comfortable at low memory, 2.6 is less so.

I am running MySQL 4.1 on Linux 2.4.

[Yan] What does your my.cnf look like, I think that would be useful for the person who posted.

I just look at MySQL 4.0.20 and it's 30MB virtual and 10MB resident with default settings, i.e. an empty my.cnf.

I am using the default settings for mysql, if you have a config setting that is more lightweight then I am willing to try it out.

[Yan] And I'm not talking $MASSIVE_RAM. I'm talking 96MB. By todays standards that's nothing. This is 2005, a barebones system is 1GHz with 256MB of RAM. A P166 was state of the art 10 years ago, though I'm glad to see you're recycling it.

:) I have spare machines at home but don't have the bandwidth to support my site so I have to stick with this server until I can either convince my boss to let me host my site on an office server or convince my wallet to pay for a bandwidth upgrade at home.

[Yan] increase your LVS to 96MB and re-test. The other thing to do is try and run the MySQL database on another machine/virtual server over the network.

[Thomas] Which would be very slow indeed.

[Yan] MySQL is NOT slow over the network, that's a complete myth. It's not as fast as running it over a Unix socket, but it's not slow by any means, especially compared to the slow down of a P166 Try doing a benchmark between your P166 running mysql and a faster machine over the network running MySQL. Any query of decent complexity will still run faster on the network machine. Plus if his VLS is crashing all the time he'll spend more time running myisamchk on it than he would just moving it to a stable box.

I was considering hosting the DB on my home machine but thats connected to the net using a cable modem so I don't know how feasible that would be. Will try out some tests and see how that looks.

The site wasn't doing too bad till 2 days ago then suddenly for some reason the processes started dying every few hours. Maybe my traffic shot up or the blog software went nuts.

[Yan] He also stated he's running an app he didn't write, a blog. So who knows what's it's doing with memory or SQL queries. Perhaps it has some configuration knobs that can be used to minimize it's footprint.

Yeah, I think the blog is the biggest resource consumer. Anyone know a more lightweight blog thats not horibly ugly looking?

I have put in the changes Thomas suggested on the system and so far it seems to be stable. I am waiting till tomorrow and see how it works out overnight but so far that one change seems to be working great.

[Thomas] I assume you've just joined, Yan-Fa Li? Welcome, if so. Perhaps you'd oblige is by NOT top-posting? Thanks.

[Yan] Sorry for top posting, my bad.

[Yan] You can edit the /etc/my.cnf and cut it to the bone, but you're really trading RAM for performance. The less RAM, the lower your performance. Go to mysql.com and look at the variables you can set and set them to the bare minimum.

Also, configure apache to not load so many modules. You can control that from your httpd.conf. But honestly, the amount of effort to get this to work vs just adding more RAM or swap, I'd say it isn't worth it.

Just my 0.02c

[Ramon] Have you taken a look at wordpress ( http://www.wordpress.org ) ?

It's a 300k download, looks nice (subject to personal opinion) and isn't as resource-hungry as serendipity. If you don't like the default look it has extensive theming features and css support.

---

This page edited and maintained by the Editors of Linux Gazette
HTML script maintained by Heather Stern of Starshine Technical Services, http://www.starshine.org/

---