Results 1 to 25 of 27
-
01-24-2007, 11:18 AM #1Web Hosting Evangelist
- Join Date
- Apr 2004
- Location
- Australia
- Posts
- 456
How-To: Server Optimization Guide (2007)
Hello,
Well I'm sure by now most of you have read the Web Server Optimization Guide by Shaw Networks. It came in handy for me when I was first starting out, I reduced my load & memory usage by tonnes. I thought I would make a new thread with an updated How-To.
Recommended Tools/Programs:
Putty - Free SSH Client - http://www.putty.nl/download.html
WinSCP - Free SFTP and SCP Client - http://winscp.net/eng/index.php
MySQL Optimization:
BACKUP:
cp /etc/my.cnf /etc/my.cnf.backup
Use Pico (pico /etc/my.cnf) or Download via WinSCP for editing,
Delete everything that is currently in the file and add the following...
Code:[mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking skip-innodb query_cache_limit=8M query_cache_size=256M query_cache_type=1 max_connections=500 max_user_connections=100 interactive_timeout=60 wait_timeout=60 connect_timeout=30 thread_cache_size=128 key_buffer=16M join_buffer=1M max_allowed_packet=16M table_cache=1024 record_buffer=1M sort_buffer_size=2M read_buffer_size=2M max_connect_errors=100 # Try number of CPU's*2 for thread_concurrency thread_concurrency=2 myisam_sort_buffer_size=64M #log-bin server-id=1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
Restart Service: "service mysqld restart" or "service mysql restart" or "/etc/rc.d/init.d/mysql restart"
HTTP/APACHE Optimization:
BACKUP:
cp /usr/local/apache/conf/httpd.conf /usr/local/apache/conf/httpd.conf.backup
Use Pico (pico /usr/local/apache/conf/httpd.conf) or Download via WinSCP for editing,
Change the following settings in your httpd.conf...
Set "Timeout" value to "Timeout 60"
Set "KeepAlive" to "KeepAlive on"
Set "KeepAliveTimeout" to "KeepAliveTimeout 3"
Set "MinSpareServers" to "MinSpareServers 16"
Set "MaxSpareServers" to "MaxSpareServers 32"
Set "MaxRequestsPerChild" to "MaxRequestsPerChild 256"
Set "HostnameLookups" to "HostnameLookups Off"
Note:
These settings will not work under all server environments its recommended that you tweak around with the numbers until your web server is running 100% please read Apache documentation before changing any settings so you know what you are changing http://httpd.apache.org/docs/
To Save: CTRL-X
Restart Service: "service httpd restart" or "/etc/rc.d/init.d/httpd restart"
Installing eAccelerator:
eAccelerator is a further development from mmcache PHP Accelerator & Encoder. It increases performance of PHP scripts by caching them in compiled state, so that the overhead of compiling is almost completely eliminated. http://eaccelerator.net/
If you run CPanel please visit http://www.webhostingtalk.com/showthread.php?t=549666 for an auto-installer which will do all the hard work for you.
Run the following in SSH.
Code:cd / mkdir ea cd ea wget http://optusnet.dl.sourceforge.net/sourceforge/eaccelerator/eaccelerator-0.9.5.tar.bz2 bunzip2 eaccelerator-0.9.5.tar.bz2 tar -xvf eaccelerator-0.9.5.tar cd eaccelerator-0.9.5 export PHP_PREFIX="/usr" $PHP_PREFIX/bin/phpize ./configure --enable-eaccelerator=shared --with-php-config=$PHP_PREFIX/bin/php-config make make install mkdir /phpcache chmod 0777 /phpcache
Code:To install as a ZEND extension: zend_extension="/ea/eaccelerator-0.9.5/modules/eaccelerator.so" eaccelerator.shm_size="16" eaccelerator.cache_dir="/phpcache" eaccelerator.enable="1" eaccelerator.optimizer="1" eaccelerator.check_mtime="1" eaccelerator.debug="0" eaccelerator.filter="" eaccelerator.shm_max="0" eaccelerator.shm_ttl="0" eaccelerator.shm_prune_period="0" eaccelerator.shm_only="0" eaccelerator.compress="1" eaccelerator.compress_level="9" OR to install as a PHP extension: extension="/ea/eaccelerator-0.9.5/modules/eaccelerator.so" eaccelerator.shm_size="16" eaccelerator.cache_dir="/phpcache" eaccelerator.enable="1" eaccelerator.optimizer="1" eaccelerator.check_mtime="1" eaccelerator.debug="0" eaccelerator.filter="" eaccelerator.shm_max="0" eaccelerator.shm_ttl="0" eaccelerator.shm_prune_period="0" eaccelerator.shm_only="0" eaccelerator.compress="1" eaccelerator.compress_level="9"
For a great tutorial on Optimizing host.conf & sysctl.conf visit..
http://www.eth0.us/node/104
Common SSH Commands visit..
http://www.webhostgear.com/35.html
How to block an IP using iptables visit..
http://www.netadmintools.com/part216.htmlLast edited by scribby; 01-24-2007 at 11:25 AM.
-
01-24-2007, 11:27 AM #2Web Hosting Master
- Join Date
- Apr 2002
- Posts
- 780
one question with the mysql ... why did you turn locking off ??
-
01-24-2007, 11:33 AM #3Problem Solver
- Join Date
- Mar 2003
- Location
- California USA
- Posts
- 13,681
Some constructive criticism:
I wanted to point out record_buffer=1M was in the 3.x versions and was changed to read_buffer_size in mysql 4.0 and up.
http://dev.mysql.com/doc/refman/4.1/...from-3-23.html
"The startup options record_buffer, sort_buffer, and warnings still work in MySQL 4.0 but are deprecated."
see the section: "The following startup variables and options were renamed:"
You are also missing a very useful buffer: tmp_table_size
Dont forget: read_rnd_buffer_size
keep this in mind aswell:
"The mysqld startup options --skip-locking and --enable-locking were renamed to --skip-external-locking and --external-locking."
Also:
query_cache_limit=8M
query_cache_size=256M
Those are definately to big for most sites if you are using such low buffers everywhere else.
When you optimize mysql, you don't do a one size fits all. Every server is different. I do not think I have a server in my possession that configuration would do well on.Steven Ciaburri | Industry's Best Server Management - Rack911.com
Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance
-
01-24-2007, 11:35 AM #4Web Hosting Evangelist
- Join Date
- Apr 2004
- Location
- Australia
- Posts
- 456
I cannot remember why I turned it off as it was a long time ago that I optimized my.cnf but some Google'in shows that its a performance increase.
http://www.webhostingtalk.com/showthread.php?t=492727
Also read..
http://dev.mysql.com/tech-resources/...719/index.html
-
01-24-2007, 11:36 AM #5Problem Solver
- Join Date
- Mar 2003
- Location
- California USA
- Posts
- 13,681
Delete everything that is currently in the file and add the following...Steven Ciaburri | Industry's Best Server Management - Rack911.com
Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance
-
01-24-2007, 11:39 AM #6Eternal Member
- Join Date
- Dec 2004
- Location
- New York, NY
- Posts
- 10,710
I agree with everything Steven has said. Posting your MySQL configuration is pretty useless as it may actually make performance worse for someone. Same goes for the Apache configuration.
Also, on eAccelerator:
extension="/ea/eaccelerator-0.9.5/modules/eaccelerator.so"
set extension_dir in php.ini to /usr/local/lib/php/extensions, for example, then move the eaccelerator.so
cp /ea/eaccelerator-0.9.5/modules/eaccelerator.so /usr/local/lib/php/extensions
then load it like this:
extension="eaccelerator.so"
-
01-24-2007, 11:40 AM #7Problem Solver
- Join Date
- Mar 2003
- Location
- California USA
- Posts
- 13,681
Originally Posted by layer0
I agree on eaccelerator.Steven Ciaburri | Industry's Best Server Management - Rack911.com
Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance
-
01-24-2007, 11:40 AM #8Web Hosting Evangelist
- Join Date
- Apr 2004
- Location
- Australia
- Posts
- 456
Originally Posted by Steven
If possible maybe you could write up a quick my.cnf that would be considered average.
-
01-24-2007, 11:42 AM #9Problem Solver
- Join Date
- Mar 2003
- Location
- California USA
- Posts
- 13,681
Originally Posted by scribby
Every configuration I make is dependent on what the server is doing. I have some servers doing close to a 1k queries all the way to 10 queries. I BET if you tweaked your config even more you would get even better loads.Steven Ciaburri | Industry's Best Server Management - Rack911.com
Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance
-
01-24-2007, 11:48 AM #10Web Hosting Evangelist
- Join Date
- Apr 2004
- Location
- Australia
- Posts
- 456
Originally Posted by Steven
Thanks for all the replies so far, I must admit that I have learnt allot in the last few minutes.
-
01-29-2007, 03:30 AM #11It could break plesk aswell since you enable skip-innodb
I DEFINITELY agree with posting sql configuration here being a bad idea. Your configuration may work for you but it may not work for someone else who has a better or worse server than you do. It really is all up to what the server does.Tom Whiting, WHMCS Guru extraordinaire
Linux problems? WHMCS Problems? Give me a shout
Check out my WHMCS Addons
-
01-31-2007, 06:30 PM #12Newbie
- Join Date
- Nov 2004
- Posts
- 12
hi
Hi
I was going to use the above for a Optimization untill i read that it can break Plesk,is there any particular reccommendations for a server running Debian lamp server with Plesk 8.01 ? I would be greatfull for any tips.
btw nice Tutorial Scribby
-
02-01-2007, 02:03 AM #13Problem Solver
- Join Date
- Mar 2003
- Location
- California USA
- Posts
- 13,681
Originally Posted by linux-tech
I use innodb on many mysql databases for its row locking rather then table locking ability with no issues at all.Steven Ciaburri | Industry's Best Server Management - Rack911.com
Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance
-
02-08-2007, 05:11 PM #14Web Hosting Evangelist
- Join Date
- Dec 2006
- Posts
- 480
As has already been mentioned several times replacing your entire mysql configuration with someone elses is a bad idea - you need to tune it to your application's needs.
It is virtually impossible to look at an application day 1 and say "this is how you optimally configure mysql" - you have to make a guess then look at the feedback given in the output of "show global status" to see whether you are making full use of the memory you've allocated. There are tools to help with this but a few simple checks you can do AFTER YOUR APPLICATION HAS BEEN RUNNING FOR A WHILE are:
- Look at key_blocks_unused to key_blocks_used ratio - that shows how much of the memory you allocated with key_buffer_size is being used. Use it to decide wether to increase or decrease your key buffer size.
- Look at sort_merge_passes. How quickly is it increasing as your application is under normal use? If its steadily increasing, increase your sort_buffer_size. If its stable maybe you can decrease your sort_buffer_size a bit and reuse that memory elsewhere. This is a setting you can just change on the fly with set global sort_buffer_size= XXXXXXX till your happy then put it in the config file.
- Look at query_cahce_lowmem_prunes. If its high you should test out an increased query cache size although its not always possible or desirable to prevent prunes. If its zero, then your query cache was never filled so is probably wasting memory.
These are just examples - reading the manual pages on the meaning of the output of show global status tells you most of what you need to know.
-
03-10-2007, 04:37 PM #15Web Hosting Guru
- Join Date
- Dec 2003
- Posts
- 262
I figured I'd post this, seeing as most people agree that using someone else's configuration would probably hurt your server's performance because everyone's needs are different. It depends on how much database driven your site is and how much traffic you actually get. Obviously a large forum would need larger caches and such (I'm not a professional web server tweaker, I'm just saying this from experience). Anyways, I found this awhile ago while digging around and thought I'd share it:
http://www.day32.com/MySQL/tuning-primer.sh
It's a script which goes through your server's logs/past activity and recommends what you should set those variables in your my.cnf file to based on previous usage. I've used it on my server and it worked pretty well to optimize my database usage, so it might be worth trying out. You can read more here:
http://forge.mysql.com/projects/view.php?id=44
http://www.day32.com/MySQL/
-
05-28-2007, 03:06 AM #16Web Hosting Master
- Join Date
- Mar 2006
- Location
- Johannesburg,South Africa
- Posts
- 601
TO critisize someone like this for their efforts to help other's isn't nice guys. He tried to show some people what could be helpfull, and I'm sure it did help someone, somewhere.
Let's face it, not everyone is a server guru, like you are. Not everyone knows the ins and outs of MySQL (in this case), or knows how to tweak it properly. And very few people can actaully afford the high prices tagged with server optimizations, especially since a lot of it only takes a few minutes to an hour to setup.
Yet, scribby thought he'd offer his help to someone who could use it, and come to places like WHT for some free help. Give him some credit.
True, not every config works for everyone, but since the config files are backed up, it's easy to revert if there are problems, but it's also a starting point to see what can be optimized, and where.South African Web Hosting - http://www.SoftDux.co.za || SA WebHostingTalk - http://www.webhostingtalk.co.za
-
06-16-2007, 02:41 AM #17WHT Addict
- Join Date
- Sep 2003
- Location
- DataCentre
- Posts
- 161
thanks .. sorted out my server load problems a bit
<<Please review forum rules for signature formatting restrictions>>
-
09-29-2007, 09:00 PM #18Ex-Hoster
- Join Date
- Apr 2005
- Location
- Istanbul
- Posts
- 769
yes this configration is breaking the plesk server please update the first message with a warning
Kind regards
-
10-03-2007, 06:31 AM #19Junior Guru Wannabe
- Join Date
- Sep 2007
- Posts
- 30
it customb option ! Thank.
-
03-06-2008, 03:32 AM #20Web Hosting Guru
- Join Date
- Jun 2005
- Posts
- 300
Set "Timeout" value to "Timeout 60"
Set "KeepAlive" to "KeepAlive on"
Set "KeepAliveTimeout" to "KeepAliveTimeout 3"
Set "MinSpareServers" to "MinSpareServers 16"
Set "MaxSpareServers" to "MaxSpareServers 32"
Set "MaxRequestsPerChild" to "MaxRequestsPerChild 256"
Set "HostnameLookups" to "HostnameLookups Off"
-
03-15-2008, 09:24 AM #21Quality Web Hosting Matters
- Join Date
- Mar 2006
- Location
- Servers
- Posts
- 1,590
Hello,
Yes there are no universal mysql and apache settings. They are unique for every server. BTW your mysql key buffer is totally small (64MB). Seems a good option for 1GB server.
Thanks,
-
03-19-2008, 05:55 AM #22New Member
- Join Date
- Jan 2008
- Posts
- 3
As has already been mentioned several times replacing your entire mysql configuration with someone elses is a bad idea - you need to tune it to your application's needs.
-
03-30-2008, 03:59 PM #23Web Hosting Master
- Join Date
- Jul 2005
- Location
- Los Angeles, California
- Posts
- 1,370
These settings are not all in one place. You have to look around for them individually. When I edit my files I don't use the pico command, I use the nano command instead and it has a feature where you can look for certain words in the entire script. I hope that helps .
Rageki Web Hosting Solutions - Canada & United States Web Hosting
DirectAdmin & cPanel Control Panels
99% Uptime!
30 Day Money Back Guaranteed!
-
06-12-2008, 12:56 PM #24New Member
- Join Date
- Jun 2002
- Posts
- 1
Set "MinSpareServers" to "MinSpareServers 16"
Set "MaxSpareServers" to "MaxSpareServers 32"
Apache Docs says:
Tuning of this parameter should only be necessary on very busy sites. Setting this parameter to a large number is almost always a bad idea.
Some websites actually recommend to increase it, and others don't. After reading some more tutorials, I find that it's better to increase these values if the server has a lot of memory.
I'm running a website on a vhost with 1GB memory. Getting about 8000 visits, 25,000 page views per day. Would you think it's necessary to change the values from their default (5 and 10)?
-
03-14-2010, 03:18 PM #25Junior Guru Wannabe
- Join Date
- Apr 2006
- Posts
- 58
Uhm...
I don't think the MySQL Part is bad. It look good, and when you optimizer your server, you should look again on document or google search the new way on new version.
Nice topic for newbie, bro