Magento – Getting SQLSTATE[HY000]: General error

categorydatabaseerrormagento-1.9sku

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?

Best Answer

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:

Related Topic