Heavy CPU Usage Then Fail When Bulk Updating Product Categories

[desktronix]

I am running Magento 1.9.3 with 140,000 products on a dedicated server that is still in development mode.

When I try to bulk change categories on products (Catalog -> Manage Categories -> Category Products), I will get an innodb timeout error. When I increase the time to four hours, the job never finishes. When I check the processes, MySQL will work it's way up to using all of the CPU time and then time out. I am able to update the product categories on products individually, but I have about 15,000 to do, so this will not work.

I have tried the following:

• Magento Database Repair tool (did not find any errors)
• Truncated the log files (This cleared 700MB from the database and improved performance, but does not fix this issue.)
• Run mysqlcheck on the database (did not display any errors)
• Transferred the SQL database from the server to my personal computer and replicated the error demonstrating it is not a problem with my server. The server is a high end machine with lots of RAM and using an SSD for the database.
• Tested the problem on older database backups. If I go back three months, I am able to resolve the issue.

If I restore the three month old backup, I could probably get up to speed on it within a day of work, however the problem is I do not know what caused the issue or whether it will reappear. Since it is causing such high CPU usage on a server that has already been hardware and software optimized for Magento and a large database, I think there is an infinite loop somewhere.

I have a dedicated Magento dev shop looking into this, but it has entirely stopped everything I can do with the site. Can anyone suggest anything before I determine I may have found a bug?

Thanks,

Dx.

[Tom Robertshaw]

It's unlikely that you'll be able to bulk change that many categories through admin. Is the task you're trying to achieve assigning products to categories? If so, I'd recommend trying to do this via product import rather than through mass actions. 

Otherwise I think the only practical option would be to use a third party extension that provides the import process you need or to write a bespoke programmatic solution. 

[kgerasimov]

You are right it is not the server but Magento which is the problem. Many many times I found Magento default product/category upload functionality very slow. 

I am using Magmi for all data entry needs. https://sourceforge.net/projects/magmi/

It is free, fast and highly configurable. Try it.