What is the best method to remedy Magento being slow with 20,000+ products

Question

I'm running magento on 3 Amazon EC2 instances. One is set up to be accessed directly for the admin panel, the other two are sitting behind a load balancer.

Things were running smoothly until we imported our data with 20k+ products, each a configurable product with ~4 simple products (for different sizes, colours etc.)

The only things running slow seem to be things that loop over products - both admin and frontend catalog pages take 5-10+ seconds to get a response from the server. Static/CMS pages load fine.

They're all connected to the one RDS MySQL database which is running fine - I can do queries and get them back quickly.

We have all caching (including full page cache) enabled and have enabled flat catalogs with no real change in speed.

The magento directories are independent per-server, except for the media/ dir which is kept in sync with lsyncd. The admin server behaves as the master and the two load-balanced frontend servers are the slaves.

Answer 1

Let's break this down:

1. Assumptions I will make (please check them)

   • You are running on fairly powerful EC2 instances eg m3.large
   • You are running a PHP cache like APC
   • You are using the Magento compiler system->tools->compilation
   • You are using Apache webserver
   • '5 to 10 seconds to get a response from the server' means the response time and does not include image and CSS and JS download time to the browser
   • You have flat tables for products and categories (but if your cache is working properly then this shouldn't dominate speed. You should also run tests without the flat tables; they are not always quicker)
   • Your webserver configuration is optimized for high volume traffic for 'keep alives' and 'expires headers'

---

1. Triple check your full-page caching

The really strange thing about your question is that you say you have a full page cache yet product front-end pages are taking 5 to 10 seconds before the server sends them to the browser.

In my opinion it means your full page cache is not working. A full page cache, when implemented properly, will serve the page from Apache directly without running the Magento app (Mage.php) at all, that is why it is so quick. It means there is no overhead when a cached page is requested and it is why a full page cache system should have 'requesting' times of less than 0.25 seconds and sometimes less than 0.1 seconds.

I suggest you turn your full page cache off and see what difference it makes. Check your theme and your cache documentation for how they handle non-cacheable page content like basket summary and display user name - but any cache should cache all the product blocks anyway so making a front-end product page should access the cache and not access the database.

Of course if you have 20000 products (or 100,000? = 20,000 configured + 4*20,000 simples) then every page needs to be visited once to populate your caches so you might want to set a link checker running overnight to hit all your URLs and prime the full page cache for each category and/or product page.

---

2. Check your theme .phtml files for the dreaded

Mage::getModel(catalog/product)->load($_product->getId())

this line hits your database hard and if you are doing that for every product on a category page then it will spell trouble. If your theme is using ->load() then talk to your theme designers about creating collections with just the attributes they need. BUT if you have a page cache then this won't necessarily matter (hence why I think your cache isn't working).

---

3. Take a look at your core_url_rewrite table

Chances are that this table is huge due to all the products you have. It might help to make your simple products not visible and only make the configurables visible in catalog and search. Check how many rows the table has, truncate it, then go to the Magento Admin and re-index the rewrites - this will re build the table and you can see if it has less rows (Magento seems to double up a lot of rewrites over time). Also you will get a full set of rewrites for each store in Magento so delete any stores you aren't using.

Now another note on caches. I find the core_url_rewrite one of the bottle necks so I put a cache around the .phtml that generates the store menu because the menu doesn't change much and doing so saves a lot database time. BUT if you have a cache already then this won't be an issue unless your cache isn't working or isn't set up properly.

---

5. Get the Varien profiler working

So that you can see what is taking magento so long. I think you will need to turn off the caches for it to work. This is a useful tool for speed profiling, but other free tools exist and actually you can just use the Varien profiler without a tool. The tool will give you an indication of what is taking a long time to build on the page (but if your cache is working properly then it won;t matter how long the page tales to build because the page would be served from the cache which is why I think your cache isn't working).

---

6. You say 'MySQL database which is running fine - I can do queries and get them back quickly.'

But that isn't the test for whether your database is working fine or not. Maybe you know all this but you can use phpmyadmin to check if your my.cnf settings are optimised. phpmyadmin->status->advisor will give you hints for innodb_buffer_pool and key_buffer_size and table_cache. Whatever you do, Magento does not have optimised indexes so mysql will always have a lot of work to do. You might want to look at your innodb files like what is suggested here (and I this is required reading too), but if your ibdata1 file and you innodb log files aren't too big and you don't have anything in the slow query logs and you aren't suffering too many lock waits then there maybe isn't an advantage to running with 'innodb_file_per_table'. Some people suggest innodb_file_format=Barracuda, but I think we are getting into the fine tuning to squeeze the last millisecond out.

Here is a truly excellent Stackoverflow Q&A about ibdata files, table optimization and innodb management. Caveat: I don't know the optimum innodb set up for Magento but when I read articles like that one I think it seems like the correct way to go.

Anyway you should ensure your my.cnf is set to use the memory available to it in the optimum way (there is no one magic set up, I cannot tell you it, but study these parameters: ).

max_allowed_packet =  
table_cache =  
sort_buffer_size =   
read_buffer_size =   
read_rnd_buffer_size =   
myisam_sort_buffer_size =   
tmp_table_size =   
max_heap_table_size =  
query_cache_size =  
query_cache_type =  
thread_cache_size =  

innodb_fast_shutdown = 0  
innodb_file_per_table  
innodb_buffer_pool_size =  
innodb_additional_mem_pool_size =  
innodb_log_file_size =  
innodb_log_buffer_size =  
innodb_flush_log_at_trx_commit =  
innodb_lock_wait_timeout =  
innodb_thread_concurrency =  
skip-external-locking  
max_connections =  
read_buffer_size =  
sort_buffer_size =  
key_buffer_size =

---

7. SSH into your boxes

and run 'top' to watch the memory and cpu loads of the http daemon and the mysql server - I sometimes do this while running my link checker so I can see the system under at least a little load. If there is very little load, maybe your httpd.conf and my.cnf are not set to utilise the CPU and memory available. If your CPU and memory is getting maxed out then maybe you need bigger boxes but if your full page cache is working properly...

Using top will also show if your server has been compromised and all the CPU cylces are being hogged by some script kiddie's bitcoin miner.

---

8. Throw money at it

Go to M3 Extra large boxes, telephone Percona and take all its advice, get a tonne of RAM and run your database in a ramdisk, hire some kid from Facebook to run Magento on HHVM, or say 'screw it' and pay a Magento expert hosting company to do it all for you. But if your full page cache is working...

----------

Any way, I hope you have fun. I enjoy making Magento run faster. There are a tonne of knobs to tweak and it is very rewarding to see the page load times dropping bit by bit.

Oh, I think the slow admin area won't be helped by full page caching but there are some modules out there that make managing large product catalogues much simpler.