Magento on DigitalOcean Server
I have a problem with a Magento store running on a Digital Ocean server.
This is my blob config:
2GB Ram | 40GB SSD Disk | New York 2 | Ubuntu Ubuntu 12.04.3 x64
When I use Magento store, it works fine, except when I try to create an account or make a purchase.
Then it takes 2 minutes to complete the purchase.
I have loaded the entire site locally (using db) and it only takes a few seconds to order and create an account.
I tried with a drop upgrade to 4GB Ram but still the same.
This is my php config:
max_execution_time 30
max_file_uploads 20 20
max_input_nesting_level 64
max_input_time 60
max_input_vars 1000
memory_limit 512M
I am not sure how to proceed with debugging. Can anyone for some advice?
Update # 2 (Based on UPD from MageWorx): Based on MageWorx's suggestion (updated), I ran mysqltunner on the server. Here are the results:
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.38-0ubuntu0.12.04.1
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 25K (Tables: 28)
[--] Data in InnoDB tables: 36M (Tables: 1386)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 68)
[!!] Total fragmented tables: 1387
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 49s (338 q [6.898 qps], 71 conn, TX: 181K, RX: 51K)
[--] Reads / Writes: 92% / 8%
[--] Total buffers: 832.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 1.1G (54% of installed RAM)
[OK] Slow queries: 0% (0/338)
[OK] Highest usage of available connections: 2% (2/100)
[OK] Key buffer size / total MyISAM indexes: 32.0M/178.0K
[!!] Query cache efficiency: 12.9% (30 cached / 233 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 12 sorts)
[OK] Temporary tables created on disk: 25% (57 on disk / 222 total)
[OK] Thread cache hit rate: 97% (2 created / 71 connections)
[OK] Table cache hit rate: 25% (1K open / 6K opened)
[OK] Open file limit used: 1% (104/8K)
[OK] Table locks acquired immediately: 100% (248 immediate / 248 locks)
[OK] InnoDB buffer pool / data size: 512.0M/36.8M
[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
Variables to adjust:
query_cache_limit (> 4M, or use smaller result sets)
These are the results from apachebuddy:
Your server has 2002 MB of memory
The largest apache process is using 39.78 MB of memory
The smallest apache process is using 15.04 MB of memory
The average apache process is using 17.32 MB of memory
Going by the average Apache process, Apache can potentially use 433.00 MB RAM (21.63 % of available RAM)
Going by the largest Apache process, Apache can potentially use 994.50 MB RAM (49.68 % of available RAM)
Generating reports...
### GENERAL REPORT ###
Settings considered for this report:
Your server physical RAM: 2002MB
Apache MaxClients directive: 25
Apache MPM Model: prefork
Largest Apache process (by memory): 39.78MB
[ OK ] Your MaxClients setting is within an acceptable range.
Max potential memory usage: 994.5 MB
Percentage of RAM allocated to Apache 49.68 %
source to share
I dare to assume that the problem is caused by a lack of optimization of the mysql parameters. The point is, without optimizing mysql, you won't be able to improve website speed as mysql won't use 100% of the server's resources.
These are the basic settings for a 2Gb blob (/etc/mysql/my.cnf):
key_buffer=32M
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
table_cache=128
innodb_buffer_pool_size=256M
Since these are basic settings, you can use the http://mysqltuner.com/ app to optimize mysql further.
source to share
UPD:
As I can see from the results obtained with mysqltuner, you need to experiment with the following settings:
skip-networking query_cache_limit = 4M query_cache_size = 256M thread_concurrency = 4 table_open_cache = 4096 innodb_buffer_pool_size = 512M join_buffer_size = 1M
Also note that after making changes to my.cnf file, remember to restart mysql
sudo service mysql restart
After the settings have been applied, check your site for a while and then run mysqltuner to see the results.
source to share
I am using this exact Digital Ocean configuration (in terms of RAM and SSD mine on Centos and with default PHP.ini configuration) for various Magento development / build sites, they work fine, so I would say your server config is not problem. It must be ineffective code in a template or module.
I would start profiling it,
https://www.nublue.co.uk/blog/using-magento-profiler-to-speed-up-magento-performance/
I am still using the AOE profiling module, it makes life easier, I think:
https://github.com/fbrnc/Aoe_Profiler
I also ran n98-magerun to see if there are any module conflicts;
https://github.com/netz98/n98-magerun
Then if you still can't find it, switch to the default theme and start disabling modules to see which one it has.
source to share