5

I'm running CE 1.9 on a VPS. Have a problem with the category, which contains more than 350'000 SKUs. When I try to open this category in the backend, nothing happens, so I cannot edit any category data. When I try to access this cat in the frontend, products are shown but FILTER is not shown. In the same time, an error is generated as follows:

a:5:{i:0;s:2777165:"SQLSTATE[HY000]: General error: 2006 MySQL server has gone away, query was: SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e` WHERE (`e`.`entity_id` IN(1, 2, 3, 4, HERE GOES 360 000 SKU'S))";i:1;s:6077:"#0 lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)

Do I understand correctly that my VPS is just out of memory? The trouble is that the store will contain 2-3 millions of SKUs and will have categories which will contain ~500 000 SKUs.

Any suggestions on how to fix it? ... or how to calculate needed VPS RAM? ...or maybe something wrong with MySQL my.cnf file?

1

Magento shouldn't be run on anything other than bare metal, certainly never in a VPS.

In terms of sizing your RAM requirements, assuming 4 cores, you'll need at least 8GB RAM to maintain the recommended 2GB/core ratio.

With a catalogue size of 350k SKUs, I'd assume around ~15GB of total table space. You can get a definitive answer using the following query,

SELECT 
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 
WITH ROLLUP) B,(SELECT 3 pw) A 
ORDER BY TSize;

Your DB server needs around twice the total amount of table space available in RAM to properly perform. Add in the other roles/requirements of your server (web/mail/logging etc.) - I'd say you would be looking at ~32GB RAM for static load (ie. no visitors).

You would need to increase CPU/RAM proportionately with your traffic levels.

In any case. Drop the VPS, its a totally inappropriate choice for a Magento store.

Sources:

  • After executing sql query you gave, I've got 2.8GB total table size. That mean I need 6GB RAM to work properly for the moment? – Alexey.R Sep 29 '15 at 11:26
0

It seems like DB a connection timeout, or max_allow_packet issue you can check the current values by running this queries:

SHOW VARIABLES LIKE 'wait_timeout';

SHOW VARIABLES LIKE 'max_allowed_packet';

If you want to change it you can do it like this:

SET SESSION wait_timeout = 999999;//whatever value you want

You can also change this values in your my.cnf. I would recommend trying to find where that query is generated and trying to refactor it, changing the timeout is not always the best idea read more here: https://stackoverflow.com/a/18527130/1016425

If you want to adjust the configuration of your mysql you can use this very nice tool from percona (will work for mysql as well): https://tools.percona.com/

Additionally take a look here for more information: http://dev.mysql.com/doc/refman/5.5/en/gone-away.html

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.