Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    Join Date
    Apr 2004
    Location
    Australia
    Posts
    448

    Exclamation 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
    To Save: CTRL-X
    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
    Edit your PHP.INI file and at the bottom add...
    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"
    Restart Service: "service httpd restart" or "/etc/rc.d/init.d/httpd restart"

    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.html
    Last edited by scribby; 01-24-2007 at 11:25 AM.

  2. #2
    Join Date
    Apr 2002
    Posts
    779
    one question with the mysql ... why did you turn locking off ??

  3. #3
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,262
    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 | Proactive Linux Server Management - Rack911.com
    System Administration Extraordinaire | Follow us on twitter:@Rack911Labs
    Managed Servers (AS62710), Server Management, and Security Auditing.
    www.HostingSecList.com - Security notices for the hosting community.

  4. #4
    Join Date
    Apr 2004
    Location
    Australia
    Posts
    448
    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

  5. #5
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,262
    Delete everything that is currently in the file and add the following...
    bad advice. This would definately break an ensim server. It could break plesk aswell since you enable skip-innodb
    Steven Ciaburri | Proactive Linux Server Management - Rack911.com
    System Administration Extraordinaire | Follow us on twitter:@Rack911Labs
    Managed Servers (AS62710), Server Management, and Security Auditing.
    www.HostingSecList.com - Security notices for the hosting community.

  6. #6
    Join Date
    Dec 2004
    Location
    New York, NY
    Posts
    10,574
    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"
    I would strongly suggest not doing this.

    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"

  7. #7
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,262
    Quote Originally Posted by layer0
    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:


    I would strongly suggest not doing this.

    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:

    I agree on eaccelerator.
    Steven Ciaburri | Proactive Linux Server Management - Rack911.com
    System Administration Extraordinaire | Follow us on twitter:@Rack911Labs
    Managed Servers (AS62710), Server Management, and Security Auditing.
    www.HostingSecList.com - Security notices for the hosting community.

  8. #8
    Join Date
    Apr 2004
    Location
    Australia
    Posts
    448
    Quote Originally Posted by Steven
    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.
    Thanks for the criticism (I guess), That my.cnf is currently what I am running and it has been alright so far, however I always reccomend that people tinker with the settings after going through the correct documentation and see what is best for them.

    If possible maybe you could write up a quick my.cnf that would be considered average.

  9. #9
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,262
    Quote Originally Posted by scribby
    Thanks for the criticism (I guess), That my.cnf is currently what I am running and it has been alright so far, however I always reccomend that people tinker with the settings after going through the correct documentation and see what is best for them.

    If possible maybe you could write up a quick my.cnf that would be considered average.

    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 | Proactive Linux Server Management - Rack911.com
    System Administration Extraordinaire | Follow us on twitter:@Rack911Labs
    Managed Servers (AS62710), Server Management, and Security Auditing.
    www.HostingSecList.com - Security notices for the hosting community.

  10. #10
    Join Date
    Apr 2004
    Location
    Australia
    Posts
    448
    Quote Originally Posted by Steven
    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.
    I understand that, I merely thought it would be a good idea for newbies to have something to start with.. but as my configuration seems outdated its a bad example. Thats why it would be a great help if someone could post a basic updated version that could be a base to start tweaking from.

    Thanks for all the replies so far, I must admit that I have learnt allot in the last few minutes.

  11. #11
    Join Date
    Sep 2002
    Location
    Among the corn
    Posts
    10,724
    It could break plesk aswell since you enable skip-innodb
    innodb is nothing but a PITA from what I've seen. Mysql is buggy enough when it crashes, innodb just makes it that much harder to get started back up. Of course that's just my personal experience and ymmv.

    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.
    Linux admin, support tech, php developer for hire. PM for more info

  12. #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

  13. #13
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,262
    Quote Originally Posted by linux-tech
    innodb is nothing but a PITA from what I've seen. Mysql is buggy enough when it crashes, innodb just makes it that much harder to get started back up. Of course that's just my personal experience and ymmv.

    I use innodb on many mysql databases for its row locking rather then table locking ability with no issues at all.
    Steven Ciaburri | Proactive Linux Server Management - Rack911.com
    System Administration Extraordinaire | Follow us on twitter:@Rack911Labs
    Managed Servers (AS62710), Server Management, and Security Auditing.
    www.HostingSecList.com - Security notices for the hosting community.

  14. #14
    Join Date
    Dec 2006
    Posts
    477
    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.

  15. #15
    Join Date
    Dec 2003
    Posts
    260
    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/


  16. #16
    Join Date
    Mar 2006
    Location
    Johannesburg,South Africa
    Posts
    598
    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

  17. #17
    Join Date
    Sep 2003
    Location
    DataCentre
    Posts
    160
    thanks .. sorted out my server load problems a bit
    <<Please review forum rules for signature formatting restrictions>>

  18. #18
    Join Date
    Apr 2005
    Location
    Istanbul
    Posts
    763
    yes this configration is breaking the plesk server please update the first message with a warning
    Kind regards

  19. #19
    it customb option ! Thank.

  20. #20
    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"
    I have apache 2.2 and I don't see any of these options. Where would it be?

  21. #21
    Join Date
    Sep 2006
    Location
    Servers
    Posts
    1,571
    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,

  22. #22
    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.

  23. #23
    Join Date
    Jul 2005
    Location
    Los Angeles, California
    Posts
    1,369
    Quote Originally Posted by azn_romeo_4u View Post
    I have apache 2.2 and I don't see any of these options. Where would it be?
    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!

  24. #24
    Set "MinSpareServers" to "MinSpareServers 16"
    Set "MaxSpareServers" to "MaxSpareServers 32"
    I find people recommending different things for these 2 values.

    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)?

  25. #25
    Join Date
    Apr 2006
    Posts
    52
    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

Page 1 of 2 12 LastLast

Related Posts from theWHIR.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •