The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Magento VPS Optimisation

Discussion in 'Workarounds and Optimization' started by James.Simpson, Jan 16, 2016.

  1. James.Simpson

    James.Simpson Member

    Joined:
    Feb 18, 2013
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    United Kingdom
    cPanel Access Level:
    Root Administrator
    Hi Guys,

    I have a test VPS with 30GB ram running SSD's and 4 quad cores, so this VPS should be a best and easily run Magento loading pages well within 2 seconds. Static pages are fast, and load just over 1 second each, but as soon as anything query driven, the time shoots up.

    I have done some optimisation, but unsure what else I can tweak. I'm running Apache 2.4 with PHP 5.5. I have also enabled OpCache to try and speed things up a bit.

    Here's my httpd.conf:
    Code:
    # These can be set in WHM under 'Apache Global Configuration'
    Timeout 60
    TraceEnable Off
    ServerSignature Off
    ServerTokens Full
    FileETag None
    StartServers 15
    <IfModule prefork.c>
    MinSpareServers 15
    MaxSpareServers 30
    </IfModule>
    ServerLimit 256
    MaxRequestWorkers 250
    MaxConnectionsPerChild 10000
    KeepAlive On
    KeepAliveTimeout 5
    MaxKeepAliveRequests 120
    Here is my my.cnf settings:
    Code:
    [mysqld]
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    max_allowed_packet=268435456
    open_files_limit=10000
    query_cache_size=100M
    query_cache_limit=1M
    join_buffer_size=8M
    sort_buffer_size = 8M
    read_buffer_size = 8M
    read_rnd_buffer_size = 4M
    table_cache=4096
    max_heap_table_size = 50M
    innodb_buffer_pool_size = 1G
    table_open_cache = 4000
    key_buffer_size = 128M
    tmp_table_size = 128M
    myisam_sort_buffer_size = 64M
    query_cache_size = 96M
    query_cache_type = 1
    thread_cache_size=16
    max_connections = 400
    wait_timeout = 300
    

    MYSQL Tuner is stating the following
    Code:
     >>  MySQLTuner 1.6.2 - Major Hayden <major@mhtx.net>
    
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    
     >>  Run with '--help' for additional options and output filtering
    
    [--] Skipped version check for MySQLTuner script
    
    [OK] Currently running supported MySQL version 5.5.47-cll
    
    [OK] Operating on 64-bit architecture
    
    
    -------- Storage Engine Statistics -------------------------------------------
    
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    
    [--] Data in MyISAM tables: 474M (Tables: 56)
    
    [--] Data in InnoDB tables: 389M (Tables: 706)
    
    [--] Data in MEMORY tables: 14M (Tables: 34)
    
    [!!] Total fragmented tables: 44
    
    
    -------- Security Recommendations  -------------------------------------------
    
    [OK] There are no anonymous accounts for any database users
    
    [OK] All database users have passwords assigned
    
    [--] There are 605 basic passwords in the list.
    
    
    -------- CVE Security Recommendations  -------------------------------------------
    
    [--] Skipped due to --cvefile option undefined
    
    
    -------- Performance Metrics -------------------------------------------------
    
    [--] Up for: 1d 16h 56m 51s (7M q [53.092 qps], 61K conn, TX: 43B, RX: 1B)
    
    [--] Reads / Writes: 82% / 18%
    
    [--] Binary logging is disabled
    
    [--] Total buffers: 1.3G global + 28.2M per thread (400 max threads)
    
    [OK] Maximum reached memory usage: 5.3G (16.85% of installed RAM)
    
    [OK] Maximum possible memory usage: 12.3G (39.29% of installed RAM)
    
    [OK] Slow queries: 0% (18/7M)
    
    [OK] Highest usage of available connections: 36% (145/400)
    
    [OK] Aborted connections: 0.01%  (9/61148)
    
    [OK] Query cache efficiency: 87.7% (6M cached / 7M selects)
    
    [!!] Query cache prunes per day: 207427
    
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 98K sorts)
    
    [!!] Joins performed without indexes: 2209
    
    [!!] Temporary tables created on disk: 32% (55K on disk / 169K total)
    
    [OK] Thread cache hit rate: 99% (168 created / 61K connections)
    
    [OK] Table cache hit rate: 21% (1K open / 4K opened)
    
    [OK] Open file limit used: 1% (165/10K)
    
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    
    
    -------- MyISAM Metrics -----------------------------------------------------
    
    [!!] Key buffer used: 23.2% (31M used / 134M cache)
    
    [OK] Key buffer size / total MyISAM indexes: 128.0M/61.1M
    
    [!!] Read Key buffer hit rate: 93.8% (4M cached / 289K reads)
    
    [!!] Write Key buffer hit rate: 91.7% (1M cached / 99K writes)
    
    
    -------- InnoDB Metrics -----------------------------------------------------
    
    [--] InnoDB is enabled.
    
    [OK] InnoDB buffer pool / data size: 1.0G/389.9M
    
    [OK] InnoDB buffer pool instances: 1
    
    [!!] InnoDB Used buffer: 41.99% (27520 used/ 65535 total)
    
    [OK] InnoDB Read buffer efficiency: 100.00% (1258211887 hits/ 1258229419 total)
    
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    
    [OK] InnoDB log waits: 0.00% (0 waits / 62203 writes)
    
    
    -------- AriaDB Metrics -----------------------------------------------------
    
    [--] AriaDB is disabled.
    
    
    -------- Replication Metrics -------------------------------------------------
    
    [--] No replication slave(s) for this server.
    
    [--] This is a standalone server..
    
    
    -------- Recommendations -----------------------------------------------------
    
    General recommendations:
    
        Run OPTIMIZE TABLE to defragment tables for better performance
    
        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 which have no LIMIT clause
    
    Variables to adjust:
    
        query_cache_size (> 96M)
    
        join_buffer_size (> 8.0M, or always use indexes with joins)
    
        tmp_table_size (> 128M)
    
        max_heap_table_size (> 50M)
    

    I'm not sure what else I can do to speed this beast up? Any ideas?
     
  2. syslint

    syslint Well-Known Member

    Joined:
    Oct 9, 2006
    Messages:
    249
    Likes Received:
    6
    Trophy Points:
    18
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Twitter:
    Install nginx too :) enable cache and compression . That will give some more speed.
     
  3. James.Simpson

    James.Simpson Member

    Joined:
    Feb 18, 2013
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    United Kingdom
    cPanel Access Level:
    Root Administrator
    This is something i am looking at doing, but trying to get the most out of what is currently installed.
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,694
    Likes Received:
    654
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  5. James.Simpson

    James.Simpson Member

    Joined:
    Feb 18, 2013
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    United Kingdom
    cPanel Access Level:
    Root Administrator
    Thanks for the suggestion, that seemed to shave off around half a second or so. Did a bit of reading up on query_cache_size, and more is defiantly not better on this setting (although mysql tuner suggests going higher with this value).

    I'm thinking of running php in FCGI mode to get things like opcache working with Magento properly and then install Memcache.

    I did try NGINX, but I don't really want to take things away from EasyApache (after installing NGINX previously and then trying to rebuild apache after a corruption left me with a load of errors). Also NGINX was a nightmare to set up especially with an SSL.
     
Loading...

Share This Page