Well, most VPSs being bought are having 128 to 512 MB of dedicated memory and I've seen people complaining slow response and heavy memory usage.

To help VPS owners tune up their Apache and MySQL quickly, I've written a do-it-yourself guide and decided to share with you all.

You can read it here : Quick VPS Apache and MySQL optimization guide (http://www.agnivo.com/tech/vps-mysql-and-apache-optimization-guide-27.html)

If you find it useful, don't forget to give a Digg so that other people can find it easily.

Digg it HERE (http://digg.com/linux_unix/Low_Memory_VPS_MySQL_and_Apache_Optimization_Guide)

If there's anything wrong with the guide or you have suggestions, feel free to reply. Mods, if anything is wrong, let me know too.

Well done! That is an excellent primer on getting your my.cnf and httpd.conf running at top speed with the smallest amount of resources. I'm sure it will help many users.

-Sean

Doesnt work now

Really? I can still access that website just fine.

It wasnt working just now, now it works.

One configuration does not fit all.

It's not that big of a deal if you use something like the above as a base, but you should be prepared to make changes (sometimes drastic) to fit the usage of the website(s) you host...

Thanks for your feedback, guys!

Yes, as suggested by layer0, I've already mentioned that fact in the concluding section of the howto.

BTW, I'm currently testing Apache 2.2, PHP 5.2.1 and MySQL 5.0.37 on the VPS.
Apache 2.2 seems to be very efficient in mem usage but a bit higher on CPU usage than 1.3.x branch.
Will publish the final outcomes soon... :)

tmp_table_size = 16M


Nice tuto, imho 16MB for the tmp table size is kind of small, this will force the system to make more tmp disk tables, considering in vps the Disk I/O is a factor that tends to produce bottleneck , i will leave it at the default 32MB or even higher.


table_cache = 1024


Also the table_cache to 1M ? , if you have mysql intensive sites this may be a problem , watch your table cache usage it should be something between 70% and 90% , also compare opened vs open table , if short after you restart mysql you see this value increasing you need to make table_cache bigger, i have it on 96M and i have a 384MB vps.

One last thing is that max_user_connections is not set, if you are using myisam tables, and sometimes the creation of tmp_tables takes a lot of time, then the table gets locked , the next mysql connections wait for it (the tmp table creation) to finish, so it will make 400 connections (max_connections) before it dies with the "unable to connect message" , this will force apache to handle the clients waiting for the results, more child servers will be needed, and this will make the entire server really slow :X,

Once again nice tutorial :)

VPS MySQL and Apache Optimization Guide (http://www.agnivo.com/tech/vps-mysql-and-apache-optimization-guide-27.html)

A "must bookmark" for me. Agnivo has gone to some effort to make instructions that even a non-techy like I might follow. I do not actually "own" a VPS and--with internet equipment so various and constantly changing--would not dare to do this type of thing myself. But, certainly something to show to friends who will understand, and to discuss with my primary webhost.

Wow...it feels good to see some words of encouragement from krystofo... :)

Thanks rdogg for your feedback...I've been waiting to see some of these.

Can you please post some alternative and revised config here or post it as a comment to the original blog post?

Cheers :)

Hello agnivo i wanted to correct myself i dont have the table_cache on 96M i have it on 512 , i got confused and tought it was about MB or KB but it is about the tables to keep in cache.

I was looking at my config today and noticed this.

regards

OK rdogg... Do you have any good apache2.2 mysql5 config ?

As I see, apache 2.2.4 is a great piece of software and with a little tweaking, it performs even better.

What doesn't work ?

how can I copy files using putty to my own computer ? what would be the command for linux ?

Victoria_jambi,

As far as copying and transferring files from your VPS to your PC, I would suggest you use an FTP client. Filezilla (which I reccomend) has a gui and ssh support built in, so you can connect the same way you do with Putty to do all your transfers to and from your VPS and PC. Much easier than transferring from command line, unless you are transferring files from one server to another.

anyone have any cpanel optimization tips?

I have a 256mb guaranteed / 1024 burstable

VPS, and I get a lot of "cpsvr" process freezes sent to me in email everyday, even when I'm not in cpanel at all, or away from computers entirely, so that tells me cpanel is running in the background or something.

I like having cpanel/WHM when I need it, but is it possible to have cpanel/whm/virtuozzo on a VPS, but not running all the time? just only when you need it?

or are there any optimization tips so it'll run better on 256mb guaranteed?

all I use it for, is to do domain and website management. I actually like Fantastico, makes installing stuff so much easier for me, and I like WHM because it makes adding/deleting accounts easier for me, but thats all I use it for every now and then, not once a month, if that, but I like to have it for when I do use it, ya know what I mean? like, do the processes for it have to be running all the time?? thats what I don't get.

thanks

Can I used this(apache and php) for grid server? ( My server are 40 PC by duo core processor)

Hello,

I don't known if this is related to the MySQL optimization but I want to share my experience and also know if someone else had some similar problems.

I have 10 VPS on two different companies. I tested the mysql configuration on 4 of them. At a first moment it worked fine. Mysql were working and was really fast.

2 days after this i realized that one entire database was marked as "in use". Ok, i tried to repair and even contacted my VPS provider. They tried to recover the database but that was not possible, mysql wasn't able to repair it. Just lost an entire database :(

Backup? Yes, but backup was overwrited by a sql file when the database was already corrupted.

At this moment i thought that it may be a coincidence. But them, some hours after that i got more 2 databases corrupted, one at the same server and another on other server.

Does anyone else had this problem?

