Magento VPS Optimisation

[James.Simpson]

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:

# 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:

[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

 >>  MySQLTuner 1.6.2 - Major Hayden <[email protected]>

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

 

[syslint]

Install nginx too enable cache and compression . That will give some more speed.

 

[James.Simpson]

Install nginx too enable cache and compression . That will give some more speed.

This is something i am looking at doing, but trying to get the most out of what is currently installed.

 

[cPanelMichael]

Hello

You can try lowering the query_cache_size value in /etc/my.cnf to "64M" to see if that makes a difference.

Thank you.

 

[James.Simpson]

Hello

You can try lowering the query_cache_size value in /etc/my.cnf to "64M" to see if that makes a difference.

Thank you.

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.