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?