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:
Here is my my.cnf settings:
MYSQL Tuner is stating the following
I'm not sure what else I can do to speed this beast up? Any ideas?
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
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 <[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?