I have a cloud server with the following specifications:
2vCPUs
4G RAM
160GB Disk Space
Network 400Mb/s
System Image: Ubuntu 12.04 LTS
I am only running Magento CE 1.7.0.2 on this server. Nothing else.
Usually, the server has a loading time of 4-5 seconds. Recently, this has dropped to over 30 seconds and sometimes the server just goes away and I get HTTP error reports to my email stating that HTTP requests took more than 20000ms.
Running top command and sorting them returns the following:
top - 15:29:07 up 3:40, 1 user, load average: 28.59, 25.95, 22.91
Tasks: 112 total, 30 running, 82 sleeping, 0 stopped, 0 zombie
Cpu(s): 90.2%us, 9.3%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.2%st
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
31901 www-data 20 0 360m 71m 5840 R 7 1.8 1:39.51 apache2
32084 www-data 20 0 362m 72m 5548 R 7 1.8 1:31.56 apache2
32089 www-data 20 0 348m 59m 5660 R 7 1.5 1:41.74 apache2
32295 www-data 20 0 343m 54m 5532 R 7 1.4 2:00.78 apache2
32303 www-data 20 0 354m 65m 5260 R 7 1.6 1:38.76 apache2
32304 www-data 20 0 346m 56m 5544 R 7 1.4 1:41.26 apache2
32305 www-data 20 0 348m 59m 5640 R 7 1.5 1:50.11 apache2
32291 www-data 20 0 358m 69m 5256 R 6 1.7 1:44.26 apache2
32517 www-data 20 0 345m 56m 5532 R 6 1.4 1:45.56 apache2
30473 www-data 20 0 355m 66m 5680 R 6 1.7 2:00.05 apache2
32093 www-data 20 0 352m 63m 5848 R 6 1.6 1:53.23 apache2
32302 www-data 20 0 345m 56m 5512 R 6 1.4 1:55.87 apache2
32433 www-data 20 0 346m 57m 5500 S 6 1.4 1:31.58 apache2
32638 www-data 20 0 354m 65m 5508 R 6 1.6 1:36.59 apache2
32230 www-data 20 0 347m 57m 5524 R 6 1.4 1:33.96 apache2
32231 www-data 20 0 355m 66m 5512 R 6 1.7 1:37.47 apache2
32233 www-data 20 0 354m 64m 6032 R 6 1.6 1:59.74 apache2
32300 www-data 20 0 355m 66m 5672 R 6 1.7 1:43.76 apache2
32510 www-data 20 0 347m 58m 5512 R 6 1.5 1:42.54 apache2
32521 www-data 20 0 348m 59m 5508 R 6 1.5 1:47.99 apache2
32639 www-data 20 0 344m 55m 5512 R 6 1.4 1:34.25 apache2
32083 www-data 20 0 345m 56m 5696 R 5 1.4 1:59.42 apache2
32085 www-data 20 0 347m 58m 5692 R 5 1.5 1:42.29 apache2
32293 www-data 20 0 353m 64m 5676 R 5 1.6 1:52.73 apache2
32301 www-data 20 0 348m 59m 5564 R 5 1.5 1:49.63 apache2
32528 www-data 20 0 351m 62m 5520 R 5 1.6 1:36.11 apache2
31523 mysql 20 0 3460m 576m 8288 S 5 14.4 2:06.91 mysqld
32002 www-data 20 0 345m 55m 5512 R 5 1.4 2:01.88 apache2
32080 www-data 20 0 357m 68m 5512 S 5 1.7 1:31.30 apache2
32163 www-data 20 0 347m 58m 5512 S 5 1.5 1:58.68 apache2
32509 www-data 20 0 345m 56m 5504 R 5 1.4 1:49.54 apache2
32306 www-data 20 0 358m 68m 5504 S 4 1.7 1:53.29 apache2
32165 www-data 20 0 344m 55m 5524 S 4 1.4 1:40.71 apache2
32640 www-data 20 0 345m 56m 5528 R 4 1.4 1:36.49 apache2
31888 www-data 20 0 359m 70m 5664 R 4 1.8 1:57.07 apache2
32511 www-data 20 0 357m 67m 5512 S 3 1.7 1:47.00 apache2
32054 www-data 20 0 357m 68m 5660 S 2 1.7 1:53.10 apache2
1 root 20 0 24452 2276 1232 S 0 0.1 0:01.58 init
Moreover, running free -m returns the following:
total used free shared buffers cached
Mem: 4003 3919 83 0 118 901
-/+ buffers/cache: 2899 1103
Swap: 0 0 0
To investigate this further, I have installed apache buddy, it recommeneded that I need to reduce the maxclient connections. Which I did.
I also installed MysqlTuner and it suggests that I need to set my innodb_buffer_pool_size to = 3.0G.
However, I cannot do that, since the whole memory is 4G.
Here is the output from apache buddy:
### GENERAL REPORT ###
Settings considered for this report:
Your server's physical RAM: 4003MB
Apache's MaxClients directive: 40
Apache MPM Model: prefork
Largest Apache process (by memory): 73.77MB
[ OK ] Your MaxClients setting is within an acceptable range.
Max potential memory usage: 2950.8 MB
Percentage of RAM allocated to Apache 73.72 %
And this is the output of MySQLTuner:
-------- Performance Metrics -------------------------------------------------
[--] Up for: 47m 22s (675K q [237.552 qps], 12K conn, TX: 1B, RX: 300M)
[--] Reads / Writes: 45% / 55%
[--] Total buffers: 2.1G global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 2.5G (64% of installed RAM)
[OK] Slow queries: 0% (0/675K)
[OK] Highest usage of available connections: 26% (40/151)
[OK] Key buffer size / total MyISAM indexes: 36.0M/18.7M
[OK] Key buffer hit rate: 100.0% (245K cached / 105 reads)
[OK] Query cache efficiency: 92.5% (500K cached / 541K selects)
[!!] Query cache prunes per day: 302886
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 15K sorts)
[!!] Joins performed without indexes: 12135
[OK] Temporary tables created on disk: 25% (8K on disk / 32K total)
[OK] Thread cache hit rate: 90% (1K created / 12K connections)
[!!] Table cache hit rate: 17% (400 open / 2K opened)
[OK] Open file limit used: 12% (123/1K)
[OK] Table locks acquired immediately: 100% (196K immediate / 196K locks)
[!!] InnoDB buffer pool / data size: 2.0G/3.5G
[OK] InnoDB log waits: 0
-------- 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
Increase table_cache gradually to avoid file descriptor limits
Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
query_cache_size ( 64M)
join_buffer_size ( 128.0K, or always use indexes with joins)
table_cache ( 400)
innodb_buffer_pool_size (= 3G)
Last but not least, the server still has more than 60% of free disk space.
Now, based on the above, I have few questions:
Are these numbers normal? Do they make sense?
Do I need to upgrade the server?
If I don't need to upgrade and my configuration is not correct, how do I optimize it?
