cancel
Showing results for 
Search instead for 
Did you mean: 

Catalog price rules Challenge with big SQL task due to product DB size price rules

Catalog price rules Challenge with big SQL task due to product DB size price rules

Can anyone help. 

We have a Magento 1 build and have had a series of problems with timeouts because of the size of the product database and number of catalogue price rules. 

The support team for the server have commented:

They reviewed the work flow of the store and said, when "Save and Apply" button is clicked, all active catalog price rules are applied for all products (currently there are about 40 active catalog price rules and about 24,000 products).

Most of the rules have several product attributes in query and every attribute in rule adds new query.

This results in a very big SQL task which can take around/upto 10 minutes to complete properly.

Reviewing the logs, though about 4GB is allocated to PHP as its memory limit, at times PHP struggles due to lack of actual physical memory. The processes/tasks on the server at the time, had used up all the available memory in there, which is why it kept throwing memory related errors while it was on LiteSpeed.

To verify this, we switched the web server back to LiteSpeed, freed up all the allocated memory and attempted the task again, which went through after waiting for 8/10 minutes. Basically, the amount of data that has to be processed is way too high and takes up all your resources.

 

We are now faced with the option to increase the RAM size to try to manage this process.

Does anyone know of any other options which could help?

 

We will eventually be moving to Magento 2 but at the minute this is causing a huge problem. 


Any help would be appreciated.

 

 

 

 

2 REPLIES

Re: Catalog price rules Challenge with big SQL task due to product DB size price rules

Hi,

 

I have faced the same problem with one my client's M1 store. The solution was to lower the number of price rules. 

 

I suggest you either lower the number of price rules or increase RAM. I doubt there is any room for optimization behind price rule logic. 

Re: Catalog price rules Challenge with big SQL task due to product DB size price rules

Difficultly is the client needs this number of price rules it looks like increasing the RAM is the only hope. 

 

Really appreciate the response !