Hi there,

I am hoping you can help me out of a jam. Our server is currently running one high traffic site (15,000+ uniques a day, if the server doesn't crash and lock people out). Most of those hits are accessing a bridged phpBB/Coppermine MySQL database.

I just moved mysql to its own drive, which doesn't seem to have helped much....which is why I need help/advice on optimizing our server.



So here it goes (these numbers are not from peak hours):

---- Server Specs ----
Intel Celeron – 2.4Ghz
2G Ram
2x 80GB ATA 100 7200 RPM HDD (one just for mysql)
RedHat Linux
Cpanel/WHM
Apache 1.3.34
MySQL 5.0.18
PHP 4.4.1
Zend Optimizer
eAccelerator
--------------------

Current Apache Configuration
---- /usr/local/apache/conf/httpd.conf -----

---------------------


Current MySQL Configuration
---- /etc/my.cnf ----

---------------------

Thanks

Change your mysqld section in my.cnf as follows:

Second, optimize your most heavily accessed MySQL databases using phpMyAdmin or by manually executing the optimize queries.

You will have to disable your access and error logs as a last option, and will have to turn to an external statistic tools such as google analytics. Since your site isn't offering media, that's quite useful solution in your case.

Another problem here is that a celeron 2.4 isn't powerful enough for handling such loads in normal conditions.

Anyways, good luck with the optimizations and let me know if this helps.

There has been a definite change: no crashing...at least for the small amount of time I have been monitoring it. One thing I noticed is that iowait is fluxuating higher than before, sometimes 75%+. I will keep an eye on it and make sure all is well.

For now Google analytics is invite only, so that solution will have to wait.




So its mainly the fact that the server is running on a 2.4GHz Celeron that is dragging the site to a halt?

Hmmm....would it make a huge difference to be running an Intel 2.4GHz Pentium 4 Processor? And on top of that how much effect will 2.8, 3.2, 3.4 GHz have on the everything? What about an Intel Pentium D 950 Processor?


Thanks for all your help.

Try keeping an eye on memory and CPU statistics (as explained in my article on server overloading). Use the command free -m to keep track of free memory buffers (read my article to be able to understand the output).

The new configuration file shouldn't result in high IO_WAIT. You should consider disabling logs and switching to a 3rd party analytics solution. Just google it, you don't need to wait for google anlytics, there are many similar/competitive solutions out there.

CPU isn't the only issue here. It's like everything combined causing the issues. Sure if you upgrade your CPU to 2.4ghz, it's going to help a bit. BTW, which dedicated company are you with? If you wish, you can get a quality and affordable server upgrade from layeredtech.com (my personal favorite!).

Please post your free -m and top stats now so that I can see if there are any improvements. Your server had previously been swapping badly, if it's still the same case, the first thing you should do is upgrade your RAM. And if you have some more cash available, upgrade CPU too.

So it's like:

1. Upgrade your RAM to at least by +512mb (according to stats you posted, it had been using much swap.)
2. Upgrade your CPU to 2.8ghz P4 or AMD 3000+.
3. Upgrade your hard drives to SATA.

I would recommend on acting on the first one, and the rest depends on the budget you got. So at least upgrade your RAM.

I will look at the company you suggested, right now I am at servermatrix/the planet.

I will also look for the anaytics programs.

Here are the outputs you asked for

oh....its back to crashing again

It seems like you really do need a RAM upgrade. Disable logs and try this alternate configuration file which will use less memory and rely somewhat on disk.

Optimizations can help you by using up some other resources instead of a specific one. If you decrease ram assigned for few buffers, they're written on disk to the temporary directory as hidden files (usually /tmp) and that means more I/O usage. The problem with your server is currently everything's maxed out, RAM, Disk and CPU. Disabling logs will help somewhat.

Another thing you can do is, if your secondary mysql disk isn't on high I/O usage (you can install "iostat" to find out -- google it), is to change your temporary directory to the secondary disk as well.

# mkdir /mnt/drive2/tmp_dir
# chown mysql:mysql /mnt/drive2/tmp_dir

In my.cnf's [mysqld] section, add:
tmpdir = /mnt/drive2/tmp_dir

And restart the mysql. This should be of a little help if your secondary disk isn't already getting hammered (Tip: Do not use your secondary disk for backups.)

Well, servermatrix/theplanet is good if you need that that advanced Dos protection and similar stuff, but otherwise layeredtech can perform pretty good for you. I wouldn't recommend anything else, because I havn't personally used too many of them.

I did as suggested with the tmpdir, shutting off logs, and changing the my.cnf again...and I put in for a gig more of ram. I am hoping that will be enough for now.

I was looking at layerdtech and their pricing and support SOUNDS good, but do they back it up...or is it all talk? If I came to them with the problems I am currently having, would they help me out like you are? Or would they shrug me off and send me elsewhere to find answers?

Generally, most of the hosts don't help you with load balancing and server optimizations. There are few which charge you way too high.

Well, layeredtech do have unmanaged support and they probably won't help you with server optimizations (infact, most dedicated hosting companies wont). Server optimization and taking care of the server is the responsibility of the server administrator.

The changes you have made should be enough. Considering you have added a GB of ram, please post your current free -m and top output so that it may be optimized better. The one I posted previously was memory friendly version that puts a bit of strain on Hard Drive, but now since you've added more memory you shouldn't be pushing your I/O over the limit.

httpd.conf

edit to

Optimize MySQL (mysql4.1.19)

vi /etc/my.cnf

Press ctrl-c to exit insert mode. Then press shift-Z shift-Z to save and quit. Restart mysqld:
Redhat:
/sbin/service mysqld restart


FreeBSD:
/usr/local/etc/rc.d/mysqld restart



done

Note that this guide is only for MySQL 4. There is a default my.cnf that comes with mysql that will work fine
cp -f /usr/share/mysql/my-huge.cnf /etc/my.cnf