I have been running those VPS for more than 4 month and never got even a single table corrupted until changing this configuration.

Wow... that's is helpfull.. thanks man

any updates to this? the original link is gone.

Link is not working. Hope new working link will given here asap.

The site is down. I hope the optimizations are not site killers.



http://www.agnivo.com site down

Website is down for everyone. This site is currently unavailable. It's not just you!

what i guess there the files you provided my.cnf is the standered one but it must be tweaked as per the server configuration and usage resources :)

Yes, actually it only help abit with this kind of global mysql server optimization. If you really hope to increase the performance for your site, you still have to analyze each of your query and fine tune it from there.

The site is down. I hope the optimizations are not site killers.
http://web.archive.org/web/20080610213501/http://www.agnivo.com/tech/vps-mysql-and-apache-optimization-guide-27.html

Accidental post in wrong thread

I've a read on your tutorial and its just awesome i can say, you done good job.

heres full post

With a considerable number of webmasters switching to Virtual Private Servers (VPS) from shared hosting these days and preferring them over costlier fully dedicated servers, memory and resource optimization has become a burning issue. Most VPS packages being bought these days, have dedicated memory constraints of 128MB to 256MB. Apache and MySQL are known to be the most memory (resource) intensive applications that tend to crash or slow down a low to mid range VPS often. While OpenVZ or Virtuozzo based VPSs can have a burst memory limit in addition to the dedicated RAM to quench occassional high memory needs, Xen based VPSs cannot use more than the stipulated amount of dedicated RAM, adding to the problem.

In this short do-it-yourself Apache and MySQL optimization guide, I will present some small yet useful tips that I have collected from the net and will also share my own VPS settings and experiences that I hope would be very useful to first time and newbie VPS owners. I would also like to make it clear that I’m not a system admin or server guru of any caliber, but a techie and geek who loves to try and do things himself. So, always make a backup of files concerned and don’t hold me responsible for any kind of loss incurred. Try at your own risk!

My VPS Specifications

CentOS 4.4 OpenVituozzo based.
256MB Guaranteed RAM, Upto 512MB burst.
4 VPSs on a Celeron 2.53 ( ~625 MHz Guaranteed CPU, 2.53 Burst ).
Apache 1.3.37, PHP 4.4.4, MySQL 4.1, VM-POP3, Exim.
DirectAdmin Control Panel ( 1.2.6 ) - Known for its resource friendliness and quite cheap yet effective.

My Own Optimizations

Note : The optimizations below are based on this platform. However, they should work fine for 128 to 384 MB Guaranteed RAM. Consult a professional system admin if you want the best optimizations for your VPS ( and be ready to shell out some 10s of dollars )

First of all, login as root user to your VPS over SSH.

Apache Optimization

1. Make a back up of your httpd.conf first. It’s generally found at /etc/httpd/conf/httpd.conf
Your path may differ…so ‘locate’ or ‘whereis’ commands should be your friend.

2. Open it up using a text editor. ( eg - ‘nano’ )

3. Make the following changes : ( you’d find these near the start )
Timeout 200
KeepAlive On
MaxKeepAliveRequests 200
KeepAliveTimeout 3
MinSpareServers 5
MaxSpareServers 15
StartServers 5
MaxClients 20
MaxRequestsPerChild 2000
HostnameLookups Off

4. Save and exit editor.

MySQL Optimization

1. First backup and then open /etc/my.cnf in a text editor.

2. Delete everything and then add the following to it :

[mysqld]
max_connections = 400
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
skip-innodb
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 32M
sort_buffer = 32M
read_buffer = 16M
write_buffer = 16M
[mysqlhotcopy]
interactive-timeout

3. Save and exit.

Restart Apache and MySQL

Based on your Operating System, the commands may differ.
On CentOS, type :

/sbin/service httpd restart
/sbin/service mysqld restart

Your control panel ( DirectAdmin, cPanel and so on ) may also have options to restart the above processes.

Closing Notes

Every VPS is different than the other, even if the basic specifications are same. Feel free to play around the settings I suggested and lock on the best values you deem fit.

It’s always better to go for a VPS provider who has good reputation for after sales support and not an overseller. Costly is not always good and cheap is not always bad. Hunt for reviews, feedback in hosting forums before buying. Remember : “You get what you pay for”!

Keep in mind that most VPSs are used like full dedicated servers and the owner often expects it to perform like a dedi box, which is simply not possible. Web, mail, ftp and so on are often loaded onto a low to mid range VPS. Resources are scarce…so optimization is the key.

For VPSs below 384MB of RAM, it’s wise to choose DirectAdmin as control panel. It’s highly feature rich yet cheap. Consumes lot less memory than cPanel and Plesk.

For 128MB VPSs, if you find the memory still low after applying my settings, try lowering MinSpareServers , MaxSpareServers , StartServers , MaxClients and MaxRequestsPerChild in small, proportional steps and lock on the optimum value.

For 256MB to 384MB VPSs, if you find a lot of memory being unused ( type ‘free’ ) after 2 days of optimization, try increasing the values of the settings in the above line proportionately.

If you find that this isn’t working for you or you aren’t satisfied, just restore the files you backed up and restart the processes.

Excellent guide! Thanks for sharing/re-posting this. I'm keeping this thread bookmarked for future reference :)

Thanks again!

-James

I'll try it on my new box, thanks for sharing :)

the link is down? but thanks for the repost onelove

in my VPS it looks different. hope i should look it more deeper.

report: the link is down, bro.

I usually tune my mysql configuration by using mysql tuning primer, script is downloadable from here https://launchpad.net/mysql-tuning-primer/