Page 1 of 2 12 LastLast
Results 1 to 15 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,257
    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,257
    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,257
    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,257
    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,648
    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.

  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,257
    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/


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
  •