We have around 200K products on Magento CE 1.8.02, catalog_product_flat indexing is really creating problem for me.

Did try manually indexing, used command line to do that, all are taking lots of time and even some time process is not completed and server crashed.

We are using dedicated server with 4GB RAM and good hardware.

Also I have to do product import on daily bases so my indexing should be very quick.

Please suggest possible solution for Magento CE only for now.

  • upgrade your ram, since it's a oneshot process, it'll grow huge (especially if you have that many products), at least that's what I'd do Oct 10 '14 at 7:30
  • Thanks for your suggestion, we are planning to do that but at some point we have to load 1000K products. (Not now but some time). I am looking for solid suggestion to handle that much catalog without making site office for a Minute :) Oct 10 '14 at 10:06

You don't have any where near enough RAM. For a catalogue of 200k products, you should have at least 32GB RAM.

You can verify just how much table space is being occupied (this will dictate the absolute minimum amount of RAM needed for Magento) using this command,

IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize 
FROM information_schema.tables 
  WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') 
  AND engine IS NOT NULL 
GROUP BY engine 

The amount above will dictate your baseline. In addition you need to allocate around 768MB to each PHP admin thread and 256MB to each PHP frontend thread. Then per PHP process, around 64MB per MySQL connection. Plus the overheads of the OS and other utilities.

Magento shouldn't be run with anything less than 8GB RAM - this is the absolute minimum. With a catalogue your size, you'll find you have about 6GB of DB table space used, add in 16 PHP threads, MySQL's static allocation and per connection buffers, and you'll be close to 20GB already.

Source: https://www.sonassihosting.com/help/general/identifying-current-mysql-disk-space/

  • it's surprising he's been able to load of category page with a catalog that big and such small amount of RAM. Oct 10 '14 at 7:58
  • We are using amazon server for this job, also one thing right now we have 150K around products(we will be having 200K at-least in some time) if this will work ok 200K we will be going to load 1000K products on site. Every suggestions and help are welcome. Oct 10 '14 at 10:02
  • AWS (or any cloud platform) isn't an unlimited magical resource. Its a highly contented, contained VPS - that needs more resources than a typical dedicated server due to the poor performance I/O. I gave you advice on exactly what to do and exactly how to calculate your memory requirement - short of me logging into your AWS account and increasing your VPS size ... what more do you want? 1 Million products on Magento is no mean feat, trying to DIY your server configuration is a recipe for disaster. Oct 10 '14 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.