LEMP Stack Suggestions for a 10k + Item Magento Community Install

Question

I operate a Magento Community install that currently contains 30k SKUs (6k configurable with 24k variants).

We are having an ongoing issue with pervasive CPU overloads that result in MYSQL connection interruptions and httpd lockups.

Typically, the issues begin when Catalog_URL Rewrites are Reindexed, or when I mass update Amazon listings with M2ePro.

Just to get through my reindexes I:

Clear Cache and Temporal files:

rm -rf downloader/pearlib/cache/*   
rm -rf downloader/pearlib/download/*   
rm -rf var/cache/*   
rm -rf var/session/*   
rm -rf var/report/*   
rm -rf var/tmp/*  

Truncate Logs:

TRUNCATE log_customer;   
TRUNCATE log_quote;   
TRUNCATE log_summary;   
TRUNCATE log_summary_type;   
TRUNCATE log_url;   
TRUNCATE log_url_info;   
TRUNCATE log_visitor;   
TRUNCATE log_visitor_info;   
TRUNCATE log_visitor_online;   
TRUNCATE report_event;   
TRUNCATE catalogsearch_fulltext;   
TRUNCATE catalogsearch_query;   
TRUNCATE catalog_compare_item; 

Clear the Image Cache:

rm -rf media/catalog/product/cache/*   
rm -rf media/tmp/* 

Set index mode to manual:

php indexer.php --mode-manual catalog_product_attribute  
php indexer.php --mode-manual catalog_product_price  
php indexer.php --mode-manual catalog_url  
php indexer.php --mode-manual catalog_product_flat  
php indexer.php --mode-manual catalog_category_flat  
php indexer.php --mode-manual catalog_category_product  
php indexer.php --mode-manual catalogsearch_fulltext  
php indexer.php --mode-manual cataloginventory_stock  
php indexer.php --mode-manual tag_summary 

*Restart Cloud DB, MYSQL, and HTTPD

Reindex all indexes:*

php indexer.php -r --reindexall

Set index mode to realtime:

php indexer.php --mode-realtime catalog_product_attribute  
php indexer.php --mode-realtime catalog_product_price  
php indexer.php --mode-realtime catalog_url  
php indexer.php --mode-realtime catalog_product_flat  
php indexer.php --mode-realtime catalog_category_flat  
php indexer.php --mode-realtime catalog_category_product  
php indexer.php --mode-realtime catalogsearch_fulltext  
php indexer.php --mode-realtime cataloginventory_stock  
php indexer.php --mode-realtime tag_summary 

Restart Cloud DB, MYSQL, and HTTPD

Madness I know, but now we are looking at a non-bandaged solution.

Rather than just increasing my RAM/HD/BOX allocation I would really appreciate some feedback on my current configuration, along with any suggestions as to how to improve response time and reduce overall load.

Particulars:

Magento Community 1.6.2

Apache build with NGINX serving static content (and configured to be used as a reverse proxy server).

M2ePro (most active connection requests - used to sync Amazon/ebay orders/listing/revisions/inventory)

Server Details:

Box:

CentOS 6.3
4096 MB RAM
160 GB Disk (40% Disc Utilization)

Cloud DB:

2 GB RAM
3 GB Disk Current DB size (expanded) 1.7 GB

Pertinent Installed Software Info:

Apache 2.2.15
NGINX 0.91
PHP 5.3.3
MYSQL 5.1.66

Magento Caching Options:

APC Caching
Memcache

Server my.cnf Details:

query-cache-size= 64M
query-cache-limit   = 8M
innodb_buffer_pool_size = 1000M
query_cache_size= 64M
default-storage-engine  = InnoDB
thread_concurrency  = 6
innodb_thread_concurrency   = 6
max-connections = 100

Money/time are limited so I would like to figure out if they would best be spent:

1) Continuing to focus on the optimization of currently allocated resources.

2) Leasing another cloud server, and set up NGINX load balancing

3) Loading Apache onto a few in house boxes (DSL connected) for the purpose of CPU load balancing.

4) Some other option I am not thinking of. Any and all useful suggestions are very much appreciated.

Thank You!

Answer 1

From what I gather, your DB server is geographically remote to your web server - connected via a DSL connection?

• You don't need to load balance Nginx, it isn't your bottleneck.
• You do need to buy A LOT more RAM for your DB server to match that of your innodb table space + allocations per connection, which I would guess is at least 8GB
• Your web server and DB server should be either extremely well connected (minimum of 1Gbps) or on the same machine. Given you've got a single server set up, MySQL could be on the same machine without issue.
• Your HDDs are probably too slow (160GB is commonly a SATA disk)
• Your CPU is probably too slow
• You should never restart MySQL - it flushes all the caches
• You should never restart PHP, it flushes your opcode caches

It doesn't matter how many config files you post, or how detailed a specification you provide of your architecture and hardware, there is no one size fits all series of configuration files that will suddenly make your store perform well. Improving Magento performance is an iterative process of continual fine tuning - using system graphs/stats by means of feedback to inform you better on how to progress.

You are drastically under spec'ed for what you are trying to do and likely have the machines completely mis-configured. You're not going to be able to have cheap, high performance and soon. So make a compromise and pick any two.

At the risk of self promotion. You would be better off cancelling whatever you are currently paying for and hosting with a recognised Magento specialist - then just focus on running your business, selling goods and making money, not trying to be a system administrator.

Answer 2

Sounds like MySQL is the bottleneck here, maybe a dedicated MySQL server?Or better, make that 2!One read, one write. Less locks, should work faster.

Answer 3

Thanks for your response @Sonassi. It's easy to appreciate someone that takes the time to detail their thoughts. I should also clarify that I hold the same pertinent views. This post was a successful attempt to settle an in-house disagreement about where to spend and on what.

In my post I mentioned that I was considering leasing another cloud server, but was not clear that the one I am am referencing in this post is a RackSpace provided cloud server instance. MySQL has been restarted (when it is) after one of the many rounds of conf tweaking.

I ended up doubling my processors to 4, and my RAM to 8 GB on the main server. After updating my.cnf to make use of the additional threading and increasing INNODBs memory allotment everything has begun to run at much more acceptable levels. There is no question that we need to increase RAM/CPU allotment further, but at the level of business (earnings) that we are currently operating at, this will definitely suffice.

I eliminated the cloud DB (and am running it locally again) to cover the cost.

Answer 4

We do many things said above and 2 additional:

a) Delete old baskets:

DELETE FROM sales_flat_quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 90 DAY);

OPTIMIZE TABLE sales_flat_quote;

Because the table grows to much for us.

b) Reindex on a dedicated server. Reindexing is quite "expensive". It sets locks on the database and runs many hours. Even if your MySQL server is on SSDs. So we run indexing for URLs and products an a dedicated server. This server has not direct connection to your live system. It is a little bit tricky to implement. You have to export and import tables etc. Another aspect is tha Magento creates a lot of URL index entries you do no need: Additional hints here: Too many URL Rewrites