1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Apache & MySQL Optimizing

Discussion in 'Server Configuration and Hosting' started by yavuz, Feb 11, 2012.

  1. yavuz

    yavuz Well-Known Member

    For several days, I'm trying to tune my server setting, my.conf and other stuff and I thought I ask for your suggestions.

    Server Specs:

    Intel(R) Core(TM) i7 CPU VPS with two cores avaliable (total 5000 Ghz)
    4 GB RAM
    cPanel as control panel
    MySQL 5.1.56, php 5.3.10

    Zend Engine v2.3.0, eAccelerator v0.9.6.1, ionCube PHP Loader v4.0.9, Suhosin v0.9.32.1
    APC

    my.conf is currently set to default:

    Code:
    [mysqld]
    set-variable = max_connections=500
    log-slow-queries
    safe-show-database
    wait_timeout = 28800
    max_allowed_packet = 16777216
    
    php.ini

    Code:
    extension_dir = "/usr/local/lib/php/extensions/no-debug-non-zts-20090626"
    zend_extension = "/usr/local/IonCube/ioncube_loader_lin_5.3.so"
    extension = "eaccelerator.so"
    extension = "suhosin.so"
    extension = "pdo.so"
    extension = "pdo_sqlite.so"
    extension = "sqlite.so"
    extension = "pdo_mysql.so"
    extension = "apc.so"
     
    apc.enabled = 1
    apc.shm_segments = 1
    apc.shm_size = 128M
    
    This is the output I get with mysqltuner:

    Code:
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 5h 5m 25s (5M q [52.293 qps], 354K conn, TX: 176B, RX: 1B)
    [--] Reads / Writes: 82% / 18%
    [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (34% of installed RAM)
    [OK] Slow queries: 0% (101/5M)
    [OK] Highest usage of available connections: 5% (25/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/254.0M
    [OK] Key buffer hit rate: 96.1% (72M cached / 2M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 520K sorts)
    [!!] Joins performed without indexes: 47179
    [!!] Temporary tables created on disk: 46% (84K on disk / 182K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 1M opened)
    [OK] Open file limit used: 1% (42/2K)
    [OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
    [!!] InnoDB data size / buffer pool: 693.9M/8.0M
     
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Set thread_cache_size to 4 as a starting value
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
        query_cache_size (>= 8M)
        join_buffer_size (> 128.0K, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        thread_cache_size (start at 4)
        table_cache (> 64)
        innodb_buffer_pool_size (>= 693M)
    
    This is what I put in my library/config.php

    Code:
    $config['cache']['enabled'] = true;
    $config['cache']['frontend'] = 'Core';
    $config['cache']['frontendOptions']['cache_id_prefix'] = 'xf_';
    $config['cache']['backend'] = 'Apc';
    
    I assume this is the correct for frontent and backend caching?

    Server load is usually ok but loading website pages become a pain. What would you suggest I set me my.conf and php.ini?

    Thanks.
     
  2. Ghan_04

    Ghan_04 Active Member

    Here are a few MySQL settings I'd recommend putting in your my.cnf right now and then tuning over time:

    key_buffer_size = 512M
    max_allowed_packet = 8M
    table_open_cache = 4096
    sort_buffer_size = 8M
    read_buffer_size = 8M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 16M
    join_buffer_size = 1M
    query_cache_size = 256M
    query_cache_limit = 2M
    table_definition_cache = 4096

    That VPS you have is a beast and I expect it to be able to handle thousands of concurrent users. You might also want to look into switching your webserver to Litespeed (http://www.litespeedtech.com) to increase performance even further. Litespeed is fully compatible with Apache config files and is generally considered to perform better.
     
    yavuz likes this.
  3. yavuz

    yavuz Well-Known Member

    I've installed nginx from nginxcp.com which supports reverse proxy.

    I had several suggestions in the last couple of days, modified my.conf settings with your suggestions which look like this now:

    Code:
    [mysqld]
    wait_timeout = 28800
    query_cache_limit=2M
    query_cache_size=256M
    query_cache_type=1
    max_connections=1000
    max_user_connections=300
    interactive_timeout=100
    wait_timeout=100
    connect_timeout=10
    thread_cache_size=512
    key_buffer=512M
    join_buffer_size=1M
    max_allowed_packet=32M
    table_cache=128
    table_definition_cache = 4096
    sort_buffer_size=2M
    read_buffer_size=2M
    read_rnd_buffer_size=3072K
    max_connect_errors=10
    thread_concurrency=8
    myisam_sort_buffer_size=16M
    max_allowed_packet = 8M
    skip-locking
     
    [myisamchk]
    key_buffer=512M
    sort_buffer=256M
    read_buffer=64M
    write_buffer=64M
    
    Seem fine to you Ghan? Thanks for your input.
     
  4. Walter

    Walter Well-Known Member

    max_connection is far to high - have you considered your memory consumption??? Either you need really so many connctions (but then your 4GB will be exhausted) or you are wasting resources.
     
  5. yavuz

    yavuz Well-Known Member

    Lowered to 500.

    Code:
    [mysqld]
    wait_timeout = 28800
    query_cache_limit=2M
    query_cache_size=256M
    query_cache_type=1
    max_connections=300
    max_user_connections=100
    interactive_timeout=100
    wait_timeout=100
    connect_timeout=10
    thread_cache_size=512
    key_buffer=512M
    join_buffer_size=1M
    max_allowed_packet=32M
    table_cache=128
    table_definition_cache = 2048
    sort_buffer_size=2M
    read_buffer_size=2M
    read_rnd_buffer_size=3072K
    max_connect_errors=10
    thread_concurrency=8
    myisam_sort_buffer_size=16M
    max_allowed_packet = 8M
    skip-locking
    
    [myisamchk]
    key_buffer=512M
    sort_buffer=256M
    read_buffer=64M
    write_buffer=64M
    
    I run mysqltuner and got back with this:

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.56
    [OK] Operating on 64-bit architecture
    Code:
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 625M (Tables: 1115)
    [--] Data in InnoDB tables: 710M (Tables: 405)
    [--] Data in MEMORY tables: 7M (Tables: 17)
    [!!] Total fragmented tables: 440
    
    Code:
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 11s (546 q [49.636 qps], 42 conn, TX: 15M, RX: 232K)
    [--] Reads / Writes: 72% / 28%
    [--] Total buffers: 794.0M global + 8.2M per thread (300 max threads)
    [OK] Maximum possible memory usage: 3.2G (79% of installed RAM)
    [OK] Slow queries: 0% (0/546)
    [OK] Highest usage of available connections: 1% (3/300)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/258.2M
    [!!] Key buffer hit rate: 88.1% (1K cached / 211 reads)
    [OK] Query cache efficiency: 40.8% (144 cached / 353 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 53 sorts)
    [!!] Joins performed without indexes: 5
    [!!] Temporary tables created on disk: 41% (7 on disk / 17 total)
    [OK] Thread cache hit rate: 92% (3 created / 42 connections)
    [OK] Table cache hit rate: 92% (128 open / 138 opened)
    [OK] Open file limit used: 3% (54/1K)
    [OK] Table locks acquired immediately: 100% (584 immediate / 584 locks)
    [!!] InnoDB data size / buffer pool: 710.8M/8.0M
    
    Code:
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Enable the slow query log to troubleshoot bad queries
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        join_buffer_size (> 1.0M, or always use indexes with joins)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        innodb_buffer_pool_size (>= 710M)
    
     
  6. Walter

    Walter Well-Known Member

    You have mysql configured so it will use a maxium of 3.2 GB out of 4 GB - if this server also runs a webserver you will run into problems.

    Data in MyISAM tables: 625M
    Data in InnoDB tables: 710M

    But you reserve 512M as MyISAM key_buffer but have no Inndob buffer? I would lower the keybuffer and make at least some Innodb buffer (e.g. each 256M). The suggestions of mysqltuner are pretty good :)
     
  7. yavuz

    yavuz Well-Known Member

    Walter thanks again. Would you modify my my.conf and write down your suggestions? I'm doing so much tuning that everything is blurr to me :D
     
  8. yavuz

    yavuz Well-Known Member

    Guys, how do should I read these suggestions and what example (to put in my.conf)

    Code:
        join_buffer_size (> 1.0M, or always use indexes with joins)
        tmp_table_size (> 54M)
        max_heap_table_size (> 65M)
        innodb_buffer_pool_size (>= 752M)
    
     
  9. ChemicalKicks

    ChemicalKicks Well-Known Member

    Just going to through a curveball out at you, have you considered a LEMP setup?
     
  10. yavuz

    yavuz Well-Known Member

    I currently have nginx installed ChemicalKicks, haven't considered LEMP, no.
     
  11. ChemicalKicks

    ChemicalKicks Well-Known Member

    Maybe consider it. If you're at the stage now where you're having to spend so much time configure Apache why not just get rid of it altogether and you'll have the added bonus of not having cPanel installed on your server :p but seriously if you do need cPanel then it wouldn't be a goer.
     
  12. Ghan_04

    Ghan_04 Active Member

    If you want cPanel and you don't want Apache, use Litespeed. I've had great success with it. (Though I've never installed cPanel myself so I'm not sure what you need to do to get it running)
     
  13. EasyTarget

    EasyTarget Well-Known Member

  14. Ghan_04

    Ghan_04 Active Member

    cPanel doesn't support 5.5 yet, as far as I know.
     
  15. yavuz

    yavuz Well-Known Member

    Yeap, I've purchased litespeed and will install it in a couple of hours. Lets see how it turns out.
     
  16. Ghan_04

    Ghan_04 Active Member

    I know that there is a Litespeed plugin for cPanel that allows EasyApache to work seamlessly with Litespeed. You might want to check that out.
     
  17. yavuz

    yavuz Well-Known Member

    just installed and yes I'm using that plugin. Maybe it's too early but I couldn't see any performance boost at all
     
  18. Ghan_04

    Ghan_04 Active Member

    Litespeed, like anything else, still requires some configuration tuning. Specifically, you should look into tuning the settings for the PHP external app on the server config level.
     
  19. CyclingTribe

    CyclingTribe Well-Known Member

    Backup your current my.cnf and try this alternative one:

    Code:
    [mysqld]
    wait_timeout = 28800
    query_cache_limit=2M
    query_cache_size=32M
    query_cache_type=1
    max_connections=300
    interactive_timeout=100
    wait_timeout=100
    connect_timeout=10
    thread_cache_size=40
    key_buffer=512M
    join_buffer_size=512K
    max_allowed_packet=16M
    table_cache=2000
    table_definition_cache = 2048
    sort_buffer_size=512K
    read_buffer_size=1M
    read_rnd_buffer_size=512K
    max_connect_errors=10
    thread_concurrency=8
    myisam_sort_buffer_size=64M
    max_allowed_packet = 16M
    skip-locking
    
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    Also, have you turned on and configured InnoDB for your MySQL server (it doesn't appear so if the my.cnf you posted above is your full my.cnf)?

    Cheers,
    Shaun :D
     
  20. yavuz

    yavuz Well-Known Member

    It was the default that cpanel created, yes Shaun. Rebooted the server, will add that when it comes up.
     

Share This Page